Introduction
In this article, we will be looking at a feature of SQL Server Analysis Service (SSAS) OLAP Cube that is SSAS Perspectives. We discussed creating SSAS OLAP Cubes in a previous article: OLAP Cubes in SQL Server. In an SSAS OLAP cube, there can be a large number of measures, dimensions and dimension attributes. The following screenshot is the star schema for the selected example that was created from the AdventureWorksDW sample database that can be visible at Data Source View.
When the above schema is converted to an OLAP Cube, you will see a lot of measures and dimensions as shown in the following screen.
This is what you will see when an OLAP cube is accessed from the Microsoft Excel pivot tables.
Measure Groups
When an end-user needs to analyze data from an OLAP cube, he might not need all measures to complete his tasks. Further, he may need only selected dimensions and its attributes.
One of the easiest ways to filter objects in an OLAP cube is, by using the measure groups. Typically, for every fact table, one measure group is created. In the above OLAP cube, there will be three measure groups such as FactInternetSales, FactFinance, and FactProductInventory. These three measure groups will be visible at the client tools such as Excel and SQL Server Management Studio in order to perform filtering.
The following screenshot shows the filtering with measure groups in SQL Server Management Studio.
Similarly, Measure Group filtering can be done using Microsoft Excel as shown in the below screenshot.
Once the correct measure group is selected, relevant fact tables along with the related dimensions will be filtered so that end users can work on limited objects.
However, this cannot filter dimensions and their attributes and it can only filter fact tables.
SSAS Perspectives
There is a more extended feature in SQL Server Analysis Service named Perspective to filter any objects in SSAS OLAP cube. In simple terms, SSAS Perspective is a view of OLAP cubes where you can filter for any objects in the OLAP Cube.
Let us create an perspective in a SSAS OLAP Cube and look at the usage of them in detail.
SSAS Perspectives can be created by opening in the cube in visual studio as shown in the below screenshot.
When you clicked the New Perspective option, a new column is added as shown below.
In the above screen, users can deselect unwanted measure groups and dimensions. An important feature in SSAS Perspective is the ability to create different levels. In a selected measure group, you can choose different measures as shown in the below screenshot.
In the above perspective, Revision Number, Unit Price, Freight are removed from the newly created Perspective.
Further, to select and deselect dimension, you have the option of selecting dimension attributes. For example, there are attributes to support different languages. Multiple languages may not be required for all the users. Therefore, you can deselect those attributes so the unnecessary attributes are not visible to all users.
In the above Product Dimension, except for the English language, other language descriptions and product names are removed.
You can create any number of SSAS Perspectives with unique names as shown in the below screenshot.
When creating Perspectives, you have to decide what objects to be chosen. Apart from Measure groups, Measures, and Dimensions, there are few other objects in the SSAS. Those objects can be managed from the Perspectives too. Those OLAP Cube objects are
- Hierarchies
- Named sets
- KPIs
- Actions
- Calculated members
Another important point in the SSAS Perspective is that it is only available with the Enterprise Edition of the SQL Server. This means that you need to spend a lot of money to use perspectives. If you are using the standard version of the SQL Server, you have no choice but to use Measure groups option to filter data.
Like other options, you can modify and delete the created SSAS Perspective when needed.
Performance
As we discussed at the start of the article, perspectives are views of the OLAP cube. This means Perspectives are only metadata. This means there is no performance impact on the increasing number of perspectives. Though cube processing is a resource incentive task, adding perspective will not add CPU or IO cost. In fact, there is no need to re-process the cube, if you just add perspectives to the cube. Further, when there are changes to the perspective, you do not need to process the cubes.
Security
There is a myth among the SSAS designers as well as among the users that SSAS perspectives can be used as a security tool. It is a completely false assumption. The security of an OLAP cube is handled at the cube level with measures and dimensions. Those security configurations are inherited from the perspectives. Apart from that, there is no security configuration in Perspectives.
Connectivity
After creating Perspectives, the next important aspect is how to connect to them. Let us look at how to connect to a Perspective using SQL Server Management Studio.
As shown in the below screenshot, by clicking the marked button, it will draw the list of available perspectives so that users can choose one of them.
The first entry (Adventure Works DW2017) is the entire cube and the rest of them are created perspectives. It is important to note that you can work on a single perspective only at one time. This means that you cannot link objects between perspectives. To avoid unnecessary confusion, it is better to create perspective considering the usages. However, even if there is a lack of objects, you can add necessary objects to the perspective later when needed as it does not have any impact on the performance of the OLAP cubes as it is a metadata change only
Most of the end-users are connecting to the Cubes via Microsoft Excel. Therefore, let us see how perspectives can be accessible via Microsoft Excel.
There are two ways of making a connection to an OLAP cube from excel. One of them is, making the connection from Microsoft Excel itself. When making a connection to the OLAP cube, it will request the perspective as shown in the below screenshot.
As shown in the above screenshot, the user will know whether he is connecting to the Cube or the Perspective.
When a user wants to connect to Excel from SQL Server Management Studio, it will ask for the perspective that you want to connect as shown in the below screenshot.
After connecting to the SSAS perspective, the user can work on the selected objects from the selected perspective. You can see the connection properties from the Microsot Excel as shown in the below screenshot.
In the connection string as marked in the above screenshot, you provide the cube name (cube name is PerspectiveCube in this example) and the English is the perspective Name as shown in the Command Text above.
Conclusion
SSAS Perspective is a view for OLAP cube where you have the option of filtering different types of objects in the cube. With this option, uses can easily manage OLAP cubes. Since perspectives are metadata, there is no impact to the cube performance. In addition, you can add OLAP cube objects to the perspective when needed as it does not impact cube performance. Further, it is important to stree that perspective is not a security mechanism in the OLAP cubes. When connecting to Excel or SQL Server Management Studio, users can provide the perspective name and connect to the relevant OLAP cube objects.
View all posts by Dinesh Asanka
- Testing Type 2 Slowly Changing Dimensions in a Data Warehouse - May 30, 2022
- Incremental Data Extraction for ETL using Database Snapshots - January 10, 2022
- Use Replication to improve the ETL process in SQL Server - November 4, 2021