Search code examples
sql-serverssasolap-cubessas-2012

SSAS Dimension design with hierarchy and multiple attributes

I have a dimension design question that I would like to run by the community for the best practice to implement. I've watched a number of Youtube videos and read through various blogs and message boards without a clear solution. I have a dimension similar to the dimension below, with a single hierarchy and three attributes (Cause, Resolution, Satisfaction). The hierarchy could have over five thousand issues. If I include combinations of attributes the dimension table could easily expand to hundreds-of-thousands of records.

What would be the best way to model this in SSAS?

My thought would be to build the dimension table with the hierarchy levels and include every combination of attributes in the table, similar to image below. The sp_FactLoader stored procedure would link to the dimension table and pull the correct IssueKey for the dimension key.

Someone mentioned that what I should do is keep the dimension table simple with the levels of the hierarchy, and load the attributes as a series of many-to-many relationships. I not sure how that can be done, but would like to learn.

I apologize if this has been answered somewhere else. I just haven't found it.

Issue Hierarchy

Category
    System
        Section
            Issue

Attributes

Cause
Resolution
Satisfaction

image

Solution

  • You don't need to have every possible combination, just every existing combination.

    Another approach could be to use 2 dimensions. One to classify the issue and another to sort the cause, solution and customer response, both dimensions would use the IssueKey as Key. You could even go a step further and use 3 very simple dimensions for cause, resolution and satisfaction, linking them directly to the fact table (if that's an option)