In creating a data model, you come across challenges in terms of different formats of data, loading multiple fact tables, and performance issues. There are techniques to resolve these challenges.
In this article by Chandraish Sinha, the author of QlikView Essentials, we shall:
- Learn about loading the cross table
- Learn about the Autonumber function
- Learn about creating a link table to load multiple fact tables
(For more resources related to this topic, see here.)
Cross table essentials
In the folder of downloaded code files, look at EmployeeSalesTarget.xls. This file is located at C:\QlikViewEssentials\Data.
File of this format is called a Crosstable. Cross table is a special format of data in which some of the fields are displayed in rows and some in columns. If this table in loaded in QlikView using a regular load statement, it will load each of the fields separately.
The problem with this kind of structure is that the table can grow very huge, if each of the field is stored separately. Performing aggregation on such a table will be difficult. You have do to Sum(2005) + Sum(2006) … to get the total sales. It will better for QlikView application to load a table in the following format.
In this format, aggregation of data will be much more easy. Use Crosstable prefix to load a cross table in QlikView.
We will load EmployeeSalesTarget.xlsx using CrossTable because it is in cross table format.
- Open QlikView QlikViewEssentials_Chap3.qvw and save it as QlikViewEssentials_Chap4_Crosstable.qvw.
- Go to the script editor and create a new tab and call it Crosstable.
- Click on table file and browse to EmployeeSalesTarget.xlsx. Check all the default settings and click Next.
- Click Next.
- Under File Wizard: Options, click on Crosstable under Prefixes:
- Select the Qualifier Fields, Attribute Field, and Data Field. Qualifier fields are columns on the left. You can specify any number of Qualifiers. The attribute and data fields can have any names. Number of qualifier fields are the fields which do not get transformed by Crosstable syntax. The attribute field is the field that gets transformed. In this case, it is Year. The attribute field will transform Year under one column. The data field will contain the data of the attribute field.
- Give the name of the table as SalesTarget. The load script will appear as follows:
- Save and reload. Notice the formation of synthetic table key and synthetic key due to the multiple common fields between Orders and SalesTarget tables:
The above data model scenario is a typical scenario which occurs due to the presence of multiple fact tables. In this case Orders and SalesTarget are two fact table and they share the common dimensions. The star schema prefers one single fact table in the schema. This scenario can be resolved with the help of a link table.
The link table is a central table that will contain common fields from the two tables and therefore creates one table and avoids synthetic keys.
Link table essentials
The following steps are followed in the creation of any link table:
- Create a composite key in all the concerned fact tables. Use the Autonumber function to make this key unique and numeric. This composite key will act as a key field to connect the link table with fact tables.
- Load all the common fields from all the fact tables in one table called the link table. Use concatenate for this purpose.
- Drop these common fields in the original Fact table.
Creating a link table
In our data model we have two fact tables Orders and SalesTarget. To resolve this, we will create a link table by following these steps:
- Use QlikViewEssentials_Chap4_Crosstable.qvw and save it as QlikViewEssentials_Chap4_LinkTable.qvw.
- Invoke script editor and create a new tab link table after the last tab.
- Identify the common fields in Orders and SalesTarget tables. These common fields are Year, CustomerID, EmployeeID, and ProductID.
- Create a new table EmpSalesTarget. This table will be created from the SalesTarget table. The SalesTarget table was created when we loaded the cross table in the previous section. We will load all the fields from the SalesTarget table using resident load. The SalesTarget table contains the common fields. We will create a composite key using the common fields and name it KeyField.
- Use the Autonumber function to create a unique integer values for %Keyfield.
The Autonumber function is a very important function in QlikView. It is used to convert string into unique numbers. In the absence of the Autonumber function, the above composite field will be a string field and will not be better suited for joining the two tables. It will also take more memory as compared to a numeric field.
- Now, drop the SalesTarget table as this table is no longer needed. Contents of this table is loaded in the EmpSalesTarget table. Your script will look like the one below:
- Now, create another table Facts. This table is created to have a clean table which contains the fields from Orders, OrderDetails, and OrderAggregate tables. Resident load will be used to achieve this because Orders table is already present in the memory. Create a composite key in the Orders table similar to EmpSalesTarget table. Drop the table Orders as it is no longer needed.
- Create a new table and name it LinkTable which will hold common fields from both the above tables. Make sure to load only distinct fields in order to avoid duplicates.
- Perform resident load from the Facts table and store the distinct combinations in LinkTable. Create a duplicate %KeyField and name it %TempKeyField. This field will be used while loading data from EmpSalesTarget:
- Concatenate distinct common fields from the EmpSalesTarget table. Use %TempKeyField to make sure that %KeyField from EmpSalesTargetTable does not exist in the fields from Facts. After loading this statement, drop %TempKeyField as it was required only for comparison.
- Once LinkTable is created and loaded, drop the fields which are no longer required. Drop the common fields from Facts and EmpSalesTarget as these fields are now stored in LinkTable.
Your overall LinkTable script will appear like this:
The link table should be used with caution. As the size of the link table grows, performance degrades.
Concatenating two fact tables is also an efficient way to merge to fact tables.
The link table and concatenate are both the ways to merge the fact tables. Use concatenate when granularity of the tables and dimensions they connect to are same. Use link tables when granularity of the tables and connected dimensions are different.
After creating LinkTable, the resulting data model will appear like the one below:
In this article, we primarily learned how we can load multiple fact tables and aquainted ourselves with the use of the Autonumber function.
Resources for Article:
- Marketing Perspective [article]
- Are You Sitting Comfortably? Be More Productive [article]
- Getting Started with Tableau Public [article]