SSAS: Query Power Pivot DMVs with VBA

  • Article

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

MSDN

Use Dynamic Management Views (DMVs) to Monitor Analysis Services

See Also