Design question

[Anonymous]

Hello!

I am working on a developing a set of financial reports. Currently everything is being done in Excel. I am trying to fit that data into model. Now I have Project wise Budget, Actual and Forecast values for each month. What will be optimized design?

1. Have all values in one fact table with a flag for identifying Budget, Actual and Forecast values.

2. Have 3 different tables each for Budget, Actual and Forecast values and connect them with dimesion tables. 

Regards

Priya

[Anonymous]

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.

[Anonymous]

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 · Accepted solution]

@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!