2017-08-11

The growth of

The Data Warehouse still surprises me. After 16 years it still grows in terms of users, jobs, sql queries, reports etc. I had a look the other day at a Twitter published graph and saw jobs/month has increased from 58000 to 71000 during the last year, a staggering 22% that’s a lot.
Number of jobs or resource consumption are not a very good measures of the useful production of a Data Warehouse or any other application. It is very hard to measure the useful production of most computer apps. Number of jobs may indicate inefficiency or a stupid divide of logical tasks into too many jobs. Anyway it is a measure, assuming the Data Warehouse is not deteriorating the increase of jobs is an indication of increased use. As far as I can see the Data Warehouse is not deteriorating, if anything it is getting better.

Never in my wildest dream did I anticipated the phenomenal progress of the Data Warehouse when we started 2001 by solving one problem for one purchaser. And never did I expect the Data Warehouse be alive and kicking 16 years later. I'm not surprised however, useful applications have a tendency to live long. After my rewrite of the first version 2006, the Data Warehouse was superior to anything there was on the market. Some claim it still is, I don't know about that but it is certainly different from anything on the market.
   

2017-08-06

Amazon delivery time

2017-07-07 I ordered a book in paper format from Amazon. I choose the most economic transport without looking at delivery times. When I looked a my order, to my astonishment I realized the delivery date was 2017-08-17!
My word, what kind of process can result in that delivery time for a book year 2017? Does Amazon send the paper book from USA? With a sailing ship to Holland? And from there with a horse coach to Stockholm, Sweden?
I thought Amazon was a rational modern company with a serious customer focus. Not in my wildest dreams could I imagine such a long delivery time. I get the feeling Amazon purposely extend the delivery time to ‘punish’ me for not choosing a more expensive delivery alternative.

It would be interesting to know where Amazon prints my copy of the book and the route Amazon sends the book to me.
Looking at the order I can see the book is on its way to me😈

2017-07-16

Perl6, what's in a name

I occasionally follow Perl 6 development. Now when version 1 of Perl6 can be glimpsed at the horizon, a quibble of the name have arised. When I wrote version 1, I mean the first version the developers think is of such quality they can recommend it for professional production use, that is how most of us define a version 1 of any software be a language or not. But I’m not sure that goes for the Perl6 guys, they speak of version star, christmas instead of 1,2,3 etc. Normally you increase a software version number when you introduce non backward compatible code or new important features have been introduced. Now Perl6 is just not an upgrade of the old Perl which now seem to be called Perl5  (by the Perl6 developers), it’s a complete new language. The name Perl6 introduce some interesting questions. What should a new version of the old Perl be? Perl vs 6, or Perl vs 7, Perl5 vs 6?. And Perl6 itself Perl6 vs 1? To me it seems not very clever creating a software give it a name of an existing software just appending the name with the next version number. This is bound to lead to confusion and annoyance about both languages in future.
The reason for keeping Perl in the name is as I understand ‘positive connotations of the name Perl’. That was a valid argument the previous century, today Perl as a name of a computer language is on par with Cobol. There is nothing wrong (in general terms) with Cobol or Perl, but positive connotations, come on in an age where PHP is associated with old farts, the word Perl does not arouse the masses.
I have seen a suggestion to add a compiler name to the name ‘Rakudo Perl6’, that’s really daft. Perl6 is marketed as a language specification, adding a compiler name to it is really an odd suggestion.
‘Hey what language did you use for your last mobile app?’, ‘Rakudo Perl6 version 5’ that is a conversation you will never hear.
My suggestion is to rebrand the language as ‘PerlyGate’. It is a compromise name, that solves the most irritating problems with the name Perl6, but keeps the connotations whatever they are with Perl, and also links to the Christian mumbo jumbo and connotations the Perl community seems so fond of.


I write this because I care, I want a language like Perl6 to become a success. It is a programmers dream, let be it is too large for my liking, but still it is better by far than any other 3g language I have seen..

2017-07-15

Why SAP BAPIs may be bad for you

