Yet another year in The Data Warehouse

 2001 I created the data warehouse. The reason was to help a purchaser with a forecast report. It would have been easy just to create a paper report from the mainframe ERP system I created 1979, but I wanted to create a better tool for business analyzing and reporting, but I did not get founds to by any software, so I had to create a system from what I had and free software. I was pretty sure the data warehouse would be around for some ten years or so, but not for twenty three years, still counting! I some years ago wrote that the swan song for the data warehouse had begun, it is a very long tune indeed. I would not be overly surprised if I can celebrate a twenty fifth jubilee next year. 

Of course I have not done The Data Warehouse alone, I made the infrastructure and the tools to import and massage the data. Most reports, marts, apps etc, have been made by others, not to forgot the operations. A data warehouse is a complex piece of machinery that needs supervising, maintenance and tinkering 24x7.

For this year I expect to finalize the PHP 7.4 to 8.4 conversion, this is my worst job ever and I have done a lot of really bad jobs during the years. This upgrade is something I'm forced to do, I really did not want to do it, but you do not argue with IT security. I also need to upgrade MySQL. Both these things should have been done ages ago. But now I have to do it boring, boring, boring. 

When I retired I said 'From now on I only do fun things that interest me.' Since then I only do boring things or even worse, I had to maintain some SharePoint apps for some years, that was hopefully the low moment of my years in IT. I have for some ten years wanted to start D programming, but I can not muster the energy needed to learn D. I also have looked at Raku but have not started yet. And now I'm looking at Java script again! I really hope I can muster energy this year to restart my career, I'm still only seventy one. 

Trick from Walter Bright

 Yesterday I learnt a neat syntactical trick from Walter Bright founder of the D language.

do
{
    if (x) break;
    if (y) break;
    if (z) break;
    hotPath();
} while (0);

This is very useful if you like me often have more than one return from functions and subroutine. For many reasons you only want one return point. This little trick may come handy in some situations. 

I recommend the D language page https://dlang.org/ if you are interested in Programming in general. I found the discussions of the D language itself very interesting.    

New year new currency rates

Last year, actually last Friday we had an incident in the Data Warehouse. The currency rates for next month must be loaded otherwise the Data Warehouse stops. It litterary stops, you cannot do any updating at all, so updating the currency rates it a pretty important task. Now I have not been involved in this since 2013 and the task was handed over to a new guy on the job, he is located in India and that is important for this story. I thought of course this will go wrong, but I created the currency procedure so I can fix it this should not be a problem, but of course it was. 

This story begins in 2008, every month since we introduced currency rates in the Data Warehouse I had to manually copy the rates from an excelsheet pubished on a crap web site. Around 2008 I was fed up with this and created a procedure that use CURL to grab the Excel sheet and updated the Data Warehouse. This worked fine until I was on vacation when a summer stand-in at the finance added comments and rearrage the rates on the Excel.They call me from the office when I was on the autobahn in southern Germany, that is when I invented speed debugging. Back at the office I had a meeting with the finance and made them promise never ever change the Excel format of the currency rates without telling in advance. (It turned out noone else progmatically grabbed and interpreted the Excel sheet.) 

Later at 2016 when I was the enterprise lead information architect, I together  with a finance guy created a system grabbing the currency rates from Bloomberg and published them in MS sharepoint, which was and is the company's platform for data interchange. We were ready to go into production when the finance department responsible for currencies was moved over Seas. The new department manager stopped my new 'rate system'. (They created a currency system of their own. Better? My system you can see, the new system you will never see). Anyway the rates were published on Sharepoint and I created a Sharepoint API import system for the Data Warehouse. At that time I had been removed as an IT architect. The new information architects decided it was forbidden to fetch data directly from Sharepoint! Data must only be extracted by APIs developed by a consultant.  I found it a bit strange not to use Sharepoints own API for sharing, but OK I can use the new APIs. But the management of the Data Warehouse did not allow me to use the new API. I find it a bit comic I who was behind the idea and developed the APIs for programatically extract the currency rates was in the end the only one who was not allowed to use the currency rates API when it was finally put into production.

