The Magic of link Table
In Data warehousing world, many a times you come across a situation where you have to handle multiple Fact tables.
Star schema usually have a single Fact in the center and dimensions surrounding it but some times you have a situation where you have more than one Fact table.
If you want to learn more about Facts and Star schema, please visit my earlier blog http://www.learnallbi.com/dimension-and-fact/.
In this blog i will discuss about how to handle multiple fact tables in QlikView data modeling.
Link table vs Concatenate
When you have multiple Fact tables in QlikView, it can be handled in 2 ways, by using concatenate or by using Link tables. If the granularity and columns in the fact tables are same then you can use Concatenate which will merge the tables into one and resulting table will have the sum of rows of the two tables.
Use Link table, when the granularity of the facts tables are different and when they are joined to different dimensions.
Concatenate and Link Tables are ways to resolve synthetic keys which formulate due to the presence of multiple fact tables.
Link Table example
To explain the concept of Link table, I will load few fact tables and dimension tables.
if you load these tables, you will get synthetic fields in your data model. These synthetic keys are formed since we have multiple Fact Tables and these fact tables are sharing the same dimensions.Since these fact tables are joining to the same dim tables they have duplicate/common fields.
Resolving Synthetic Keys using Link table
To resolve these synthetic keys we will :
– Create a composite key. Link table can connect to the original fact tables using this composite Key
– Load all the common fields in one table called Link table
– Drop these fields from the original tables.
Let’s see these steps in screen shots below:
After following the above steps and loading the data , you will get the following data model. As you will notice, Link table is in the center. Link table contains all the common fields and Link table is linked to other fact tables using the Key field.
Link Table is a very useful way to resolve synthetic keys when multiple fact tables are present in the data model.
You should always load Distinct records while creating Link table. Also if the Key field/composite field contains several individual fields then it is good to create Key field using Auto number ( ) function like the one below
AutoNumber(YEAR_MONTH&’|’&CUSTOMER_KEY&’|’&HIERARCHY_KEY&’|’&LOGO_KEY) As [%Key field]