First published on TechNet on Mar 28, 2016
My name is Brad Watts and I'm a SCOM PFE. I wanted to take a little bit of time to demonstrate how you can use the Power BI Desktop application to build useful Key Performance Indicator reports from the information collected by System Center Operations Manager. Power BI toolset allows you to interact with various types of data big and small. The data can vary from text files to SQL Databases to FaceBook. The ability to interact with the data using filters and interactive charts differentiates it from using the reports built into System Center Operations Manager. In the below example we will be using a SQL Database and more specifically the OperationsManagerDW.
Prerequisites
The below scenario assumes that you have the following in place:
- System Center Operations Manager
- Agents Deployed to Servers
- At least read access to the OperationsManagerDW for the user running Power BI Desktop.
- Power BI Desktop: https://Power BI.microsoft.com/en-us/
Overview
I decided to break this blog article into four sections: Section 1: SQL Query: The most important part of any report is the query used to gather the information. For this example, we will use a single SQL Query. Section 2: Power BI Desktop Datasets: Once you have the queries you need to get the information you need to pull that information into Power BI Desktop. Section 3: Power BI Desktop Building the Report: We will walk through creating a Windows Operating System KPI Report. Section 4: Power BI Desktop Interacting with the Report: Once you have the report we'll walk through interacting with the data.
SQL Queries
Let first take a quick look at what the SQL Query looks like that we will use. We are doing a Key Performance Indicator report so our query is pulling performance information from the OperationsManagerDW database. My intention is not to teach you how to write the query but to highlight what I consider the import aspects of the query. The first highlight shows how I build the "ChartName." The purpose of this case statement is to make creating the charts easier once I get into Power BI. In essence what this does is if the Object Name is Logical Disk I will output <Server Name>:<Logical Disk Letter> and in any other instance the "ChartName" will just be <Server Name>. The second highlight shows which one of the Performance Views that I'm pulling from. You typically use either vPerfHourly or vPerfDaily. In some cases, you may also pull from vPerfRaw but this isn't a common scenario for reporting. In this case I'm pulling from vPerfHourly so the information is going to be aggregated hourly. The third highlight is how I'm determining how many days of performance data that I'm pulling back from the database. In this have I'm pulling 7 days worth of data. The OperationsManagerDW keeps 400 days of performance data by default but you only want to pull in what is important for your report for performance purposes. The fourth highlight determines which Performance Counters are being pulled in. In the below example I'm pulling anything with Object Name Processor, Processor Information, Memory, or Logical Disk and also any Object Name that contains SQL in the name.
select vme2.ManagedEntityRowId,vme2.DisplayName as ComputerName, (case when pr.objectname = 'LogicalDisk' then vme2.displayname + ':' + pri.instancename else vme2.DisplayName end) as ChartName ,vme.DisplayName as ClassName,vr.RuleDefaultName, pr.ObjectName,pr.CounterName,pri.InstanceName, vpd.DateTime,vpd.SampleCount,vpd.AverageValue,vpd.MinValue,vpd.MaxValue,vpd.StandardDeviation from vPerformanceRule pr
join vPerformanceRuleInstance pri on pri.rulerowId=pr.RuleRowId
join vRule vr on vr.RuleRowId=pr.RuleRowId
join Perf.vPerfHourly vpd on vpd.PerformanceRuleInstanceRowId=pri.PerformanceRuleInstanceRowId
join vManagedEntity vme on vme.ManagedEntityRowId=vpd.ManagedEntityRowId
join vManagedEntity vme2 on vme2.ManagedEntityRowId=vme.TopLevelHostManagedEntityRowId
where vpd.DateTime > DATEADD(dd,-7,GETUTCDATE())
and (pr.ObjectName in ('Processor','Processor Information','Memory','LogicalDisk') or pr.ObjectName like '%SQL%')
order by vpd.datetime asc
Power BI Desktop DataSets
Out next step is to take the above query and use it in the Power BI Desktop tool. When you launch the Power BI Desktop tool you will get a splash screen. On this screen you should see the option to "Get Data." Once you select the "Get Data" option you will see some of the different types of data that you can pull information from. In this example we will use "SQL Server Database." In Power BI Desktop you are using Power Query to pull in the information. Power Query can be very powerful if you don't have experience writing SQL Queries and will assist you in getting the information that you need without having to know SQL Query syntax. In our example we have the SQL Query we want to use so we'll enter the following information:
- Server: SQL Server name that has the OperationsManagerDW database. If it is a named instance you should enter <Server Name>\<Instance Name>
- Database: OperationsManagerDW
- SQL Statement: The SQL query from above.
Once you click next you will see a preview of the data that was pulled from the database. You can review this information before you import it into Power BI Desktop. Once you click on "Load" you have the option to either have the data cached in Power BI Desktop so that it's quicker to interact with but takes up memory and storage space or have the information pulled from the SQL Database each time you use the tool. One in the Power BI Desktop tool you can add additional datasets by clicking on the "Get Data" option on the tool bar. When you create a DataSet they come in with generic names such as "Dataset1". We want these to have a more meaningful display name. In order to do that you select on "Edit Queries" on the toolbar. Then under the "Query Settings" you can give each query a more friendly name.
Power BI Desktop Creating a Report
Now that we have the data pulled into Power BI Desktop we want to start creating reports. To start on a report you can choose the "Report" icon on the left hand side. This will open up a new report if this is you haven't previously created a report. Over on the right hand side of the screen you will see all of the different "Visualizations" that are available. For our KPI Report we could logically choose from two different types of "Visualizations." We could use either a "Bar Chart" or a "Line Chart." For this example we are going to use the "Bar Chart." Select the bar chart icon in the top left corner and it will place an empty bar chart in the report. Our next step is to start configuring the "Bar Chart" with information from out Data Set. In order to do this we simple drag and drop fields from the data set on the far right side into the different options for the chart. Drag the following fields to the following chart options:
- Data Set: ChartName goes to Chart Option: Axis
- Data Set: Average Value goes to Chart Option: Value (note: once this is moved over select the down arrow and change the aggregate from "Sum" to "Average")
So this populates that chart with performance data from out data set. But right now the chart is showing the average value for "ALL" performance data for each computer. Our next step is to set up the filters so it is only showing the data that we want. When we look at filters there are two types that we will use:
- Visual Level Filter: this filter will only apply for the visualization that is highlighted report. So in our case it only applies for the "Bar Chart" that is selected.
- Page Level Filter: this filter will filter data for the entire report. So if there are 4 bar charts this filter would affect each one.
In our case we will set up the following filters (to add filters you simply drag the field over from the dataset):
-
Visual Level Filters
- Average of Average Value
- ChartName
- ObjectName
- CounterName
-
Page Level Filters
- DateTime
- ComputerName
Now that we have the filters in place it's time to configure our first chart. We will configure this chart to show "LogicalDisk: Avg. Disk sec/Transfer." So just use the drop down on your ObjectName and CounterName to select this counter. Once you add a text box for a header to your "Bar Chart" you should see something similar to the below. Note that your chart will not be sorted by default. I will demonstrate doing this in the "Interacting with Reports" section. We are going to add an additional 3 bar charts to this example. The easiest way to do this is to simply highlight the chart that has already been created and then copy and paste it. Then you simply have to change the "Visualization Level Filter" to contain the proper Object Name/Counter Name combination. We will set up the additional 3 charts to contain the following performance data.
- Processor Information: % Processor Time
- Memory: % Memory Used
- Logical Disk: sec/Transfer
Once I add in these additional "Bar Charts" my report looks like the following:
Power BI Desktop: Interacting with Reports
Once you have created your reports in Power BI Desktop the real power comes with the ability to interact with this report. I will walk through some of the basic ways you can interact with the KPI Report we've created. By default each of our charts are not sorted. We'd like to have the worst performing in each chart show up at the top. In order to do this it's as simple as selecting the "…" at the top right corner of each chart and selecting to sort the data. You can click on the "Sort By" to change the direction it is sorted. So we'll select each of our charts and set up the sort order for each chart. We want to be able to pull in fresh information to our report at any time. In Power BI Desktop you accomplish this by simply clicking on the "Refresh" button on the toolbar. You need to have read access to the OperationsManagerDW to do this of course. We can also use the Page Level Filters to fine tune the information that we are looking at. Our SQL Query pulled the last 7 days' worth of information but what if we wanted to focus on the last 2 hours. To do this simply drop down on the DateTime filter and choose "Advance filtering." In the advanced filtering we can choose "is after" and specify and specific time. Once you have the DateTime configured like you want click on "Apply filter" and all of the charts in the report will refresh and show the performance data for that time period. What if we want to focus down the list of computers that are displayed in the charts? In this case we can use the "Page Level Filter" we set up for "ComputerName." Just use the drop down and select the servers you want to focus on. After you've chosen your servers all of the charts will only show information related to those servers. What if we want to focus on a particular chart instead of particular servers? In Power BI Desktop each chart has the option to do into "Focus Mode." This will take that chart and bring it out to full screen. This is just a couple of examples of how you can interact with Power BI Desktop reports. Of course if you have different visualization types then you can interact with them differently. You can also add in additional filters to add additional functionality.
Summary
In the above example we've walked through creating a new Operating System KPI report using System Center Operations Manager and Power BI Desktop. The great thing about this example is that you can easily take this example and easily create new KPI Reports for different products. The example query is already set up to create a SQL Server KPI but you could easily add in the proper Object Name/Counter Names to create Active Directory or SharePoint or any other application KPI that is monitored by System Center Operations Manager.
Microsoft
