How To Build IT-Control Chart - Use the Excel Pivot Table!

Continuing the topic of the previous post “One Example of BIRT Data Cubes Usage for Performance Data Analysis” I am showing here the way how to transform raw data to a “SEDS DB” format suitable for IT- Control Chart building or for exception detection. Based on the published on this blog SEDS-lite introduction it is “...building data for charting/detecting” task which is seen on the picture:

But in this case it is strictly manual process (unless someone wants to use VBA to automate that within MS Excel….) and requires the same basically approach as Data Cube/CrossTable usage in BIRT and in MS Excel it is called “PivotTable and PivotChart report“ listed under “data” menu item.

Below are a few screenshots that could help someone who is a bit familiar with EXCEL to understand how to build IT-Control Charts in order to analyze performance data in SEDS terms.

The input data is the same as in the previous post – just date/hour stamped system utilization metric (link to it). Additionally three calculated variables were added: Weekday (using Excel WEEKDAY () function) and weekhour as seen on the next picture:

/CPUdata/ sheet

Then the pivot table was built as shown on the next screenshot against raw data plus calculated weekhour field, which is actually is specified in “row” section of Pivot Table Layout Wizard (it is a bit similar with CrossTable object in BIRT; indeed, the Excel Pivot Table is the another way to work with Data Cubes too!):

/PivotForITcontrolChart/ sheet

Then three other columns were added right next to the pivot table to be able to compare Actual vs. Base-line and calculate Control limits (UCL and LCL). To do that, the “CPU util. Actual” data were referenced from the raw /CPUdata/  sheet where the last week data considered as Actual. Control limits calculation was done by usual spreadsheet formula and the picture shows that formula for UCL.

The last step was to build a chart against the data range,  which includes pivot table and those three additional fields. See result IT Control Chart on the final picture:

Do you see where exceptions (anomalies)  happened there?

Note that is IT-Control chart where the last day with actual data at the very right last 24 hours on Saturday. So that report made by Excel or BIRT is good to run once a week (e.g. by Sundays before work hours) to get all last week exceptions. To be more dynamic this report should be a bit modified (by adding "refreshing" birder) to run it daily, so minor exception first happened in the Thursday could be captured at least on Friday morning and one could make some proactive measures to avoid overutilization issue the chart shows for Friday and especially Saturday. The most dynamic way is to run that hourly (Excel is not good for that - use BIRT!) to be able to react on the first exception with a few next hours! See live example how that's suppose to be here: http://youtu.be/NTOODZAccvk or here: http://youtu.be/cQ4bk1HNuRk

By the way, I plan to prepare one another workshop type of presentation to demonstrate the technique  discussed in my last posts and also to share actual reports maybe during some CMG.org events in the nearest future...