Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Maximum number of dimensions and facts in Star schema? Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, March 6, 2014 6:13 AM
 Forum 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 Group: General Forum Members Last Login: Yesterday @ 11:18 PM Points: 13,545, Visits: 11,360
 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 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 Group: General Forum Members Last Login: Yesterday @ 11:18 PM Points: 13,545, Visits: 11,360
 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 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’reapproaching 15 to 18 dimensions. If your design has more, considercombining correlated dimensions into a single dimension."
Post #1548383

 Permissions