General Purpose Data Model for SCOM Data Warehouse

  • Article

Power View allows the correlation of a large amount of data as long as a relationship exists. The datasets in the SCOM Data Warehouse share relationships across time, classification, and technology. I put together a PowerPivot data model for analyzing the most recent week's data (alerts, events, performance, and state changes) in the SCOM Data Warehouse.  Attached are the data model with example report views and a text file with the SQL queries.  I use the model mostly for tuning and troubleshooting. Sometimes I use it in conjunction with the short term and long term performance models to judge the value of alerts. I won't go into a detailed explanation about the design other than to say I brought together the earlier models and added state changes. I made a diagram to lay out the design:

I would note a few things about the design decisions for the model:

  • The model is generic.   I intended the model as a foundation for customization. 
  • The model is meant to scale to large SCOM DWs.   I chose a week to keep it manageable for the >10,000 agent range, but a smaller environment could easily go back a month.  Also, the performance data comes from the daily aggregation, but it could be switched to hourly aggregated data for more detail in smaller environments.
  • Workflows are a combination of rules and monitors.   There is some manipulation of RuleRowId and MonitorRowId values to come up with a unique list of workflows.  I used the monitor indicator field to distinguish them (0 = Rule, 1 = Monitor). 

I suggest running the SQL Queries individually before populating the model to see how long they take and how much data is returned. I would suggest adding configuration data at the entity level (see my earlier post), too.

 

Overview

A. Change Existing Connection

B. Add a Technical Knowledge Dataset for Management Packs

C. Refresh the Data Model and Review Reports

 

Step by step

A. Change Existing Connection

  1. Start Excel

  2. Open the SCOM DW Model file

  3. Click Enable Content if there is a security warning

     

  4. Click Manage in the Data Model section of the ribbon in the POWERPIVOT menu

  5. Click Existing Connections in the Get External Data section of the ribbon in the Home menu

     

  6. Select the SCOM Data Warehouse from the PowerPivot Data Connections

  7. Click Edit

  8. Enter an appropriate name in the Friendly connection name: field

  9. Enter the SCOM DW server (server\instance if using a named instance) in the Server Name: field

  10. Change providers in the Advanced Properties if there is a provider error

  11. Select the SCOM DW database (OperationsManagerDW by default) from the pulldown list for the Database name: field

  12. Click Save

  13. Click Close

  14. Select Refresh All from the Refresh pulldown in the ribbon in the Home menu

  15. Click Close when the refresh is complete

 

B. Add a Technical Knowledge Dataset for Management Packs

  1. Open a new Excel workbook

  2. Copy the ManagementPackDefaultName column from the MPs dataset

  3. Paste the ManagementPackDefaultName column in the new workbook

  4. Create a column named Product

  5. Enter desired values in the cells for any or all management packs (empty cells will show as "Blank" in Power View). For example:

    ManagementPackDefaultName

    Product

    Operations Manager APM Web

    SCOM

    Operations Manager APM WCF Library

    SCOM

    Operations Manager APM Windows Services

    SCOM

    Windows Server 2012 Operating System (Monitoring)

    Windows

    Windows Server 2012 R2 Operating System (Monitoring)

    Windows

    Windows Server Cluster Disks Monitoring

    Windows

    Microsoft SQL Server 2012 AlwaysOn (Discovery)

    SQL

    Microsoft SQL Server 2012 AlwaysOn (Monitoring)

    SQL

    Microsoft SQL Server 2012 (Monitoring)

    SQL

  6. Rename the worksheet to MP Product

  7. Save the workbook as Tech Knowledge

  8. Close the workbook

  9. In the Power Pivot model, click From Other Sources in the Get External Data section of the ribbon

  10. Select Excel File from the Table Import Wizard then click Next >

  11. Click Browse

  12. Select Tech Knowledge and click Open

  13. Check Use first row as column headers. then click Next >

  14. Click Finish then click Close

  15. Click on Diagram View from the View section of the ribbon in the Home menu

  16. Create a relationship by dragging ManagementPackDefaultName in the MPs datasetto ManagementPackDefaultName in the MP Product dataset

      

C. Refresh the Data Model and Review Reports

  1. Select Refresh All from the Refresh pulldown in the ribbon in the Home menu

  2. Click Close when the refresh is complete

  3. Close the Power Pivot window

  4. Click OK in the Power View dialog window

  5. Review the reports

  6. Click on the Perf Insertions tab

  7. Select the column chart

  8. Click ALL under the Power View Fields

  9. Expand the MP Product dataset

  10. Drag the Product field to the LEGEND to replace Object

  11. Analyze the volume of performance data collected

  12. Save the workbook

SCOM DW Model and Queries.zip