Tuesday, 17 May 2011

Rules for creating a Key/Link Table in QlikView

A key/link table is frequently required in QlikView to resolve Synthetic Key or Circular Join issues.  It can also help to tidy  up a schema that has tables all over the place.  The goal is to create a star (and sometimes snowflake) schema with dimension tables connected to a central key table.  Unlike in classical 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 you need 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 a function like autonumber or autonumberhash256.

2.  Break all the existing joins by renaming foreign keys (e.g. in the Orders table, rename CustomerID to O_CustomerID).  For a "pure" star schema, there should be no QlikView Associations remaining and all the tables should be standalone.  From a pragmatic point of view, it is fine to leave some hierarchical 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 will load the previously renamed foreign key with the correct name so that they connect to the right dimension 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 OrderDetail I 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 key
   ProductID 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 already renamed 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;

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

   Join (Key) Load
      OD_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 Concatenating additional tables.  For example, I could concatenate data from Purchases that also has Date and ProductID information.

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


Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner

13 comments:

  1. Hello Stephen Redmond,

    I will be grateful & thankful to you if you share complete code of this example.

    Thanks
    Manish

    ReplyDelete
  2. Hi Manish,

    Probably more useful for you to work it out on your own data which is going to be different than my data.

    There is enough code here to get you going.


    Regards,



    Stephen

    ReplyDelete
  3. Hi Stephen

    As a QlikView newbie and someone more familiar with a relational database schema this is very useful - Thank you

    Paul

    ReplyDelete
  4. Thanks for putting it in chronological steps

    ReplyDelete
  5. How to Create The Adhoc Reports. any Can send me the details Information.
    surk147@gmail.com

    ReplyDelete
  6. it was very helpfull thankyou.

    ReplyDelete
  7. This post is really helpful sir. But I still have a confusion i.e. why we need to create the Link Table ? Can't it be solved by using Concatenating the tables? More specifically my question is- tell me such situation where Link Table is the only solution. Thanks in advance.
    Regards,
    Joy

    ReplyDelete
  8. This comment has been removed by a blog administrator.

    ReplyDelete
  9. This comment has been removed by a blog administrator.

    ReplyDelete
  10. I use the link table frequently. However, I am on a project now where the other consultant did not like the use of a unique key in the link table. Instead he created a Many to Many link table. The key to link the facts to the link table is a combination of the multiply date fieds and date types

    Date1 & '-' & 'StartDate' & '-' & 'Date2 ' & '-' & 'EndDate' as DateLinkKey,

    THis creates a many to many relationship which i usually try to avoid. Do you see any issues with this in a model that could grow quite large.

    ReplyDelete
  11. This comment has been removed by a blog administrator.

    ReplyDelete
  12. Hi Sir,
    Can we create link table between 3 tables from two different sources?
    I tired to create and data is populating correctly with relation between 3 tables in data model but if pull it into my application through binary load the 3rd table columns itself not related to each other also to other tables.

    Request your help in resolving the issue.

    ReplyDelete
  13. Hi Sir,
    Can we create link table between 3 tables from two different sources?
    I tired to create and data is populating correctly with relation between 3 tables in data model but if pull it into my application through binary load the 3rd table columns itself not related to each other also to other tables.

    Request your help in resolving the issue.

    ReplyDelete