SCOM – Part 1 Create Excel 2013 PowerView Dashboard

Last week I had to travel to China. So far, I haven’t had any experience with Asian countries nor culture, except of the local Chinese restaurant in Switzerland. What I learned, that there are different ways to visualize common scenarios and how important it is to make the visualization readable and understandable, also for foreigners. This sample shows a some sort of limited dashboard technology, which not everybody understands…

Well, this leads us to a similar problem we have in SCOM. We are able to display data in different ways like views, SSRS reports, Visio dashboards, SCOM 2012 dashboards etc.

What I am missing, is the ability to properly visualize the data in a more flexible and adjustable way. Although Microsoft started building dashboards in SCOM 2012 and created widgets which are getting close to what we are going to need, I found another approach to visualize the data, which might overcomes certain scenarios, where the other technologies don’t quite fit. The technology is based on Excel 2013 (yes, Excel) and SharePoint 2013. Sounds cool? Yes, indeed it is.

Ok what is this all about? As you might know since Excel 2010, Excel has PowerPivot built in. PowerPivot let’s you visualize data in many ways e.g. charts, diagrams etc. This is a cool solution, but the downside is, that PowerPivot only let’s you visualize the data within an Excel sheet.
Starting in Excel 2013 Microsoft integrated another visualization option called PowerView. PowerView uses the same data model (data source) called PowerPivot, but gives you different options in visualizing the data. It offers you an additional visualization layer, which can not only live in the Excel environment. You can save the PowerView chart / dashboard in e.g. in SharePoint 2013 and provide this to a broad audience.

Power View is an interactive data exploration, visualization, and presentation experience that encourages intuitive ad-hoc reporting. Power View is a feature of Microsoft Excel 2013, and of Microsoft SharePoint Server 2010 and 2013 as part of the SQL Server 2012 Service Pack 1 Reporting Services Add-in for Microsoft SharePoint Server Enterprise Edition. (Source: TechNet)

To give an impression what PowerView can do, I created an Operations Manager Health Dashboard. Believe me, this is just a basic example…

Ok, great but how do we get started? Let’s start with the requirements.

Requirements

• For creating PowerView charts, you need Excel 2013 or you also could do it directly in SharePoint. This example here uses PowerView in Excel 2013.
• If you would like to upload the charts into SharePoint you need SharePoint 2010 SP1 or SharePoint 2013 with Excel Services enabled if you upload Excel workbooks. If you are going to create PowerView reports in SharePoint itself you need Microsoft SQL Server 2012 Reporting Services add-in  installed for the SharePoint server.
• To run the dashboard in a browser you need on the client side Silverlight
• Here the browser requirements which are supported by PowerView

Very detailed system requirements can be found here and here .

Getting Started

In this first part we are going to create a dashboard only in Excel 2013 with some data about our SCOM server. In an upcoming part I will show how to display this dashboard in SharePoint.

Let’s assume you fulfilled the requirements for your scenario, then let’s start creating your first dashboard. For my scenario I decided to ONE dashboard, displaying FOUR different charts. These charts contain the following data:

• Count of client connections (SDK connections) over time getting the data from the SCOM DWH .
• Visualizing the OperationsManager database file size, free space and space used in a meaningful way.
• Getting a summary of closed alerts per user from the SCOM DWH.
• Count of Alerts added per day from the OperationsManager database.

Ok that is our goal. Next we need to get the SQL queries. Yes, because we are going to get the data from different sources (OperationsManager and OperationsManagerDW databases) we need to get the proper source data. A very helpful source in terms of SQL queries is Kevin Holman’s older post “Useful Operations Manager 2007 SQL Queries” which are mostly still valid.

If you got your query together, then we are able to get our hands dirty. Here I picked some SQL examples queries and slightly modified them. In the bracket you see which database is the source.

Client Connection Count (OperationsManagerDW)

SELECT DateTime,
ObjectName,
FullName,
Path,
DisplayName,
CounterName,
SampleValue,
Name
FROM Perf.vPerfRaw pvpr
INNER JOIN vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId
INNER JOIN vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId = vpri.PerformanceRuleInstanceRowId
INNER JOIN vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId
WHERE Path = ‘scom2012R2.lab.itnetx.ch’ AND CounterName = ‘Client Connections’

Operations Manager DB File Sizes (OperationsManager)

