Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / General / Data Warehousing / November 2005

Tip: Looking for answers? Try searching our database.

what should be in Dimension table?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
UA - 17 Nov 2005 18:07 GMT
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 -
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.