This is first in series of specific blogs that I will be writing explaining the fundamentals of Data Modelling in Qlik.
Data Model is the heart of your Qlik dashboard and can affect each of the following:
-
- Expression Complexity
- Performance
- Scalability
- Maintainability
- Reload Times
Link Table design and Star Schema are the most favoured data models and we shall be covering both of these in these blogs.
I plan to take the reader through a series of practical exercises that shall demonstrate the various concepts. The initial exercises shall be trivial since the blog is being written for the absolute newbie but as we proceed, I shall be bringing in more and more complexities.
In each of these exercises it is my recommendation that before looking at the solution provided by me, try to solve the same on your own.
Incidentally I am assuming that all readers are familiar with ETL scripting on Qlik. This blog does not cover any concepts related to ETL scripting.
Exercise 1
The source system has the following three tables. Create a Data Model in QlikView for the same.
1.Customers
2. Parts
3. Sales
Try out the above exercise and compare the results below. Please note to avoid complexities related to Date fields I have kept the data in Dates trivial (D1,D2 etc.,) since manipulation of date fields is not the objective of this blog.
Solution to Exercise 1:
In all probability you should have arrived at the following Data Model.
Exercise 2:
Now let’s complicate our Data Model requirements a little bit. To the above three tables, let us add one more table as shown below. Now modify your data model to incorporate the same.
4. Inventory
Try adding this table to your data model and compare the results.
Solution to Exercise 2:
If you have followed the same approach as previous exercise, the Data Model that you arrive at shall be something like
Here you will notice that System has introduced a Synthetic Key Table. The reason being the Join between Sales and Inventory is a composite key (Part No and Date).
Synthetic Keys are much maligned in our industry, but strictly speaking Synthetic Keys do not cause any harm. Remember, Synthetic Keys are just system generated Link Tables. The only harm perhaps that synthetic keys cause is that they destroy the readability of your Data Model and a maintenance programmer who has to work on Data Model full of Synthetic Keys may end up cursing to no end the programmer who ever designed a Data Model as bad as this!
Read Part 2 :Here
PS: With this I will end my first blog on this subject. In the next blog, with the same data model as our background I shall start an introduction to Link Table Design. Those of you who would like to experiment, try eliminating the Synthetic Key by introducing a Link Table in the above.