Recently we spotted some scripts that were trying to incorporate history in the dimensions by creating Slow Changing Dimensions (SCD). It appeared that the QlikView autonumber function had been used to create surrogate keys, which was also used to create the surrogate keys for the fact table. This kind of behavior could lead to some dangerous situations because in this particular case the autonumber function was used in a different script, which ran at a different time in the QlikView Server, and didn't result in the same results when calculating the surrogate key with the autonumber function. The second script generated different surrogate keys for the same concatenated key and as a result we don’t have to mention (still we will do anyway) what happened when reloading the QlikView analytical applications. We noticed that a sales application was providing different results than expected; sales per region & sales per customer were showing different results as one would expect.

The lesson to be learned from this situation is that when trying to maintain the different state of a dimension record in the QlikView application one should take a different approach. In described scenario it would have been sufficient to join the surrogate key to the fact table (in the second script) based on the concatenated natural key (there were no slowly changing dimensions yet).

As a rule of thumb one could say that the following steps should lead to a situation where history in the dimension is preserved without losing the historical sense of the dimension:

• Create the dimension table;
• Compare the new dimension table with the old dimension table for any differences;
• Create the new dimension table (with possibly multiple occurrences on the natural key & only one occurrence on the surrogate key);
• Create the fact table and join the surrogate key based on the natural key to the fact table (for the most current surrogate key based on the indicator from the dimension table);

Please keep in mind that this is just a guideline on how to record history in the dimension table and how to get this linked to the fact table. As a result of different requirements one could take a slightly different approach. We just hope that you don’t fall in the same trap that we have seen with the sales application like we described before.

Kind regards,

The QlikConnect team.