1. Create a primary key in all the tables,
2. Rename the foreign keys to break the links,
3. Use a mixture of Concatenate and Join to generate the Key table using the Resident data.
These rules will still create you a Key/Link table that will work, but it might not always be the most efficient. As I have had to deal with larger and larger data sets in QlikView, I have learned the hard way that things that work quite well on a table with a few hundred thousand records do not work quite as well when your tables with a few hundred million.
It is really the first "rule" here that can be the problem. In a dimension table, the key that associates to the Key/Link table does need to be the primary key, however, the key that associates fact tables to the Key/Link table does not have to be a primary key! This key only needs to be unique for the set of keys being linked to in the Key/Link table, not for the whole record.
For example, if my fact table contains CustomerID and DateID, most of the time that combination will not make a primary key in the fact table, but it is all the key that we need to associate to a Key/Link table containing CustomerID and DateID. By not using a primary key in the fact table, we are vastly reducing the cardinality of the key being used and hence the amount of memory required to store it.
Another change that I would make is that I no longer use AutoNumberHashxxx to generate a key value. Now, I always use AutoNumber with an AutoID. This is because AutoNumber with an AutoID will generate a set of sequential integers which do not, in general, get stored in symbol tables, further reducing the memory requirement. Using AutoNumberHashxxx to generate multiple different keys will result in non-sequential integer key values which will have to be stored in Symbol tables.
Yet another change that I would require is that the Key/Link table is generated using the Distinct keyword. Nothing kills performance quite like having duplicate key values in a Key/Link table.
Finally, I no longer require to rename the foreign keys and then rename them back when creating the Key/Link table - I just leave them as is and then use the Drop Field xxx From yyy statement to remove the old foreign keys.
So, the new rules become:
1. Create a key in each table at the correct granularity to associate to the Key/Link table. Use AutoNumber with an AutoID.
2. Use a combination of Concatenate, Join and Distinct to load a Key/Link table of distinct values.
3. Use Drop Field xxx From yyy to remove the old foreign keys from the fact tables.
For example:
I have a simple structure with Customer, Calendar, Orders. I have a separate fact table with Customer, Calendar and Delivery information. I get a synthetic key that I want to resolve by using a Key/Link table:
Step 1 - Customer and Calendar (DateID) already have a primary key. In Order I will create an ID from CustomerID and DateID. I will do the same in Delivery:
...
AutoNumber(CustomerID & '-' & DateID, 'CD') As LinkID,
...
Step 2 - Now I load my key table. I will begin with data from the Order table:
Key:
Load Distinct
LinkID,
CustomerID,
DateID
Resident
Order;
Now Join in the Product and OrderDetail keys from the OrderDetail table.
Concatenate(Key)
Load Distinct
LinkID,
CustomerID,
DateID
Resident
Delivery;
Step 3 - Drop the old foreign keys:
Drop Fields CustomerID, DateID From Order;
Drop Fields CustomerID, DateID From Delivery;
Now all should be good:
Of course, we don't just create link tables to avoid synthetic keys, we usually create them to overcome association difficulties such as loops.
There is a special prize of a free copy of my forthcoming book, Mastering QlikView, for anyone who can give me a good, technical (i.e. not "because Qlik say so"), reason as to why the original, synthetic key containing, data structure is less desirable than the link table structure.
Stephen Redmond is author of QlikView Server and Publisher and the QlikView for Developer's Cookbook
He is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond
