Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Degenerate dimension & accumulating fact

3 posters

Go down

Degenerate dimension & accumulating fact Empty Degenerate dimension & accumulating fact

Post  lmorgan Mon Jun 01, 2009 11:06 am

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?

lmorgan

Posts : 3
Join date : 2009-02-27

Back to top Go down

Degenerate dimension & accumulating fact Empty Re: Degenerate dimension & accumulating fact

Post  ngalemmo Mon Jun 01, 2009 12:10 pm

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.
ngalemmo
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

http://aginity.com

Back to top Go down

Degenerate dimension & accumulating fact Empty Re: Degenerate dimension & accumulating fact

Post  BoxesAndLines Mon Jun 01, 2009 12:37 pm

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
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

Back to top Go down

Degenerate dimension & accumulating fact Empty Re: Degenerate dimension & accumulating fact

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum