Forum Discussion
How do model data with different granularity?
I have a situation where I want to compare cost and revenue. My revenue number is just an aggregate, however cost has sub categories. How would you model this?
I can think of these ways.
1) Put revenue in one row and have 0s in all others
| Project | Revenue | Cost | Cost Category |
| Project A | 1000000 | 500 | Cat 1 |
| Project A | 0 | 90000 | Cat 2 |
| Project A | 0 | 8855 | Cat 3 |
| Project A | 0 | 6000 | Cat 4 |
2) Make single column for cost and revenue.
| Project | Type | Value |
| Project A | Revenue | 100000 |
| Project A | Cost Cat 1 | 500 |
| Project A | Cost Cat 2 | 90000 |
| Project A | Cost Cat 3 | 8855 |
| Project A | Cost Cat 4 | 6000 |
3) Create two fact tables one for revenue and another for cost.
In general, I have been struggling with this concept of different grains in data for a while. Any pointers on what are the best practices?
4 Replies
Have a read of https://www.daxpatterns.com/budget/ by the SQLBI guys, it should give you some ideas about working at different granularities.
I would go for option "3) Create two fact tables one for revenue and another for cost" and have a bridge table for Project and any other fileds common to both tables.
Thank you for your response. Do you think I can create this using matrix visual and two fact tables?
Sure, but you will need a table with the row structure for the matrix. SImilar to this, but for rows instead of columns:
