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: 
tom2qlik
Creator
Creator

Dealing with two fact granularities - Dimensional Model

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:

No Aggr.PNG

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:

Fact Join.PNG

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:

2nd Fact.PNG

Or is there another approach which beats these two?

Your wisdom and input is appreciated.

Thanks,

Tom

2 Replies
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand
tom2qlik
Creator
Creator
Author

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?