Data modeling in QlikView Part 1

Data Modeling in QlikView

is a very broad concept. QlikView is a wonderful tool and it gives you whole gamut of functionalities to perfect your data model.

With this blog my intention is to give QlikView beginners an insight into QlikView data modeling. I have received lot of emails from BI developers who say that they are interested in QlikView but don’t know from where to start. Some say that lot of information is available which they can search but atleast they should know what they searching on.

With this blog my objective is that without going into too much details, give these developers an idea into QlikView data model,some important keywords,functionalities and to drive them in exploring the awesome world of QlikView.

1. Load statement in QlikView

In developing your application, first thing you have to do is to load data. QlikView can extract data from disparate data sources such as Text files,XLS,CSV,XML,QVDs,SAP,relational db,cloud and custom datasources.

To load data from Text files,CSV files and QVDs go to script editor(Cntl +E) and click on Table files under Data from files.

You can load data from databases by clicking on connect and Select.

You can also copy your SQL query directly into QlikView script editor.

There are many different types of Load statements in QlikView. Search on Load statements in QlikView

Loading excel file in QlikView

Preceding Load in QlikView

Tip – If you are using your laptop for development and using 32-bit version of QlikView desktop,make sure that your data is not more than 2gb. For larger data use QVDs or develop on a machine with more RAM and 64 bit version of QlikView.

2. QVDs in QlikView

QlikView data files compress data from the database. It is 10-100 times faster to read data from QVDs as compared to database. QVDs are best for large datasets and to reduce load on the database. You can extract data from db and store it in a QVD

QVDs in QlikView

3. Association

In QlikView two or more tables are associated on common field names between the tables. Two tables can be associated on a single column. If they are associated on more than one field then Synthetic keys will be created. Look for how to associate  tables when there are no common field/names and also how to break association between the tables

4. Synthetic keys

When you load multiple tables, then 2 or more tables can get associated based on multiple field names.This will generate synthetic keys/sythetic tables. QlikView recommends that synthetic keys should be removed as they are resource intensive. Look for ways to resolve synthetic keys

Resolving Sythetic keys in QlikView

5. Loops

When keys between three tables in a data model form a circle,circular references are created.Avoid circular reference or loop by using loosely coupled tables or  by aliasing one of the columns

Circular References

6. Reduce number of tables

 – Always try to reduce the number of tables and joins in your data model. Use Mapping tables and ApplyMap function to clean up your data model as necessary

Mapping Tables in QlikView

7. Inline tables

To create smaller reference tables,create Inline tables.

8. Joins

In QlikView result of a join between two tables is a single table. Joins works in a similar fashion as SQL joins with exception being KEEP. Left Keep and Right Keep can be very useful

Explaining joins

Using joins in QlikView

9. Organize your Script

– Organize your scripts by creating Tabs for related tables or logic. Use comments to make your scripts logic easier to understand. All the tables should be named.Remember QlikView script execution is from left to right and top to bottom. Any referenced tables should be created before being referenced.

 10. Master calendar

No dashboard is ever complete without Time dimension. Create Master calendar if you don’t have continous dates in your fact table. Peek function is very useful while creating a calendar.

Master Calendar in QlikView

I will discuss more data modeling concepts in my blog  Data Modeling in QlikView Part 2.

3 thoughts on “Data modeling in QlikView Part 1

  1. Venkataramanan Devendran says:

    Brief and awesome explanation on the QlikView Data Modeling. It will surely help developers to gain a better insight on QV modeling best practices.

    Thanks for the good work!!!

  2. TAHIR ABBAS says:

    Very useful for those who are novice in Qlik Development.

    Tahir
    Systems Engineer
    Qlik Developer

  3. Arun Patil says:

    very good explanation for Data Modeling.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.