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.

SQL SERVER - Boosting User Experience with Analysis Services Perspectives - Notes from the Field #116 Bill%20Anton

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:

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…

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

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…

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

11 measure groups, 21 dimensions

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

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

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)

, , , ,
Previous Post
SQL SERVER – How to Start SQL Server Service Without tempdb?
Next Post
SQL SERVER – 2016 – Check Value as JSON With ISJSON()

Related Posts

3 Comments. Leave new

  • I one time observed that by making perspective, cube size was increased so just wanted to ask u first of all that is
    this true ? Because I found this was in very small cube and size increases in very small amount. If this is so then for large cube like 5 GB or say 7 GB, to make perspective will become very costly.

    I mean to say it is really case to case basis which/how many number of dimensions and measures you want in
    perspective and based on that size differs for cube.

    e.g.
    If we have SalesOrderBacklogSpend Cube which contains all the information of Sales-Orders-Backlog-Spend in whole one picture and in turn it contains lot of mix of dimensions and measures (sometimes the same attributes
    duplicates).

    Now user wants all this in different module like the concept of perspective because it requires them lot of efforts to search. And please note, we can not separate the cube as there are some business level relationships between this 4 entities and need to process it together.

    Size of this SalesOrderBacklogSpend cube is around 6 GB. Now if we make perspective by separating only requires dimensions and measures in Sales/Orders/Backlog/Spend then there will be huge size difference.

    So in this kind of cases we should not use perspective.

    Kindly share your thoughts for the same.

    Warm Regards
    Anuj Soni

    Reply
    • Perspectives are metadata-only; a few additional lines of XML to the cube structure. Creating a perspective does not create additional copies of the data and should not increase the size of a cube (or tabular model) by more than a few kbytes (corresponding with the metadata).

      Reply
  • Recently working on SSAS Perspective – I ran into a weird issue. A little background here: –

    Dynamic Set A –> CUBE Calculation B –> Cube Calculation C.

    All of the above are defined in the SSAS CUBE. I am exposing Calculation C – along with a bunch of dimensions in my perspective. The perspective is accessed through Excel Pivot Table.

    What I noticed is that – the measure C shows up as blank in Excel. I tried to capture the query sent over to SSAS server through Excel – using Profiler. Upon running the query – I noticed that – the FROM clause in the query uses the name of the perspective 0 and the query does not give back any values for Calculation C. But when I replace the name of the perspective with the name of the base CUBE – the query works fine and also returns the correct value for Calculation C.

    This was a new behavior for me – with SSAS Perspectives. Is it due to the fact that the dynamic set and the calculation B are not exposed – or may be something else is happening here ?

    Reply

Leave a Reply

Menu