seds lite using open source tools r birt and mysql to report and analyze performance data l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
SEDS-Lite: Using Open Source Tools (R, BIRT and MySQL) to Report and Analyze Performance Data PowerPoint Presentation
Download Presentation
SEDS-Lite: Using Open Source Tools (R, BIRT and MySQL) to Report and Analyze Performance Data

Loading in 2 Seconds...

play fullscreen
1 / 26

SEDS-Lite: Using Open Source Tools (R, BIRT and MySQL) to Report and Analyze Performance Data - PowerPoint PPT Presentation


  • 385 Views
  • Uploaded on

SEDS-Lite: Using Open Source Tools (R, BIRT and MySQL) to Report and Analyze Performance Data . Igor Trubin, PhD http://www.itrubin.blogspot.com. A genda. SEDS-Lite project: Open source based implementation of Statistical Exception Detection System - SEDS-Lite Introduction

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'SEDS-Lite: Using Open Source Tools (R, BIRT and MySQL) to Report and Analyze Performance Data' - barney


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
seds lite using open source tools r birt and mysql to report and analyze performance data

SEDS-Lite: Using Open Source Tools (R, BIRT and MySQL) to Report and Analyze Performance Data

Igor Trubin, PhD

http://www.itrubin.blogspot.com

a genda
Agenda
  • SEDS-Lite project: Open source based implementation of Statistical Exception Detection System - SEDS-Lite Introduction
  • Open Source and “free” tools for Capacity Management – review
  • Control Charts as a good test of using different tools for Capacity management - Power of Control Charts and IT-Chart Concept (Part 1) 
  • R-system to built Control Charts (System Management by Exception: Power of Control ChartsMar 25, 2009)
  • 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 (to build using BIRT)
  • Simplified Exception Detection using BIRT: UCL=LCL : How many standard deviations do we use for Control Charting? Use ZERO!
slide3

SEDS-Lite

  • Open source based implementation of Statistical Exception Detection System - SEDS-Lite Introduction
open source and free tools for capacity management
Open Source and “free” tools for Capacity Management
  • Previous reviews:
    • MeasureIT - Issue 2.05 - Open Source Capacity & Performance Management Tools for Windows & Unix Systems (2004 - Brian Johnson )
    • Capacity Planning and Performance Monitoring with Free Tools (for mspcmg 2011! - Adrian Cockcroft)
  • The missed point is BI and Generic DB open source tools usage for Performance Analysis and Reporting
    • BIRT (Business Intelligence and Reporting Tools)
    • MySQL
    • NoSQL
control charts as a good test of using different tools for capacity management
Control Charts as a good test of using different tools for Capacity management
  • What is the Control Chart?
    •  Power of Control Charts and IT-Chart Concept  (CMG’09 -’10)
r system to built control charts
R-system to built Control Charts
  • CMG’09 workshop “Power of Control Charts “, SCMG - Mar 25, 2009

The input data is Unix File Space Utilization:

  • EXAMPLE3: Monthly Profile
  • R download: http://www.r-project.org/

R-script (published on my blog):

