SQL SERVER – Boosting User Experience with Analysis Services Perspectives – Notes from the Field #116

[Note from Pinal]: This is a new episode of Notes from the Field series. When I was new to SSAS it took me quite a while to understand the various terminology of SSAS. The reason for the same was, I was from networking background and not familiar with many of the analytical terms. I am sure if you are not familiar with SSAS the very first line of this blog “Analysis Services Perspectives are a feature that enable the designer to control which portions of the data model are exposed to the users.” may be confusing. It is alright if you are not familiar with the concept, the goal of this blog post is to understand what is perspective and how it helps user experience.

In this episode of the Notes from the Field series I asked SQL Expert Bill Anton a very crucial question – What can we do to boost user experience with perspectives in the Analysis Service. ? Bill was very kind to answer the questions and provides plenty of information about how a novice developer can work with SSAS.

Perspectives are a feature in Analysis Services that enable the designer to control which portions of the data model are exposed to the users. When designed and implemented correctly, perspectives can greatly improve the experience for your end-users.

Dimensional models (when designed correctly using conformed dimensions) are incredibly intuitive to the business users – much MUCH more so than an OLTP data model designed in the 3rd (or higher!) normal form – which is just one of the primary reasons they continue to be found in the majority of successful business intelligence implementations.

However, as the dimensional model continues to evolve, especially true for large enterprises with multiple divisions, it isn’t long before that simple and highly intuitive dimensional model morphs into a raging hot mess of complexity feared by business users across the company.

Consider the business analyst in the sales department who is responsible for creating reports and analyzing trends related to sales performance. It’s likely this user only needs access to the 20 or so tables and calculations related to sales. Unfortunately, the dimensional model also contains tables for production, warehouse, distribution, human resources, finance, and several other lines of business. For a large enterprise, we’re easily looking at 100+ tables. This user will have to scroll through these 100+ tables (made up of potentially thousands of attributes) just to find the field (or fields) he or she would like to drag out into the pivot table or into the Power BI report/dashboard.

Note: while it’s unlikely that the entire dimensional model across all divisions and lines of business for a large organization would be shoe-horned into a single cube, it can (and sometimes does) happen.

Don’t do that to your users! It’s cruel, unnecessary, and makes for a poor user experience.

A better approach is to create perspectives that include only the relevant subset of the entire dimensional model for the sales analysts. However, keep in mind you’ll probably be asked to do the same for the warehouse analysts and finance analysts once they hear what you did for their buddies over in sales. But hey, that’s a pretty small price to pay in return for the love and admiration of your users!

Creating Perspectives

An Analysis Services multidimensional database is composed of one or more cubes – each of which consists of dimensions, attributes, measure groups, measures, etc. Each cube can have one or more perspectives. And in each perspective, the designer can choose which dimensions, attributes, measure groups, measures, KPIs, and/or actions to expose to the users. The same logic applies to Analysis Services tabular databases, except that a tabular database can only have a single model. Other than that the rest is the same… that single model can have one or more perspectives, and in each perspective, the designer can choose which tables, columns, calculations, and KPIs to expose to the users.

Here are 2 links that walk you through the process of creating a perspective for each type of Analysis Services database:

• Creating perspectives for multidimensional cubes
• Creating perspectives for tabular models

Before diving in and creating a bunch of perspectives, I strongly advise you to include your end-users in the discussion/decision about which objects to include in the perspective(s).

Using Perspectives

The details for how to use a perspective depends on the reporting tool(s) in place and how the connection to Analysis Services is established. In most cases (e.g. Excel, Power BI, SSRS) you’re going to take the same steps as if you were connecting directly to the Analysis Services multidimensional cube (or tabular model) only instead of specifying the name of the cube (or tabular model), you’ll specific the name of the perspective.

Here’s what it would look like for a user connecting to a perspective via Excel…

At this point in the connection process, the user has the choice to connect to the cube (current selection) or one of the 5 different perspectives.

Note: A more thorough/thoughtful implementation would include detailed descriptions for each perspective to help users determine the most appropriate perspective (or cube) to connect to. Unfortunately, it seems the BI developers over at AdventureWorks were a bit lazy.

If the user connects to the cube, they will see all measure groups, all dimensions, and all calculations…

11 measure groups, 21 dimensions

However, if they connect to the Sales Summary perspective, they only see a small subset…

3 measure groups, 5 dimensions

I don’t know about you, but I know which one I’d prefer to work with day in and day out.

Perspectives Are Not Security!

No blog post on Analysis Services perspectives is complete without the obligatory warning about perspectives and security.

So let’s hold hands and say it out loud…

Perspectives. Have. Nothing. To. Do. With. Security. Period.

Just because an attribute or measure isn’t exposed through a perspective, doesn’t mean the user can’t access that attribute or measure. Security is managed via “Roles” …not perspectives. A user is assigned to a role (sometimes multiple roles) which in turn grants (or denies) access to certain pieces of the multidimensional cube (or tabular model). In fact, a user can’t event connect to a perspective if they don’t have access to the cube (or tabular model) under which the perspective has been defined.

If you want to hide information from a user, the proper way to do it is through a role, not a perspective. In other words, please don’t include a salary attribute/column in the employee table and then try to prevent users from seeing it by simply leaving it out of the perspective you create for them. That’s a good way to find yourself unemployed.

Conclusion

Perspectives are a useful feature available in Analysis Services that can greatly improve the user experience when designed and implemented correctly with very little effort. Every user doesn’t need to see (nor do they likely care about or even want to see) every single table, attribute, measure, and KPI in the dimensional model.

Don’t forget – perspectives are not (and never were) intended as a means for implementing security. Just because a perspective doesn’t include an attribute or measure, doesn’t mean a business user can’t figure out how to get to it – business users are crafty animals not to be trusted!

If you want to get started on performance monitoring with SSAS with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (https://blog.sqlauthority.com)