Posted by One of the wonderful features of Analysis Services (SSAS) are perspectives. Perspectives allow you to limit what measure groups, measures, dimensions, attributes and hierarchies are displayed.
This is great if you have lots of measure groups and dimensions because you can build them all in one cube and use a perspective to display only a subset of them. You can configure as many perspectives as you want and users can easily connect directly to them via Excel.
This is a big improvement on SSAS editions prior to 2005 where you didn’t have this feature. You were forced to build separate cubes.
As a consequence, this “single cube” approach is quite popular. Not only is it a one stop shop for analysts, but developers only have one cube to maintain. Everyone is happy.
But on a recent project I was working on, our “single cube” approach caused a few problems.
Business (un)intelligence
Our cube had four measure groups and four perspectives (one for each measure group). But some of our users were very competent with the data and tended use the overall cube view; where you see everything. Not the perspectives we’d built.
As it happened, our users started to apply filters to dimensions not related to the measure groups and of course they had no effect. Soon the defects started rolling in and user acceptance testing ground to a halt.
What our users were doing was not conceptually wrong. The dimensions had an implicit relationship to the measure groups. But the relationships weren’t implemented for one reason.
The granularity of measure groups were different.
The real issue was with one specific relationship between a dimension and a measure. It’s a highly contentious relationship in the business that’s not well defined. But our cube was suggesting that our users could report on it.
And of course someone did and all hell broke loose.
There were tears.
Back to intelligence
The confusion around seeing unrelated dimensions and measures far outweighed the benefits of the single cube. So we split the cube up into separate cubes, one for each granularity, with amazing success.
Our end users found the new solution far more intuitive to use. The misleading dimensions were now gone removing the ability to mistakenly report on one. The granularity of each cube was now obvious.
So if you’re going to use perspectives, by all means go for it. They are extremely helpful when you want to narrow your users focus on one particular area.
Just be careful if you measure groups have different granularities.