Dimensional Modeling design question

Last Post 05 May 2007 02:13 AM by SQLUSA. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Taurkon
New Member
New Member

--
03 May 2007 06: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)

ID1
ID..11
AMT_BUCKET1
AMT_BUCKET2
AMT_BUCKET3
AMT_BUCKET...53

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.
SQLUSA
New Member
New Member

--
05 May 2007 02: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


You are not authorized to post a reply.

Acceptable Use Policy