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:

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.