Automatically create data dictionary for your Power Pivot model

By Avichal Singh www.linkedin.com/in/avichalsingh

There are many reasons why you would want to document your Excel Power Pivot or SSAS Tabular model in an effective manner:- to better understand your model yourself as it moves from simplistic to advanced, facilitate the use of your model by other users, ability to transition model development to another person and a few more.

Diagram View is good, but you can do better . You could automatically build a data dictionary – listing all the Dimensions, Attributes, Measures and their Description – and maintain it with almost no effort at all. Watch the video and read the rest of the article to find out how.

Diagram view is good…

But Data Dictionary is better! Make use of them both

Download files used in this post:

We would use Dynamic Management Views (DMV) to pull the schema information from our models, especially the Description field. Have you been using the Description field? Now would be a good time to start.

In Power Pivot, you can right click any element (Table, Column or Measure) and click ‘Description’

This would allow you to enter the Description for that element

Why this is better:

* Reuses the ‘Description’ field that you have hopefully already been using or can easily start using as you are building your models

* Allows you to quickly scan or search through the Dictionary. In Power View you can see the description as you hover over elements, but that is a lot of hovering if you are trying to study the model! Also the hover over descriptions are not available for Excel 2013 Power Pivot models (seems like a step backwards). Another scenario, where hover over descriptions are unavailable, is when you are hosting the Tabular Model on an SSAS server and your users are connecting via Excel.

* Documents your model for use within your BI team (even if it’s just one person, you ) and for use by the users who connect to the BI model

* Publish the Data Dictionary along with screenshot of Model Diagram View and your users have the best of both worlds

We will show you how you can do this for (click to jump to the section):-

• Tabular Model on SQL Server Analysis Server (SSAS)
• Excel Power Pivot Model

Create Data Dictionary: SSAS Tabular Model

Follow the steps below, if your model is already hosted on SSAS or you have it in Excel but have access to a SSAS Server.

1. If you happen to have your model in Excel, but have access to an SSAS Server, you can “restore” an Excel PowerPivot Model into SSAS quite easily. Just right click on your server in SQL Server Management Studio and select ‘Restore from PowerPivot…’ then select your Excel Power Pivot file and step through the options.

2. Open a new Excel file, open the PowerPivot window and create and import the required DMV queries one by one, connecting to your SSAS Server. (See DMV Queries.txt)

i. Perspectives (read more about Perspectives)

ii. Dimensions

iii. Measures

iv. Attributes

3. In Power Pivot Create relationships from…
[Dimensions], [Measures], [Attributes] ==to==> [Perspective] table
…so I can easily filter on elements for only one Perspective

4. I also created a simple VISIBLE = TRUE, FALSE dimension (I just copy pasted the data into PowerPivot) and create relationships from…
[Dimensions], [Measures], [Attributes] ==to==> [Visible] table
…so that you can easily filter to only see elements that are actually visible to the end user.

Your data dictionary model you should look like this:-

5. Add simple measure to [Dimension], [Measure] and [Attribute] tables so the [Perspective] and [Visible] filters take effect. Simple RowCount does the trick.

dimensionCount := CountRows(‘Dimension’)

To get fancy I created a measure using FirstNonBlank to return MeasureDescription (see Rob’s blog post demonstrating FirstNonBlank)

DescriptionDimension:=FirstNonBlank(‘Dimension'[Description]
,IF([dimensionCount]>=1,1,Blank())
)

Your data dictionary is ready to use!

Bonus Points: I will not be covering cover this in the article but if you wanted to, you could pull Dimensions, Attributes, Measures all in a single Table and display them more elegantly. I could not figure out how to do a UNION in a DMV query but you can certainly transfer the tables to SQL and then write a SQL query with UNION clause.

Create Data Dictionary: Excel Power Pivot Model

The preferred approach is to import (Restore from PowerPivot) your Excel Power Pivot model in SSAS (previous section shows you how) and follow the steps in the SSAS Tabular section. If that is not an option, read on.

All steps remain the same except the first part of how to query the Excel+PowerPivot model; which I cover in the steps below. But after that point, follow the same steps as for SSAS in previous section.

1. Install DAX Studio (daxstudio.codeplex.com)
Some notes on usage: Worked fine with Excel 2010, read notes on the Download page about Excel 2013. I did not try it with Excel 2013. Thanks to the team who created the tool, including Marco Russo and Darren Gosbell.

2. Open your Excel Power Pivot model (the one you are attempting to document) and then from the Add-Ins tab on the ribbon, open DAX Studio

3. DAX Studio should automatically connect to the Power Pivot model in your workbook. If that does not happen and you see the error message below, read this discussion. I had to make sure I had a measure defined and that I created a Pivot Table in an Excel sheet using that measure. After that it connected to my Excel Power Pivot model without any issues.

If you get an error as below try the steps specified above

4. In the DAX Studio window, if needed change Perspective to Model and Output to New Query Sheet.

DAX Studio happily connected to the Power Pivot model (yes, it says Microsoft_SQLServer_Analysis but it is in fact connected to the Excel Power Pivot model). Change the elements shown by arrow.

5. Execute the queries (See DMV Queries.txt)  one by one, the results would be output to a new excel sheet

6. Open a new Excel file and copy paste the output to this new file and then add these to your Power Pivot model (Power Pivot > Create Linked Table). You should end up with four linked tables in Power Pivot: Perspectives, Dimensions, Measures, Attributes.

7. Now follow the SSAS steps in the previous section starting with step 3.

As you can see this is slightly tricky to do in Excel, but well worth the effort. Share the newly minted Data Dictionary along with a screenshot of the Model Diagram View with your team and users.

Disclaimer: The opinions and views expressed in this post are those of the author and do not necessarily state or reflect those of Microsoft