2014-10-10

Agile development

In some posts I have written a little about Business Support organisations and developing methods. I am a strong believer in the agile method. First time I saw agile properly formulated was around 1982, then my now retired colleague/mentor Rolf Carlsson wrote a paper titled Experimental software development through iterations (which I unfortunately have lost), where he described at that time a new revolutionary development model, which today is called agile development. I had already worked according to Rolf’s development model for some year’s so it was nothing new to me, but most people I discussed this with didn’t get it at all. Central to Rolf’s agile model is simple use cases, users must not only understand the scenarios they should feel comfortable with them and recognize themselves in these use cases. A developer creates a basic prototype from a use cases and then gradually refine the prototype together with users. Program specifications in the classical sense is not used at all. Deadlines so important in other project models are insignificant. And that is what I think is the thing most people find the hardest to get a grip on, time based planning is not a part of the agile development processes.    


Today most systems claim to be ‘agile’ and everyone is for ‘agile development’ models, but still few seems to to get around their heads what agile is all about. Agile development has been eloquently and succinctly described in this manifesto and these principles. If you are not familiar with the agile manifesto please study these links carefully before proceed. Agile means you should not use detailed specification or time planning nor should you use deadlines. The world is changing and the only thing we can be sure of, forecasts and predictions of the future are wrong, time plans are forecasts and specifications or software blueprints are predictions of the finished application. Agile means a radical shift away from waterfall methods planning, if you do agile development with detailed specifications and deadlines or time based ‘toll gates’ the agile is just a false label. Agile means continuous real user interaction, users do not (need to) know all up front and they have the right to change their mind any time. Since agile also means refinement of the application through iterative development, the application can be taken into production or ‘system tested’ in an early stage, since you start build a simple working model gradually augmented by required functionality. This is in sharp contrast to waterfall methods where you have a functionality freeze and a development period where the parts are assembled late in the development process for system testing, where users for the first time see the application.


First application I developed according to agile principles was an ERP system including purchase, logistics and production control. That application was in production at different plants (here is one) for some 26 years before it was replaced by SAP. (Yes I’m immensely proud of that.) If you looked at the video you also saw a distribution center, the application controlling the distribution was an agile developed application built by Rolf and a colleague of him, that application was in service only for 25 years. There was a lot of users involved in these projects and I had a lot of help from Rolf with my project, and I assisted Rolf to a small extent with his.


On top of my head I cannot find any special type of application not suited for agile development. But there are IT projects not suitable for agile methods. I can imagine very large projects can be hard to manage without time planning. I believe  large scale (i.e. many IT developers involved) is a recipe for failure and should be avoided.  Implementing standard applications like SAP ERP, is not an agile process in my eyes. You need a meticulously worked out timeplan with deadlines to keep track of the project. If you lose control over the implementation with hordes of consultants and the entire company involved it will be a very costly project, it might even ruin the company. On top of every successful large scale turn key system implementation there is a stern policeman with a rigid timeplan and stopwatch in one hand, a whip in the other and a tight grip on the consultants with his/hers third hand. One can question if implementing a SAP system has anything to do with software development.

Business Intelligence in general is very agile. I argue you must follow agile principles if you want to create great BI applications. My intention with this post was to describe an example of agile BI the application and the organisation behind, but if you have followed me this far you can see I failed. I will make a new attempt to write about that, until then you can read this post.  

2014-10-05

PDO connecting to MS SQL and prereqs



The other day I created an interesting job in the Data Warehouse. It’s not a Data Warehouse job as such, I read logs from another application PricePoint and mails the result to concerned. PricePoint is a Windows application and store it’s data in an MS SQL server database. Many here at the company thinks the Data Warehouse is an odd application Linux only, not able to communicate with ‘proper’ software at all (proper is MS). This is of course false, the DW is compatible with many things. The DW have no problem accessing MS SQL server databases or MS office document if required.
There was a little twist with the job I was asked to do. There is a table PendingMessages containing transactions not yet processed. ‘Can you send a mail with failed transactions attached when all transactions are processed, but not later than eight thirty in the morning?’
I could just schedule a job 08:30, but I decided to be a bit creative, so I created a job that checks when all transactions are processed and then sends the mail but no later than 08:30. First I created prereq that checked for this:


