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

Better Data Model

Hi, i would like to know as many opinions as possible about, what is the best approach to create data model in QlikView. What is better for QlikView performance?

1. Concatenate different fact tables into one big fact table and after that join all dimensions to this table and have a nice star schema. Add source column, to distinguish different data sources. Do set analysis to select from big fact table only those fact that belongs to first resp. second fact table, that are concatenate now.

2. Load each fact table as separate table in data model and connect shared dimensions trough linked table and other specific dimension directly to appropriate fact table.

I am asking this, because i am about going to build QlikView app with various sources, planned is 5 different sources with data about same business, but different parts of it, dfata are barely connectable and there is different granularity. This fact tables have many shared dimensions like country, customers, product and so on, but each one has few specific dimensions. So before i start, i have to find out best approach to have model, that is readable and suitable for to reach best performance.

Thanks

10 Replies
kuczynska
Creator III
Creator III

Hi,

Both options mentioned by you above are absolutely valid and both of them have slightly different purposes. It basically all comes to a couple of things:

1. What are the volumes of your data?

2. You mentioned something above about granularity - so, what's your lowest granularity? If you have different granularity, it might happen that following approach 1 you would end up with duplicated rows and plenty of nulls and performing sum() or count() which require set analysis, distinctness, aggr() etc from the very beginning and it some occasions you might be adding some extra complexity to your model, which could be omitted.

3. Linking your data through link table - ask yourself a question what are the associations between your data subsets? It might happen that your only common dimension is for example time/date and/or maybe something else. Then is good to have your link table, with all dimensions used as filters in front end in it.

Btw - in situation when we were to build a report to gather data from different parts of the business where we had plenty of mutual dimension (and the same granularity in most of the occasions) we decided to have a big facts table and star schema. On the other occasion when we were asked to build a report gathering data that didn't have any similarities and different granularity - we decided to build a link table, where our key was only the time/date.


But at the end - it all depends on the business requirements of your report, so speak to your users and try to get an idea how the data is linked.

hope this help.

sarafamiglietti
Creator
Creator

Hello,

See the attached file from page 15 to page 22.

It may help you.

Regards

Sara

Anonymous
Not applicable
Author

I always try to go for a single concatenated Fact table in the centre of a Star Schema.

  • Unless the underlying facts are so different as to make concatenating silly.
  • Have a look at this blog post Generic keys

p.s. If all your facts have similar granularity then you won't need to worry about Generic Keys.

Anonymous
Not applicable
Author

Here is another Blog worth reading Concatenate vs Link Table

Not applicable
Author

Thanks for sharing your experience.

Not applicable
Author

Thanks I already read those two articles.

Not applicable
Author

Thanks Sara, that is very useful document

sdmech81
Specialist
Specialist

Hi,

I Would prefer concatenating tables to create fact table and also flagging them wherever needed.Which leads to start schema aswell as flags created helps a lot to play with parts of concatenated bits..

Sachin

Not applicable
Author

I read some articles about this, and seems like it is slightly better to use concatenated fact tables then linked tables, but it depends on task you have. Thanks for all answers, i will go the way of concatenated facts.