In this short post, I show how we can integrate a data dictionary using DMV queries within an SSAS tabular model and why we should do so.

Why should we have a Data Dictionary within our model?

The more complex a model becomes the more documentation we need to have about it. The more measures and complex formulas we have in a model the more calls/questions we have from end-users and thus the less time we can spend on getting projects done.

The best way to reduce the time spent on support calls is to provide the end-users with the clearest reports possible.
It is quite common to have an SSAS model with a few hundred measures and this is almost impossible to remember what each measure is doing what either for the developers or for the end-users/customers.

One solution could be to share a document with the end-users with the list of the measures and their descriptions.
But this document is likely to end up in a shared folder with another hundred of documents and not being used at all.
Why sharing another document when we can directly have all the data dictionary available within a report itself.

A simple way to document an SSAS model is to use the out of the box description field of a measure and to fill it by whatever best describes it and then display it.

A few hundred measures to describe might seem a lot of work and I believe that we don’t need to describe every single measure. I usually describe the most used measures and the most complex or ambiguous ones.

What is Data Dictionary?

This is simply a sort of measure reference table that contains the list of all the measures available within a model with their descriptions.

This measure reference table should be made visible to the end-user on a specific page of a report.
So when they need to know more about a measure they can easily find the needed information in this measure reference table.

I used to have a lot of calls/questions from end-users but since I put this Data Dictionary Using DMV Queries in place the number of calls has significantly decreased.

What are DMV queries?

Analysis Services Dynamic Management Views (DMVs) are queries that return information about model objects, server operations, and server health. Read more about DMVs here.

How to create a data dictionary using DMV queries

To create a data dictionary using DMV queries we first need to set up a connection to the tabular model itself.
Then we can use TMSCHEMA_MEASURES schema browser which provides information about the Measure objects in each table.

Add an SSAS connetion to the SSAS model itself

Copy paste the DMV query

select 	        [Name]
,		[Expression]
,		[IsHidden]
,		[DisplayFolder]
,		[Description]
from 	$SYSTEM.TMSCHEMA_MEASURES

Here is the output of the DMV query:

If we want to return all the information of each measure we can simply select all the column “*”.

select 	*
from 	$SYSTEM.TMSCHEMA_MEASURES

A good practice is to only make visible to the end-users the relevant columns and to hide the more technical columns.

Display the Measure Reference Table

The golden rules to have a clear data dictionary are as follows:

• Hide the hidden measures
• Show only the relevant columns
• Don’t show the Dax Expression unless required
• Organize the measures by folder so the end-users can search measures by folder
• Validate the description of each measure with the end-users
• Add two slicer “Folder” and “Measure Name”
• Make sure to enable the search box to your slicer so users can type for a specific measure name

To improve the user experience we can also add an action button on each report page to redirect the users to the measure reference table page.
And vice versa add a “Back” button to the measure reference page so they can go back to where they were before landing to the Measure reference page.

To recap

• Data Dictionary Using DMV Queries should be added to every model
• End users or customers should be involved in the definition of the measures’ description
• Can greatly improve user experience
• Only work for SSAS model (as far as I know)