March 6, 2014 at 6:13 am
What is maximum number of dimensions tables in Star schema?
And within the fact table, what is the maximum number of facts?
March 6, 2014 at 6:20 am
I don't think there is a hard-coded limit.
But if your number of dimensions is going over 30, you might want to investigate of some dimensions can't be combined.
Regarding facts: if they are calculated facts based upon other facts in the same table, leave them out. The reporting layer can do that as well.
If all the facts are valid measurements of your business process, you don't have too many facts.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 6, 2014 at 6:24 am
Hi,
Can I know in terms of relation between fact table and dimension table.
Could one fact table be connected to a maximum of 16 dimension tables?
Regards
March 6, 2014 at 6:28 am
You can connect a fact table to as many dimension tables as you like. There's no technical limit on that.
You just have to make sure that it make sense to link a fact table to a certain dimension.
There are practical limits though. Having too much dimensions makes it unwieldy to browse through the cube (especially in Excel).
Personally I try to stay below 15 dimensions, ideally below 10.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 6, 2014 at 10:25 am
I agree with Koen. The KimballGroup states that 5 to 15 dimensions is usually the most common range and that is at the PHYSICAL LEVEL. The only thing I would add is that 3 role playing dimensions based off the same PHYSICAL dimension do not count as anthing more than 1 dimension within the context of Kimball's statement.
For example, if there is an order date, shipping date, and payment date based of the same physical table, DimDate, this is considered one (1) physical dimension with three (3) logical roles.
All of this said, I have seen situations in Healthcare where 20 - 25 physical dimensions have been justified, but, more often than not, if you have more than 15 physical dimensions, chances are you may need to go back to the drawing board.
Kimball's official quote is this:
"You’re nearing the practical limit on the number of dimensions when you’re
approaching 15 to 18 dimensions. If your design has more, consider
combining correlated dimensions into a single dimension."
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply