Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Relational vs. Dimensional Data Model

Hello QlikCommunity,

I am currently researching best practices and techniques for data modeling within QlikView. As a new user, I want to be sure I understand these best practices before diving into my first project. However, my research almost seems to be creating more questions than it is answering.

When working with a relational/transactional database (with 50+ tables), how do you decide whether to connect QlikView directly to these tables, or create a dimensional model (star schema) first?

Some sources say that QlikView is made for working with a star schema, and so the model better be in this format (few exceptions aside). Other sources say that QlikView works with just about any model if its built right... But I have yet to find any explanations of how to get QlikView to work with such a large transactional database. It seems that all examples I find are working with star schema's, or models that are derived from a star schema.

Does anyone have any advice for me? Or maybe a link to an example I missed in my search?

Thank you for your help and expertise!

-Chris

1 Solution

Accepted Solutions
hic
Former Employee
Former Employee

You can work with any model, as long as you don't have circular references. In a normal transactional model, you can have a table that is used in several roles, creating a circular reference, so this you must load several times - once for each role. See more in

http://community.qlik.com/blogs/qlikviewdesignblog/2012/08/30/master-table-with-multiple-roles

When you have done this, you have something that usually looks like a snowflake model - which works fine. However, fewer tables may make your QlikView app work faster, and this is the reason why many people prefer a star scheme. See more in http://community.qlik.com/blogs/qlikviewdesignblog/2012/09/12/to-join-or-not-to-join

HIC

View solution in original post

4 Replies
hic
Former Employee
Former Employee

You can work with any model, as long as you don't have circular references. In a normal transactional model, you can have a table that is used in several roles, creating a circular reference, so this you must load several times - once for each role. See more in

http://community.qlik.com/blogs/qlikviewdesignblog/2012/08/30/master-table-with-multiple-roles

When you have done this, you have something that usually looks like a snowflake model - which works fine. However, fewer tables may make your QlikView app work faster, and this is the reason why many people prefer a star scheme. See more in http://community.qlik.com/blogs/qlikviewdesignblog/2012/09/12/to-join-or-not-to-join

HIC

Not applicable
Author

Wow! Thank you for the help, those are great references. The benefits of a normalized model you mentioned in the second post were enough to persuade me against a complete denormalization into a star schema.

...It does feel a bit counterintuitive to run a BI tool on a normalized transactional model. But that will just have to be something I get over while working with QlikView some more.

Thanks again! 

Not applicable
Author

1. I thought that Dimensional Modeling was also Relational? So you have overall relational DB, which then can be modelled by either an ER or Dimensional approach?

I think that a star schema can be either ER or Dim.?

2. Also I got the impression that it was considered best practice in QlikView to always use dimensional modelling, and that everybody was fully committed to only that way. So far I have not been convinced with the arguments. Until I see a practical prove that dimensional modelling is faster for the end user than a smart ER approach in QlikView, I don't believe in the dimensional approach.         

hic
Former Employee
Former Employee

Both the Entity Relationship (ER) model and the Dimensional Modelling (DM) are relational. The main differences are

  • The ER model is a complex maze of hundreds of tables linked with each other, i.e. a table can have foreign keys in many other tables. This leads to circular references, which is not a problem in the ER model.
  • The DM model has a fact table that contains all measures, surrounded by the dimensional tables that don’t contain any measures.

For QlikView, the classification into ER or DM is not so important: Any DM model (star schema or snowflake schema) will work fine. In addition, QlikView can handle a model where measures are found in several tables, i.e. a model which is somewhere between ER and DM, since it does not fulfill the demands of a DM model.

But you cannot just take any ER model and load it into QlikView. You need to remove the circular references, i.e. denormalize by loading some tables several times, once per foreign key.

HIC