Tag Archives: PowerPivot

SCOM meets Excel Power Pivot part 2

17 Mar

This post will be describing the extensions mention in my pervious post about SCOM and  Power Pivot:

Predictive trend line so we can see when the disk space will be 0 in the future.

Monthly / Weekly / Yearly Aggregations so we can drill in and out of the data. SCOM is only providing daily and hourly aggregations but if you have a lot of data you will have to look at a higher perspective.

Real data mining so you can detect strange behaviors in your data.  [I am out of time this week. Maybe I will blog on this later]

So here we start.

Predictive trend

This one is quite simple to realize. I really love Power Pivot , ill show you!

  1. Select in the graph the line you want to add a predictive forecast to. The data point will be highlighted.
  2. Select in the “Layout” tab the “TrendLine”
  3. Select “Linear Forecast trendline”


Now you will see a trend forecast line. Leave the line selected and now go to the same menu and select “More trendline Options”


In the following menu we set “Forward” to lets say 10. Now the forecast will calculate 10 data points forwards on the X (date) axis.


Next we select “Glow and Soft Edges” because we want to give the trendline forecast some highlighting. Select Presets and take Red.


The result will be:


Cool isn’t it !!

I don’t have a solution to calculate the dates on the x axis so you can see on what date the line will hit 0. But I guess with a macro you could do this. OR by RTM Knipogende emoticon


Monthly / Weekly / Yearly Aggregations

Now comes the part that is most interesting. When a KPI outputs an logical disk measurement it will be written to the OPS DB and to the OPS DWH. Since the DWH will be holding a long time period of this data you can imagine that the size of the DWH DB will grow huge. To solve this the data must be aggregated to periods. If the aggregate process is successful completed the RAW data will be deleted/groomed.  This sounds good but not for the performance data, the only default aggregations are Hourly , Daily periods. But what if you want Monthly or even Yearly aggregations. ???? Again Power Pivot to the rescue !

Thanks to a friend of mine , expert in DWH Datamining and also Excel  (Albert van Dok), I found the solution. In excel we create a new work sheet. In this sheet, name it “Date Sheet”,  we make a Day (dag), Month (mnd) , Year (jaar) column. And fill it with a date range of lets say 2 years. (2011,2012).


Now we link this table to the data table we got from the SCOM DWH. See next steps. Select all the data rows from the A B and C columns. Now we select in the “PowerPivot” tab “Create Linked Table”


The power pivot windows will popup and show you the date range data you selected. Next step is to link the date columns to the date columns from the SCOM logical disk data.

Select in the right corner “Diagram View”


Now we select the DateTime column from the Query table and drag it to the to the dag column in the Table1. The result should be as below.


Next step is to create the Hierarchy calculation in order for getting the correct aggregations.

Select on Table1 all the columns and select right click “Create Hierarchy”


And the result will be :


You may/can rename the default created names.

Now we are going back to the excel sheet. Select the Trend Graph you want to display the monthly aggregation into and minimize in the Field List windows the query tree. And maximize the Table1 tree. Now drag the Jaar and mnd columns to the axis fields and the jaar,mnd,dag columns to the Slicers Horizontal.


The end result will be on the sheet: Selection windows for the year month and day.
When you select 1 or more columns in this windows the graph will be recalculated and the aggregated monthly data will be displayed. Look at the x axis. It displays the selected months and the y axis will contain all the AVG diskdata.


Next step is to create a new graph and do the same for the yearly aggregated data. Almost the same as the field selections above but now only the jaar (year) column in the Axis Fields window.

The end result will be:


Now you have created 2 new aggregations for the SCOM data. Good work !! Knipogende emoticon



Michel Kamp

SCOM Meets Excel PowerPivot

10 Mar


In this post I will share some ideas I had a long time but never had time to realize it. It is just a showcase in the future I will of course extend this to a real production version.

The idea:

I wanted to do data mining on performance data that System Center Operations Manager SCOM has collected. So a data analyst could look at the data , do real-time / interactive actions to the data view perspectives and use forecasting to find quickly the bottlenecks to solve. Again a great predictive approach for the availability increment of your IT environment!


Some solutions for this came up in my mind:

writing my own SQL query’s to do this.

Disadvantage: complex to maintain and not end user (data analyst) friendly

writing my own SQL reports to do this.

So combine the SQL query’s and use the output to render a report.

Disadvantage: In most cases a data analyst wants to change the perspectives at runtime. A report is static and needs to be rerendered every time and this is time consuming.

– Performance Point !

Yhea that sounds good. A few years ago Microsoft it self posted a demo to use performance point and SCOM DWH to do this. ( BI – Dashboard Integration )

Disadvantage: O course I tried this , It works but it is really hard to configure and extend and for sure if you aren’t a expert in maintaining SharePoint , SQL analyze services and OLAP.

Hmm … then I found the magic word: “PowerPivot”