This is what this prereq does; It call the PricePoint MSSQL database via PDO and selects number of pending transactions. Since we want the prereq to be false as long as there are pending transactions and true when there is no transactions left we simply negate the result, (we could have created a correct result by writing a more complex SQL query, but just negate the prereq is simpler). We check the prereq every 300 seconds and we do this 20 times or until 08:30 whichever happens first. But there is a snag to this, when time is up we should proceed no matter what the prereq says but this prereq fails when time is up and that is what you want most of the time, wait for an event and if that event does not happen fail. There are some ways to circumvent this problem. You can put the prereq into an <init> section and then explicitly set the init return code to TRUE with a rectify statement like this:


But this not only looks ugly, the logic behind is not obvious and it is a misuse of  both the <init> section and the rectify statement. I do not like to trick my way around programming difficulties so I did not go for this solution. Instead I inserted a <prereqwait> which handles the timing functionalities of the prereq. When I implemented prereqwait I thought it might make sense to allow for a happy ending so I introduced timesup=’ok’ like this:


This is definitely an improvement, actually it’s quite neat, but having second thoughts about the entire job I decided to skip the prereq altogether and go for the simpler solution, just schedule the job at 08:30 like this:




Much better, now no one has  to figure out what the odd looking prereq is good for, but can appreciate the simple MS SQL connectivity via PDO. Take a look at the schedule, there is some nice functionality in there. And I also showed some nice timing functionality by <prereqwait> and <prereq>. I think my jobscheduler with the Integration Tag Language can give any job scheduler on the market a run for their money. (I would love to see someone prove me wrong:)


At last this is what the laconic result from a run looks like in MS Outlook:

Here you see not all transactions were processed at 08:30 and the attachment is a CSV file, it should have been an xmlx file but I missed that, however its just a matter of changing the sql converter in the getMsgs job above.

2014-09-27

Good guys Bad guys

This post is a bit off from what I normally write, but it is very much IT related and I try to follow what happens in IT intelligence area.


The other day I saw that Edward Snowden has received the swedish Right Livelihood Award.for exposing mass surveillance of electronic media done by various western defense organisations, among those NSA and FRA (Swedish Defence Radio Establishment). It came as little surprise to me FRA is/was involved, these guys they know a thing or two about (encrypted) telecommunications.
You must be pretty naive if you do not understand we are all constantly monitored on the electronic communications channels we use. Personally I do not have a problem with that, you have to adopt to the situation. But I do have a problem with the fact that non democratic regimes, terrorists and organized crime can interfere with my virtual life. I like all protection I can get from those what I consider bad guys. FRA and their fellow organisations (which I in this respect consider to be the good guys) try to expose and intercept the bad guys. To efficiently fend off the bad guys, the good guys cannot be open and at times probably have to circumvent laws. I do not have a problem with that, FRA is supervised by my parliament which I trust. In the end FRA protects me from the bad guys and I like that.
Edward Snowden have exposed surveillance activities of the good guys. In USA it  seems to be a criminal act to expose NSA , as a consequence Edward Snowden has fled to Russia which at the moment is a safe haven for him.
Now there seems to be a controversy here in Sweden if Edward Snowden should be allowed to enter this country to receive his Right Livelihood Award or not. I think not. Edward Snowden and I are on opposite sides and he has caused damage to FRA which I support. With his actions Edward Snowden direct or indirect have come to support evil forces in the world, I do not like that. 
I rather wish Edward Snowden had exposed activities of the bad guys, that would have made him a more worthy receiver of the Right Livelihood Award in my eyes.

2014-09-20

Back To School

Normally I avoid writing about closed source software in this blog simply because I do not want to advocate commercial products without getting payed. I also do not like express negative opinions on specific products, when I do it’s mostly to illustrate a more general phenomenon like e.g. NoSQL (this morning I read a great post on the word NoSQL).
I do not bear any grudge against closed source, I respect both copyright and patent, I would love to own a successful closed source product myself but that’s another story.
In this blog I focus on my own professional life and  this week I attended a four day MS SharePoint introduction course, and that is what this post is about.

SharePoint introduction

Four days for a key user introductory course may sound overkill. But on the contrary, if anything it was a day to little, MS SharePoint is loaded with functionality, it is a full fledged web development platform for a lot of types of web apps. I thought it was some kind of document system but it is a hell lot more than that. The Learning Tree course was given in a brisk pace. The teacher never gave me a chance to do what I do the most attending IT classes, sleep. I do not like IT courses, I normally find them slow, boring with dull exercises which I never complete successfully. This time however I stayed alert most of the time, however I still failed most of the exercises. I really tried the patience of the teacher, he gave me unlimited time ‘Lars stay after we finish today, we can work it through together’. With a ‘little’ bit of help, in end I got all exercises stitched together. I am by no means a key user of MS Sharepoint, no one is after a four day course, but it gives you an understanding of what the product can do, and sufficient knowledge to start work in SharePoint. You need at least an additional three months of hard work to qualify as a key user.

