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.
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!
Hi Konrad,
This does not work for Excel 2010 as these objects are not exposed in the Excel OM in 2010.
Kasper
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.
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.
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.
Hi Kasper,
Thanks for sharing this. Is there anyway we can produce memory stats for a Power Pivot model in Excel 2010?
Thanks
Kaz
I’m also very interested in an answer!
Excel 2010? Would be much appreciated as our company will not upgrade yet (several years)
If you are ever in England, the beer is on me !!! Thank you so much for sharing.
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
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..
This is not available for Excel 2010.
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.