Skip to Content
Author's profile photo Former Member

Monitoring XI/PI queues on your iPhone — Part 3 – Create charts

h4. Introduction    This blog is part of a series on Monitoring XI/PI queues on your iPhone  (Monitoring XI/PI queues on your iPhone — Part 1) . In Part 1 of this series, I described the goal of the project. In Part 2, I setup the Perl script to automatically download queue data from SAP every 5 minutes. In this part, I discuss how to analyze this data and create a bar chart for a iPhone WebApp.   You can download the full environment from {code:html}*PI-monitor project at sourceforge*{code}. Read the Quick Start section in Part 1  (Monitoring XI/PI queues on your iPhone — Part 1) to setup the environment.   {code:html}*View a live demo*{code}.  h4. The constraints and their consequence on design * The charting / graphing must be Javascript (to run on the iPhone). These constraints led to my design of a seperate Perl script to analyze the queue data and publish the charts as a JQuery javascript which is uploaded to the website. The web application is simply a set of static HTML / javascript files. To generate the bar charts, I decided to use Google Charts as it was adequate for the task.  So in this blog, you combine the following technologies to get a iPhone Webapp. For screenshots, look at Part 1  (Monitoring XI/PI queues on your iPhone — Part 1).  1. Perl and SQLite to analyze data. 2.  [JQuery |] and Google Charts  ( to create bar charts.0.1. [iWebKit |] – an open source kit for iPhone web applications. 0.2. h4. Analyzing the stored Queue Data   SAP PI/XI relies on queues in the ABAP stack to get messages processed. The blog {code:html}XI Asynchronous Message Processing: Understanding XI Queues -Part I{code} provides a good explanation of how PI uses queues. For my application,I want to see a stacked bar chart of all messages in the PI message queues (SMQ2). There should be a different color for each queue status.  Lets examine the SQLite Queue database. 1. Download SQLite (if you haven’t already). 2. Open your database from the command line *sqlite3.exe c:strawberrypi_monitorQueueMonitoring.sqdb*  3. To leave SQLITE, type *.exit* at the command line. 4. Type in the SQL query on the QueueData table in the QueueMonitoring.sqdb. (if you are copying to clipboard, edit this in notepad before trying it in SQLite3). select SnapshotTime,System,Count(QNAME) as Queues,Sum(QDeep) as Total, SUM(CASE WHEN QSTATE=”SYSFAIL ” THEN QDEEP ELSE 0 END) AS Sysfail , SUM(CASE WHEN QSTATE=”RETRY ” THEN QDEEP ELSE 0 END) AS Retry , SUM(CASE WHEN QSTATE=”RUNNING ” THEN QDEEP ELSE 0 END) AS Running , SUM(CASE WHEN QSTATE=”READY ” THEN QDEEP ELSE 0 END) AS Ready from QueueData group by SnapshotTime,System,QueueDirection having QueueDirection=”IN” AND SnapshotTime>datetime(‘now’,’-1 days’) AND system=’PXP’ order by snapshottime Desc, system   This summarizes the queue data for the last 24 hours into Snapshot time |SID|queues|messages|sysfailmessages|retrymessages|runningmessages|readymessages 2010-05-08 19:24:00|PXP|2|111|110|0|1|0 2010-05-08 19:19:01|PXP|1|99|99|0|0|0 2010-05-08 19:14:01|PXP|1|91|91|0|0|0 2010-05-08 19:09:01|PXP|1|81|81|0|0|0 2010-05-08 19:04:01|PXP|1|71|71|0|0|0 2010-05-08 18:59:01|PXP|2|62|61|0|1|0 2010-05-08 18:54:01|PXP|1|52|52|0|0|0 2010-05-08 18:49:01|PXP|1|42|42|0|0|0  h3. Graphing the data with Google Charts Now lets take the above data and convert it into a stacked bar chart. I chose a Javascript library (Google Charts) to do this at the client’s browser. That way, no server side logic is needed (see constraints).  Google Charts provides a good API – however it is difficult to create the correct string by reading the documentation. So I suggest youu spend some time at their website. There are two flavors of {code:html}Google Charts — Image Charts and Interactive Charts{code}. For now, I am happy with using the Image Charts API to create a static image. Even the Image Chart API has a rich selection of charts that can be seen in their chart gallery  ( To get a good feeling of their API, I spent a lot of time in the Live Chart Playground  (, which is a lot more convenient than creating the URLs by hand.  The LiveChart parameters for a stacked bar chart are cht=bvs chs=320 chco=4D89F9,C6D9FD chd=t:10,50,60,80,40     50,60,100,40,20 chds=0,160 *This results in the following Image Chart URL* [,C6D9FD&chd=t:10,50,60,80,40|50,60,100,40,20&chds=0,160 |,C6D9FD&chd=t:10,50,60,80,40|50,60,100,40,20&chds=0,160]  !,C6D9FD&chd=t:10,50,60,80,40|50,60,100,40,20&chds=0,160|height=100|alt=|width=100|src=,C6D9FD&chd=t:10,50,60,80,40|50,60,100,40,20&chds=0,160|border=1!  I quickly found that constructing the URL for my data would look very ugly. So I needed something more. h3. JQuery to the rescue  JQuery is a brilliant Javascript library that simplifies scripting for webpages. If you haven’t come across JQuery, recommend you take a look at the JQuery web site  ( JQuery is highly extensible and has a large set of JQuery extensions.  I found a JQuery plug in for Google Charts  ( With this jQuery, it becomes very easy to create a graph. I can concentrate on what I want – rather than the URL syntax. Here is the final jQuery that I used for the chart.  $(function () { $(‘#*basicGChart_PP4’*).gchart({type: ‘barVert’, maxValue: 672.0, title: ‘PP4 2010-05-08 16:42:31’, titleColor: ‘white’, titleSize: 14, barWidth: 12, backgroundColor: ‘black’, legend: ‘right’, axes: [ $.gchart.axis(‘left’, 0, 672.0, ‘white’, ‘right’)],  dataLabels:  (‘:47′,’:57′,’:36′,’:58′,’:06′,’:31′,’:55′,’:49′,’:54′,’:42′),   series: [  $.gchart.series(‘Sysfail’,  ( 0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0 ), ‘FF8000’),   $.gchart.series(‘Retry’,  ( 0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0 ), ‘F7BE81’),   $.gchart.series(‘Ready’,  ( 350.0,2.0,121.0,20.0,1.0,0.0,0.0,1.0,1.0,0.0 ), ’58ACFA’),   $.gchart.series(‘Running’,  ( 322.0,1.0,41.0,35.0,1.0,1.0,2.0,1.0,1.0,1.0 ), ‘088A08’),   $.gchart.series(‘Other’,  ( 350,2,121,20,1,0,0,1,1,0 ), ‘FFFFFF’)  ]}); });  The resulting bar chart is show below. The “basicGChart_PP4” is the link from jQuery to HTML. It tells JQuery which DIV to modify in the HTML page.Bar Chart of the data

Assigned Tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.