2017-06-22

A small hack

“Hi can you help us? Everyone has gone home and we need the financial transactions for our new financial app, it is super many transactions. Is there any way we can get them to our Windows share with period number in the filename before tomorrow. We like to test the new app during the Midsummer.  Everyone is gone, you are the only one we could get hold of. Can you help? And yes we need the file to be tab separated.”

In Sweden we take Midsummer seriously, we basically close down the country to party. But this was just a five minute hack, so I promised to help out just for the hell of it. First a little detective work, where the f-k are the transactions after a short while I found them already being in a SQL table in the Data Warehouse, great! It’s just copy a job and adjust a bit. Five minutes later I could call back “You have your transactions in your WinShare.” “Thank you ever so much, you are fantastic!” Some times it is not easy to be humble, this is one of those times. I remember another midsummer when I was not so cocky.

Some years ago I rebuild the Linux system in the ETL server of the Data Warehouse on midsummer eve morning in an attempt to connect ODBC to Iseries DB2 systems. In the middle of this my siblings called me and told me they were waiting on me going out to the countryside. I stopped my rebuilding sure my tinkering could not possibly intervene with the normal operations of the ETL server. Of course it did, it killed all ETL jobs for the entire holiday. The guys responsible for the Data Warehouse had to work two days repair the damages, and they made me promise never to do ANYTHING  next midsummer eve or ever any midsummer eve after.


And now I just did it again! I hope noone ever finds out:) The job I just assembled and run:


A mini app!

Yesterday a consultant called me “Hey Lars I’m building a new app feeding a new financial app with master data from SAP. They need the the data in tab separated files exported to a windows share. Do you have standard functions for that in the Data Warehouse or do I have to code that myself?”
“Funny you mention this, I created a similar app  the day before yesterday.”
“First the tab separation.    
You do this by adding a delimiter to the sqlconverter_CSV.php”:
“Next export to a Windows share. The Data warehouse supports the CIFS file system, just do a CIFS mount and copy the data to the windows share.”
“Okay” the consultant replied.
Later at the the end of the day on my way home I passed the consultant. I asked the consultant if he had any more problems. “No I’m done. Six feeds from SAP exported to this new application, tested and put into a daily production schedule ”:
“And a button on an Excel sheet, so the finance manager can fire off the export at will..”
“Why in Excel” I asked
“Why not” the consultant replied.
“It looks you made a nice little mini app today.”
The consultant looked at me and said “Miniapp?!? Do you realise how long time this would have taken any other place?”
“Yes I know” I replied, “but here it’s a nice miniapp”.

Actually it was a hell of a job done by the consultant, no matter where.

2017-06-05

ITL - awful syntax awesome functionality

Last week I was asked If I could help design the integration between ERP systems and a Jira application, the idea was to deploy a new SQL server instance and an application server to create a “funnel” application of some sort. The ERP data should be massaged into an access friendly  format for the Jira app.
I like agile development so I created a crude prototype for testing and then  iteratively refine the prototype together with users, the first thing I did was to create a process map:
For the prototype I suggested the Data Warehouse since it is up and running and is designed for this type of applications. For this first prototype we decided to use a few handmade MS Excels as data, these Excels were FTPed over to the DW FTP landing zone, and then I created an ITL script:
If you look carefully  you can see I added support for an XML and a cvs format as well.
The I run the script:
And the result:

Now we have to wait wait for the Jira guys to tell if this table is OK for them or if we have to modify our prototype.
As you can see there are some functionality cramped into the script. Look at the <forevery>  line, intersect=FTP, means this list is sifted by the actual FTP directory, removing lines which have no corresponding file in the FTP directory, which makes the script simpler since we do not have to think about ‘file does not exists’ conditions.



FTP is not something used for anything else than truly public data these days, but it is easily to setup and for the prototype with test data it works well.