Hi All,

Referring to the FoodMart - Sales Cube.
There is a Dimension table called Promotion - Which caters
to two dimensions 'Promotion' and 'Promotion Media'.

We have a Invoice Cube - Star Schema with about 20
dimensions. There is a Dimension table called Vendor -
Which caters to about 5 other dimensions just like the
Promotion table in the SALES Cube.

We have a dimension called VendorType build out of the
Vendor Table. Currently the VendorType is a Single Level
Dimension. Due to our business requirements we have a
hierarchy for the VendorType now. (2 more additional
levels are added above existing VendorType - So in total
we have 3 levels for VendorType now )

For Designing this, we have the follwoing ideas.

1) Add two more columns in the Vendor table called
VendorTypeLevel1 and VendorTypeLevel2. Rebuild
the 'VendorType' Dimension to include the first two levels.

2) Create a separate table called VendorType with 4
Columns - VendorTypeID, VendorTypeLevel1, VendorTypeLevel2
and VendorTypeLevel3. Add a column in existing Vendor
Table for VendorTypeID. This would lead to Snowflake kind
of architecture.

3) Create a separate table called VendorType with 4
Columns - VendorTypeID, VendorTypeLevel1, VendorTypeLevel2
and VendorTypeLevel3. Add a column in the exisitng fact
table for VendorTypeID.
This would increase the number of joins between the Fact
and Dimension table - as we have added a new table join
with the fact table.

In the currently existing Cube - the fact table has got
around 5 million records and the vendor table has got
around 200,000 records.

Any idea on this is highly appreciated.

Cheers,
Sanka

Hi Sanka,
here is my guess.

Solution #3:
Creating a separate dimension table would create a many
to many relation between vendor and vendor type - which,
I assume, does not exist. One vendor is of one type. Thus
#3 would not reflect the data relation correctly.

Solution #2:
The snowflake design seems like a possible solution. If
you are using Molap it will only affect the cube
aggregation, not the response time. It creates a more
complex er-model, but saves disk space. (If you are using
rolap, it will probably slow response time)

Solution #1: I think this is best, since it is the
leanest design and reduces joins to a minimum (star). You
trade this for storage space, though.

For all of the above considerations, I'd go with #3.

But maybe the approach I am taking is too simple.

HTH Lutz

Hi Lutz,

I have started implementing as Solution #3: But I really didn't get your statement - "Thus
#3 would not reflect the data relation correctly."

Could you please explain more.

Cheers,
Sanka


----- Lutz Morrien wrote: -----

Hi Sanka,
here is my guess.

Solution #3:
Creating a separate dimension table would create a many
to many relation between vendor and vendor type - which,
I assume, does not exist. One vendor is of one type. Thus
#3 would not reflect the data relation correctly.

Solution #2:
The snowflake design seems like a possible solution. If
you are using Molap it will only affect the cube
aggregation, not the response time. It creates a more
complex er-model, but saves disk space. (If you are using
rolap, it will probably slow response time)

Solution #1: I think this is best, since it is the
leanest design and reduces joins to a minimum (star). You
trade this for storage space, though.

For all of the above considerations, I'd go with #3.

But maybe the approach I am taking is too simple.

HTH Lutz