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:

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”…

Below are some examples from the Adventure Works database…

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:

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

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.