Recently I wrote a post on the updated twitter workflow from the Data Warehouse, publishing a status message each morning. Now a graph is included in the tweet the first day of the month. Not only must workflow steps creating the monthly graph be added, logic to execute these steps once a month must also be provided. The full prototype workflow was included in this post. Here the workflow steps (jobs) have been moved to separate XML scripts, showing the overall logic better.
In this workflow there are three jobs:
- crtSQLMsg - creates the Twitter status message
- monthlyGraph - creates the monthly graph
- twittaMSG - publish the status msg and the monthly graph on Twitter
The tag includeGraph contains a boolean expression which is true when the graph should be included, this tag is then used in the prereq for job monthlyGraph and in the image tag in job twittaMsg, (the function evaluates to the name of the graph or blank if no graph is created).
The jobs crtSQLMsg and twittaMSG has been described in this post and this post, I just added some logic for the monthly graph. (For the tweet itself II use David Grudl’s Twitter for PHP ).
Creating the Graph
I use the pchart2 package creating the graph, the job to create the graph monthlyGraph consists of two nested sub-jobs crtData01 and crtGraph01.
The crtData01 job creates the data for the graph. (The SQL statements are suppressed, can be found here.) The result is ‘massaged’ by sqlconverter_splitcolumns.php which structures the result data for pchart2.
The crtGraph01 job calls the php script pgraph04.php which creates the graph. This php script is still a prototype, I’m not happy with parts of the script, I need to produce some more graphs to better understand how to structure the ITL syntax and the underlying php code. But for that I need a demand, contrary to what BI product makers seem to believe users/managers are not that keen on static graphs, users want’s Excel sheets and visual analytics you do with products like Qlikview or Lumira. My intention with these graphics is to please the right side of the brain, more practical for the eye than the intellect. (This is important, but the guys with the money does not always share this opinion.)
I am happy with how neatly this rather complex workflow can be expressed in ITL and how simple it is to create the graph with pchart2.
1st of August 2014
If you follow the DataWarehouse on Twitter, you will see if the update of the workflow is a success or not. This month twitter rejected the graph complaining the status message was too long. I have shortened the status message but we have to wait and see. (I’m probably on vacation and may not follow Twitter myself.) There are some glitches in the graph produced in july, hopefully we’ll see an improvement.
This is the log from running the schedule (at top) not creating the Graph:
If you follow the log you can see that the job monthlyGraph is bypassed.
This is the log running the schedule creating the Graph:
I end this post with sqlconverter_splitcolumns.php, (see crtData01 above):
<?php
/**
* SQL result converter - dynamically included in function {@link execSql()}
*
* This converter converts SQL select(s) result to an array targetet for the {@link http://www.pchart.net/ pchart program product}
* Note! First we call {@link sqlconverter_default.php} to create a result php array, then transform the array
* into something pchart can digest.
* Syntax:
*<sqlconverter> <br>
* <name>sqlconverter_splitcolumns.php</name> <br>
* <target>table</target> <br>
* <delim>,</delim> <br>
*</sqlconverter>
*
* @see sqlconverter_default.php
* @author Lasse Johansson <lars.a.johansson@se.atlascopco.com>
* @version 1.0.0
* @package adac
* @subpackage sqlconverter
*/
$sqllog->enter('Note',"sqlconverter_splitcolumns.php meta=$metafile,sql=$sqltarget");
$mysqltarget = $sqltarget;
$sqltarget = $temparr = 'column0';
/** Include the default converter to do a basic conversion of the result table */
include("$sqlconverterDir".'sqlconverter_default.php');
$sqltarget = $mysqltarget;
if(is_numeric(substr($sqltarget, -1,1))) {
$sqltarget = "$sqltarget";
} else {
clearstatcache();
for ($x=0; 1==1; $x++){
if (!file_exists("$sqltarget$x")){
$sqltarget = "$sqltarget$x";
break;
}
}
}
$sqlarray = unserialize(file_get_contents("$temparr"));
$xtab = $xmlconverter['xtab'] == 'yes' ? TRUE : FALSE;
if($xtab) {
$XTAB = [];
foreach($sqlarray as $row){
$k = array_keys($row);
$v = array_values($row);
$XTAB[] = [$k[0] => $v[0],$v[1] => $v[2]];
}
$sqlarray = $XTAB;
unset($XTAB);
}
$STEM = [];
foreach($sqlarray as $row){
foreach($row as $coln => $colv){
$STEM["$coln"][] = "$colv";
}
}
unset ($STEM["rowindex"]);
if($xtab) $STEM[$k[0]] = array_unique($STEM[$k[0]]);
file_put_contents("$sqltarget",serialize($STEM));
|
No comments:
Post a Comment