Author Archive | Mark Drewfs

Performance Reporting with Power View

Hello, my name is Mark Drewfs.  I am a Premier Field Engineer for Microsoft with a focus on Systems Center Operations Manager.  I started building data models of the SCOM Data Warehouse to use for interactive reports, and I would like to share some techniques.  Tim McFadden graciously offered to edit the work and host this blog post.  Thank you, Tim!

The data SCOM collects and Business Intelligence tools fit well together, but there can be a significant barrier to combining them.  Power View lowers that barrier a lot.  Users can move quickly between performance counters and go from an enterprise view down to a single server without much effort. 

Here is an example where I filtered the view to look at four Windows servers to see general data for the past week.  Each chart is filtered to a specific object and counter:

 

 pic001

 

I can analyze the data, too.  For instance, I popped out the Memory chart and highlighted a server in the legend.  Then I hovered over a low point to get the time and value:

pic002

 

The SQL 2012 reporting services feature, Power View (http://technet.microsoft.com/en-us/library/hh213579.aspx), is included with Excel 2013.  It provides quick and dynamic access to data from the SCOM Data Warehouse.  This post describes how to setup Excel 2013 with a PowerPivot model and Power View reports using SCOM performance data. 

 

Overview

A.      Setup Excel 2013

B.      Create a data source for the SCOM Data Warehouse

C.      Add datasets

D.      Create relationships

E.       Make a view

 

Step by step

A.      Setup Excel 2013

1.       Install Excel 2013.  I use the 64-bit version, because it can make use of more physical memory.  Select File à Account à About Excel to check the bitness.

2.       Load the add-ins for PowerPivot and Power View.  Select File à Options à Add-Ins

3.       Select COM Add-ins from the Manage: pulldown and click Go…

4.       Check the boxes for Microsoft Office PowerPivot for Excel 2013 and Power View then click OK

      pic003

5.       Accept the prompts to install Silverlight if necessary.

 

 

B.      Create a data source for the SCOM Data Warehouse

1.       Click the POWERPIVOT tab

2.       Then click Manage

     pic004

3.       Select From Database  then select From SQL Server from the pulldown

    pic005

4.       Enter a name like SCOM_DW in the Friendly connection name: field

5.       Enter the SCOM Data Warehouse server name (with instance and port, if necessary) in the Server name: field

6.       Select the name of the SCOM DW (OperationsManagerDW by default) from the Database name: pulldown

7.       Click Next

8.       On the How to Import the Data dialog select Write a query that will specify the data to import

9.       Click Next

10.   In the Friendly Query Name field type Perf Data

11.   Copy and Paste in the following query (Simple Perf Queries):

SELECT     PERF.ManagedEntityRowId

,PR.RuleRowId

,PERF.DateTime

,PERF.AverageValue

,PERF.MinValue

,PERF.MaxValue

,PERF.SampleCount

,PR.ObjectName

,PR.CounterName

,PRI.InstanceName

FROM Perf.vPerfHourly PERF

INNER JOIN vPerformanceRuleInstance PRI ON PRI.PerformanceRuleInstanceRowId = PERF.PerformanceRuleInstanceRowId

INNER JOIN vPerformanceRule PR ON PR.RuleRowId = PRI.RuleRowId

WHERE PERF.DateTime > (GETUTCDATE() – 7)

/*WHERE PERF.DateTime > (getutcdate() + 1) — Dummy condition to return no rows*/

pic006

12.   Click Finish

 

C.      Add Additional datasets

1.       Click Existing Connections in the Ribbon

        pic007

2.       Select SCOM_DW from the PowerPivot Data Connections

 pic008

 

3.       Click Open

4.       Select Write a query that will specify the data to import and click Next >

5.       In the Friendly Query Name type Perf Entities and Types

6.       Copy and Paste in the following query:

select distinct

ME.ManagedEntityRowId

,ME.Name

,ME.DisplayName

,ME.Path

,MET.ManagedEntityTypeDefaultName

from Perf.vPerfHourly PERF

inner join vManagedEntity ME on ME.ManagedEntityRowId = PERF.ManagedEntityRowId

inner join vManagedEntityManagementGroup MEMG on MEMG.ManagedEntityRowId = ME.ManagedEntityRowId

inner join vManagedEntityType MET on MET.ManagedEntityTypeRowId = ME.ManagedEntityTypeRowId

where PERF.DateTime > (GETUTCDATE() – 7)

order by MET.ManagedEntityTypeDefaultName

7.       Click Finish

8.       Repeat steps 1-7 to create another dataset

9.       Name it MPs and Rules

10.   Use the following SQL Query:

SELECT distinct RU.RuleRowId

,MP.ManagementPackDefaultName

,MP.ManagementPackSystemName

,RU.RuleDefaultName

FROM vManagementPack MP

inner join vRule RU on RU.ManagementPackRowId = MP.ManagementPackRowId

inner join vPerformanceRule PR on PR.RuleRowId = RU.RuleRowId

where PR.LastReceivedDateTime > (GETUTCDATE() – 7)

 

D.      Create relationships

1.       Click Diagram View in the Ribbon

pic009 

2.       Click ManagedEntityRowID in the Perf Data dataset and drag a line to ManagedEntityRowID in the Perf Entities and Types dataset

        pic010

3.       Click RuleRowID in the Perf Data dataset and drag a line to RuleRowID in the MPs and Rules dataset

        pic011

4.       Close the PowerPivot window

        pic012

 

E.       Make a view

1.       Click the INSERT menu

2.       Click the Power View icon

pic013 

3.       Rename the tab to SCOM Perf

pic014 

4.       Expand the Perf Entities and Types dataset in the Power View Fields pane

        pic015

5.       Drag the ManagedEntityTypeDefaultName to the Filters area

        pic016 

6.       Check the boxes for managed entity types of interest

        pic017 

7.       Expand the Perf Data dataset in the Power View Fields pane

8.       Check the box for ObjectName

        pic019 

9.       Click Slicer in the ribbon

        pic020 

10.   Click in the empty space of the view to deselect the slicer

11.   Check the boxes for AverageValue and DateTime in the Perf Data dataset

      pic021 

 

12.   Select Other Chart à Line
pic022

 

13.   Change the AverageValue field to be averaged instead of summed

        pic023

 

14.   Drag CounterName into the TILE BY field

        pic024

 

Note: The managed entity will determine the values for DisplayName, Name and Path.  For top level entities, the path is null.

 

15.   Select the chart

16.   Drag the Path to the Legend

pic025

 

17.   Save the workbook

  

Here is my example:

  

 pic026

Sample Files: https://www.scom2k7.com/downloads/PowerPivotExample.zip

Continue Reading