Power Pivot Data Dictionary From VBA

The folks over at PowerPivotPro have blogged like… everything under the sun about Power Pivot.   It’s always a bit embarrassing to search for a technique like… the formula for a calculated column for “End of Week” date… and it turns out that *I* wrote it on PPP blog and forgot about it .  Anyway, I was starting this article and decided to do something crazy and search for it first… and naturally, they already have it blogged.

Their technique is super fancy.  This is so simple that it isn’t even complete   And it only works for 2013… though, I suspect 2010 maybe be a simple change.

Sub GetDocs()
    ActiveWorkbook.Model.Initialize
   
    Set conn = ActiveWorkbook.Connections("ThisWorkbookDataModel").ModelConnection.ADOConnection
    Set rs = CreateObject("ADODB.Recordset")
   
    sQuery = "select MeasureGroup_Name as TableName, Measure_Caption as MeasureName, Expression, " + _
             "[Description] FROM $system.MDSchema_Measures where measure_aggregator=0"
    rs.Open sQuery, conn
   
    Do While Not rs.EOF
        Output _
            rs("TableName"), _
            rs("MeasureName"), _
            rs("Expression"), _
            rs("Description")
           
        rs.MoveNext
    Loop
End Sub

Sub Output(TableName, MeasureName, Expression, Description)
    Debug.Print TableName
    Debug.Print MeasureName
    Debug.Print Expression
    Debug.Print Description
    Debug.Print "--"
End Sub


I have no idea what you actually want to do with the output.  So, I just stuck it in the subroutine called “Output” that does Debug.Print.  You can go write to an xml file or build and html page or whatevs.   The code is obviously simple… it just uses an ADO connection to query the DMV, loops the records calling the Output routine.

Enjoy!

• About Scott
• Latest Posts
• Contact

About Scott Senkeresty

Scott Senkeresty here… Founder and Owner of Tiny Lizard. Yes, I have done some stuff: A Master’s Degree in Computer Science from California Polytechnic State University in beautiful San Luis Obispo, California. Over twelve years developing software for Microsoft, across Office, Windows and Anti-Malware teams… Read More >>

• The streak is alive! – August 10, 2017
• DAX KEEPFILTERS Plus Bonus Complaining! – July 20, 2017
• VAR: The best thing to happen to DAX since CALCULATE() – July 11, 2017
• Review: Analyzing Data with Power BI and Power Pivot for Excel – June 20, 2017
• Power BI Date Table – June 2, 2017

View All Posts

Scott Senkeresty here… Founder and Owner of Tiny Lizard. Yes, I have done some stuff: A Master’s Degree in Computer Science from California Polytechnic State University in beautiful San Luis Obispo, California. Over twelve years developing software for Microsoft, across Office, Windows and Anti-Malware teams… Read More >>