SSAS: Query Power Pivot DMVs with VBA
Introducing
Power Pivot is designed to be compatible with the tabular/multidimensional database of SSAS.
So also the DMV = Dynamically Management Views, as known from SSAS, is available here.
The DMVs provide useful information about existing objects like dimensions and measures, as well as statistical information like memory usage.
We can query a Power Pivot DMV with a SQL-like SELECT statement:
SELECT * FROM $System.<schemaRowset>
But Power Pivot doesn't support external connections to the engine, so we have to utilize the internal connection within a Microsoft Excel workbook using VBA and Microsoft ActiveX Data Objects (ADO) to query the DMV's.
How To
Create a new MS Excel workbook or open an existing one.
Save it as XLSM = "Excel Macro-Enable Workbook"
Ensure that a Power Pivot table is added to a sheet
Switch to the VBA Editor with short key ALT + F11
Open the VBA module "ThisWorkbook"
Open menu "Tools" => "Add Reference", select "Microsoft ActiveX Data Objects 2.8 Library" or another version then 2.8, then click "OK" button
Paste the following VBA code to your VBA module "ThisWorkbook"
Option Explicit
Private rowID As Integer
' Main method to create an Excel sheet with the result
' of the DMV queries.
Public Sub CreatePowerPivotDmvInventory()
Dim conn As ADODB.Connection
Dim sheet As Excel.Worksheet
On Error GoTo FailureOutput
' Open connection to PowerPivot engine, the name is fix.
Set conn = ActiveWorkbook.Connections("PowerPivot Data").OLEDBConnection.ADOConnection
' Use the active sheet for output of the information
Set sheet = ActiveWorkbook.ActiveSheet
rowID = 1
' Call function by passing the DMV name
WriteDmvContent "DBSCHEMA_CATALOGS", conn, sheet
WriteDmvContent "MDSCHEMA_CUBES", conn, sheet
WriteDmvContent "MDSCHEMA_DIMENSIONS", conn, sheet
WriteDmvContent "MDSCHEMA_HIERARCHIES", conn, sheet
WriteDmvContent "MDSCHEMA_MEASURES", conn, sheet
MsgBox "Finished"
Exit Sub
FailureOutput:
MsgBox Err.Description
End Sub
' Method to write the result of a single DMV query to the Excel sheet
Private Sub WriteDmvContent(ByVal dmvName As String, ByRef conn As ADODB.Connection, ByRef sheet As Worksheet)
Dim rs As ADODB.Recordset
Dim cell As Excel.Range
Dim mdx As String
Dim i As Integer
' SQL like query to get result of DMV from schema $SYSTEM
mdx = "select * " & _
"from $SYSTEM." & dmvName
Set rs = New ADODB.Recordset
rs.ActiveConnection = conn
rs.Open mdx, conn, adOpenForwardOnly, adLockOptimistic
' Output of the DMV name
sheet.Cells(rowID, 1) = dmvName
FormatDmvName sheet.Cells(rowID, 1)
rowID = rowID + 1
' Output of the column names
For i = 0 To rs.Fields.Count - 1
sheet.Cells(rowID, i + 1) = rs.Fields(i).Name
FormatColumnHeader sheet.Cells(rowID, i + 1)
Next i
rowID = rowID + 1
' Output of the query results
Do Until rs.EOF
For i = 0 To rs.Fields.Count - 1
WriteFormatedCellValue sheet.Cells(rowID, i + 1), rs.Fields(i)
Next i
rowID = rowID + 1
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
rowID = rowID + 2
Exit Sub
FailureOutput:
MsgBox Err.Description
End Sub
' Write and Format the value depending on the data type
Private Sub WriteFormatedCellValue(ByRef cell As Excel.Range, ByRef field As ADODB.field)
cell = field.Value
Select Case field.Type
'Date/Time
Case ADODB.DataTypeEnum.adDate, DataTypeEnum.adDBDate
cell.NumberFormat = "m/d/yyyy"
' Integers
Case DataTypeEnum.adBigInt, DataTypeEnum.adInteger, DataTypeEnum.adSmallInt, DataTypeEnum.adTinyInt
cell.NumberFormat = "###0"
' Decimals / Floating Points
Case DataTypeEnum.adCurrency, DataTypeEnum.adDecimal, DataTypeEnum.adDouble, DataTypeEnum.adNumeric, DataTypeEnum.adSingle
cell.NumberFormat = "#,##0.00"
End Select
End Sub
' Write the formatted DMV name to the sheet.
Private Sub FormatDmvName(ByRef cell As Excel.Range)
cell.Font.Bold = True
With cell.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
End Sub
' Write the formatted DMV column name to the sheet.
Private Sub FormatColumnHeader(ByRef cell As Excel.Range)
cell.Font.Bold = True
With cell.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 14408667
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
End Sub
To execute the VBA macro place the cursor inside the routine "CreatePowerPivotDmvInventory" and press F5. Alternatively, click the green "Play" button in the symbol bar. Or in Excel on ribbon "View" click "Macros", select "ThisWorkbook.CreatePowerPivotDmvInventory" and click "Run".
The result will be written to the current Excel sheet. Examples of the "Measures":
Notes
In an MS Excel Workbook, you can have several data connections to a different source, but there can be at least only one PowerPivot connection. In MS Excel 2010 the connection always does have the fix name "PowerPivot Data", so there is no need to iterate through all existing connections to find the right one, you can address it directly by its name.
You can check the name in Excel Ribbon "Data" => "Connections"
Also, the main object has always the same names: Catalog Name = "Microsoft_SQLServer_AnalysisServices" and Cube Name = "Model"
For some DMVs you must apply "restrictions", see example at SSAS Info: SSAS 2008 RC0 – New function SYSTEMRESTRICTSCHEMA for restricted schema rowsets – DMVs
Links
MSDN
Use Dynamic Management Views (DMVs) to Monitor Analysis Services