Twitter Updates

    follow me on Twitter

    Wednesday, February 01, 2012

    PowerPivot Nuggets - Part 14 - Generate a list with all your DAX calculations

    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!!



    No comments: