MS SQL Data Warehousing - what should be in Dimension table?

Author

what should be in Dimension table?

2005-11-17, 1:23 pm

I am using Yukon.

I am designing a datamart which should satisfy the OLAP browsing as well
as drill through reporting for which I think I may have to include some
other tables into my datamart which contains all the detail information for
the drill through reporting.

The design that I have modeled contains a 1 fact table which has all the
surrogate keys from 5 dimension tables. The dimension tables that I have is
Geography dim, Date dim, Organization dim, product dimension and onecurrency
dim which is a flat dimension.
Now my question is I want to store the detail information as well for the
drill through reporting, so where should I save?
Shall I include the detail information into the same dimension table like
for ex. for Product dim if I want name,size,type also to be stored shall I
create
another dimension table which contains all these detail information and just
create the relationship between Product Dim and detailProduct dim or shall I
store this detail information in the product dimension itself.

The detail information for each level of the dimension table could be very
large if I
save all the information (hierarchical information plus the detail
information for each level) in the same dimension table.

Which way is advicable?

adil1@transinfo.com

2005-11-19, 8:23 pm

It depends on the volatility of the attribute values. For example,
Suppose Product is a type 2 dimension with three attributes: Size,
Price, Quantity. I am assuming that:
- Size: Changes rarely
- Price: Changes every now and then
- Quality: Changes rapidly.

A solution could be:

Size: Store in the Product Dimension Table and create a new dimension
record with a different Surrogate key when it changes
Price:Store in the dimension and fact table, and apply a type 1
dimension update (Ovewrite value)
Quantity: Store in fact table

Hope this helps,

- Adil -

Sponsored Links