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.

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.

2017-05-10

PHP at Bloomberg's - 4

Part four, requesting data (this post)


In the last post (which I hoped to be the last) in the series PHP at Bloomberg’s I described how you fetch a result with a responseId after you have requested information from Bloomberg’s.
Having shown the ‘retrieve app’ for the business owner I was asked “Can’t you request an information set in one shot mode, and then retrieve the result”. “Why do you want to do that? We schedule a request periodically at Bloomberg and then we just retrieve the result when time is due and have ever green data. Schedule once fetch fresh data forever” I responded. “Schedule periodically at Bloomberg has contractual implications. We like to be able to schedule ourselves. We like the abillity to request "one shot" or “ad hoc” reports then fetch the result, and schedule this daily. We know this is more cumbersome, but can you have try?”  
Easy I thought, first I create a job to send the request to Bloomberg and then wait for the report to be produced:
Then I had to modify the PHP code from the previous post a bit. In the original code I create the parameter list directly in the PHP code as it is just a one liner:




But here the parameter list is hundreds of lines. I decided to specify the list as plain XML text following Bloomberg SOAP standard, (you see it above in the <soaprequest> tag). And add code to convert it to an array:




I added a <top> node making the XML well formed and via JSON encode/decode created a perfect array for the soap request.
Then I just run the job and after a few tries it worked. This was easy, unfortunately it turned out to not be as easy with the scheduling. Right now I’m thinking about how to solve the scheduling, but there are some twists to it that I don’t get my head around. When the scheduling is done I might write yet another post about PHP at Bloomberg’s.

2017-05-06

Den Bock zum gärtner machen

Some while ago the company decided to make the goat the gardener, so they put me in charge of group codes, not only the application managing the codes (which I agreed to), but also the codes themselves. I told all and everyone I’m not qualified to do that and all and everyone agreed. The codes are serious business e.g. we have a number of codes to classify our products to keep track of what we sell and analysing marketing trends etc. We have number ranges for parts. If these codes are messed up the consequences may be dire. I take the codes dead serious, that’s why I said “I’m not qualified” in the first place. Some may think it is easy to hand out and keep track of codes you only have to be careful, accurate and meticulous. It’s easy to say, but if you are the antithesis of those traits it is very very hard to keep accurate records of things. If you add to that inability to correctly repeat manual tasks and periods of indolence, it is difficult. (That’s why I became a programmer not a keeper of records.)


I was assured “You do not have to keep track of the codes, the responsibility sits in the business. This is a self service application.” And sure as hell the first thing happend “Hi Lars, I’m Riccardo responsible for new spareparts at the Milano Factory, I need new spare part numbers, can you help?”
“Well you know, you are supposed to do that yourself, frankly I do not know how to do it” I responded.
“I’m told you are the expert and this is a matter of urgency we need those numbers now, please”.
After an hour I had figured out how to assign spare part numbers to Riccardo.
Riccardo was just the first, almost every day I get new requests which I forget about or put at the bottom of my pile of tasks to do. It takes me hours to do these tasks, which in the hands of a capable keeper of records would have been expedited in minutes, what they do swiftly with confidence, I have to check, cross check and check again, as I said I take this seriously this must be correct the first time.     
The other day I was cced a mail conversation about MAC addresses. I did not look at it, but these mails just kept coming, I opened the fifth MAC address  mail, to my astonishment I realised I was responsible for handing out MAC addresses, not only within the company but to sub suppliers too.
In this last mail a purchaser begged for more MAC addresses, a supplier had just stopped production of a component the Tierp Factory was in short supply of. I take codes serious, but that’s nothing compared with production stop in a factory. I take great pride in helping the factories improving their processes by better IT systems, Reading about halted production really got me going, in no time minutes that is I produced the required MAC addresses, later it took almost half an hour for me to check I done it right.


p.s.
Riccardo does not exists, but I get similar urgent requests almost daily.

2017-04-20

Find an item in SharePoint

I got a request from user “Can you help me find number series owned by ‘AFS’, it seems I cannot search myself”. Sure this easy - SharePoint to the rescue:


Easy peasy I thought, but Oh no the dreaded 5000 limit again:


I tried the search window, but no SharePoint is relentless:




No! Search cannot be done in SharePoint. Why am I not surprised?
Ok, Excel to the rescue:
Export the SharePoint list to Excel. First I get a friendly reminder:
Yes MS Excel is compatible.
Then I get another friendly security notification:
This I’m not sure what it is about but let’s go crazy, Rock’n’Roll I press ‘Enable’.

