By: Scott Murray | Comments (1) | Related: > Analysis Services Development
Problem
What are SQL Server Analysis Services Perspectives? What do they do and how can they be used? Check out this tip to learn more.
Solution
When working with data warehouses and in many theoretical fights between the Inmon and Kimball camps, you often encounter the term data marts. Data marts generally provide a subset of data within the data warehouse. They are designed to allow end users to focus on just a specific area of a data warehouse. In a like manner, perspectives provide the ability to create a data mart "like" subset of a SQL Server Analysis Services (SSAS) cube. This subset acts and appears to many users as if it was an actual cube. However, it is actually just a predefined set of selected cube objects that are contained within the perspectives selected. Also, perspectives use the exact same security as its parent cube, and is NOT in any way a security mechanism. If a user knows the backend attributes and dimensions, they could easily write their own MDX to query data not shown in a perspective. Perspectives are ultimately most helpful in instances where you may have a very large cube with multiple fact tables, dimensions, and measure groups, but you would like to limit the end users exposure to just a subset of dimensions, attributes, KPIs, actions, and measure groups. Often narrowing the cube alleviates some of the confusion and hesitation caused by a large complex cube and encourages adoption and use.
Using SQL Server Analysis Services Perspectives in a Cube
To add a perspective to a cube, we first must decide what objects to include in our perspective. In order to implement a perspective, we will again use AdventureWorks 2012 SSAS database; the database and SSAS database are available on Codeplex at: http://msftdbprodsamples.codeplex.com/releases/view/55330.
Once you download and install the SQL Server database and SSAS database, open up SQL Server Data Tools (SSDT) and then open the AdventureWorks SSAS database.
Once the perspective tab is selected from the tab bar, the perspective maintenance screen appears as noted below. This screen allows for the creation of a new perspective or the deletion of an existing perspective.
Clicking on the New Perspective button, as noted above, adds a new column with the ability to add a perspective name on the first row and then select a default measure on the second row, all displayed in the below screen print.
After specifying the perspective name and default measure, you can select the individual items to be included in the perspective by clicking the appropriate check box. The objects are grouped by object type. As shown below, initially many of the objects are expanded to their lowest level. You can "check" measure groups, dimensions, KPIs, actions, and calculated measures.
Of course, you can use the plus / minus buttons on the left side of the window to toggle the object folders opened and closed. Furthermore and as shown next, for those objects that have hierarchy structures, such as measure groups and dimensions, a red box appears around the parent object if not all the underlying objects are selected.
Finally, when all the appropriate objects have been selected, you must save the perspectives. The save process is all or nothing, meaning that if you make changes to multiple perspectives, the clicking the save button will save all perspective changes.
In order to be able to utilize a perspective, the cube changes must be saved, and then the cube must be processed. Once the cube is processed, the perspective should then be available for end users to view, query, and use as a basis for pivot tables in Excel. To follow this example, open Excel, navigate to the Insert tab, and click on the Pivot Table button. Finally, select the Use an external data source radio button and then click Choose Connection as shown below.
Next, Click the Browse for More button to enter our new connection and then on the next screen, select New Source. Both of these steps are displayed subsequently.
Next, on the Data Connection Wizard, select Microsoft SQL Server Analysis Services
Then, as illustrated below, enter the server name where the SSAS database resides.
Finally, the Select Database and Table screen appears (the screen's title is a bit of a misnomer as you actually can select a table or cube as noted in the second part of the screen print below). Also, notice in the screen print how the Type actually shows CUBE or PERSPECTIVE. Our newly created Internet Sales perspective appears in the list. Selecting this perspective and then clicking Next brings up the Save Connection File window where Finish button is clicked.
To return the Perspective back to Excel, we can finally click Ok on the Create PivotTable screen as outlined below.
Finally, we can use the perspective we created. Certainly, you will notice only the objects we selected during perspective creation are available in the pivot table, as shown below.
As illustrated in the next screen print, if you use the SSDT cube browser, you also have the option of limiting the cube browser to a specified perspective instead of the entire cube. However, if you attach to and navigate through the SSAS database in Management Studio, the perspectives do not show up in the object explorer.
Conclusion
SSAS Perspectives are a great tool to reduce the complexity of large and convoluted cubes. A perspective gives a cube designer the ability to create a subset of objects from an original cube. The perspective can include: measures, measure groups, dimensions, actions, calculations, and KPI's. However, perspectives fully inherit all security and permissions from the original cube and thus should not be used for limiting access or in a security capacity.
Next Steps
- Once you add a perspective, you will want to deploy it using this tip: http://www.mssqltips.com/sqlservertip/1883/using-the-sql-server-analysis-services-ssas-deployment-wizard/
- Dimension security is still important within perspectives: http://www.mssqltips.com/sqlservertip/1844/sql-server-analysis-services-ssas-dimension-security-stored-procedures/
About the author
Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
