How To Quickly Get A List All DAX Formulas From Your Excel 2010 PowerPivot Data Model

It is good practice to keep track of all the formulas you write in a PowerPivot Data Model. I suppose you’d even be required to document the formulas at work, for the audit trail. I was working on a rather large data model, and was manually copy-pasting each and every DAX formula into an Excel sheet; and updated the sheet every time I tweaked a formula. It was frankly a very annoying necessity, and I wanted to remedy the situation. Microsoft introduced the Model Object in Excel 2013, allowing users to access and control PowerPivot using VBA, but there is no straightforward means to programmatically access the Excel 2010 add-in. In this post, I describe a quick way to list all the DAX formulas in your PowerPivot for Excel 2010 Data model.

I scoured the internet and learned about a brilliant method that establishes a connection with the PowerPivot Model’s internal schematics, and quickly lists all the DAX formulas from your PowerPivot model in an Excel Table. PowerPivot stores all the details about the Data Model in a bunch of hidden tables known as Dynamic Management Views (DMV). We will look at how to establish a connection to the DMV, and query the data we are interested in.

There are third-party Add-ins like DAX Studio that can visually let you access these tables and list them on your workbook. This post is for the strugglers who are stuck with PowerPivot on Excel 2010 at work, and do not have access to those third-party-add=ins.

Here is what you need to do

1. Create a very simple Pivot Table from your PowerPivot Model by checking just one measure from the field list.
   • Note that a numerical value needs to be calculated by the pivot table.
   • Ignore this step if you already have a PowerPivot Pivot Table in the workbook.
2. Double Click any one of the cells that contain a number, in the Pivot-table.
3. This initiates what is known as a Drill-Through.
   • Excel creates a new sheet, and displays all the data that went into that particular cell in an Excel Table.
4. This new Table establishes a connection to the DMV.
   • The pivot table created earlier in Step 1 is now redundant; you may delete it if you prefer to.
5. Right click anywhere on that table; Click Table; Click Edit Query.
6. A dialogue box pops up. In the Command Text Text-box, replace the text in there, with any of the following DMV queries.

DMV Queries

Here is a list of all the queries that I curated for you.

1. List all DMV’s that are available in the current version of PowerPivot:

   SELECT [SchemaName]
   FROM $system.discover_schema_rowsets
   ORDER BY [SchemaName] ASC
   

2. List all Tables in a Model along with their Date-Created and Date-Modified:

   SELECT [TABLE_NAME], [DATE_CREATED], [DATE_MODIFIED]
   FROM $system.DBSCHEMA_TABLES
   WHERE [TABLE_SCHEMA] = 'MODEL'
        AND [TABLE_TYPE] = 'SYSTEM TABLE'
   

3. List all the Columns in the Model along with their Table Names:

   SELECT [TABLE_NAME],[COLUMN_NAME]
   FROM $system.DBSCHEMA_COLUMNS
   WHERE [TABLE_SCHEMA] = 'Model'
        AND [COLUMN_OLAP_TYPE] = 'ATTRIBUTE'
   

4. List all Calculated Columns and their Formula:

   SELECT DISTINCT [Table],[Object],[Expression]
   FROM $system.discover_calc_dependency
   WHERE Object_Type = 'CALC_COLUMN'
   

5. List all Measures and their Formulas:

   SELECT DISTINCT [Table],[Object],[Expression]
   FROM $system.discover_calc_dependency
   WHERE Object_Type = 'MEASURE'
   

You do not have to repeated the drill-through process to create subsequent tables. Just copy the first table that you created, over to another location, and alter its command text. Keep going on until you have all the tables that you need. The last two queries are the most important for documenting formulas. The other queries may interest a few readers.

If you have some interesting DMV queries of your own, do share them with me.

The best part of this technique is, when you hit the Refresh All button in the Data Tab of Excel, these tables get updated with the latest formulas automatically. It not only lists the formulas for you, it also keeps them up to date.

Downloads

I understand that copying the queries from the code box can be annoying sometimes, so I saved these queries in a text file for you.

Reference

I used a combination of techniques described in the following articles, and added my own touches:

1. Querying PowerPivot DMVs from Excel by Chris Webb
2. Other (Better) Ways to Get All Measures As Text by Rob Collie

A few years ago, the term Business Intelligence was privy only to the Information-Technology savvy bunch. Microsoft set out with a bold ambition to bring Business Intelligence to the masses and I’d say they have done an amazing job!  They introduced PowerPivot as an add-in for Excel 2010 and then built it right into the application in Excel 2013. With the DAX formulas resembling the Excel formulas, we have been using all our lives, I believe setting up a Data Model in PowerPivot could never be any easier. Business Intelligence is a valuable skill to have. I have been using it at work for a year now, and I intend to share my experiences here. This is the first of hopefully many PowerPivot themed posts here at Struggling To Excel.