sql server data warehouse: 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?

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 -