When an Optimized Load is not Optimal
When working with QlikView and moving data from the original database to your finished application I often create many qvd files. They are very fast to load and are reusable across many applications. In most cases we want to achieve an optimized load for a qvd because this is the fastest way to load data into QlikView. There are always some potholes that you have to watch out for I’d like to point out a few and hopefully save you the time of troubleshooting these yourself.
If you have ever done a cross table load from a qvd then you may have seen this wonderful little bug. If your code allows for an optimized load with a Crosstable statement you will see what I like to call the orphanage, your table will be processed as you’d expect but a collection of $orphan tables with a single column for each of your Crosstable data columns will remain.
Luckily the solution is pretty simple, use a where clause to override the optimized load.
“Star is *;” is no star when an optimized load is performed.
When building security structures in QlikView it is very common to use wildcards to associate users with all the data in your document. I am working on a project where we are pulling security information from active directory and storing it to qvd files before using it in the actual data model. When we brought in the user data we saw the following results. Notice the asterisk in the department column Joe and Sally should have access to see all the departments but on an optimized load QlikView did not obey the “Star is *;” command.
When we override the optimized load in this scenario we see very different results. Joe and Sally are now associated with every department as we were expecting.
Mapping loads are never optimized and if you have a big maps this can add significant time to your data load. When dealing with big maps you may want to consider left joins to speed up the processing. There are many other reasons to use mapping tables but if you need to decrease your load times nothing beats an optimized load.
I learned about this little nugget from the QlikView Masters on the Natural Synergies Blog. When concatenating two tables the Concatenate load is not optimized. We often do this when creating fact tables so the data volume is often significant, therefore the impact on load time is substantial. The work around for this one is to use a Join, specifically a full outer join. You’ll have to create a field that will be unique to each table so don’t mess up your granularity, we like to use a field called “Fact Type” to prevent row collisions and duplication.