So here we were at the end of 2023 failing to update the currency rates using my old semi automatic system from long time ago. First problem, my old system transfers the rates to the data warehouse with FTP. Now at the end of year someone has firewallblocked the FTP transfer to the Data Warehouse. It took quite some time to realize this. If FTP does not work I had other ways to transfer the data to the data warehouse. Now we run the insert procedure and it failed,  now the Data Warehouse was blocked, if there is a problem with the currencies the Data Warehouse is blocked. W-T-F was the reason for the failure. After a long time I realised there was a problem with the input. At this point a colleague come to the rescue. Before sending the currency rates to the Data Warehouse the excelsheet is converted to a CSV file. My colleague told us you must use Swedish locale when doing the CSV conversion, something I had forgot, the (swedish) colleague sent me a correct CSV file. Now when we tried to insert the currecies we were told the Data Warehose was blocked due to invalid currencies. Of course we have a force option, I use the more colorful 'gogogo', adding gogogo we were able to insert correct currencies.

Now in hindsight, the 'currency procedure' is not that bad, but it is brittle. We have some work to do until next month's end. 

p.s.

The result of  last currency intake, (the last row  is the important one).

STATUS
DISTINCT_VALUES	1: AVG AND EOP HAVE DIFFERENT VALUES FROM LATEST PERIOD
NEW_RATES	WARNING: TOTAL NUMBER OF EXCHANGE RATES IS DIFFERENT FROM PREVIOUS PERIOD
MAX_EOP	1: EOP SINCE LATEST PERIOD HAS NOT INCREASED MORE THAN 20%
MIN_EOP	1: EOP SINCE LATEST PERIOD HAS NOT DECREASED MORE THAN 20%
MAX_AVG	1: AVG SINCE LATEST PERIOD HAS NOT INCREASED MORE THAN 20%
MIN_AVG	1: AVG SINCE LATEST PERIOD HAS NOT DECREASED MORE THAN 20%
MAX_EURX	1: EURX SINCE LATEST PERIOD HAS NOT INCREASED MORE THAN 20%
MIN_EURX	1: EURX SINCE LATEST PERIOD HAS NOT DECREASED MORE THAN 20%
DISTINCT_DATE	1: EOP,AVG,EURX ALL HAVE A NEW DATE DIFFERENT FROM LAST PERIOD
ACAB_DATE	1: ACAB HAVE NEW DATE FROM LATEST PERIOD
ACAB_VALUE	1: ACAB HAS DIFFERENT VALUE FROM LATEST PERIOD
BUD_DATE	1: BUD HAVE NEW DATE FROM LATEST PERIOD
BUD_VALUE	1: BUD HAS DIFFERENT VALUE FROM LATEST PERIOD
CURRENT_PERIOD	1: LATEST PERIOD IN EXCHANGE_RATE IS CORRECT

    

Yet another Year

This year was an eventful year for my Data Warehouse. A new guy Hans Mattson stepped in to help me running the Data Warehouse. In no time Hans became proficient in running the Data Warehouse, with his extreme analytical skills he solved some very complex problem we faced. 'You know I'm curious, I want to know how things works' he used to say. Unfortunatly his previous employeer realized how much they missed him and gave him an offer he could not refused. I'm happy for Hans, but I miss the almost daily chats we had about IT in general and Data Warehousing. Hans is more than a good IT guy who know his craft, he is also a very nice guy to work with.
(A new promising guy have just stepped in to replace Hans.)

I have started upgrade the Data Warehouse from PHP 7.4 to PHP 8.1, (the plan is to go to PHP 8.3). This turned out to be tidious and much harder than I had anticipated. PHP is not very PHPish anymore. All the nice features/defaults I used to silently take care of conversions, bad and buggy code now issues tons of warnings, deprications and errors. This upgrade takes a long long time, eventhough the code in general is pretty good and well documented, sometimes it is hard to remember the reason behind code written in PHP 4 twenty years ago. Upgrades to new PHP versions have been simple until now, (PHP 7.4 forced me to rewrite the entire SAP interface, apart from that very little problems). This time I will remove not only errors, but depreciations and warnings as well.
This year we upgraded MySQL to vs 5.7, next year the plan is to upgrade to vs 8.

Other things.
After years of procrastination I have now almost finalized Rey Valeza's D language web tutorial. It is a really great web programming tutorial, if you are an experienced programmer without 'web experience' and like to get your hands dirty this is a tutorial for you. Next I see if I can find a D language tutorial on creating a 'bare metal' program. I want to be a D programmer, I am not today, I cannot even read D code.

At last a happy new year😊

Import SAP data into MySQL with little effort

 This morning I stumbled upon a SAP import script written in my Integration Tag Language. The script is a little gem, it is probably written about 2008.

