Bad Design

Part 1.

This summer my dishwasher broke down. It was a Miele from the nineties, a very good dish washer I was happy with. A bit noisy but it washed my dish all right. But this summer the water pump broke. Instead of replacing the water pump I decided to buy a modern dishwasher, I decided to go for a Bosch machine, and I installed it some weeks ago. It does a good job, the dish is clean, but still I’m a bit disappointed, the new machine is a tiny bit less noisy but it takes longer time to do the dish, and I do not like that. And it doesn’t dry the dish as good as the old one, I do not like that either. But  what really bugs me is the cutlery basket of the Bosch dishwasher:
In front the old lighter Miele cutlery basket and behind the darker Bosch basket.

As you can see the handle of the Miele basket is strengthened  by two beams for better stability. The Bosch handle is reinforced by beams on the inside of the handle creating perfect pockets trapping condensing steam making sure you always get wet and water is dripping down on the cutlery when you grab the handle.
The Bosch handle with inside water trapping beams.

This is not only an example of bad design but also of inadequate testing. If the Q&A guys in the Bosch laboratory had bothered to test the cutlery basket they would have spotted the problem, and sent it back to the developers.

Part 2.

Last week I had a call from a colleague ‘can you help with an interface, we need to transfer purchase orders from our SAP ERP system to the ERP systems of vendors’.
The vendors didn’t want to use the web portal as such, but preferred to exchange orders and acknowledgements via files. The problem was our ERP system could only communicate via CSV files and the vendors systems expected messages in their XML formats. If you have XML files on both sides a small XSL script is all you need, but our ERP system’s vendor portal can only communicate via CSV files. The year is 2014! I would not have created such an interface.

"Interface Type:","POCONF",,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
"Owner Partner:","XY00000025",,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
"Selection Profile Number:","00000000000001000045",,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
"Selection Profile Name:","PD_CONFIRM_PO",,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
"Created By:","XYZ123",,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
"Created On:","17.11.2014 10:47:59 CET",,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
"PO No.","PO Item No.","Requested","Confirmed","To Be Confirmed","To Be Rejected","Product","RevLvl","Quantity","UoM","Deliv. Date","Deliv.Time","DlvTZ","Ship. Date","Ship. Time","ShipTZ","Requested Price","Confirmed Price","Crcy","PrU.","PrUoM","Reference Document Number of Sales Order","Requested MPN","Confirmed MPN","Requested Mfr","Confirmed Mfr","Customer Loc.","Component ID","Product","RevLvl","Requirement Date","Qty","UoM","Cust. Batch"
"4700000386","20","X","","","","ABC0221300","","2.200","PC","05.01.2015","00:00:00","CET","24.12.2014","23:59:55","CET","4,85","0.000000 ","SEK","1","PC","","","","","","6100","","","","","","",""
A CSV file, SAP interface format.

As you see this is some CSV file! First we have a file header, then two! lines of labels and then purchase order lines. Anyone with experience of parsing CSV files understands this is not the easiest file to parse.
This is not only an example of bad design but also of inadequate testing. If the Q&A guys in the SAP laboratory had eaten their own dog food, they would had sent this back to the developers.


PHP 5.6 Mojibake

The three little piggies have shown their ugly faces and bit me again. The three little piggies are the swedish national characters ÅÄÖ. The three letters with the ring and the dots are more than cute IKEA-dots as I once had them described to me, they are vowels in their own rights. Å is pronounced as the vowel sound in the english word ‘your’, Ä  as the vowel sound in ‘bear’ and Ö as in ‘sir’. These three letters has haunted me in my entire professional career. from EBCDIC in the IBM mainframes and now lastly in PHP 5.6. PHP now has a default encoding setting and this is UTF-8 by default. This is in itself is a good thing, prior to 5.6 character encoding was a mishmash of ISO-8859-1 and UTF-8. I knew of the change, but I didn’t pay attention to it and didn’t test it. I did not even ask someone else to test it. I should have known better. All the Ås, the Äs and the Ös was distorted when we made the switch to 5.6. In our Nantes plant they had managed to put in Äs in some part numbers (material number in SAP english), this caused some  ‘disturbance’ in BOM structures and some other places. How the french guys had managed to use the swedish Ä in part/material numbers is a mystery to me, it might just have been some double encoding error, I do not know.
The remedy for this mistranslation  is simple, just set default_charset=’ISO-8859-1’, but I had not tested it, so I sat the old ICONV( ISO-8859-1, ISO-8859-1, ISO-8859-1) instead, although deprecated in PHP 5.6. I had tested ICONV before and was pretty sure it would work. The right solution is of course to figure out what the problems with UTF-8 are and fix them. Since encoding now is addressed in a clear way in PHP,  it should be simpler to attack the problem, simple it will never be but simpler than before version 5.6.