SELECT SysFile.FileId [FILE ID],
[FILE SIZE MB]=CONVERT(DECIMAL(12,2),ROUND(SysFile.Size/128.000,2)),
[SPACE USED MB]=CONVERT(DECIMAL(12,2),ROUND(FILEPROPERTY(SysFile.Name,’SpaceUsed’)/128.000,2)),
[FREE SPACE MB]=CONVERT(DECIMAL(12,2),ROUND((SysFile.Size-FILEPROPERTY(SysFile.Name,’SpaceUsed’))/128.000,2)) ,
[GROWTH MB]=CONVERT(DECIMAL(12,2),ROUND(SysFile.Growth/128.000,2)), Name=LEFT(SysFile.Name,15),
FileName=LEFT(SysFile.FileName,60)
FROM dbo.SysFiles SysFile

Closed Alert Count Per User (OperationsManagerDW)

SELECT StateSetByUserId  [STATE SET BY USER], COUNT(*) [ALERT COUNT]
FROM Alert.vAlertResolutionState ars
WHERE ResolutionState = ‘255’
GROUP BY StateSetByUserId

Alerts Added Per Day (OperationsManager)

SELECT CONVERT(VARCHAR(20), TimeAdded, 102) [DAY ALERT ADDED], COUNT(*) [NUMBER OF ALERTS]
FROM Alert WITH (NOLOCK)
WHERE TimeRaised is not NULL
GROUP BY CONVERT(VARCHAR(20), TimeAdded, 102)

These queries build the source for your dashboard.

 Creating Excel Dashboard

Now let’s start creating the dashboard. Open Excel 2013 and click PowerPivot…

Now, we need to create two connections. One to the OperationsManagerDW database and one to the OperationsManager database. Here I will just show how to do it for the OperationsManagerDW database because the steps are the same for any additions SQL database.

Choose to create a new connection From SQL Server…

A table import dialog opens and first we give it a Friendly connection name and provide the Server name where the SCOM database are installed. In my lab I have everything installed on one server called SCOM2012R2. Next provide an account which has read permission on the SQL databases, just for ease I will use my current logged in account (Windows Authentication) which has enough permission. Finally, you just need to select the database you want to run the query on…

Choose to Write a query that will specify the data to import…

Give this query a friendly name and paste your SQL query, which we prepared in advance. Validate the query and click Finish…

If the query runs successfully you will see something like this…

Finally, we see the imported data from the database. This is our source data, which we will use to build one chart…

Next, we would like to import the data from the second OperationsManagerDW query Closed Alert Count Per User. Because we have already an existing connection to the OperationsManagerDW database, we select Get External Data and choose Existing Connections…

Choose the OperationsManagerDW connection and click Open…

Seelct Write a query that will specify the data to import…

Give this query a friendly name and paste the second query which targets the OperationsManagerDW database…

Again we imported the data successfully…

Next you need to create a connections to the OperationsManager database, the same way we did it for the OperationsManagerDW database. Use the same steps to import the data from the OperationsManager db for the OperationsManager DB File Sizes and Alerts Added Per Day queries as we did previously.

If you managed to import all the data you will see four data sets…

Close the dialog and select the INSERT menu and click Power View. If you start PowerView for the first time you will be asked if you want to enable PowerView….

If PowerView has been successfully activated your Excel will look like this…

Choose from the Power View Fields the values / fields you would like to display (Step 1). Immediately a table will appear with the column / rows you selected. Next, choose what kind of chart you need / want e.g. Line (Step 2) and the drag’n drop the values / fields into the right areas / axis (Step 3)…

Go to LAYOUT and select if you want to display Data Labels e.g. Auto. This will show the Line values…

Repeat the steps again by selecting other Power View Fields e.g. I want to visually display the Operations Manager DB File Sizes. I select the fields (Step 1), choose Pie Chart (Step 2) and drag’n drop the fields into the proper place (Step 3)…

Repeat the steps again for the other data sources, according to your needs. I just want to give you an idea how to start and what the basic steps are.

You can select from many different chart types to visualize your data like Bar Chart, Column Chart etc.

If you have you dashboard designed, you can start playing around with your data. In the Filters pane you are able to select per chart the values you would like to have displayed and the chart will dynamically adjust. Pretty cool!

Because this dashboard based on Excel, you can adjust the size of the dashboard text, add titles, change the background and and and. There are almost no limits to your designs and many more things you could do, we just touched the surface.

Excel 2013 let’s us also build relationships to the table / data you import, calculate values, create and display KPI’s and and and. I strongly urge you to have a look at this superb technology, which will let us give some more flexibility in visualizing and analyzing out SCOM data. A good starting point is TechNet.