Too many degenerate dimensions?
2 posters
Page 1 of 1
Too many degenerate dimensions?
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.
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
Re: Too many degenerate dimensions?
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.
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.
Re: Too many degenerate dimensions?
That makes sense. Thanks!
machine2473- Posts : 2
Join date : 2012-10-06
Similar topics
» Degenerate Dimensions
» many degenerate dimensions to one fact
» Common attributes across multiple facts
» Surrogate keys for degenerate dimensions?
» Bridge Table and Degenerate Dimensions
» many degenerate dimensions to one fact
» Common attributes across multiple facts
» Surrogate keys for degenerate dimensions?
» Bridge Table and Degenerate Dimensions
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum