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


3 Responses to “SCOM meets Excel Power Pivot part 2”

  1. Anonymous June 21, 2012 at 12:54 #

    nice work, keep it coming

  2. gerrie June 21, 2012 at 12:56 #

    how can i add parameters fields to the powerpivot query?

  3. software development services August 7, 2013 at 09:29 #

    My brother recommended I might like this blog.
    He was entirely right. This post truly made my day.
    You can not imagine simply how much time I had spent for this
    information! Thanks!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: