jryan999
Profile Answers by jryan999 Questions by jryan999

Sep 1st, 2008

The number of Dimensions depends primarily on the requirement. I have built systems with a few FOUR Dimensions on a Fact and as many as TWENTY. There is no physical limit imposed by either the database or ETL tool.

Kimball recommends however, after about 20 Dimensions you should review the design. If for example many of your Dimensions are simply low volume CODE/DESCRIPTION fields, consider combining these (every combination) into a "Junk Dimension".

eg. Product Type has five values, and Product Category has 10 values. Consider combining these which will give an absolute maximum of 50 values.

Primary reason behind avoiding huge numbers of Dimension tables is each Dimension table requires a Surrogate Key on the fact. This will result in very wide (and therefore) very large Fact tables reducing performance.

If you do need 50 Dimensions on a Fact - you need them - but I'd question any design over about 15-20.

Hope this helps.