Subscribe to Blog feed
One of the great features of using PerformancePoint Services in SharePoint Server is the ability to be able to drill down to (or cross drill) and perform additional ad-hoc analysis. This features is built into the analytical reports, both grid and chart, as well as the decomposition tree. Here is what the feature looks like with each of these options along with the output in an existing dashboard page in SharePoint:
Analytical Reports (Chart and Grid)
| Initial Report | Drill Down To menu | Final Result |
 |
 |
 |
| Sales chart displaying data by Product Category | Able to Drill Down To any of the 14 dimensions that are in cube | Home Appliance sales data drilled down to Machine Type – unrelated dimension to sales |
 |
 |
 |
| Sales grid displaying data by Product Category | Able to Drill Down To any of the 14 dimensions that are in cube | Home Appliance sales data drilled down to Geography Region Country |
Now the one thing you might have noticed when using this feature is that the list of available dimensions (entities) that are available in the list can become overwhelming at times if you are utilizing cubes that include many dimensions and fact tables (measure groups). It is quite common that when cubes are setup this way that not all of the dimensions are related to each fact table. Like if we look at this dimension usage design you can see that there are 14 dimensions and 7 measure groups and that there are quite a few instances where the dimensions are not related to the measure groups. This can produce odd results based on how the measure group is setup in the cube such as the chart results above after the sales data was analyzed by machine type.
In these cases you then have to evaluate whether or not it even makes sense to include the fact tables in the same cube. That is one option of course when designing your model, another is the ability to limit the items that you present to your users to help limit the scope. The way you can do this is with the use of perspectives. A perspective is a way that you can limit the scope of items that are presented to a user to help remove the clutter of items that are available. This is subset of the cube and can then be exposed to users to select from so that they can quickly perform analysis on just the items that they need to reference. Perspectives are not an option to use for security purposes, just an option to use to filter the items for analysis.
NOTE: In Analysis Services perspectives are only available as an Enterprise Edition feature (which is also available in the Developer and Evaluation Editions) with SQL Server (2005 through 2008 R2 versions). Now with SQL Server 2012 perspectives are also available in the new Business Intelligence Edition of the product (for both Multidimensional and Tabular) as well as in the new version of PowerPivot for Excel.
Before we explore this let’s take a quick look at the other type of visualization available for analysis that is provided in PerformancePoint that allows you to cross drill, the decomposition tree.
Decomposition Tree
| Initial Report | Drill Down To menu | Final Result |
 |
 |
 |
| Home Appliance sales amount broken out by Product Subcategory | Able to Drill Down To only the related dimensions in the cube | Washers & Dryers sales amount broken out by Channel Name |
Notice anything different here? This type of report has built-in logic that only displays the related dimensions available to the measure that you are analyzing. Pretty cool.
Ok, now back to our initial issue that we saw with the Analytical Reports. If we look at the cube designer above and go to the perspectives tab we can see that three perspectives (IT Machine, Inventory, and Sales) have been defined for particular areas to focus in on the items that are relevant for their analysis:
So if we go back into PerformancePoint and if we setup our data source to reference the perspective instead of the cube we can then design our Analytical Reports off that data source. In doing so we will then be able to limit the items presented to our end-users just like the decomposition tree does so that it only displays items relevant for analysis.
Here is an example of the chart initially displayed and the Drill Down To list based on a reference to the Sales perspective instead of the Operation cube in our Analysis Services database.
Now instead of being presented with all 14 dimensions we only see 11 of them. In this case the Scenario dimension is setup in the Sales perspective and that is needed to evaluate the Sales Quota data. We can see that in the decomposition tree that it filtered that dimension out. Now you have the ability to control what is available for users by the use of perspectives. These can also be used with out tools such as Report Designer, Report Builder, Excel, etc. When users connect to your database they can select from a drop-down list and connect to a perspective if you have them defined and also if you are using a Edition of SQL Server that supports this.
As I mentioned in the Note above, in SQL Server 2012 perspectives are available now in the Business Intelligence Edition as well as Enterprise. You can also defined perspectives now in the new release of PowerPivot for Excel as well.
When you are in the PowerPivot Window you will need to enable the Advanced Mode option in the file menu and then you will see the Perspectives button in the Advanced tab of the Ribbon. This will only be active if you have data imported into the PowerPivot Window. These PowerPivot files can then be utilized in PerformancePoint and I highlighted the new features in a previous posting (I did not mention perspectives though…) – Using PerformancePoint Services (PPS) with PowerPivot SQL Server 2012 RC0.
Summary
As you have seen it can be very helpful to limit the scope of items presented to a user for analysis. As cubes get larger and more entities are added for analysis along with fact tables it can become quite overwhelming to end-users. We can help out the users by providing perspectives which can provide a limited scope of items that are needed for analysis and that are related. This can be very helpful and simplify things for end-users and can reduce confusion. I would highly recommend that before you dive into creating lots of Analytical Reports that you define perspectives in your models. The reason for this is because you cannot change the data source an Analytical Report is associated with once you have created it.
Happy analysis!
Dan English's BI Blog 