Case Study: Multiple facts together with slowly changing dimension

Background: 

In data warehouse, it is a typical topic regarding how to handle multiple fact tables who share several conformed dimensions. 

In Qlikview, the most common solutions including concatenating multiple fact tables into one fact table if they are quite similar. 

Another solution is to create a link table to join the fact tables and the conformed dimensions.

In this case study, we will see the modelling changes to link the two fact tables Sales and Plan together with three conformed dimensions Department,Employee and Product.

Please note that the employee dimension has the support for slowly changing dimension.

Let see the example below:

After loading all the tables into Qlikview, the diagram in Table Viewer shows :

There are SSyn Keys between the tables

Data Modelling:

//1.LOAD FACT TABLES 

Plan:

LOAD EmployeeID&Name&DepartmentID&Department&CountryID&Country&ProductID&Product&Year As ID, 

     PlanID, 

     EmployeeID, 

     Name, 

     DepartmentID, 

     Department, 

     CountryID, 

     Country, 

     ProductID, 

     Product, 

     Year, 

     Amount AS Plan_Amount

FROM

[C:\Users\Peidu\Desktop\Project\2017_01_07\Plan.txt]

(txt, codepage is 936, embedded labels, delimiter is ',', msq);

Sales:

LOAD EmployeeID&Name&DepartmentID&Department&CountryID&Country&ProductID&Product&Year As ID,

     SalesID, 

     EmployeeID, 

     Name, 

     DepartmentID, 

     Department, 

     CountryID, 

     Country, 

     ProductID, 

     Product, 

     Year, 

     Amount AS Sales_Amount

FROM

[C:\Users\Peidu\Desktop\Project\2017_01_07\Sales.txt]

(txt, codepage is 936, embedded labels, delimiter is ',', msq);

//2.CREATE LINKTABLE

LinkTable:

LOAD ID,EmployeeID,Name,DepartmentID,Department,CountryID,Country,ProductID,Product,Year RESIDENT Plan;

CONCATENATE(LinkTable)

LOAD ID,EmployeeID,Name,DepartmentID,Department,CountryID,Country,ProductID,Product,Year RESIDENT Sales;

DROP FIELDS EmployeeID,Name,DepartmentID,Department,CountryID,Country,ProductID,Product,Year FROM Plan,Sales;

//3.LOAD DIMENSION TABLE EMPLOYEE 

Employee:

LOAD EmployeeID&From&To AS KEY,

     EmployeeID, 

     //Name, 

     From, 

     To,

     City

FROM

[C:\Users\Peidu\Desktop\Project\2017_01_07\Employee.txt]

(txt, codepage is 936, embedded labels, delimiter is ',', msq);

After loading Employee, we see the diagram below:

    

The results is also wrong:

We need to make slowly change dimension first, then remove synthetic key:

//4.SLOWLY CHANGE DIMENSION WITH LINKTABLE 

Bridge:

INTERVALMATCH (Year,EmployeeID)

LOAD From,To,EmployeeID RESIDENT Employee;

LEFT JOIN(LinkTable)

LOAD Year,From,To,EmployeeID,EmployeeID&From&To AS KEY RESIDENT Bridge;

DROP TABLE Bridge;

//5.GENERATE COMPOSITE KEY BETWEEN EMPLOYEE AND LINKTABLE SINCE THERE IS Synthetic Key IN TABLE VIEWER

DROP FIELDS EmployeeID,From,To FROM LinkTable;

The diagram shows below:

The result shows below, notice Year column matches From & To columns now:

Loading other dimension tables and disselect repeated columns in LinkTable:

//6.LOAD DIMENSION TABLES

Product:

LOAD ProductID, 

     //Product, 

     Producer

FROM

[C:\Users\Peidu\Desktop\Project\2017_01_07\Product.txt]

(txt, codepage is 936, embedded labels, delimiter is ',', msq);

Department:

LOAD DepartmentID, 

     //Department, 

     Discription

FROM

[C:\Users\Peidu\Desktop\Project\2017_01_07\Department.txt]

(txt, codepage is 936, embedded labels, delimiter is ',', msq);

Country:

LOAD CountryID, 

     //Country, 

     Region

//     City

FROM

[C:\Users\Peidu\Desktop\Project\2017_01_07\Country.txt]

(txt, codepage is 936, embedded labels, delimiter is ',', msq);

The Final Diagram is:

You can see the data modelling is Star Schema.

And we also get report with correct data: