Data Modeling in Qlikview
As you probably may have experienced, QlikView performs better when the data source is a perfect
star schema. However, many
times, a star schema is not feasible or preferred given the requirements of the BI
application, the amount of time budgeted for development, or the resources of the team tasked with building an app. There are often challenges
in dealing with dimensions that have multiple granularity levels and large
data volumes or multiple fact tables that have different dimensions and/or multiple
relationship levels.
Below are a few tips for getting the most out of your QlikView data model.
1. Having a star schema modeled in the source data
does not mean you will automatically get a star schema in QlikView.
When the source data are from tables that
have more than one common column, QlikView will end up adding synthetic keys
to tables and create a more complicated data model when it translates relational data into associative
data. Common columns here are meant to
be those columns having identical column names.
The scenario below looks at a
simplified case where 1 fact table and 4 dimension tables from the source are in
a relational database. The relationships are mapped out nicely in
diagram 1.
Diagram 1. Sales Database Diagram
However, importing the data as is
into Qlikview yields the data model shown in Diagram 2. As you can see, not only it is not a star
schema any more, but also there are multiple synthetic keys that are going to
negatively impact the QlikView application front end features and performance.
Diagram 2. Qlikview data model (after a star schema data
source is loaded as is)
The problems is that there are tables that use two
key columns to define their relationships.
For example, both SalesPersonID and TerritoryID columns are used to
define the relationship between the Sales fact table and the SalesPerson dimension
table. Similarly, both CustomerID and
TerritoryID are used to define the relationship between the Sales fact table and
the Customer dimension table. Qlikview created three synthetic keys to
resolve the same relationships.
The solution in this case can be concatenating the common
keys to produce an unique key:
1)
Concatenate SalesPersonID and TerritoryID to resolve the relationship between
Sales fact and SalesPerson dimension.
2)
Concatenate CustomerID and TerritoryID to resolve the relationship between
Sales fact and Customer dimension.
As a result, QlikView data model is now a simple star
schema as shown in diagram 3.
Diagram 3. QlikView
data model after keys are redefined
2. Having a star schema modeled for source data
does not mean that it is the best way to model in Qlikview
When source data from a relational
database is modeled as a start schema, it is not necessarily that
a star schema is the
right data model for QlikView applications.
The simple star schema in the
scenario above is rarely implemented in real QlikView applications because most of
the BI applications have to deal with more than one fact table in
the same application, horizontal hierarchy, or more than one level
of relationships for associated dimensions.
QlikView can work with data
sources from different databases. When
the source data are brought in from different databases each modeled with a
star schema, star schema is not necessarily the data model feasible or best suited
for the QlikView application. When there
are more than one fact table each with multiple dimensions, the best option is
to consolidate all facts into one table if possible, which often resulted in snow
flake instead of star schema. If it is not possible to consolidate all facts
needed into one fact table, a link table may have to be used, which will often
resulted in a modified star schema or snow flake data model.
3. Choosing the best data model for Qlikview applications
A simple star schema will usually
result in the best performance for Qlikview applications. However, when denormalizing dimensions in
order to achieve star schema resulted in millions of repeated data in the
dimension table(s), the impact of that on the performance may overweight the
benefits of a star schema. Then, it is
time to consider snow flake instead. If
multiple fact tables can be consolidated into one fact table, even if it resulted
in a snow flake, usually it is better than a link table that is a modified star
schema unless the link table is very simple, in which case, a link table is
probably not necessary to start with.
Compared to the traditional relational databases, Qlikview data
modeling can be implemented quickly and modified fairly easily. If you are interested in data modeling in
Qlikview or in designing a Qlikview application, Qlik Tech offers free personal
edition Qlikview that can be downloaded
free.
There are also resources available at Qlikview Community and free training
from Qlik Tech for beginners.
Labels: BI, Data Model, Qlikview
