Number of Degenerate Dimensions in Fact table

View previous topic View next topic Go down

Number of Degenerate Dimensions in Fact table

Post  Raza on Fri Jan 08, 2016 8:00 am

How many degenerate dimension should actually be stored in a fact table? I am building a star schema and have to store some columns (10 of them to be precious) which are on a per transaction level. They don't fit in my dimensions so the next place to store them is in a fact table as degenerate dimension. From what i have read it seems 1-2 degenerate dimension makes sense to be stored in a fact table however i am not sure if 10 makes sense? Should they be stored in a junk dimension? Because there is a 1to1 relationship between the fact and these degenerate dimension, will making a separate junk dimension effect query performance? If i store them in my fact, suddenly my fact is alot wider than it needs to which has its own drawbacks. Any suggestions or ideas will be appreciated. Thanks

Raza

Posts : 3
Join date : 2015-06-23

View user profile

Back to top Go down

Re: Number of Degenerate Dimensions in Fact table

Post  ngalemmo on Fri Jan 08, 2016 7:57 pm

One option is to group various attributes into junk dimensions. Suitable attributes have relatively low cardinality (values that are close to 1:1 to the fact are best left as degenerate values). Which attributes you combine into a single junk dimension is determined by their correlation. This is not a big consideration for very low cardinality values. You can check correlation by doing a count distinct on the combination of attributes you are considering.

Generally, this saves a large amount of space, thins out the fact and helps performance.
avatar
ngalemmo

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

View user profile http://aginity.com

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