Budget and different versions

Hello,

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 year
RFQ1 for Q2, Q3 and Q4
RFQ2 for Q3 and Q4
RFQ1 for Q4
For 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.

What do you think...

Hennie

What does the business want to do with it?

Do they always want the current version of the budget? If so, just update the budget data and forget about versions.

Do they want to see old versions of the budget? If so, have a version dimension.

Good question. I'll ask ;-) ...and asked right away...

In general they want two versions (B4 and the adjusted RFQ for the next period).

So, we need a selector dimension ?! That would mean implicit knowledge of working of the fact. It doesn't work out of the box.

Or perhaps in columns in the fact? the B4 column, RFQ1 column, RFQ2 column..

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.

Thanks for your insights and questions.

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 day
Budget fact
Forecast fact

Seems more intuitive...

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

Hennie