What is eating up my memory the PowerPivot / Excel edition

A common question asked is what columns in my model are using the most memory. Earlier I created a workbook that was able to get the memory for a Tabular server which you can download here but that didn’t work with an embedded Excel Data model or PowerPivot workbook.

But I recently found a new way that would allow me to get this information in PowerPivot / Excel 2013 using a macro. Many thanks to Ken Puls  who helped me clean up the macro and give me some great Excel tips. It always great to see SQL and Excel folks learn things from each other :). Thanks to his help this macro, once created, will be accessible for all workbooks without having to change the individual workbooks.

This macro uses the Excel connection retrieved from the Excel OM and sends a SSAS DMV query to the embedded model to retrieve memory information. Using the connection to send custom query is not a scenario that is supported, meaning custom queries send through the connection will not be guaranteed to work in any future updates of the product.

When you run this macro in any workbook that contains a Excel Data Model will give you a new worksheet with a table containing all tables, columns, datatype and MemorySize (KB). It also has a Pivottable that aggregates the results and is sorted by size:

sheet

The SSAS DMV used to get this information does only return the segment size of a column, this is not all the memory that is used per column but will give enough information to get an good idea of the actual usage. For more details on the internals of the Tabular engine please watch this teched presentation

To get this Macro available for all you workbooks in Excel the following steps are needed.

  • If you haven’t already exposed the Developer tab in Excel, do that first. (right mouse click on the ribbon)
  • On the developer tab, record a new macro, and choose to store it in your “Personal Workbook”
  • Stop recording
  • Press Alt+F11 to get into the Visual basic editor
  • Find Module1 in the personal macro workbook
  • Replace ALL the code for the code you just recorded in there with the code in the attached file
  • Go back to Excel
  • Press Alt + F8 and run the Macro

The entire Macro is as following:

Option Explicit

