Boosting Developer Experience with Analysis Services Perspectives

Perspectives are typically used to simplify and improve the user experience, but a lesser known fact is that they can also simplify/improve the developer experience.

Problem

Developing tabular models that contain a lot of tables can be a real nightmare. In most enterprise solutions based on a dimensional model, it is not uncommon to end up with 30 tables (5+ fact tables) in a single tabular model. Unless you’re working on a fancy 4k display with super high resolution, you’re going to burn quite a bit of time searching for specific tables – especially in the diagram view (but also in the grid view).

Here’s an example from a recent client:

image

To be sure, we didn’t just throw all of this into a tabular model on day 1. It took a few weeks to get here, but that’s mostly because we started from scratch and worked our way through the bus matrix: 1 business process at a time. If you already have a solid dimensional data models, it is completely possible for an experienced developer to build out the model you see above in just a few days. However, one thing that will become clear – painfully clear – is that development slows down as the number of tables in the model increases.

Solution

One way to reduce this pain is to create a separate perspective for each “star” in the dimensional model.

Note: a dimensional model is made up of stars: a fact table (the center of a star) surrounded by related dimension tables (the points of the star). In theory, each star supports the analysis of a single business process. In practice, however, there are certain scenarios where several fact tables / stars are needed to support the analysis of a single business process (usually due to performance). With that understanding, when I refer to a “star” I’m referring to the tables that support the analysis for a single business process – which may in fact be multiple stars.

The magic begins as soon as you notice the dropdown in the Visual Studio header next to the words “Select Perspective”…

image

Below are some examples from the Adventure Works database…

image

image

image

In most cases you should be doing this anyways – creating perspectives for each business process to boost the user experience when working with self-service tools (e.g. Power BI Desktop, Excel). However, most developers (myself included) have a tendency to do all the UX-focused stuff at the end of the development cycle not throughout – and therefore are unable to take advantage of the hidden benefits.

On the other hand, adding a separate perspective for each “star” can result in having too many perspectives and end up confusing the users rather than improving their experience. For example, when a user connects to a Tabular database that contains a model and a bunch of perspectives using Excel, they see something like this:

image

Or if they connect to that same Tabular database using Power BI Desktop, they see something like this:

image

Neither of those are very helpful.

In an enterprise solution, you might have 10+ perspectives – some of which might have similar names – and without a clear description it will be confusing for a user (especially new users) to know which perspective is the correct one.

A better idea is to add a description/annotation property for each perspective where a more helpful text description can be provided indicating the business process, common types of analysis, etc. This would provide a metadata hook for self-service reporting tools (e.g. Excel, Power BI) as well as enterprise data cataloging solutions such as Azure Data Catalog.

Another helpful feature would be the ability to set the visibility of a perspective – or if you’re more familiar with the Tabular vernacular: “hide it from client tools”!

By the way, if you haven’t seen the marketing videos and/or played w/ Azure Data Catalog yet, you are missing out on a really important component of Microsoft’s EIM (Enterprise Information Management) strategy.

5 thoughts on “Boosting Developer Experience with Analysis Services Perspectives

  1. 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 SQL SErver 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 ?

    1. That’s an interesting issue and admittedly one that I’ve not come across before. In most cases, I try to avoid using named sets (dynamic or otherwise) in calculated members as I’ve been bitten a few times over the years with terrible performance.

Leave a Reply