Forum Discussion
Design question
Anonymous if every record is at the same granularity, then yes, Option 1 should lead to a more optimized model. Tall and narrow fact columns tend to compress better. Just be sure to create your measures properly:
$ Budget = CALCULATE( SUM(Fact_Table[Amount]) ,Fact_Table[Flag] = "Budget" )
You don't want to add up Budget, Forecast, and Actual values in the same measure!
I would lean towards option 2. The only way that Option 1 will work is if you have the exact same granularity across all three tables.
I'm guessing you have "Actual" transactions daily for specific accounts. Do you have daily "Budget" and "Forecast" amounts? It's probably only monthly values, and maybe only at a higher parent account level.
SQLBI wrote a great article on Budget Patterns. I recommend reading it and then creating your data model based on your findings.
Hi Anonymous
Thank you so much for the reply! I am not reading data directly from the source system. I will receive monthly files for Budget, Actual and Forecast. So yes, they will have same granularity. In that case option 1 is better since I will be able to avoid joins? It will be a long and thin table if option 1 is chosen.
Regards
Priya
Anonymous if every record is at the same granularity, then yes, Option 1 should lead to a more optimized model. Tall and narrow fact columns tend to compress better. Just be sure to create your measures properly:
$ Budget = CALCULATE( SUM(Fact_Table[Amount]) ,Fact_Table[Flag] = "Budget" )
You don't want to add up Budget, Forecast, and Actual values in the same measure!