Sub GetMemoryUsage()
    Dim wbTarget As Workbook
    Dim ws As Worksheet
    Dim rs As Object
    Dim lRows As Long
    Dim lRow As Long
    Dim sReportName As String
    Dim sQuery As String
    sReportName = "Memory_Usage"

    'Suppress alerts and screen updates
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With

    'Bind to active workbook
    Set wbTarget = ActiveWorkbook

    'Check if a worksheet already exists
    Err.Clear
    On Error Resume Next
    Set ws = wbTarget.Worksheets(sReportName)
    If Err.Number = 0 Then
        'Worksheet found
        If MsgBox("A memory usage sheet workbook is already detected, " & _
            "do you want to remove the existing one and continue?", vbYesNo) = vbYes Then
                ws.Delete
        Else
            GoTo ExitPoint
        End If
    End If

    On Error GoTo ErrHandler

    'Make sure the model is loaded
    wbTarget.Model.Initialize

    'Send query to the model
    sQuery = "SELECT dimension_name, attribute_name, DataType,(dictionary_size/1024) AS dictionary_size " & _
        "FROM $system.DISCOVER_STORAGE_TABLE_COLUMNS " & _
        "WHERE dictionary_size > 0"
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open sQuery, wbTarget.Model.DataModelConnection.ModelConnection.ADOConnection
    lRow = rs.RecordCount

    If lRow > 0 Then
        'Add report worksheet
        Set ws = wbTarget.Worksheets.Add
        With ws
            .Name = sReportName
            .Range("A1").FormulaR1C1 = "Table"
            .Range("B1").FormulaR1C1 = "Column"
            .Range("C1").FormulaR1C1 = "DataType"
            .Range("D1").FormulaR1C1 = "MemorySize (KB)"

            lRows = 2
            rs.MoveFirst

            Do While Not rs.EOF
                'Add the data to the rows
                .Range("A" & lRows).FormulaR1C1 = rs("dimension_name")
                .Range("B" & lRows).FormulaR1C1 = rs("attribute_name")
                .Range("C" & lRows).FormulaR1C1 = rs("DataType")
                .Range("D" & lRows).FormulaR1C1 = rs("dictionary_size")
                lRows = lRows + 1
                rs.movenext
            Loop

            'Format the Memory Size field
            .Columns("D:D").NumberFormat = "#,##0.00"

            'Create table
            .ListObjects.Add(xlSrcRange, .Range("$A$1:$D$" & lRow + 1), , xlYes).Name = "MemorySizeTable"
        End With

        'Create PivotTable
        wbTarget.PivotCaches.Create(SourceType:=xlDatabase, _
            SourceData:="MemorySizeTable", _
            Version:=xlPivotTableVersion15).CreatePivotTable _
            TableDestination:="Memory_Usage!R2C7", _
            TableName:="MemoryTable", _
            DefaultVersion:=xlPivotTableVersion15

        'Modify the PivotTable
        With ws
            With .PivotTables("MemoryTable")
                With .PivotFields("Table")
                    .Orientation = xlRowField
                    .Position = 1
                    .AutoSort xlDescending, "Sum of MemorySize (KB)"
                End With
                With .PivotFields("Column")
                    .Orientation = xlRowField
                    .Position = 2
                    .AutoSort xlDescending, "Sum of MemorySize (KB)"
                End With
                .AddDataField .PivotFields("MemorySize (KB)"), "Sum of MemorySize (KB)", xlSum
                .PivotFields("Table").AutoSort xlDescending, "Sum of MemorySize (KB)"
                .PivotFields("Column").AutoSort xlDescending, "Sum of MemorySize (KB)"
             End With

            'Format the Memory Size field in the PivotTable
            .Columns("H:H").NumberFormat = "#,##0.00"

            'Add conditional formatting
            With .Range("H3")
                .FormatConditions.AddDatabar
                .FormatConditions(.FormatConditions.Count).ShowValue = True
                .FormatConditions(.FormatConditions.Count).SetFirstPriority
                With .FormatConditions(1)
                    .MinPoint.Modify newtype:=xlConditionValueAutomaticMin
                    .MaxPoint.Modify newtype:=xlConditionValueAutomaticMax
                    With .BarColor
                        .Color = 13012579
                        .TintAndShade = 0
                    End With
                    .BarFillType = xlDataBarFillGradient
                    .Direction = xlContext
                    .NegativeBarFormat.ColorType = xlDataBarColor
                    .BarBorder.Type = xlDataBarBorderSolid
                    .NegativeBarFormat.BorderColorType = xlDataBarColor
                    With .BarBorder.Color
                        .Color = 13012579
                        .TintAndShade = 0
                    End With
                    .AxisPosition = xlDataBarAxisAutomatic
                    With .AxisColor
                        .Color = 0
                        .TintAndShade = 0
                    End With
                    With .NegativeBarFormat.Color
                        .Color = 255
                        .TintAndShade = 0
                    End With
                    With .NegativeBarFormat.BorderColor
                        .Color = 255
                        .TintAndShade = 0
                    End With
                    .ScopeType = xlSelectionScope
                    .ScopeType = xlFieldsScope
                End With
            End With

            With .Range("H4")
                .FormatConditions.AddDatabar
                .FormatConditions(.FormatConditions.Count).ShowValue = True
                .FormatConditions(.FormatConditions.Count).SetFirstPriority
                With .FormatConditions(1)
                    .MinPoint.Modify newtype:=xlConditionValueAutomaticMin
                    .MaxPoint.Modify newtype:=xlConditionValueAutomaticMax
                    With .BarColor
                        .Color = 15698432
                        .TintAndShade = 0
                    End With
                    .BarFillType = xlDataBarFillGradient
                    .Direction = xlContext
                    .NegativeBarFormat.ColorType = xlDataBarColor
                    .BarBorder.Type = xlDataBarBorderSolid
                    .NegativeBarFormat.BorderColorType = _
                        xlDataBarColor
                    With .BarBorder.Color
                        .Color = 15698432
                        .TintAndShade = 0
                    End With
                    .AxisPosition = xlDataBarAxisAutomatic
                    With .AxisColor
                        .Color = 0
                        .TintAndShade = 0
                    End With
                    With .NegativeBarFormat.Color
                        .Color = 255
                        .TintAndShade = 0
                    End With
                    With .NegativeBarFormat.BorderColor
                        .Color = 255
                        .TintAndShade = 0
                    End With
                    .ScopeType = xlSelectionScope
                    .ScopeType = xlFieldsScope
                End With
            End With

            'Collapse the PivotTable
            .PivotTables("MemoryTable").PivotFields("Table").ShowDetail = False

            'Set selection to top
            .Range("MemorySizeTable[[#Headers],[Table]]").Select
        End With
    Else
        MsgBox "No model available", vbOKOnly
    End If
    rs.Close

