8/10/2019 Rules for Creating Link Tables

1/2

Rules for creating a Key/Link Table in QlikViewA key/link table is frequently required in QlikView to resolve Synthetic Key or Circular Join issues. Itcan also help to tidy up a schema that has tables all over the place. The goal is to create a star (andsometimes snowflake) schema with dimension tables connected to a central key table. Unlike inclassical data warehousing, the central table doesn't often have the measures - they generally stay in

the dimension tables.

There are 3 rules for creating a key table. The first 2 are very straightforward - the last is where youneed to use your skill and judgement to create the right result. So, here are the rules:

1. All tables should have a primary key. If a table doesn't have one unique key, derive one using afunction like autonumber or autonumberhash256.

2. Break all the existing joins by renaming foreign keys (e.g. in the Orders table, rename CustomerIDto O_CustomerID). For a "pure" star schema, there should be no QlikView Associations remaining andall the tables should be standalone. From a pragmatic point of view, it is fine to leave somehierarchical tables associated (e.g. Product to ProductCategory) to have a more "snowflake" schema.

3. Use a mixture of Concatenate and Join to generate the Key table using the Resident data. You willload the previously renamed foreign key with the correct name so that they connect to the rightdimension table (e.g. ... O_CustomerID As CustomerID).

For an example, if I have a simple structure with Customer, Calendar, Order, OrderDetail and Product.

Step 1 - Customer, Product, Calendar (DateKey), and Order already have a primary key. In OrderDetailI will create an ID from OrderID and LineNo (we will do a bit of step 2 while we are at it):

...AutoNumberHash256(OrderID, LineNo) As OrderDetailID,OrderID as OD_OrderID, // rename order foreign keyProductID as OD_ProductID, // rename product foreign key

...

Step 2 - Customer and Product are not an issue because they don't have a foreign key. I alreadyrenamed my foreign keys in OrderDetail so I need to attend to Order:

...OrderID,CustomerID as O_CustomerID,DateKey as O_DateKey,...

Now all my links will be broken.

Step 3 - Now I load my key table. I will begin with data from the Order table:

Key:Load

OrderID,O_CustomerID As CustomerID,O_DateKey as DateKey,

...Resident Order;

8/10/2019 Rules for Creating Link Tables

2/2

Now Join in the Product and OrderDetail keys from the OrderDetail table.

Join (Key) LoadOD_OrderID As OrderID,OD_ProductID As ProductID,OrderDetailID,

...Resident OrderDetail;

Now I have a key table which will connect all my detail. I can extend this by Joining or Concatenatingadditional tables. For example, I could concatenate data from Purchases that also has Date andProductID information.

Any of the previously renamed foreign keys (e.g. O_CustomerID) can actually be dropped now - theirinformation is encapsulated in the key table so that keeping them is just duplicating data. I mightchoose to hang onto them for a while, just to test my relations, but best practice will be to removethem.