Yesterday I got a call "the users are displeased with some  reports. The column 'project_definition' is defined as char(9) while it should be char(11), this makes many joins usesless or even invald. How can we fix this?'.
Since the most data coming from SAP into the Data Warehouse is automagically created by the import mechanism is not only redefine a definition from char(9) to char(11), it's correcting the import mechanism, that's why I was called.
I was puzzled, I was pretty sure the algorithm calculating column length in MySQL was right. It had worked untouched for ten years. Recently I done some minor changes in other parts of the Data Warehouse (first in about 5 years), ‘that could not possibly be the reason’ I said to myself, but as any experienced programmer knows when you say something as stupid as that it often is the very reason for the bug, it’s called bit rot, and it can do very awkward things, so I decided to take a look at the code, it was untouched and looked alright to me. Then I looked at the produced definition of ‘project_definition' it showed char(14) not char(11). That’s funny now I got three disparate definitions I decided to have a look at the definitions of 'project_definition' in the Data Warehouse.
Using the information_schema database in MySQL:
SELECT `TABLE_NAME`FROM `COLUMNS` WHERE `COLUMN_NAME` = 'project_definition'


Project_definition showed up in 69 tables (including test and obsolete tables), here are a few:


Then I checked the length of project_definition:
SELECT distinct `CHARACTER_MAXIMUM_LENGTH` FROM `COLUMNS` WHERE `COLUMN_NAME` = 'project_definition'


Oh golly! now I had 5 lengths (including 11). What was going on here?
Then I remembered, I did this ‘SAP Project’ 9 year ago for a workshop in Nantes. Instead of extracting data directly from SAP tables, I decided to extract data via BAPI’s. I didn’t know the SAP projects subsystem and I had very little time, so I thought BAPIs would be a shortcut since SAP had prepared the data I needed, or so I thought.
I asked my Data Warehouse accomplice at the time, Ulf Davidsson, he told me ‘It’s an exceptionally bad idea, BAPIs never give you what you want, there is always data missing, you have to enhance the BAPI and then you have lost the advantage you are looking for, and it takes longer time than finding the data yourself, grab table data or write your own ABAP code, then you have control’.
In retroperspective Ulf was right, actually in more ways than he knew at the time. But I’m a stubborn son of a bitch, so I created the Projects database entirely of BAPIs. The first thing the users told me ‘we are missing X,Y,Z…’. I stupidly asked ‘is that really necessary?’. ‘Without X,Y,Z… this is useless’ was the reply, so I had to roll up my sleeves and start digging in the SAP database, fortunately it was not that much missing. The real problem for me if I recall this right, I did not have control of the data I just run the Bapis enforced it with some table data. I had to spend much time figuring out how to join the data, eventually I got it right and the users were happy. But due to the atomic nature of  BAPIs it is extremely time consuming to extract data via BAPIs. It took about an hour to extract the requested data, it was bad but the night is long. After a few years, there were many more projects, another workshop started to do projects and then a distribution center started to use projects and the night was not so long anymore. Grabbing purchase requisitions began to take ridicules long time. I did a quick fix by parallelize the extractions and cut the time down to about 3 hours. It was bad, but manageable.After a few years it was not manageable any more.  
I had to rewrite the worst performers into table extractions. This stabilized the performance it’s stable under the hour now, it’s bad but stable.It is a mess, a mix of BAPIs and tables, but apparently a useful mess, when the extraction is late or have failed, users start complaining early morning.
I have asked a number of coworkers to rewrite the projects app, but the reply is always ‘NO, that mess you have to clean up yourself, I will not touch it’. And to be honest neither will I. And now I have this length problem. I suspect the BAPIs do not have as strict and consistent definitions as the SAP tables. And that the BAPI definitions have changed over time. And this has gone unnoticed as long as project names were not longer than 9 chars. I can probably just delete the projects database and it will correctly rebuild itself, but I’m not sure, so I change the char(9) definitions into char(14) in MySql and wait and see what happens.


BAPIs or the BAPI concept is not bad. Mass extractions is clearly not the intended use of BAPIs, they are low volume schnittstellen into SAP, e.g. extract one Sales order and eventually update it is the intended use of BAPIs. Nor should you combine the output of BAPIs to create a reporting structure,.

For mass extraction of SAP data be cautious with BAPIs, use table extraction (RFC_READ_TABLE et al.) or own ABAP code you have control over.

2017-07-09

Introduction to Perl6

I find this blog post http://perl6intro.com/ a well written introduction to Perl6.
Ever since I first read about Perl6 around 2001 I have been intrigued by the language and the meticulous construction process of the language. Now some sixteen years later Perl6 appears to be reaching production status! If nothing else the development team should have some recognition for endurance.
The blog post is worth reading even if you have no intention of using Perl6.

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.