Degenerate dimension & accumulating fact

View previous topic View next topic Go down

Degenerate dimension & accumulating fact

Post  lmorgan on 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

View user profile

Back to top Go down

Re: Degenerate dimension & accumulating fact

Post  ngalemmo on 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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Degenerate dimension & accumulating fact

Post  BoxesAndLines on 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.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Degenerate dimension & accumulating fact

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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