Degenerate dimension & accumulating fact
3 posters
Page 1 of 1
Degenerate dimension & accumulating fact
I am currently looking at the design of an accumulating fact table that has order number as a degenerate dimension (the snapshot is one row per order). There are a few additional fields that apply to the order, that are also being carried as additional degenerate values.
The question is should we make these related values into a dimension of their own?
The new dimension would have the same grain as the accumulating fact (one-to-one), thus we would end up with two tables with the same number of rows. One of the advantages of pulling these into their own dimension is that it provides us with another conformed dimension that can be reused in other facts. But, are we going to take a performance hit in having to join two multi-million row tables in almost every query that hits this fact?
The question is should we make these related values into a dimension of their own?
The new dimension would have the same grain as the accumulating fact (one-to-one), thus we would end up with two tables with the same number of rows. One of the advantages of pulling these into their own dimension is that it provides us with another conformed dimension that can be reused in other facts. But, are we going to take a performance hit in having to join two multi-million row tables in almost every query that hits this fact?
lmorgan- Posts : 3
Join date : 2009-02-27
Re: Degenerate dimension & accumulating fact
A dimension that is 1:1 to the fact is not a good way to go. I would keep the degenerate order number and collect the other attributes into one or more junk dimensions. The resulting dimension should be much smaller than the fact table.
Re: Degenerate dimension & accumulating fact
Create the dimension so you can use it for other fact tables and add to your fact table. You won't use it here for reporting but it will be available for other facts to drill across. Store the required order attributes on the fact table.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Similar topics
» Conformed Dimension for Transaction Fact and Accumulating Snapshot Fact Table
» is it ok to join two fact tables on a degenerate dimension?
» Dimensional Modeling for WorkGroup Management in Electrical Utility space.
» FACT Design Question - How to design FACT Table when computation is required on the degenerate dimension?
» Accumulating Snapshot Fact OR Type 3 Dimension?
» is it ok to join two fact tables on a degenerate dimension?
» Dimensional Modeling for WorkGroup Management in Electrical Utility space.
» FACT Design Question - How to design FACT Table when computation is required on the degenerate dimension?
» Accumulating Snapshot Fact OR Type 3 Dimension?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|