How many dimensions are too many? RRS feed

  • Question

  • Somewhat new to SSAS, and am working to model the relational store that will feed SSAS cubes.  I'm wondering how many dimensions are too many dimensions.  The business (medical related) situation is that lots of questions are asked surrounding a medical event regarding everything from where the event occurred, under what circumstances, questions about the mother of the person, the father, race, medical conditions, etc.  I'm feeling like if I do a true star schema, there will be too many dimensions (50+?), and I need to  normalize it back and snowflake it out.  For example, coming off the main fact table, should I have a "mother" dimension and then snowflake all the mother related stuff out from that dimension?  One of my main concerns is having a fact table that's too wide joining out to too many dimensions.  Should I try and keep my fact table < ?? # of columns?

    Thursday, January 10, 2008 7:05 PM

All replies

  • Hi


    One of the SSAs advantages is that a dimension can contain lots of attributes that can be viewed in different axis when pivoting in an OLAP client tool.


    So you can build a relative small relational fact table only with the main dimension entities and then use snowflake schemas to populate the entire dimension.

    If you set appropiate attributes and properties end user can see all them as different pivotable. hierarchies


    You have a detailed feature description in Analysis Services Book online:




    Thursday, January 10, 2008 8:35 PM
  • Hi! What is important is the cube space. In SSAS2005 you can calculate this by the number of attributes and members in each attribute in a dimension.


    In each cube a limit of 10-15 is a performance limit.


    End users can normally work and understand the instersection of 4-6 dimensions in a report or cube browser.


    In SSAS2005 a cube can have several fact tables/measure groups and a dimension consist both of hierarchies and individual attributes.


    Finally.In SSAS2005 enterprise edition you have the option to use perspectives in a cube and they are the same as views in a relational database. A large cube can be divided into several perspectives(select appropriate dimensions and measures for each).


    I would never recommend anyone to build a cube with 50 dimensions.



    Thomas Ivarsson


    Thursday, January 10, 2008 9:29 PM