(FYI: qcc: An R package for quality control charting :

http://cran.r-project.org/web/packages/qcc/index.html)

slide7

R-system to built Control Charts

  • CMG’09 workshop “Power of Control Charts “, SCMG - Mar 25, 2009
birt based control chart
BIRT based Control Chart 
  • What is BIRT – see BIRT Report Designer Tutorial Demo
  • There is a tool that generates control charts against performance data using BIRT:
  • The IT-Control Chart would give much more value for analyzing time stamped historical data. Is that possible to build using BIRT?

8

slide9

BIRT based IT-Control Chart: Live demo

  • The case where the input data source is CSV file:

But how to calculate that UCL and LCL numbers!?

how to calculate that ucl and lcl numbers use excel pivot table
How to calculate that UCL and LCL numbers!? Use EXCEL Pivot Table
  • Pivot Table creation (IT-Chart Builder)
  • Weekhour calculation:
  • Pivot Chart built
how to calculate that ucl and lcl numbers birt data cubes usage for performance data analysis
Data behind IT Control chart is a Cube:How to calculate that UCL and LCL numbers!? BIRT Data Cubes Usage for Performance Data Analysis
  • Input data is raw stamped measures
  • The result (in the form of BIRT report designer preview) is on the picture at left

11

how to calculate that ucl and lcl numbers birt data cubes usage for performance data analysis12
(1) The Reference set or base-line is based on the input raw data with some filtering and computed columns (weekday and weekhour)How to calculate that UCL and LCL numbers!? BIRT Data Cubes Usage for Performance Data Analysis
  • (2) the Actual data set is the same but having the different filter: (raw[“date”] Greater “2011-04-02”)

12

how to calculate that ucl and lcl numbers birt data cubes usage for performance data analysis13
(3) To combine both data sets for comparing base-line vs. actual, the “Data Set1” is built as a “Joint Data Set” by the following BIRT Query builder:How to calculate that UCL and LCL numbers!? BIRT Data Cubes Usage for Performance Data Analysis
  • (4) Then the Data Cube was built in the BIRT Data Cube Builder with the structure shown on the following screen:
  • (5) The next step is building report starting with Cross Table (which is picked as an object from BIRT Report designer “Pallete”):

The picture above shows also what fields are chosen from Cube to Cross table.

13

how to calculate that ucl and lcl numbers birt data cubes usage for performance data analysis14
(6)The final step is dropping “Chart” object from “Palette” and adding UCL calculation using Expression Builder for additional Value (Y) Series:How to calculate that UCL and LCL numbers!? BIRT Data Cubes Usage for Performance Data Analysis

14

how to calculate that ucl and lcl numbers sql script for mysql database upload
How to calculate that UCL and LCL numbers!? SQL script for MySQL Database (upload)
  • (1) That raw data (the same) need to be uploaded to some table (CPUutil) in the MySQL schema (ServerMetric) by using the following script (sqlScriptToUploadCSVforSEDS.sql):

16

slide17
(2) Then the output (result) data (ActualVsHistoric table) is built using the following script (sqlScriptToControlChartforSEDS.sql):

How to calculate that UCL and LCL numbers!? SQL script for MySQL Database (Calculate)

17

building it control chart by birt against data from the mysql database
(3) In the BIRT, the connection to MySQL database is established (to MySQLti  with schema  ServerMetrics to table ActualVsHistorical):Building IT-Control Chart by BIRT against Data from the MySQL Database 

18

building it control chart by birt against data from the mysql database20
(5) In BIRT you can specify report parameters, that could be then a part of any constants including for filtering (to change a baseline or to provide server or metric names). Building IT-Control Chart by BIRT against Data from the MySQL Database 
  • (6) Finally the report should be run to get the following result, which is almost identical with the one built for BIRT based Control Chart post (slide 15)
  • Lastly: R-system can be used to run the same SQL script against MySQL by using Package 'RMySQL‘!!!!

20

ev control chart to build using birt
EV meta-metric introduced in 2001 as a measure of anomaly severity. (The Exception Value Concept to Measure Magnitude of Systems Behavior Anomalies)EV-Control Chart (to build using BIRT)
  • In CMG’08 paper “Exception Based Modeling and Forecasting” That metric was plotted using Excel to explain how it could be used for a new trend starting point recognition:
  • It is the difference (integral) between actual data and control limits.

21

ev control chart to build using birt22
Plotting that meta-metric and/or two their components (EV+ and EV-) over time gives a valuable picture of system behavior.EV-Control Chart (to build using BIRT)
  • The BIRT example of the EV-chart against the same sample data:

EV calculation

22

ev control chart to build using birt23
EV-Control Chart (to build using BIRT)
  • The EXCEL example of the EV-chart against the same sample data:

23

seds lite exception detection ev based using birt
It is based on real script developed and successfully tested against real data forBIRT toolto get exceptional servers list:SEDS-Lite: Exception Detection (EV-based) using BIRT

Lite version: (UCL=LCL)

  • Below is example of DB2-like SQL script to implement that.

24

references
References

Igor Trubin: “Global and Application Levels Exception Detection System, Based on MASF Technique”, Proceedings of the Computer Measurement Group, 2002. (http://www.cmg.org/measureit/shared/trubin_02.pdf)

Linwood Merritt, Igor Trubin: “Disk Subsystem Capacity Management Based on Business Drivers I/O Performance Metrics and MASF”, Proceedings of the Computer Measurement Group, 2003. (http://regions.cmg.org/regions/ncacmg/downloads/june162004_session3.doc)

Linwood Merritt, Igor Trubin:: “Mainframe Global and Workload Level Statistical Exception Detection System, Based on MASF”, Proceedings of the Computer Measurement Group, 2004. (http://www.cmg.org/membersonly/2004/papers/4179.pdf)

Igor Trubin: “Capturing Workload Pathology by Statistical Exception Detection System”, Proceedings of the Computer Measurement Group, 2005. (http://www.cmg.org/membersonly/2005/papers/5016.pdf)

Igor Trubin: “System Management by Exception, Part 6”, Proceedings of the Computer Measurement Group, 2006. (http://www.cmg.org/membersonly/2006/papers/6120.pdf)

Igor Trubin: “System Management by Exception, Part Final”, Proceedings of the Computer Measurement Group, 2007.(http://regions.cmg.org/regions/scmg/fall_07/richmond/SEDSCMG2007_v4.pdf )

Igor Trubin: “Exception Based Modeling and Forecasting”, Proceedings of the Computer Measurement Group, 2008. (http://itrubin.blogspot.com/2008/08/exception-based-modeling-and.html )

Igor Trubin: “IT-Control Chart”, Proceedings of the Computer Measurement Group, 2010.(http://itrubin.blogspot.com/2010/07/my-new-cmg10-paper-it-control-charts.html )

25

questions
Questions?

Everything is ready to buildSEDS-Lite: Using Open Source Tools (R, BIRT and MySQL)

Igor Trubin, PhD

http://www.itrubin.blogspot.com

SCMG’11

26