PowerPivot – Perspectives
Many times when developing larger Analysis Services cubes you may find it necessary to use Perspectives to logically split up your cube so users can find what they want faster. A perspective is similar to a TSQL view because it will take a larger object (the cube) and filter out objects that you want to split up. It cannot however perform business logic or filtering of data like a view can. So the case for perspectives would be if you have a cube for your entire company but you don’t want HR to have to filter through sales data to find what they need, and you don’t want the sales team to have to filter through HR data. Having perspectives would save the user valuable time in finding the data they need without having to sift through other’s data.
**WARNING** Perspectives are not for security. It is only a usability feature and does not prevent a user to see the entire cube.
Now that you have a little background on what Perspectives are in Analysis Services let’s talk about how you can now use them in PowerPivot. When creating PowerPivot reports Perspectives appear similar to Measure Group selections appear in Analysis Services. You will find a dropdown box that allows you to select the desired perspective, assuming you have already created one. If you have not created one or more perspectives then the dropdown box is not visible.
To create a perspective open the PowerPivot window. I’m going to assume you know how to get started with PowerPivot and not describe connecting to a data source and importing tables. Select the Advanced tab in the Office Ribbon. What you don’t see an Advanced tab? That’s because it’s hidden. Remember PowerPivot is intended for end user development so some advanced functions are hidden. To unhide Advanced Mode click on the file menu in the top left of the window and select Switch to Advanced Mode. This will give you the new Advanced tab.
Navigate to the Advanced tab and click the Perspectives button.
The Perspectives dialog box will open and you can now create a new perspective by clicking the New Perspective button. When the new perspective is creating you will place checkboxes next to each object you wish to include. You can select entire tables or just certain columns from table. The buttons above the new perspective from left to right are. Delete, Rename, and Copy which will allow you to copy what is in one perspective to a new one.
After you hit OK the perspective is done and you can use it when creating PowerPivot reports in Excel. Remember the perspective will appear in the PowerPivot Field List as a dropdown box like this:
Read some of my previous PowerPivot Denali Blogs:
