Dimensional Modeling design question

Last Post 05 May 2007 01:13 AM by SQLUSA. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
New Member
New Member

03 May 2007 05:12 AM
In our staging area before we move data to a warehouse, we have a COSTS table that contains an additive amount value, and a related table BUCKET_TYPE that relates each row in COSTS to a bucket it belongs to.

When our warehouse was initially designed, a the data for the fact table was collected and dropped into the COST fact table using a pivot query. The result of the pivot query was a column for each bucket. So in essence, we ended up with a fact table with 53 measures.

FACT TABLE DEFINITION (ID's represent FKs for 11 dimensions)


My question is, does it not make more sense to create a BUCKET dimension and have a single AMOUNT measure in the dimension table? Each month, there is approx 20 million rows added to the FACT table if we use the Pivot.
New Member
New Member

05 May 2007 01:13 AM
Is this an operational or design question?

Operationally probably makes sense, because not easy to deal with a stretched-out pivot table of 53 columns.

Design-wise, would there be a user for the new BUCKET dimension? If not, probably better to keep it as is.

Kalman Toth, Business Intelligence Architect
The Best Business Intelligence Training in the World - http://www.sqlusa.com/businessintelligence

Acceptable Use Policy