Tuesday, December 4, 2012

Link tables

Basic use of Link tables is to solve data modeling issues in qlikview- Synthetic keys and loops.
Main idea behind using linktable is to establish star schema.With Link table at center connecting all other tables.

This post explains basic 3 steps of generating linktables using a example data.
First two steps are common for all data types and 3rd steps requires either joining or concatenation depending upon nature of tables linked.

Consider we have 4 tables in our data model. Sales, Purchases, Product and Country.
Sales table will connect with Product and Country tables with ProductId and CountryCode respectively. and same is the case with Purchases table.

Below is sample data to illustrate: 
---------------------------------------------------------------------------------------------------------------------------
                       Sales table:

                        Purchases table:

                        Product table:
                       Country Table:

                                           
---------------------------------------------------------------------------------------------------------------------------
 When we load above data to qlikview report it's going to form synthetic key table with ProductID and CountryCode fields as below.(If we solve synthetic key problem by using autonumberhash it will give us loop in data model).


Code:
*****************************************************


Sales:
LOAD [SalesTrans ID],
    
CountryCode,
    
ProductId,
    
SumOfSales,
    
Date
FROM
..\Linktable.xlsx
(
ooxml, embedded labels, table is Sheet1);

Purchases:
LOAD [PurcTrans ID],
    
CountryCode,
    
ProductId,
    
Date
FROM
..\Linktable.xlsx
(
ooxml, embedded labels, table is Sheet2);

Product:
LOAD ProductId,
    
ProductName
FROM
..\Linktable.xlsx
(
ooxml, embedded labels, table is Sheet3);

Country:
LOAD CountryCode,
    
CountryName
FROM
..\Linktable.xlsx
(
ooxml, embedded labels, table is Sheet4); 

*****************************************************

Steps to generate link table:

Step1: Make sure all tables which has to be connected with link table will have  primary key.This key is used to connect with linktable.
( In our example all tables have primary keys , if primary key is not present generate them using RowNo() or RecNo() etc. )

Step2: Rename all foreign keys in relevant tables( which are causing synthetic keys). By this associations with dimension tables will be removed. Rename Productkey to O_ProductKey and CountryCode to O_CountryCode. This fields can be drooped at end of the code as they will be loaded into link table in below step. More over if present they form synthetic key.

Step3: Generate Link table: depending on nature of data, we need to do joins and/or concatenations to generate link tables. here as sales   and purchase tables will not have any relationship among them we can concatenate data of these 2 tables to generate Linktable. Load Primarykey and foreign key of Sales table and concatenate it with loading  Primarykey and foreign key of Purchase table.

Code:

 *****************************************************


Sales:
LOAD [SalesTrans ID],
    
CountryCode as o_CountryCode,
    
ProductId as o_Productid,
    
SumOfSales,
    
Date as SalesDate
FROM
..\Linktable.xlsx
(
ooxml, embedded labels, table is Sheet1);

Purchases:
LOAD [PurcTrans ID],
    
CountryCode as o_CountryCode,
    
ProductId as o_Productid,
    
Date as PurchaseDate
FROM
..\Linktable.xlsx
(
ooxml, embedded labels, table is Sheet2);

Product:
LOAD ProductId,
    
ProductName
FROM
..\Linktable.xlsx
(
ooxml, embedded labels, table is Sheet3);

Country:
LOAD CountryCode,
    
CountryName
FROM
..\Linktable.xlsx
(
ooxml, embedded labels, table is Sheet4);

/************************/
Linktable:
load
[PurcTrans ID],
o_CountryCode as CountryCode

Resident Purchases;

Concatenate(Linktable)
LOAD [SalesTrans ID],
      
o_CountryCode as CountryCode,
     
o_Productid as ProductId
Resident Sales;

/************************/

Drop fields o_CountryCode,o_Productid


  Resultant Data model :

  Hope it helps you. :))