SharePoint reminds me a lot of mindtouch an open source product I installed at the Company during my vacation 2008. At that time those I showed it for didn’t get the idea, they could not see a need for it and ‘we will soon install Quickr’. With all respect Quickr (at least at that time) was a different piece of software. Anyway I worked with mindtouch enough to understand what this kind of collaboration software is, (and I have opinions how it should  be used). A few first impressions of SharePoint:  

The good

The integration with office and mail (an absolute must for collaboration software) is top class. As a user and developer you work seamlessly with MS Office and MS outlook.

The GUI is consistent and intuitive and lends heavily from MS Office, which makes it familiar right from start.

Lists (a cornerstone in SharePoint) are great and easy to work with.

The implementation of workflows looks decent. Workflows are very complex a decent implementation is good. (I managed the workflows exercises without help). But to judge workflows you really need to work with them and see how they interact with humans and other software in your environment.

The bad

The GUI was cluttered with a zillion of options, I had severe problems finding the options I wanted, most of my failures was due to not finding the configuring option I was looking for.

Web development looked mezzy to me. Any app beyond the very simple was very hard to realize. Expressing apps by configuring options (which you do in SharePoint) is tedious and cumbersome. This is because creating a complex app is complex. There are good reasons for programing languages, good ones can express logic and actions succinct and unambiguous with a limited set of operations.
I do not think no-programming applications development is the right way to go. I rather see a revival of my beloved Mims 4GL, where you tell the computer what you want in coherent and super condensed code, that together with a WYSIWYG screen editor.

The fear

I see an upcoming clash between titans in the collaborative software arena at the Company, SAP and Microsoft. SAP are working their butts off to launch collaborative tools. A product company having their business in the SAP ERP system is likely to favor SAP for collaboration, since most information they use is already in there. On the other hand SharePoint may fill ‘the holes’ and act like glue between ERP and other software. On the other hand again SAP is doing it’s best to fill ‘the holes’ and act like glue between themselves and other software. Anyway it turns out, these alternatives are a big vendor lock ins, these collaboration packages comes bundled or connected with all the products the Vendor can possibly throw in. Once in exit will be excruciatingly painful.

The epilogue

Will I recommend SharePoint to a friend? I do not know yet. It is a well designed quality product that’s for sure. During the course we did not encounter unlogic behavior or bugs, it’s darn good having twenty newbies hammering wildly for four days not showing any erratic behavior.

What I happily can recommend is both the introductory course and the teacher.

2014-09-14

Dynamic PHP code 2

In the previous post I showed how ITL can be used to generate HTML tables from MySQL selects. Now I have cleaned the prototype and added the ability to customize the  html table rows. The intention with generating HTML tables in ITL is not to give the ability to customize all aspects of an HTML table, but to easily create decent tables. This is not easy to do since it is complex to format HTML.  The present ITL HTML generating capabilities are primarily for adding HTML tables to mails, still it is complex to format HTML so it looks nice. Therefore I decided to do the formatting with PHP code, this is not the simplest way to add HTML formatting, but it gives the flexibility I find necessary. I added the ability to modify the row tag <tr> by <htmlrow>php code </htmlrow>, the SQL result table is exposed to this code row by row as the cells are exposed to <htmlcell>php code</htmlcell>.  
I expose the entire SQL table to the htmlrow function and an index pointing out the current row in the table. In the htmlcell function I expose the entire table row with an index pointing at the current cell. This gives freedom to format depending on values of other rows and cells.

If we look at the new version of the job ‘noCellColors’:
You see I added an <hmlrow> tag to get a (row-) striped table, and a maroon row if the first cell’s value is ‘en’, like this:


The second job (from the post) ‘cellColor’ now looks like this:

Here I added some ‘cell logic’ formatting background colors depending on the value of the second cell in each row, giving this html table:  
Maybe not so pretty, but it illustrates the ability to format depending on values.

I use the eval function to import the PHP code from the job XML script. I think it’s a bit clumsy and suspect there is better ways to do it, but I could not find a better way. What I found was this  „If eval() is the answer, you're almost certainly asking the wrong question.“ Rasmus Lerdorf.

