Too many degenerate dimensions?

View previous topic View next topic Go down

Too many degenerate dimensions?

Post  machine2473 on Fri Aug 16, 2013 8:56 am

Question for the group: At what point does the number of degenerate dimensions in a fact table become too many?

In a DW project here I have denormalized a sales header/detail record combo into a fact, but the requirements specify that a number of character data elements from both header and detail be available. The end design is expected to support reporting requirements as well as analytical ones. The sales fact table design is beginning to look like it will have more varchar columns than numeric measures. A number of the varchar columns are header elements that do not directly relate to the transaction detail.

As my newbie eyes see the options..
1. Leave the degen dimensions in the fact,
2. Move as many degenerate elements as possible to a "reference" dimension (although that seems to smack of reverting to header/detail), or
3. Test the suggestions of the experts on the Kimball forums.

I'm a newbie to dimensional design, and have been poring through my copy of The Microsoft Data Warehouse Toolkit, but haven't yet found a clear guideline or decision process for this design topic. Any help or guidance would be greatly appreciated.

machine2473

Posts : 2
Join date : 2012-10-06

View user profile

Back to top Go down

Re: Too many degenerate dimensions?

Post  ngalemmo on Fri Aug 16, 2013 10:26 am

You don't want to store them all as degenerate dimensions. About the only degenerate there may be would be something like the order/invoice number and a line number. Wide fact tables containing a large number of degenerate dimension values tend to perform poorly on almost every DBMS. The only databases where it doesn't matter are columnar databases such as Sybase IQ and Vertica.

You create 'junk' or 'mini' dimensions for the miscellaneous attributes. These dimension tables contain a handful of left over attributes, the natural key being the value of those attributes. You may have more than one of these tables depending on the number of attributes, the cardinality of those attributes and the correlation between the attributes.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Too many degenerate dimensions?

Post  machine2473 on Sat Aug 17, 2013 6:40 am

That makes sense. Thanks!

machine2473

Posts : 2
Join date : 2012-10-06

View user profile

Back to top Go down

Re: Too many degenerate dimensions?

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