Search

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!

Select in the graph the line you want to add a predictive forecast to. The data point will be highlighted.

Select in the “Layout” tab the “TrendLine”

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

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 !!

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!

Solution(s):

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.)

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..

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 and maybe it will share it …