[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 80
  • Last Modified:

Create a Dimension with Drilldown But The Fields Are Unrelated

I want to create a dimension that has drilldown between 3 fields in my fact table.   The fields are not a parent - child relationship, they are just 3 independent fields.   To make it work at this point I have setup a dimension with each field in the key and then each field is also an attribute.  I am then able to setup the drilldown hierachies that I need.  Is this the proper way to have done this or is there a better way?
  • 2
1 Solution
The problem you will have is that if the fields do not actually have any integral hierarchical relationship then trying to enforcing this within the cube may well lead to data inconsistencies. Also from a 'correctness' perspective, a dimension should not really contain unrelated attributes.

I would let SSAS create three seperate dimensions directly from your fact table, then view the required hierarchies within the client application. i.e. rely on the client application to use filtering to simulate a hierarchical drilldown rather than using an enforced hierarchy. i.e. In an Excel pivot you can combine any number of dimensions into a real time hierarchy by dragging extra atributes into the row/column.

However, if you can get your current method to provide reliable data at all levels of drilldown then by all means stick with it, just test the results very carefully... And re-run thorough tests if you alter the attribute relationships.
curtis591Author Commented:
The problem that we had, at least in 2000, was that you hit a wall when you get around 8 dimensions in your pivot table.   We have a group of fields that if you look at one you probably are going to want all 3 to go with it.  When the user was looking at some detailed information and they had the 3 individual fields it would take 30 minutes to calculate.  If you had the two dimensions as drilldowns of 1 of them the same pivot table would return in a few seconds.  

I suppose I should actually check to see if this is even a problem anymore.   I could be working around a problem I don't have any longer.
Have you tried increasing the number of aggregations you are storing? It is worth redesigning you aggregations to make sure they are appropriate.

Also, you should definately find that 2005 has some performance enhancements over 2000.

Mind you as I mentioned earlier, as the old adage goes: if it aint broke, don't fix it!
If it works properly, and you get the results you want, then I'd be reluctant to change it, even if it isn't perfect...

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now