Maybe PHP is the wrong language, if eval() is the answer. 
I would have better use of  practical advice than a quibbling quote, but I didn’t find any so I use eval(). Anyway for the moment I’m happy with the HTML formatting so far, but I suspect this is not the final code.
I end this post with the sql converter code creating these amazing tables.
<?php
/**
* SQL result converter - dynamically included in function {@link execSql()}
*
* This code converts a mysqli select result into a HTML table.
* For documentation see {@link sqlconverter_default.php}
*
*
* <sqlconverter name='sqlconverter_HTML03.php' target='table' totline='yes'>
*  <htmlrow><![CDATA[
*    var_dump($cnt);
*    if($rno % 2 == 0) $tag = '<tr class="ett">';
*    else $tag = '<tr style="background: maroon; color: white;">';
*    if ($tbl[$rno]['0'] == 'en') $tag = '<tr style="background: maroon; color: white;">';
*  ]]></htmlrow>
*  <htmlcell><![CDATA[
*    if ($cno == 1){
*      if ($row[$cno] < 2) $tag = "<td style="background: red; color: black;border:1px solid #D5D5D5; padding:15px;">";
*      else if ($row[$cno] < 10) $tag = '<td style="background: blue; color: white;">';
*      else $tag = "<td class='green'>";   
*    }
*  ]]></htmlcell>
* </sqlconverter>
*
* The parms:
* 1 totline=yes|no determines if a total line is inserted at the end  
* 2 emptytable=yes|no determines if the an empty table is written to disk or not
* 3 width=width of table  
*
* htmlrow($tbl,$rno,$meta)
* $tbl array, $rno=row number, $meta array
* return $trtag
*
* htmlcell($row,$cno,$meta)
* $row array, $cno=position in row (0=1st cell), $meta array
* return $tdtag
*
* @author Lasse Johansson <lars.a.johansson@se.atlascopco.com>
* @version  1.0.0
* @package adac
* @subpackage sqlconverter
* @param string $sqltarget filename where we should save the file.
* @param object $result mysqli SELECT result set
*/
$mysqltarget = $sqltarget;
$sqltarget = $temparr = 'html0';
/** Include the default converter to do a basic conversion of the result table */
include("$sqlconverterDir".'sqlconverter_fetch_row.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"));
$arrMeta = unserialize(file_get_contents("$temparr".'meta_.TXTArray'));

$filePfx = '.ARRAY';
$metafile = $sqltarget.'meta_';
if(is_numeric(substr($sqltarget, -1,1))) {
$metafile = "$metafile$filePfx";
$sqltarget = "$sqltarget";
} else {
clearstatcache();
for ($x=0; 1==1; $x++){
if (!file_exists("$metafile$x$filePfx")){
$metafile = "$metafile$x$filePfx";
$sqltarget = "$sqltarget$x";
break;
}
}
}
$sqllog->logit('Enter',"sqlconverter writing output to $metafile and $sqltarget");

if (array_key_exists('table',$xmlconverter)) {
   $xmltable = $xmlconverter['table'][0];
   if (array_key_exists('width',$xmltable))
$xmltablewidth = is_string($xmltable['width']) ? $xmltable['width'] : $xmltable['width'][0]['value'];
}

$rf =  
'$rowfunc = function($tbl,$rno,$meta) {$tag = "<tr>";'.$xmlconverter['htmlrow'][0]['value'].'return "$tag";};';
eval($rf);
$cf =  
'$cellfunc = function($row,$cno,$meta) {$tag = "<td>";'.$xmlconverter['htmlcell'][0]['value'].'return "$tag{$row[$cno]}</td>";};';
eval($cf);
   
if (isset($xmltablewidth)) $Table.= "<table id='mysql' width=$xmltablewidth>";
else $Table.= "<table id='mysql' style='table-layout: fixed; width: 100%;'>";
//Header Row with Field Names
$NumFields = $result->field_count;
$Table.= "<tr>";
foreach($arrMeta as $field) {
   if ( $field->type == 253) $Table.= "<th align='right'>".$field->name."</th>";
   else $Table.= "<th>" . $field->name . "</th>";
}
$Table.= "</tr>";

foreach($sqlarray as $rno => $Row){
   $Table.= $rowfunc($sqlarray,$rno,$arrMeta);
   foreach($Row as $key => $value){
$Table.= $cellfunc($Row,$key,$arrMeta);
   }
   $Table.= "</tr>";
}
if ($xmlconverter['totline'] != 'no'){
 $Table.= "<tr class='tot'><td colspan='$NumFields'>Query returned " . $result->num_rows . " rows</td></tr>";
}
$Table.= "</table>";
if (!($xmlconverter['zerorows'] == 'no' and $RowCt == 0)){
   file_put_contents("$sqltarget",$Table);
}
unset($arrmeta,$Table,$rowfunc,$cellfunc,$rf,$cf);
$sqllog->logit('Note',"Exit sqlconverter");