Technoforte Blog

Know more on WMS, Business Intelligence and Analytics

Data Modelling on Qlik

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

image-1

2. Parts                                                                Parts Data Model

3. Sales          c3
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.

capturemage-2

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       c4

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

image-change

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.

Yogesh Pathak

Co-Founder and Head - BI, Big Data & Analytical Services at Technoforte Software Pvt. Ltd.

7 thoughts on “Data Modelling on Qlik

  1. Hi Yogesh

    Very clear and crisp article. Thank you. For an absolutely absolute newbie like me, do you have anything on ETL scripting on Qlikview which one could go through in parallel or as a precursor.

    Regards
    Sam

Leave a Reply to Chandu Cancel reply

Your email address will not be published. Required fields are marked *

Top