As your PowerPivot workbooks grow larger and contain more calculation the urge for some kind of documentation becomes stronger. In this PowerPivot Nugget I will describe a simple way to generate a list with all your DAX calculations.
The technique uses the internal dynamic management views of Power Pivot. The dynamic management views are internal objects in PowerPivot that hold a lot of useful information about your PowerPivot model. In his blogpost
Querying PowerPivot DMVs from Excel Chris Webb describes step by step how to use them.
I will show you a shortcut that only takes a few mouse clicks.
Step 1 - The preparation - Create an .ODC file with the connection and query to the embedded PowerPivot model
Option 1
- Download the connection file from Skydrive "PowerPivot Get All DAX statements.odc"
- Make sure you have the extension .odc correct
- Save it in an easy to remember location (e.g. My Data Sources).
Option 2
Create the ODC file from scratch as described in the
blogpost by Chris. There are 2 important parts in the connection file you need the change.
The connection string to
the internal PowerPivot model:
Provider=MSOLAP.5;Persist Security Info=True;Initial Catalog=Microsoft_SQLServer_AnalysisServices;
Data Source=$Embedded$;MDX Compatibility=1;Safety Options=2;ConnectTo=11.0;MDX Missing Member Mode=Error;
Optimize Response=3;Cell Error Mode=TextValue
and the query to retrieve the DAX
expressions:
SELECT DISTINCT [TABLE], OBJECT_TYPE, OBJECT, EXPRESSION
FROM $system.discover_calc_dependency
WHERE OBJECT_TYPE = 'MEASURE'
OR OBJECT_TYPE = 'CALC_COLUMN'
ORDER BY 1
Step 2 - Generate the DAX documentation
Open a PowerPivot workbook and create a new blank sheet.
In the Data ribbon, choose Get External Data, Existing Connections.
Browse to the "PowerPivot Get All DAX statements.odc " file you created in Step 1.
Click OK and this is the result
In the future Excel will remember the connection to the ODC file and it will only take you 3 mouse clicks to get an updated list of all your DAX calculations.
1) Get External Data
2) Double click on PowerPivot Get All DAX statements in the Existing Connections dialogue box.
A real time saver!!