Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

SQL Server Analysis Services Perspectives


By:   |   Read Comments   |   Related Tips: > 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. 

Perspective Tabs

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.  

New 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.

New Perspective

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.

Select Objects

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.

select objects individual

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.  

New Pivot Table

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. 

Browse for More
New Source

Next, on the Data Connection Wizard, select Microsoft SQL Server Analysis Services

SSAS Source

Then, as illustrated below, enter the server name where the SSAS database resides. 

SSAS Server

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.

Internet Sales Perspective

Finish

To return the Perspective back to Excel, we can finally click Ok on the Create PivotTable screen as outlined below.

Connection finish

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.

Pivot Table Example

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. 

SSDT browser

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 


Last Update:






About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools