I have a question on modeling about different versions of budgets. During the year (every quarter) the budget is adjusted with new values (for the rest of the year).B4 for the whole yearRFQ1 for Q2, Q3 and Q4RFQ2 for Q3 and Q4RFQ1 for Q4For instance Q3 has 3 versions of a budget, Q4 has 4 versions of a budget..

If I create a dimension for the version, it seems to me like a selector and that doesn't seem a good solution.

As I'm thinking about it : I could enddate the fact budget row when a new value for a period is entered in the DWH as for every moment you have the latest version of the budget. If I enddate the budgets in the fact I can determine the latest version of the budget anytime without using a selector.

Not sure what you mean by 'That would mean implicit knowledge of working of the fact'. Do users need to know what they are doing? Yes, of course they do.

The need is to capture budget versions. From a dimensional design point of view it means carrying sufficient dimensionality in the fact to do that. Query complexity is not a consideration at this point. Ya gotta do what ya gotta do.

Views can probably simplify queries. A 'current budget' view could be created that always selects the current budget so that casual users have access to it without needing to know which budget that is. More sophisticated users can use the raw table to pick the budget(s) they want.

The fact is not completely idiot proof, you have to use the budget selector dimension in order to retrieve the right data. If you don't select the budget selector dimension then you have wrong data. This is what I mean by implicit knowledge (or do I mean explicit..;-)) Well, You get my point, I hope now..

I have some more information about this subject and it seems like it that B4 is the budget and the RFQ's are the forecasts. Now I'm starting to think about three facts:Actuals per dayBudget factForecast fact

Seems more intuitive...

Then I don't have the problem of selecting the right value in the fact (with the budget selector dimension).