SEDS-Lite: Using Open Source Tools (R, BIRT, MySQL) to Report and Analyze Performance Data - my new CMG'12 paper

20202 UPDATE: The SEDS-Lite web app is about to be released!
_________________________________________________________
I wrote this paper with some help from Shadi G. (from Dublin, also IBMer).
The paper is based on my blog postings:
SEDS-Lite Presentation at Southern CMG Meeting in the SAS Institute
SEDS-Lite Introduction
How To Build IT-Control Chart - Use the Excel Pivot Table!
BIRT based Control Chart

BIRT Data Cubes Usage for Performance Data Analysis

Building IT-Control Chart by BIRT against Data from the MySQL Database

EV-Control Chart

UCL=LCL : How many standard deviations do we use for Control Charting? Use ZERO!

HERE IS THE VIDEO PRESENTATION
Below is the abstract:
Statistical Exception Detection (SEDS) is one of the variations of learning behavior based performance analysis methodology developed, implemented and published by Author. This paper took main SEDS tools – IT-Control Chart and Exceptions (Anomalies) Detector - and showed how that could be built by Open Source type of BI tools, such as R, BIRT and MySQL or just by spreadsheet. The paper includes source codes, tool screen-shots and report input/output examples to allow reader building/developing a light version of SEDS.
-------------------------
The presentation of this paper is scheduled on December 5th, 2012 Wednesday, 2:45:00 PM - 3:45:00 PM in Las Vegas, Nevada
-------------------------

THAT IS MY SECOND CMG'12 PAPER. THE FIRST ONE ANNOUNCED HERE:

AIX frame and LPAR level Capacity Planning. User Case for Online Banking Application

R-Script to Aggregate (ETL to MySQL) Actual data with Base-line data for IT-Control Charts

At my previous post (R-script to plot IT-Control Chart against MySQL) the task was given to write a R-script to pre-process (ETL) the raw date-hour stamped data to the DATA-cubical format for Control Charting.

Here is the solution:
I have just transformed the already developed SQL script to the RODBC based R-Script which can be seen below:

The result of the script run is the "ActualVsHistorical" table in the servermentrics database on MySQL with the following data that is identical with the data used for plotting IT-Control Chart published in the previous post. The data itself can be seen by just typing the data frame name in the R-Console window:

So, all main elements of SEDS-lite project were prototyped and published on my posts. Maybe one more task is left, which is to illustrate on R how the exceptional (based on EV meta-metric filtering)  list of objects (servers) can be created as a part of anomalies detection. So far that was done and published in this blog and so far it is only in the "DB2"-like SQL format to run within BIRT. See the post about that here: UCL=LCL : How many standard deviations do we use for Control Charting? Use ZERO!

.

R-script to plot IT-Control Chart against MySQL

Continuing playing with the open-source tools to build some SEDS elements, I have developed the simple R-script to plot the IT-Control chart against data stored in MySQL database.

I used the same MySQL data that was already been built and used for IT-Control Charting by BIRT reporting system. See the following post about how that was done: Building IT-Control Chart by BIRT against Data from the MySQL Database. To do that I have used RODBC package to connect and query data from MySQL database through the MySQL ODBC driver.

Actually, I have just slightly modified the R-script which I wrote for my "Power of Control Chart" workshop That script could be found in the following post: IT-Chart: The Best Way to Visualize IT Systems Performance

Here is my new script (click on it to enlarge) :

Here is the result:

 

which practically identical with what was done by BIRT (see link to BIRT based  picture here).

If you are a programmer you would notice how it is easier to build charts using R versus BIRT (not-for-programmer, menu-based report generator).

The data used for this exercise was already preprocessed to the DATA-cubical format from raw date-hour stamped data (see the SQL script for that here). But what about doing this pre-processing also by R?

That is the next task ... (could be your homework ;). The simplest approach is again to use RODBC package just to run the mentioned above SQL script within R-system. Other and better approach is to do that using the natural R-system data manipulation technique.

My CMG'10 presentation - "IT-Control Charts"

I will go to CMG conference this time only for one day just to present my paper "IT-Control Charts" on Wednesday December 8th 10:30 - You are WELCOME!

Check it in the CMG conference agenda  - http://www.cmg.org/cgi-bin/agenda_2010.pl?action=more&token=5030

For Russian readers (Информация по русски здесь) I made a posting about that event in my Russian mirror blog: http://ukor.blogspot.com/2010/11/cmg10_15.html

Near-Real-Time IT-Control Chart R-Simulation

UPDATE: Now the following free web tool to build IT-control charts is available:

                           www.Perfomalist.com

See more explanation

Review of IT Control Chart

SEDS-Lite Introduction

In the purpose of sharing in codes some ideas of exception detection metodology I am developing the SEDS-Lite version using "R"-scripting (http://www.r-project.org/). One of the scripts (cchrt.r - see front picture of this post) has been already published on my blog and that builds control charts against CSV data: http://itrubin.blogspot.com/2009/03/power-of-control-charts.html

How exactly that works  and more R-scripts will be presented on my workshop at up-coming CMG'09 conference in Dallas (http://itrubin.blogspot.com/2009/07/my-cmg09-sunday-workshop.html ) - you are welcome to attend!

Some additional R-scripts can be found in my SCMG presentation http://itrubin.blogspot.com/2009/05/seds-charts-at-scmg.html

The trick is the SAS 9.2 can execute R scripts. You can also try another SAS-like product (http://www.teamwpc.co.uk/products/wps) which also understands R, plus there are some ways to use SAS data for R-graphing: http://www.hollandnumerics.co.uk/pdf/SAS2R2SAS_paper.pdf  or just using the "SAS and R" good book ( I have recently bought that and highly recommend): http://sas-and-r.blogspot.com/