I am struggling with Multiple Facts in the traditional Sales/Orders/Budget type model.
I have my tables of Orders & Sales with psuedo-key of Day, Product, Currency, Country & Customer (Key_Fact_Sales)
I have my Budget table, which is month, with pseudo key of Month, Product, Currency & COuntry. (Key_Fact_Budget)
These are concatenated into a link table.
I have separate dimension tables for Time (Time has Day, Month, Year etc), Product, Customer, Country & Currency.
Sales + Orders works fine.
However, when I add in Budget to the table, no data is returned - I think this is because of the different Key in the link table.
How best to resolve this?
I have thought of adding Key_Fact_Budget keys to the Sales/Orders tables, but this would imply that when I look at YTD performance vs annual budget, the budget calcs would only consider those periods where there are corresponding Sales.