Animated graphs from the Data Warehouse

Sometimes, rather often actually I got carried away and have a tendency to overdo things of less importance just for the hell of it. This happen less often now than in my younger days, I guess it has to do with age. This post  is about overdoing.
It started with twitter automation and eye candy. So far so good, but then I got this crazy idea, why not animated eye candy, get the graphics moving. This might not be the most useful feature I have created but it was fun to do.
The automated Twitter status message is actually useful for the Data Warehouse maintainers, they can check their twitter device in the morning if there is problems in the Data Warehouse or not. Then I realised I could combine eye candy with twitter and tweet images, still this is a good idea, giving the world a monthly overview of the Data Warehouse activity, it’s not very serious but nice. The automated Twitter workflow is complex and it shows the power of the ITL (integration tag language). I modified the workflow producing the monthly graph, so it produces an animated GIF instead of a static PNG. It is considerably harder to created an animated picture than a static picture, but it was surprisingly simple to do necessary changes in my ITL workflow. First of all there is a hell lot more computations to be done, all intermediate graphs have to calculated and then merged together. My static graph requires 2 SQL queries, the animated graph shown here requires 31 times 2 SQL queries. Before I create the animation I need to create 31 static graphs (one per day) which I fuse together into a ‘month animation’. For the graphs I used pchart2 and for the animation I used imageMagick. All calculations are done in parallel to speed things up.


The result is far from perfect it’s just a PoC, and I had to scale down the image to upload it, right-scaled it looks better. But animation is what matters and the graph 'moves'.

The Modified animation ETL script:

The changes done to the original workflow (described here) creating a static graph are few. If you look at the graph you see the moving part is the last month june pillar, it is a compilation of one ‘picture’ a day, so for this graph I have to calculate values for each day in the month, that is done by introducing the <forevery> job iterator a table with 31 rows (determined by the DAYSINMONTH tag). The INTERVAL column is used as a SQL predicate in the jobs crtBarData01 and crtLineData01 (which are run in parallel). The <forevery> iterates the execution of the three nested jobs crtBarData01, crtLineData01 and crtGraph01 once for each day in the month. The parallel and rowdirectory directives will execute all days in parallel independently of each other. All in all we produce 31 graphs in parallel, each throwing 2 SQL requests at MySQL at once and then produce a graph.
When this is done the <exit> tags are executed:
  1. The first <exit> fuse the 31 static graphs together forming an animated gif graph.
  2. The second <exit> inserts a 5 second pause after each animation loop.   
The job monthlyGraph is a neat graph producing package that can be included in another job or schedule with overriding parameters/tags and prereqs. There is some seriously complex computing hidden inside this succinct and simple (if you know ITL) workflow script. The result will not change the world. In the beginning of this post I wrote I created the script just for fun, but that is not really the truth. By stretching concepts you find limitations. With this example I found a bug and a limitation, which both were fixed with less than 10 lines of PHP code.

Unfortunately this is not all you need to know to create animated graphics. The graph above is very large, it should be optimized, this is no simple task. It requires skills, knowledge and practice which I do not have, but I can assure you if I had known five years ago what I now know about graphics rendering I would have had those skills, knowledge and practice. And I would have created a graphics package rivaling the best analytic graphics there is on the market today. That is of course impossible, but graphics is seriously fun, and I have a tendency to be carried away and overdo things, that’s why I believe I would have done it :-)

No comments:

Post a Comment