The Data Warehouse running on PHP 5.6.2

Stockholm, November 2014 - 59.330340, 18.129661

Finally the Data Warehouse is running on PHP 5.6.2. We have been testing PHP 5.6
since alpha 1, and the intention was to go production on PHP 5.6.0, but gather all necessary people for testing and and transition from 5.5.9 just took much longer time than anticipated. 
Next we will upgrade the Data Warehouse's Integration Workflow Engine. After that we will upgrade MySQL from version 5.5 to something newer. But for now we are pleased if we do not hit some PHP 5.6.2 nasties.


Real time reporting

About five years ago I did a PoC how to do real time report/analysis of SAP COPA data. The principles and methods behind the PoC is described in this blog post. The business did not pursue my PoC but followed another path to COPA real time reporting.
COPA or more specific the cost based actuals line items in table CE1 are the basis for the financial reporting in the company and the bean counters need fresh figures around period book closing. If you ever paid attention to the financial department in your company you cannot have missed all activities are targeted to praise the holy period closing. A financial period can be divided into the preparation for the period close and the holy period close per se. I suspect financial managers compete, who can deliver the most accurate period close the fastest. Respectable and trustworthy financial people have told me I’m wrong, they claim the market demands swift closings, but they do not compete. Anyway the midnight lamp is burning in the finance department during period close, and the last these guys want is to sit and wait for the figures after last correction.
I invented user triggered data loading to speed up the finance reporting. The rest of the business was pretty uninterested in real time reporting. I have tried to promote real time with no success, fresh figures in the morning and stable throughout the day is was the business wants. (You can see a day’s night activities in the Data Warehouse in this exciting movie.)

Give the users the option to load the Data Warehouse themselves is a brilliant idea, this option is not something you find in every Business Intelligence system. When I introduced user triggered loading I transferred the commands from MS Excel to the Data Warehouse via a SSH link. This solution is not only complex it also needs installation of extra software in the client. Some time ago I developed a Node.js web server which executes Data Warehouse jobs as a web service. Apart from the web server itself this is a much simpler than the SSH connector. When I developed this web service there was no interest for it. But now there is a new interest for real time reporting in the Data Warehouse. A BI-developer have created a much better and simpler MS Excel interface using the web service in favour of my old complex SSH link. I am excited by this newly-awakened interest in real time reporting, since I have a feeling the Data Warehouse can excel in this. I still have not seen this new MS Excel interface, but I hope it will prove useful and that I will have reasons to write more of real time reporting.


Agile development 2

In my previous post on agile development, I wrote On top of my head I cannot find any special type of application not suited for agile development, having second thoughts about that I could come up with several software development projects not suitable for agile methods. Large IT projects; not only large as in many involved, but also large as in scope, e.g. create a new programming language. If you start to create a new language, you probably need a syntax and a grammar for the new language or a specification of the new language. Just to start develop the new language with two empty hands does not sound right to me. An extreme example of this is the Perl6 language, which has been under development since 2000, in all fairness there has been a working although not complete version since 2010 or so. Perl6 have a detailed pretty static specification, but no time plan ‘it is ready when it is ready’. just lacking a time plan does not make a project qualify as agile. I would not call Perl6 agile. The Perl6 project is as far as I can see well managed, and the most interesting ongoing IT development project I know of. Agile is not the answer to everything.

Detailed time plans are not a part of agile development, but sometimes or rather most of times you need a time plan. Try to tell the boss your latest project is agile and therefore you do not need a time plan. He will not be impressed, he will probably demand  a detailed time plan where one milestone with a deadline is detailed specifications. It will be even worse if you are a consultant trying to sell a development project to a customer. I do not have a good advice how to tackle this situation, personally I cheat a bit. If you read the previous post you know simple use cases are very central to ‘my’ agile model, I call these use cases ‘specifications’. I am a realistic time optimist in the sense I know I am a time optimist. I make up a time plan like this: I start with a rough optimistic time estimate, multiply this estimate with my optimistic factor (2.5) then add another 50% for the unexpected and finally round up to next unit of time, this formula is often pretty accurate. Then I break this down into milestones with desired granularity. Needless to say this is not very scientific, it is sort of case based planning, where I base my time estimates on experience, ‘I know this task takes so long time to complete’.  
While writing this I came across this article. If I got the point of points right, it is a more elaborate ‘time planning’ method than my own but the basic idea is the same.

Once again my intention with this post was to describe an example of agile Business Intelligence, the application and the organisation behind, but if you have followed me this far you can see I failed again. I will make a new attempt to write about that, until then you can read this post.


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.  


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.