Better Data Model

[#1]

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

[kuczynska]

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]

Hello,

See the attached file from page 15 to page 22.

It may help you.

Regards

Sara

[Anonymous]

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]

Here is another Blog worth reading Concatenate vs Link Table

[#6]

Thanks for sharing your experience.

[#7]

Thanks I already read those two articles.

[#8]

Thanks Sara, that is very useful document

[sdmech81]

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

[#10]

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.