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
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
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
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
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
7. Inline tables
To create smaller reference tables,create Inline tables.
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
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.
I will discuss more data modeling concepts in my blog Data Modeling in QlikView Part 2.