ssas clip 2Continuing on from my last post, I explained what Natural Hierarchies were but I thought it would also be worth mentioning what the opposite was and the issues around that.  Unnatural hierarchies can be created between any two attributes for example in the world of human resources (HR) it is becoming more and more important not just to understand the mix of women in an organisation, but how that varies by grade.  So in analysis services we could respond to that by creating a hierarchy on the employee dimension of grade down to gender. 

 

 

 

 

When we do this we have created an unnatural hierarchy and a blue squiggly appears for this because there is no relationship between Title and Gender as we can see by selecting the  Attribute Relationship tab:

image

However we cannot create a relationship between gender and title because there simply isn't one, and if you do drag an arrow from title to gender this designer will let you, but it won't build.  So again you have choices:

  • Go with a less than perfect design and if you find users aren't making use of it remove it in a subsequent release.
  • Use many to many dimensions to model this
  • Use a calculated member to show a male/ female split.   

There is no one right answer it all depends on the use of this hierarchy, and the volumes of data and infrastructure that are in place.