Type II dimensions and budget fact tables

Hi,

I have a question concerning modelling a star schema. Every year a branch submits a budget. Branch is a TypeII dimension and regulary changed, therefore multiple records are created.
At the time the budget is inserted in the budget fact, a foreign key is placed in the budget table, pointing to the surrogate key of the branch. When now the branch is changed from district, a new record is created in the brand dimension. However, the FK in the budget table still points to the old branch. Meanwhile actuals are already inserted into another fact for the new branch.
Is the user wants to report on the figures for a branch, only the actuals of the old branch will show a budget, as there is no link to the budget of the new branch, despite the fact that for the business user, this is exactly the same branch.
How can I solve this ? Using the business key to link to the budget table is an option, but not preferred.

many thanks,

The basic process for locating current information in a type 2 dimension is to perform a self join on the dimension using the business key to locate the current row.

Hi

Thank you for your anwser. In my T2 dimension I even have a current flag sor retrieving the current record is nu issue. The problem is that if I write a query that will retrieve data from my budget fact at year/branch grain and data from the actuals transaction fact (grain: transaction) for certain transaction records the budget will not be picked up as the join is on the SK of branch. (that already created several records due to T2 changes).
I was thinking if a bridge table between branch and budget wouldn't be an option. If a new T2 change branch record is created I can create a new record in the bridge table and point it to the correct budget using the business key.
This avoids using business keys as links while doing reporting.
What do you think ?

Maarten

You don't join fact tables, you aggregate individually on attributes and join the aggregates on common conforming attributes. But, even if you use keys for aggregation, you use the key of the current dimension row, not the historical one.

Hi,

The approach of joining fact tables with a common grain using a conformed dimension is the classic and proven approach. Reporting tools however such as Cognos allow the use of multi-fact, multi-grain queries by using determinants. What basically will happen is that Cognos is intelligent enough to know that faxt X is at grain X and fact Y at grain Y using a common dimension.
This allows the reporter to create a report that shows budgets at month level and actuals at day level. Cognos will then generate its own stich query. Both facts will be correctly aggrgegated using the common dimension. This feature allows a lot of axtra flexibility and less need for aggregate tables. This feature will even work correctly within the same fact table where a 2 facts of different grain could be stored. (however I do not support/apply this approach)
The issue here is that the links between tables are on the surrogate keys and not the logical business keys.

regards,

So, the question boils down to Cognos and how it handles cross-fact queries. At issue is not keys (surrogate or business) but rather attributes. As I mentioned earlier, the aggregation and joins should occur on attributes, which is a natural extension of the query. Try creating a view on the dimension table that returns the current version of the dimension row and see if that helps...

Agree with Negalemmo, the connection point should always on dimension attributes or NK instead of SK in terms of dimension conformance between fact tables. Don't get carried away by trying to use the same dimension SK value in different fact tables as the ultimate conformance point as it's always m-m relationship because of SCD 2.

When you are trying to consolidate between budget and actual fact table, you should never assume a SK in an individual transaction fact would match another SK in a fact record in budget table. It is the NK in the dimension that ties them together whereas SK is only the entry point to that conformance. Obviously you need to aggregate actuals on NK or other dimension attributes first before connecting to another fact table, be it a budget fact, or other conformed fact tables.