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: http://www.scom2k7.com/downloads/PowerPivotExample.zip

16 Responses to Performance Reporting with Power View

  1. Tim McFadden May 6, 2013 at 6:24 pm #

    Looks Fantastic!

  2. -Jess May 23, 2013 at 12:39 pm #

    These lines didn’t copy / paste nicely, the “-” in -7 needed to be reentered and the “-Where” should be “–Where”

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

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

  3. Mark Drewfs May 23, 2013 at 12:47 pm #

    Thanks, Jess. I switched to the block comment syntax to avoid the wide character.

  4. scomurr June 21, 2013 at 4:17 pm #

    Good stuff Mark.

  5. Andre Durval August 29, 2013 at 10:20 am #

    Excelent Post Mark !!!!

    It´s possible to change the range time ? It´s showing me only the past week, but my retention policy is higher than one week.

    Best Regards,
    André Durva.

  6. Mark Drewfs August 29, 2013 at 12:03 pm #

    Thank you, Andre! The date range is set in the WHERE conditions of the SQL queries (GETUTCDATE() – 7). You can replace “7” with the number of days you would like to go back. If you are going to go beyond a couple weeks then I suggest switching to the Perf.vPerfDaily view (change the “FROM Perf.vPerfHourly PERF” to “FROM Perf.vPerfDaily PERF” in the queries).

  7. Andre Durval August 30, 2013 at 7:07 am #

    Thanks again ! Good job !!!

  8. michel October 17, 2013 at 3:21 pm #

    for people with excel 2010 see this post:
    http://michelkamp.wordpress.com/2012/03/10/scom-meets-excel-powerpivot/

  9. Blake July 10, 2014 at 1:35 pm #

    So these are good, but can these be designed and then hosted on a SharePoint site to be consumed by various people? Is there a down side to that (performance hit on DW if multiple people are using the report at the same time)?

    Running these from my desktop is nice, but I would rather be able to build these and then publish them so that others could use the data so as to see how their application is performing.

  10. Mark Drewfs July 10, 2014 at 1:52 pm #

    Blake, thank you for the feedback. Yes, a SharePoint Farm with BI features (PowerPivot and Power View) allows you to share views. The report views can be stored in the workbook or in Power View definition (RDLX) files. The RDLX files also work with BISM connections. To scale up from the limits of SharePoint the PowerPivot model may be imported into SQL Server Analysis Services (SSAS) in tabular mode. Then a BISM file in SharePoint points to the AS database storing the model. Another option for sharing the Excel workbook views is Power BI via Office 365. The Power View reports in the workbook will be accessible in read-only mode. For refreshing the data, each approach (PowerPivot on SharePoint, SSAS, and Power BI) has different methods and limitations.

    As far as performance impact to SCOM, the data is stored in the model (PowerPivot or SSAS). The only communication to the SCOM DW is during the data refresh.

  11. Karina August 16, 2014 at 9:13 am #

    I was very pleased to uncover this web site. I wanted to thank you for ones time for this wonderful read!!
    I definitely appreciated evgery bit of it aand i also
    have you saved as a favorite to check out new things in your website.

Trackbacks/Pingbacks

  1. Everything System Center Operations Manager 2007 by Tim McFadden » Blog Archive » Dashboards in SCOM 2012 - May 11, 2013

    […] « Performance Reporting with Power View […]

  2. Event Reporting with Power View - drewfs - Site Home - TechNet Blogs - August 13, 2013

    […] another post, “Performance Reporting with Power View” (http://www.scom2k7.com/performance-reporting-with-power-view/), I used Excel to create report views. This time I switch to SharePoint 2013 for creating views […]

  3. [SCOM 2012] Rapport de performance avec Excel 2013 - Florent Appointaire Blog's - Blogeurs - MicrosoftTouch - January 28, 2014

    […] à générer, et surtout, beaucoup plus lisible. Je me suis inspiré de cet article, écrit par Mark […]

  4. SCOM Performance Data and Power View - drewfs - Site Home - TechNet Blogs - August 12, 2014

    […] a previous post (http://www.scom2k7.com/performance-reporting-with-power-view/) I presented a simple example of performance reporting using Power View in Excel 2013. The purpose […]

  5. #Technet #Blog Post: SCOM Performance Da | Issues and lessons learned - August 17, 2014

    […] #Blog Post: SCOM Performance Data and Power View – In a previous post (http://www.scom2k7.com/performance-reporting-with-power-view/) I presented a simple example of performance reporting using Power View in Excel 2013. The purpose […]

Leave a Reply