Fact Table Optimal Design

• Question

• I have a working model, but looking to optimize it. Particulary the fact table. Here is how fact table looks:

ProductKey ChargeAKey ChargeBKey ChargeCKey ChargeA\$ ChargeB\$ ChargeC\$ TotalABC\$

1                  1                 0                 0                  2\$            0             0              2\$

1                  2                 0                 0                  3\$            0             0              3\$

1                  0                 1                 0                  0               4\$           0             4\$

1                  0                 0                 1                  0               0             2\$           2\$

1                  0                 0                 2                  0               0             3\$            3\$

1                  0                 0                 3                  0               0             3\$            3\$

... now some explanation.. granuality of fact table is Product + ChargeType. each charge type (A, B, C) has it's own dimension table with its own unique list of attributes and they all come from different sources. The simplest loading would append each Product + ChargeX to the table. I was thinking of option b - settingup fact table as Product + ChargeType + ChargeKey + Chargeamount, but this design in my opinion is less friendly then the original because user would have to know what charges have to be filtered down first by type to get to specific key and its attributes... what I don't like about option a is that it takes a lot of space and there are fields populated with only meningless 0 vs having everything compressed into few records. e.g. above exmple could be designed to have only 3 records vs. 6 which translates in huge savings when we are talking millions of records.Would anyone have anyexperience with similiar challenge? I would be able to solve this with complex ETL procedures, but there has to be something much simplier. I am still not sure if this is design or ETL solution... any feedback greatly appreciated.

Monday, August 20, 2007 8:22 PM

• My general recommendation on dimensional design is to stay focused on your business users.  What is the business process, activity, or event this table represents?  How would your users describe the facts associated with this process/activity/event?

It appears you have incorporated a dimension (charge type) with three values into your model.  If each charge type represents a different event/process/activity, then three fact tables are in order.  If this is a single event that could be broken down by charge type (of which there are currently three identified), then a single fact table is required with a charge type dimension.  (What happens if a fourth charge type is introduced?)

B.

Monday, August 20, 2007 8:58 PM

All replies

• My general recommendation on dimensional design is to stay focused on your business users.  What is the business process, activity, or event this table represents?  How would your users describe the facts associated with this process/activity/event?

It appears you have incorporated a dimension (charge type) with three values into your model.  If each charge type represents a different event/process/activity, then three fact tables are in order.  If this is a single event that could be broken down by charge type (of which there are currently three identified), then a single fact table is required with a charge type dimension.  (What happens if a fourth charge type is introduced?)

B.

Monday, August 20, 2007 8:58 PM
• Thank You. That's right I always keep end user in mind because it is for him that data is being presented... and you always think the performance and simplicity. You gave me the answer: "What happens if a fourth charge type is intorduced?".. I will be in a lot of work, if not trouble, to integrate everything into one fact table.. Thanks.

Tuesday, August 21, 2007 12:07 PM