Yet another year in The Data Warehouse - 2025

The Data Warehouse is still live and kicking. 25th year anniversary is coming 2026. From a very modest start 2001 it has a steady growth until about 2018 both in terms of users (+300) and workflows (+3000/day). Workflows extracting data from ERP systems massaging data, creating reports, apps, mails and acting as a data hub for external apps and GUIs. Now users and workflows are decreasing but not as fast as we wish (I'm a bit tired of  the DW, but I will maintain it until it is decommissioned, no date sat yet. 

 This year we upgraded all servers to Ubuntu 2022.04 for security reasons and I'm planning to upgrade MySQL for the same reason. I will also make an attempt to upgrade PHP to vs 8.4 if there is not too much work. These upgrades has been planned for a very long time, but there is always things coming up.  Data Warehouses are very dependent of their surroundings and thus vulnerable to disturbances in the IT landscape. Plus, most apps are never done; they evolve until they are dead. This includes the dying DW.  

 I am responsible for a stupid workflow updating currencies. At the time the currency workflow was created, it was almost decent, a manual process of extracting currencies from an Excel and downloading some extra currencies from SAP. This has to be done on the last working day of the month. Now in the last hours of 2025 I am together with an Indian colleague waiting for the SAP ERP system to update some currencies before we can update the DW currencies. this will happen around 2 pm CET, which means new year parties already started in India.  

 The Data Warehouse is written mainly in spare time between 2001 and 2006 with no proper maintenance since 2012. And it shows, in 2012 the DW was in a league of its own. Today there are a number of options that are as good or better. Two friends of mine (Camilla Hillero and Andreas Roine-Larsson)  have created successful data -warehouse/-lake/-lake house or whatever they are called, using Snowflake and Clickhouse, they are equally pleased of their own choices. 

 At last a happy new year and my best whishes for 2026😀 

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.