ExitPoint:
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
    Set rs = Nothing
    Exit Sub

ErrHandler:
    MsgBox "An error occured - " & Err.Description, vbOKOnly
    Resume ExitPoint
End Sub

Download a sample workbook here. The Macro is applied to this workbook only for you to be able to download it, to get this available for all workbooks go through the steps above.

  • Konrad

    Hey, thanks for this macro. I’ve been looking for something like this. Unfortunately it doesn’t seem to run on Excel 2010. It breaks at wbTarget.Model.Initialize.
    Is there a workaround for Excel 2010?

    Thanks!

    • Kasper de Jonge

      Hi Konrad,

      This does not work for Excel 2010 as these objects are not exposed in the Excel OM in 2010.

      Kasper

  • Liron

    Konrad, What you can do in order to make it work on excel 2010 is to remove the wbTarget.Model.Initialize. line from the macro and before you run the macro open the PowerPivot tab (which will initialize the model).

    Liron.

  • Kasper de Jonge

    Hi @Liron ,

    That wont work as Model.DataModelConnection.ModelConnection.ADOConnection does also not exist in Excel 2010. But I think you should be able to do a similar thing for Excel 2010 when you can get the connection. So more is needed.

  • Konrad

    thanks for the responses.
    Liron, I had tried your suggestion, but got stuck at Model.DataModelConnection.ModelConnection.ADOConnection 🙂

    So if I could somehow get the connection, I could run the same SQL Query against the database and continue with the macro?

  • It seems like you should also include $system.DISCOVER_STORAGE_TABLE_COLUMN_SEGMENTS.USED_SIZE data?

  • I am also working with Excel 2010. I ran the query through DAX Studio and got the expected results. Now to convert this into VBA. I am sure there must be some exposed functions that you can use through DAX Studio in VBA.

  • Kazim Rajani

    Hi Kasper,

    Thanks for sharing this. Is there anyway we can produce memory stats for a Power Pivot model in Excel 2010?

    Thanks

    Kaz

    • Olivier HBH

      I’m also very interested in an answer!

  • Martin S

    Excel 2010? Would be much appreciated as our company will not upgrade yet (several years)

  • Pingback: Analysing Power BI DMV Queries In Power BI Desktop – Chris Webb's BI Blog()

  • Pingback: Analysing Power BI DMV Queries In Power BI Desktop - SQL Server - SQL Server - Toad World()

  • Pingback: A Fabulous new Excel Add-In for Power Pivot -()

  • Brian Mather

    If you are ever in England, the beer is on me !!! Thank you so much for sharing.

  • Ginger

    Kasper —
    Great article. I am working with Power BI desktop. Do you think that if I loaded my data into Excel and ran the macro, the numbers provided would reflect the size used in Power BI Desktop?

    –Ginger

  • Jay

    Hi Kasper, Thanks for sharing this. Does the same Macro works in Excel 2010 ? I am getting a compile error message .”xlPivotTableVersion15″ Variable not defined..Any help will be appreciated..

  • Hennie de Nooijer

    Well, I’ve executed this Macro and I’ve a question about the results. The results seems counterintuitive. My key in the dimension is 2 GB and the FK in the Fact is 100 MB. I would have expected the other way around.