And voila here is Excel with its working search function, working in no time on super large datasets (5000 items and even larger than that):



And the result:  

Can it be simpler? Thank you SharePoint for superior search functionality😀

2017-04-09

Expiration date

This night my telephone rang the alarm bell to tell me my mail password had expired. To my surprise it just did it once, I expected it to wake me up every hour or so.
This morning when I switched on my computer I was greeted by ‘your outlook password has expired, consider changing…’.
‘Great’ I though, Sunday morning at home changing Windows/Outlook/AD password have never worked for me before. I always end up calling the support desk and hear them say ‘this look odd’ and then they reset all that can be reset for my account and then it works for another password period. That procedure however requires I’m at the office work hours.  
‘Great’ I though, ‘Sunday morning and I cannot log in to the company network’, I had planned to work the whole day. Anyway I had to try the password change, lo and behold, I just ctrl+alt+del ‘change password’ and it worked like a charm, the same thing with my mobile, just changed the password and the mail started to replicate. I was stunned! First time ever the password procedure worked for me. Now I only have to sit back and wait for a number of ‘job failed’ mails. Many backend jobs need access to network resources like email, databases etc. In theory you should create special userids+fixed passwords or ‘bypass procedures’ for such jobs, but this often takes effort and time you do not have and you definitely do not want to wait for this while you are developing a new backend process. I use my own userid. I’m sure many of you do the same. After a while you forget all about it, which means ‘failed job’ mails turns up somewhere and you say ‘f-k I forgot about this, I need to fix this properly’, then you update the password and forget about it until next time you password expires.

Software expiration timing.

Many years from now, 04:00 in an Italian hotel on our first honeymoon night, the night porter was banging on the door, telling me he had an important message for me. It was from the company, the company computer had stopped, it refused to do anything from midnight. After an hour discussing with the operator back home I realized the RACF compatible security system ‘Top Secret’ had silently expired three months in advance. We had to wait until the ‘Top Secret’ guys woke up, they promptly sent a patch and promised to remove all hard stops out of the software, later I was told all ‘Top Secret’ installations had stopped the same night.
2009-01-01 05:00 coming home after a serious new year's party, almost still with the funny hat on I turned on the computer just to check everything was OK. Of course it wasn’t, (the VPN software had expired 2009-01-01 00:00). I ordered a more nearby colleague to take a taxi to the office to check what the problem was, he reported all was OK, it took me a while to find out it was ‘only’ the VPN that had expired. Together with IT-support I had it all fixed by the afternoon and could go to sleep.

Software should ideally only expire the second or third  ‘work’ Wednesday in the month at 11:00 nonsensitive months, at the company that is February, April, May, October, November. There is as far as I know no software that have an expiration mechanism that can follow my recommendation. I try to go for the 15th in nonsensitive months, December and January is a nono! Interestingly the old Romans did not have month names for this period of the year, they only had ten months, I bet they foresaw the evil of having software expire that time of the year.

2017-03-29

The online administration

The other day I posted a comment on a proposed tax in Sweden to increase the defense budget with some six billion €. The proposal made by a professor of economics Lars Calmfors was sound and backed up with valid economic arguments.
This made me thinking even though political economy is an empirical science, it has come a far way from witchcraft. Why not hand over the country's economics to scholars?  But then again there is a political in “political economics”, maybe it’s best to let the politicians have a say. This again made me thinking.
The national budget in Sweden as in most countries have been managed the same way for ages, every year there is a new annual budget with a few additions.But we should be able to do better than that. Today we should be able to gather and analyse data of importance in real time, with the right BI tools for data intensive applications we should be able to give the politicians (and scholars) better decision support. Real time monitoring of the country economic key value figures, automatic alarms etc. The online administration of a country, that is able to act immediately when things change,  how cool wouldn’t that be? The smart country; Country vs. 2.  Will the world be a better place? Yes a small tiny little bit better and that is huge.
A final thought, does this already exists? Are country administrations already online?

2017-02-24

Interesting quote

I found this quote on Andy Burns' Blog:

“On two occasions I have been asked, ‘Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?’ I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.”

– Charles Babbage



I when given more time will read more of Andy Burns posts.

2017-02-19

CSOM PowerShell update SharePoint list from Excel

