Connecting Actual and Budget Fact table to same Product and Customer Dimension

Hello Folks,

I am seeking your advice on dimensional modelling problem I have.

I am modelling two fact tables of Actuals and Budget which are at different granularity, Actuals are at day, customer and product sub category level. Budgets are at month, Region and Product catgeory level.Month, Region and Product Category is present in Date, Region and Procut Category dimension respectively. I have only three dimensions as Customer, Product and Date. Linking those dimensions to Actual Fact table is not an issue, what is the best way and options are there to link budget fact table to those three dimensions.

Please advise.

Regards,
Haroon

At the very least you need product category and region dimensions. A month dimension would be preferable but some people use the existing date dimension and limit the month level key to a specific day of the month (first day is easiest).

Many thanks for that. As you suggested to have region and category dimensions. Are those dimensions going to be related to customer and Product dimensions respectively in snow flake fashion.

Not normally. They would be redundant to the information carried on the more granular dimensions.

When you combine the two facts, aggregate them on the attributes in common first.

Let’s focus on the Month. You should have a separated Month dimension. It should be a so called conformed rollup dimension: the attributes of Month dimension will be subset of the Date dimension (e.g. Month Key, Month Name …). These common attributes share the same structure and content. You will connect Budget fact to the conformed rollup tables where needed. If so you will be able to drill across between Actuals and Budget fact tables.