Number of Degenerate Dimensions in Fact table

Go down

Number of Degenerate Dimensions in Fact table Empty 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

Back to top Go down

Number of Degenerate Dimensions in Fact table Empty 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.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Back to top


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