Powerpivot is a really good tool for a data annalist. The pivot table views can be changed real-time and also aggregations can be changed on the fly. Since PowerPivot integrates smoothly with SharePoint it would mean the end user (data annalist) won’t have to install a client site software. Only a internet explorer is needed. (The SharePoint part I will blog in a future post.)

So the solution to try out was found. PowerPivot it will be….


What do you need to start:

1) operational SCOM 2007/2012 environment with the DWH role enabled.

2) Office 2010 with excel installed on your analyst end-user computer

3) PowerPivot for Excel 2010 plugin installed on your analyst end-user computer. ( http://www.microsoft.com/downloads/nl-nl/details.aspx?FamilyID=4b5d8276-d8c8-49b3-a653-f09f744ed9c5 )

4) SQL server account to access the SCOM DWH database and read tables.

If you have done all of this you are ready to start:

Filling the Pivot Table

Open Excel and create a new work book. I am not going to show you a screenshot of it Knipogende emoticon

Now notice the new powerpivot tab in the top bar. If not then you have to you google bing to solve this. Hint: add-inn security.


Click on the PowerPivot and select the icon “PowerPivot”



Now the powerpivot tool will be opened in a separate window.

In the Home Tab select “Get External Data” and “From SQL Server”


Now we must specify the SQL server connection to the SCOM DWH. Fill in the correct SQL server instance/Database name and credentials


Next steps will be something you will have to follow me blind on. For now I am not going in to deep on explaining the details.

We are going to add the SQL query we want to use for getting the data


before we know what to past in this window we must make the SQL query.

Open SQL management studio connect to the SCOM DWH and run the query below:

select displayname , ManagedEntityRowId from vManagedEntity
where displayname like ‘Windows Server 2008 Computer Group%’

Now the result is the instance id of the scom group “Windows Server 2008 Computer Group” that contains all the Windows 2008 computers targets.


Next we want to get the performance data from the logical disk concerning:

Logical Disk Free Megabytes 2008


% Logical Disk Free Space 2008

We have to find the rule GIUDs for this. Run the query:

select * from dbo.vRule where
rulesystemName like ‘Microsoft.Windows.Server.2008.LogicalDisk.%’


Select the 2 highlighted GUIDs. This are the performance rules that are collecting the wanted disk data.

Now we combine this in the query below:

exec Microsoft_SystemCenter_DataWarehouse_Report_Library_PerformaceReportDataGet
@StartDate=‘2012-03-5 09:10:00:000’
,@EndDate=‘2012-03-9 09:10:00:000’

Change the @startDate and @EndDate to the period you want to get the data. Test the query to see if it gives output.



So now you have the query we can use in the PowerPivot. Copy and past it in to this window.


Press Save and the query will be executed and the results will be displayed.


Next we have to create some Calculated fields , otherwise we can’t add this to the Value  pane window in the graph.

Go select the AverageValue column. And select in the “Home Tab” > “AutoSum”> “Average”


Now you have created a calculated field. Do the same for all other values you want to display in a graph.


At this point we have a pivottable with value data!

Pivot to Graph

Next we are going to create the trend graphs for the annalist. In the PowerPivot windows Press the PivotTable Icon > “Two Charts (Vertical)”


Now there will be in excel created 3 sheets. Two sheets containing the data from the pivot table and 1 sheet containing the 2 graphs. I rename the sheets to better names:


Now we are going to configure the graphs. Select in Excel the Graph Sheet (diskspace Analyze). Click on the top graph. On the right the field select windows will be shown. Select the fields show in the picture below and drag it to the correct pane on the bottom. (click on the screenshot to enlarge)


Some short explanation on what you just did:
The field in the Axis Fields is the X axis and shows the date times. The field dropped in the Values pane will display on the Y axis, this will be the disk space value. This are the 2 most important fields. But…. what if a computer has 2 logical disks then the values will be added to one line instead of 2 lines. So we need to get categories configured. This is done by the fields in the Legend pane. So now we have a graph but it shows all the data from the 2 performance rules. That not really interactive, we want to select the rules runtime. So we drag the RuleDisplayName into the Slicers vertical pane and whala we see our interactive selection list. We do the same for the interactive selection of the computers. Drag the Path field into the Slicers Horizontal pane.

Last we play with the Graph styles. Select the Graph and then change the style.


Select the type “Line with markers”


Next we change the looks of the graph. Select the graph and play with the Layout options.


The result will be:


Cool it is isn’t it ???? Now you can select the logical disks interactively and the graph will be rebuild. 

To be continued…

Because this post is becoming to long I will post in my next blog about some extras a data annalist has to have.
– Predictive trend line so we can see when the disk space will be 0 in the future.

– Monthly / Weekly / Yearly Aggregations so we can drill in and out of the data. SCOM is only providing daily and hourly aggregations but if you have a lot of data you will have to look at a higher perspective.

– Real data mining so you can detect strange behaviors in your data.

Below a teaser.. Knipogende emoticon



I hope you see how easy it is to use PowerPivot to analyze your SCOM data.

If you want the excel source file you will have to leave a message below  Knipogende emoticon and maybe it will share it …

Happy Scomming,

Michel Kamp