Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Maximum number of dimensions and facts in Star schema? Expand / Collapse
Author
Message
Posted Thursday, March 6, 2014 6:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 6, 2014 6:12 AM
Points: 2, Visits: 0
What is maximum number of dimensions tables in Star schema?
And within the fact table, what is the maximum number of facts?
Post #1548232
Posted Thursday, March 6, 2014 6:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:15 AM
Points: 13,017, Visits: 10,800
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1548235
Posted Thursday, March 6, 2014 6:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 6, 2014 6:12 AM
Points: 2, Visits: 0
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
Post #1548237
Posted Thursday, March 6, 2014 6:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:15 AM
Points: 13,017, Visits: 10,800
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1548241
Posted Thursday, March 6, 2014 10:25 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 28, 2014 7:12 PM
Points: 2,148, Visits: 487
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.

http://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/centipede-fact-table/

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."
Post #1548383
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse