DMV to Extract Measures from Power Pivot

I have written articles before about how you can extract measures from a data model using DAX Studio and also using Power Pivot Utilities.  These are both excellent tools in their own right and I encourage you to read up on those previous articles to learn more about these tools. Today however I am going to share another way you can extract a list of measures from an Excel Power Pivot Workbook without needing to install either of these 2 (excellent) software products.  I often get called in to help people with their workbooks and sometimes they don’t have the right software installed for me to extract the list of measures (ie DAX Studio or PPU).  This article explains how to extract the measures quickly without installing anything.

What is a DMV

First let me explain what a DMV is.  DMV stands for Dynamic Management Views.  In short they are a set of special views that you can query using SQL code to find out information about your data model.  In effect they contain metadata about your data model.

DMV to Extract Measures

This is the SQL code I use to extract a list of Measures from a Power Pivot Workbook.

SELECT
   MEASUREGROUP_NAME as Table_Name, 
   MEASURE_NAME, 
   EXPRESSION as Formula

FROM $SYSTEM.MDSCHEMA_MEASURES

WHERE MEASURE_AGGREGATOR = 0

ORDER BY MEASUREGROUP_NAME

And here is the DMV for Calculated Columns

select  [TABLE],
        OBJECT as ColumnName,
        Expression
     
from $SYSTEM.DISCOVER_CALC_DEPENDENCY

where OBJECT_TYPE = 'CALC_COLUMN' and
REFERENCED_OBJECT_TYPE = 'COLUMN'

Of course the question now is “How do I use these DMVs”?

Using a DMV in Power Pivot for Excel

Apart from using DAX Studio to connect to your data model, you can also execute a DMV query using the table trick that I learnt from Marco Russo (I use this trick all the time).  Do the following:

1. Click in a blank page/cell in your Power Pivot Workbook.
2. Navigate to the Data tab (Excel 2016)
3. Click on Existing Connections
4. Switch to the Tables tab
5. Select any existing table in your data model.  It doesn’t matter which one as we won’t be keeping it for long
6. Click Open.

You will then see the following dialog.  Leave the defaults and click OK.  Note that we are inserting a Table, not a Pivot Table.

Once the table has been added, do the following:

1. Right click anywhere inside the table
2. Select Table
3. Select Edit DAX

When you see the dialog box, do the following:

1. Switch the command type from Table to DAX
2. Copy the DMV code I provided above and paste the code into the dialog as shown below.
3. Click OK.

After you have done this, you will see a list of measures.

You can save your workbook and the list of measures will remain.  If you need to refresh the list at any time, then simply right click on the table and click “refresh”.

Extract from Power BI Desktop

You can also extract measures from Power BI Desktop into an Excel spreadsheet.  To do this, you should refer to my article here.