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.

image

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

image

Once the table has been added, do the following:

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

image

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.

image

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

image

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.

Share?

Comments

  1. G’day Matt,
    I create a few versions of a measure to test scenarios and eventually settle on one to use, leaving some without use. Trouble is after time and the creation of a number of tabs I don’t recall which ones are unused in the model. Is there a way of adapting your VB to identify measures not used in the model?

    • Are you Psychic Steve? Come back next week for my next article and I will have a solution for you. I just finished writing the post, but you will have to wait 🙂

  2. Matt, thanks for this. Have used the DAX to populate hidden ‘data dictionary’ sheets in my workbook. Also add simple connection to sql view so I picked up ‘Named Calculations’ from the underlying sql tables:

    SELECT T.name AS TableName, C.[name] AS ColumnName, [definition]
    FROM [sys].[computed_columns] C
    INNER JOIN sys.tables T ON C.object_id = T.object_id
    INNER JOIN sys.schemas S ON T.schema_id = S.schema_id
    where S.NAME = ‘XXX’

    Thanks again.

Leave a Reply

Your email address will not be published. Required fields are marked *

Ten Things I Wish I Knew When I Started With Power Pivot.   Learn  Without The Pain!

x