Hey all,
Some background:
I am trying to figure out best practice for setting up my budget within my data model.
Currently:
My company uses multiple Consultants for different Projects for a specific Department within the company. Consultants submit budgets for a Project. Projects can be as short as a couple of months, to as long many, many years. Thus, Consultants do not submit budgets annualy like most examples on the forum I've seen. Consultants submit Invoices billing the Company. Thus, my Fact Table = FactInvoiceHeader.
I've seen 3 common scenarios of Budgets within a data model:
1) Create an allocation factor and distribute the Budget within the FactInvoice Table,
2) Create a separate FactBudget Table and using bridge/filter tables build a relationship with the FactInvoiceHeader Table,
3) Create a separate FactBudget Table and use it as a virtual table (#3 is not an option)
Currently I use Scenario #1 (allocate a budget amount from the Project level to the Invoice Level). But I'm asking because Scenario #2 makes a lot of sense to me since you can literally set a budget for the year in a table.I ask because I've seen many examples use Scenario #2 in which they add the measure keys which they want to slice with (Here, 'Consultant Key', 'Project Key', 'Department Key', and then the date component (i.e. 'Invoice Paid Date'(the Date the Invoice was Paid).
The question:
Of scenarios #1 & #2 which is best practice?
Thanks a million!
