2014-06-15

pChart eye candy graphs from the Data Warehouse

Some weeks ago I implemented support for Python in the Data Warehouse for no reason at all. That was not entirely true I had in mind to introduce graphical support in the Data Warehouse. For some reason I thought there should be better alternatives with Python. I wanted to implement eye candy, a simple way to produce good looking static graphs. A problem with graphs there are so many parameters you have to set to produce nice looking graphs and that is what eye candy is all about. The graphs I had in mind should be practical for the eye rather than the neocortex, for those graphs we use the heavy artillery Qlikview. While searching for a free, easy to use graphical package I found pChart 2.0 written in PHP. You can do all kinds of advanced graphical stuff with pChart, much more than required for my eye candy. pChart is a great software product, while working with it I like it more and more.

I decided to implement a bar chart with optional superimposed line-graphs and make it pretty.

I decided to create a special sql converter for pChart bar and line graphs, which pre-formats SQL result for pChart and feed the result into a PHP program tailored for pChart eye candy. This is the ITL script I used for my first attempt:

Pgraph04.php is my php script producing the graph. Bar- and line-data comes from a preceding sql job, producing job and MySQL statistics from the Data Warehouses. The other tags are parameters to Pgraph.

And this the result:

Here you the graph on twitter. I think the result is great but the text is too small to read. After some tweaking I came up with this improved Graph:

It is still hard to read the text in twitter format, but it is much better now. Still these graphs are not intended for this small format. I had mails and other reports in mind for these eye candy graphs. and this is how the ITL script looks:

This result is at least prettier, you have to play a little with graph type and size depending on your data to get a nice looking graph.

With very little effort I can produce eye candy. I have not done graphics programming before and it has taken much time, many more hours than I anticipated to write the code that produce these graphs. It has been a very tedious trial and error process, but I learned a lot and it’s nice to have a simple workflow to create these graphs.

In the next post I'll present the SQL converter and PHP script that creates the eye candy graphs.