The script includes all code needed exporting data from a SAP rfc function into Mysql (including defining an import table in Mysql. I have removed parts not necessary for showing the complete export process from beginning to end.

The script starts defining  the workflow, run time parameter and symbolics:

(The run time parameter sap points to a SAP system.)

Next we have two jobs truncate & getSapData:

1 Truncate the result table if it exists (only for full load)

2 GetSapData (the little gem)

This job is packed with relevant and succinct logic for SAP data extraction.

(I added some comments for your convenience.)

That’s it.

The script is a complex parallel process expressed in concise and well defined code.

I have not written the script myself, but I’m proud of the Integrated Lag Language (ITL) which I wrote in 2006 that makes this script possible.

If you have a better way of importing data from SAP, please drop me a note.

If you are the script author you must give me a call🙂

Sevetieth birthday

Somethings you cannot avoid like haveing your sevetieth birthday. I just had mine, I just pretend it never happened.

A snapshot monitor for MySQL

I recently created a snapshop monitor for Mysql.

It gives nice snapshots every 15 minutes of Mysql (server) activities.

It is a useful tool for finding problems especially from night run as you can go back in time. 

The output consists of:

1 Memory stats of the server

2 Number of SQL queries since last snap

3 Processes running when snap is taken

A snapshot of the snapshop monitor output:

Fri Aug 19 03:31:10 CEST 2022              total       used       free     shared    buffers     cached Mem:         64383      63785        597          2          0      45306 -/+ buffers/cache:      18478      45905 Swap:        32767         75      32692 SQL queries since last snap 1011240 Id User Host db Command Time State Info 36790 KalleAnka toossedwvetl3-i:52859 TRANSFORM Query 1461 Sending data create temporary table LKLP_Selection (primary key(VBELN, POSNR)) as (\n selec 36798 KalleAnka toossedwvetl3-i:52868 RAW Query 4193 updating DELETE FROM TRANSFORM.Finance_Pricing WHERE Valid_From_Date >=  36848 KalleAnka toossedwvetl3-i:52930 TRANSFORM Query 56 copy to tmp table ALTER TABLE PTD_DTR_OL ADD INDEX (KUNNR, VKORG) 36967 manager %:53112 TRANSFORM Query 124 Creating sort index INSERT INTO TRANSFORM.Logistics_MaterialMovements_RollingQuantity_Periodic(\nSELECT\nA.W 36977 KalleAnka toossedwvetl3-i:53122 TRANSFORM Query 4175 Sending data INSERT INTO TRANSFORM.Sourcing_PurchaseOrders\nSELECT\nA.AFNAM as  36992 KalleAnka toossedwvetl3-i:53137 TRANSFORM Query 4170 Sending data INSERT INTO PPV_INVOICED_Temp (\n\t SELECT \n\t A.BELNR as  38395 KalleAnka toossedwvetl3-i:55708 TRANSFORM Query 2857 Sending data INSERT INTO TRANSFORM.PKBridgeMaterialCost  38554 KalleAnka toossedwvetl3-i:55976 ACTADW Query 2449 executing LOAD DATA LOCAL INFILE 'AFRU.CSV' replace INTO TABLE ZDTSQRYAFRU FIELDS B 38566 KalleAnka toossedwvetl3-i:55981 A…

 The bash code:

#!/bin/bash mv /home/tooljn/mymonlog/0mymon.txt "/home/tooljn/mymonlog/$(date '+%Y%m%d%H%M')-mymon.txt" find /home/tooljn/mymonlog -type f  -mtime +32 -delete while true do    date >> /home/tooljn/mymonlog/0mymon.txt    free -m >> /home/tooljn/mymonlog/0mymon.txt    mysql -e "show global status like 'Queries'" | grep Queries >> /home/tooljn/mymonlog/mymonQ.txt    topl="$(head -n 1 /home/tooljn/mymonlog/mymonQ.txt | grep -oE '[^[:space:]]+$')"    botl="$(tail -n 1 /home/tooljn/mymonlog/mymonQ.txt | grep -oE '[^[:space:]]+$')"    queries=$((botl-topl))    echo "SQL queries since last snap $queries" >> /home/tooljn/mymonlog/0mymon.txt    (mapfile -n 2 <  /home/tooljn/mymonlog/mymonQ.txt;    if ((${#MAPFILE[@]}>1)); then     echo "This file has more than 1 line."    fi)    mysql -e "show processlist" | sed '/Sleep/d'  | sed '/root/d' |  sed -e "s/[[:space:]]\+/ /g" | cut -c -150  >> /home/tooljn/mymonlog/0mymon.txt    echo "- - - - - - - - - - - - - - - - - - - - - - \n" >> /home/tooljn/mymonlog/0mymon.txt    sleep 15m done