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 - 19 Nov 2005 23:49 GMT
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 -