topic Dealing with two fact granularities - Dimensional Model in QlikView

Dealing with two fact granularities - Dimensional Model

tom2qlik — Thu, 19 Apr 2018 16:15:23 GMT

Hi All,

I have a question about creating a dimensional model and dealing with different levels of granularity.

I would like to know which out of these two approaches would be best and why. Or if there is another approach which would be even better.

The scenario I'm usinging is simple: I have 2 dimensions, Region and Customer and 1 fact, Sales.

This becomes two dimension tables, one for region and another for customer with a fact table containing sales looking like so:

Now I want to aggregate the sales by region. But I'm not sure which is the best approach.

Should I aggregate the sales by region and then join the data onto the fact table so the the model looks like this:

Or should I create a new table which holds the aggregated values with a key joining back to the fact and region dimension table which would look like this:

Or is there another approach which beats these two?

Your wisdom and input is appreciated.

Thanks,

Tom

Re: Dealing with two fact granularities - Dimensional Model

Gysbert_Wassenaar — Thu, 19 Apr 2018 17:00:43 GMT

Now I want to aggregate the sales by region. But I'm not sure which is the best approach.

Use Region as a dimension in table or chart in the front end and sum(Sales) as expression. Don't change the data model. One fact table with two dimension tables is perfectly fine.

Re: Dealing with two fact granularities - Dimensional Model

tom2qlik — Fri, 20 Apr 2018 08:42:44 GMT

I have thought about doing it that way but in a complex data model with far more records would calculating the aggregation in the script not have better performance doing the calculation in a chart?