As you probably know I’m not a great fan of MS SharePoint, I consider it to be a design disaster, where most sound design principles have been sacrificed on the altar of stupid simplification. The dreaded 5000 limit, the silent 2000 limit and the bizarre field naming conventions just to name a few. SP is good for (temporary) simple web sites with small amounts of data, integrating with MS Office suit FULL STOP, PERIOD, END.
For some weeks (actually a bit more) I have had this +1000 line Excel sheet on my desk of necessary changes in a +5000 SP list. The excel sheet did not contain row ID. No one at hand could help me how to apply these changes with standard SP scripting or Nintex workflows. And I sure do not know how to do this with SP or Nintex scripting. My choices were C# or Powershell script using the CSOM library, our SP service provider do not allow me to run SOM scripts on their infrastructure plus the very idea of running transactions on the SP server is daft if you ask me. I decided to go for Powershell scripting just for the hell of it. I do not know Powershell .NET or SP well and I have no documentation of it so I have to rely on Google normally my favourite source of developer visdom. Unfortunately Google is a bit complicated with SP, I connect to SP via CSOM and I do not find that many working CSOM examples on Google, moreover many of the CSOM examples are in fact SOM, it seems many do not recognize there is a difference, I can sympathize with that, it should not be different libraries for connecting SP depending on from where you are calling SP. Microsoft have a great pedagogical task to do, promote CSOM and teach the SP community to use CSOM and depricate SOM a.s.a.p. Two SP libraries are one to many. Anyway after almost a day of Googling, picking bits and pieces from posts, extrapolating and trial and error I had a working script, I had to save my Excel sheet as a CSV file. It took some 40 minutes to run which is ridiculous, but it’s SharePoint so you have to live with it I suppose. Anyway the PowerShell script is quite elegant and it did the job. I like PowerShell scripting, if I only can get my head around the .NET object model I can probably do many things fast and efficient in the Windows environment.


$x = @"
This script updates items in a SharePoint list from a csv file
The csv file does not contain the ID of list items, matching is done on 'key' fields.
Processing:
1. read the csv file into an iterator
2. iterate through the SharePoint list
3. for each list item, iterate through the csv file
4. if cvs row match update the ShartePoint list with values from the csv row

Note! This type of matching is inefficient, but quite ok for SharePoint lists as there few items in lists
and SharePoint per se is easily 'saturated', this matching gives SharePoint pauses to recover.   
"@

Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll"  
Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"  

$UserId = "USERID"
$Password    = ConvertTo-SecureString 'PASSWORD' -AsPlainText -Force
 
$siteURL = 'https://thehub.group.atlascopco.com/metadata/group_codes'
$listItemstname = "RG_PGC_Codes" # SP list
$readItems = 5000                # Items to read from SharePoint list

# csv column names
$csvRG = "Rate Group Code"       #key
$csvPGC = "Product Group Code"   #key
$csvGAC = "GAC"                  #Key
$csvUNIT = "Unit"                #key
$csvVALIDTO = "ValidTo"          #value
# csv file
$delimeter = ";"
$filePath = "C:\rgupdate.csv"
$csvData = Import-Csv $filePath -Delimiter $delimeter # Load the CSV file containing the updates
$credentials = New-Object System.Management.Automation.PSCredential $Username, $Password
$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($siteURL)  
$ctx.credentials = $credentials  
try {  
   $list = $ctx.web.Lists.GetByTitle($listItemstname)  
   $listItems = $list.GetItems([Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery($readItems))  
   $ctx.load($listItems)    
   $ctx.executeQuery()  
       foreach($li in $listItems) {
           if ($li["ValidTo"] -eq $null){    # Filter; only null 'ValidTo' are interesting
               foreach ($line in $csvData){
                   if (($li["Rate_x0020_Group_x0020_Code"].LookupValue -eq $line.$csvRG) -and ($li["Product_x0020_Group_x0020_Code0"] -eq $line.$csvPGC) ){
                        Write-Host "Match"
                        $ID = $li["ID"]
                        write-host $line.$csvUNIT,$line.$csvGAC,$line.$csvPGC,$line.$csvRG,$line.$csvVALIDTO, $ID
Write-Host "ID -" $li["ID"] ", RG -" $li["Rate_x0020_Group_x0020_Code"].LookupValue ", PGC -" $li["Product_x0020_Group_x0020_Code0"] ", GAC -" $li["GAC0"] "Unit -" $li["Unit"].LookupValue " ,validTo -" $li["ValidTo"]
                        $li["ValidTo"] = $line.$csvVALIDTO
                        $li.Update()
                        $ctx.Load($li)
                        $ctx.ExecuteQuery();
# 'Break' will stop update an item after first match, 'Return' stop processing after first match (for testing purposes)
                       Break
                       # Return
                   }                        # endif Match
               }                            # end foreach csv row
           }                             
   }                                        # end foreach SP item
} catch {  
   write-host "$($_.Exception.Message)" -foregroundcolor red  
}