Product Dimension design

The product dimension has the following levels:

Product Line
product SubLine
Major Family
Minor Family

The Sales fact table has the following details:

Minor_Family_id
Scenario_id
Time_id
Sales_Quantity
Sales_Value

The Sales cube has the following details:

dimensions:
**********

Scenario (possible values are Actual/Budget)
Product
Time

Measures:
********

Sales Quantity
Sales Value

Problem definition:

The Measure data for 'Budget' scenario is available at [Product].[Major
Family] level where as the 'Actual' scenario contains the measure
values at [Product].[Minor Family] level. I need to load the budget
data along with the actual data in Sales_Fact table.

There are two options:

1. Load the budget data in Sales_fact at Minor_Family_id level. In this
case, in Product dimension, i need to create a surrogate id for Minor
family product names as 'Not Available' under EACH Major Family and
load the budget data in SALES_FACT table using the minor_family_id,
child of the corresponding Major_family_id (for budget data).

2. Create a new key called Major_Family_id in SALES_FACT table and
create one more alternate hierarchy on Product dimension ending at
[Product].[Major Family] level.

Which one of the alternative is better? any alternate method suggestion
would be helpful.

Usually it's best to separate the fact tables according to the fact grains.

But If you still want to use only one fact table, you can consider an
alternative to allocate budget values at the Major Family to the Minor
Family. Or you can select one representative Minor under its parent and make
it have the same value as its parent.

Ohjoo Kwon
www.olapforum.com



"Ajit Singh" <ajit555 (AT) yahoo (DOT) com> wrote