unCharted Territory

In my line of work, from time to time I have to produce pretty graphs for presentations, documents etc.  One thing that drives me and my colleagues crazy is non-consistent formatting within a document; graphs that have different common features, such as fonts, background colours.

One thing I really like about working at Enkitec is our eDB360 utility, written by Carlos Sierra and available from http://www.enkitec.com/products/edb360  This utility will gather a comprehensive set of data from Oracle databases and is an excellent starting point for a health check.  One of the features I like the best are the charts that show the data very nicely (as well as the HTML, text and CSV outputs) and I recently asked Carlos if he could give me that functionality in a stand-alone script.  Happily, he obliged and came up with a script template that can be used to extract and chart data very nicely.

Here’s a sample from a test eDB360 report:

Carlos has kindly published his script on his blog for all to enjoy:

So part of my original reason for asking is I am working with a client and have no hands-on access to their systems.  To make matters worse (and through no fault of theirs), they are busy people and it can take days for me to get what I want and often it’s in the best format available – CSV or Excel.  That’s nice but doesn’t allow me to insert charts that have a consistent look and feel.  There are two solutions to this:

  • Get the client to dump out their entire workload repository tables into a TAR file using the Enkitec eAdam tool
  • eAdam tool link: http://carlos-sierra.net/2014/07/27/eadam/
  • Use the client’s CSV or Excel data to create my own instant web page charts, no database access required.

So back to that data… What can I do with it?  Often it can be quite tedious and look something like the image below.  You may even have to create an Excel pivot table to reformat some of that data using either sum or average functions.   In my example, the data is simple and represents the imaginary use of swap on a three node database cluster, averaged by hour.

It turns out that Excel would do a terrible job making it look the same as all the other charts I have available.  So here’s what my code generated for a chart – looks similar in format and feel to the charts already created by the eDB360 tool and it came from ad-hoc query data.

Moving on the the fun part, here’s what the code looks like – for my sanity, I’ve pasted the code itself at the bottom of this entry.    This is the standard template code – typically what lies in this section handles the page setup and everything you see on the page outside the chart itself.  I will point out what you would want to modify:

  • Line 4: The title which would appear on a browser bar
  • Line 76: The page title appearing above the chart
  • Lines 77 to 79:  Notes that would appear under the title but above the chart
  • Lines 83 to 85: Where notes to go with the chart could appear
  • Line 90: Final note line, appearing in light grey underneath the chart notes.

Now in fairness, I really don’t need to change those lines.  For my purposes, I will use SnagIt or some other screen capture utility to grab the chart itself and create either a jpg or png file from that.  This is what will ultimately get pasted into the document.

Ok, so where’s the meat – where’s the data?  That’s currently hidden as I have collapsed lines 29 to 71.  I should also add that this section contains the title, format and axis labels, so it’s the most important part (not that the rest of it is less important because without it, the chart might not work). So here’s what I have put into this file:

  • Line 30: Headings for my data which ultimately become the labels
  • Lines 31 to 54: The data itself.  More on formatting in a bit
  • Line 60: The chart title
  • Line 65: The horizontal axis label
  • Line 66: The vertical axis label

Looks pretty simple, right?   It is, as long as you follow some rules.

  • The chart API is particular about what stream of data it is expecting; in this case an array.  The nature of this indicates that the first column of data will be along the horizontal (x) axis and the rest of the data will be graphed along the vertical (y) axis based on the values in these columns.
  • Note the format of each data line – opening/closing square brackets, comma separated values and single quotation marks around anything that is text rather than a number.  Of course the first column could also be numeric in nature (e.g. 2001, 2002, 2003…) in which case it does not require quotation marks.

So, that’s it.  Copy the template of the file and paste it into your favourite text editor.  Paste in your data from whatever source you are using.  Save it and ensure it has a .html extension.  Open it in your favourite browser.


<!-- $Header: sample.html 2014-07-29 s.p.butterworth@gmail.com $ -->

<style type="text/css">
body {font:10pt Arial,Helvetica,Geneva,sans-serif; color:black; background:white;}
h1 {font-size:16pt; font-weight:bold; color:#336699; border-bottom:1px solid #cccc99; margin-top:0pt; margin-bottom:0pt; padding:0px 0px 0px 0px;}
h2 {font-size:14pt; font-weight:bold; color:#336699; margin-top:4pt; margin-bottom:0pt;}
h3 {font-size:12pt; font-weight:bold; color:#336699; margin-top:4pt; margin-bottom:0pt;}
pre {font:8pt monospace;Monaco,"Courier New",Courier;}
a {color:#663300;}
table {font-size:8pt; border_collapse:collapse; empty-cells:show; white-space:nowrap; border:1px solid #cccc99;}
li {font-size:8pt; color:black; padding-left:4px; padding-right:4px; padding-bottom:2px;}
th {font-weight:bold; color:white; background:#0066CC; padding-left:4px; padding-right:4px; padding-bottom:2px;}
td {color:black; background:#fcfcf0; vertical-align:top; border:1px solid #cccc99;}
td.c {text-align:center;}
font.n {font-size:8pt; font-style:italic; color:#336699;}
font.f {font-size:8pt; color:#999999; border-top:1px solid #cccc99; margin-top:30pt;}

<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">
google.load("visualization", "1", {packages:["corechart"]})

function drawChart() {
var data = google.visualization.arrayToDataTable([

var options = {
backgroundColor: {fill: '#fcfcf0', stroke: '#336699', strokeWidth: 1},
explorer: {actions: ['dragToZoom', 'rightClickToReset'], maxZoomIn: 0.1},
title: 'Swap Out Data by Hour',
titleTextStyle: {fontSize: 16, bold: false},
focusTarget: 'category',
legend: {position: 'right', textStyle: {fontSize: 12}},
tooltip: {textStyle: {fontSize: 10}},
hAxis: {title: 'Hour', gridlines: {count: -1}},
vAxis: {title: 'Swap in MB' , baseline:100, gridlines: {count: -1}}

var chart = new google.visualization.LineChart(document.getElementById('chart_div'))
chart.draw(data, options)

<h1>Swap Out Volume by hour</h1>
<br>This line chart is an aggregate per hour.
<br>Add comments here.
<div id="chart_div" style="width: 900px; height: 500px;"></div>
<font class="n">Notes:</font>
<font class="n"><br>1) Drag to Zoom, and right click to reset Chart.</font>
<font class="n"><br>2) Some other note.</font>
<font class="n"></font>
<font class="n"></font>
<font class="f">This is a sample line chart report.</font>

2 thoughts on “unCharted Territory

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s