The Data Warehouse 25 years still going in style!

It was 25 years ago today I started up the Data Warehouse, and it has been in style ever since.

The last anniversary (15 years) could not be published anywhere on the company's intranet, so I published it here. The reason - the IT-manager at that time did not like the Data Warehouse, so he told me the post was to offensive! At the time I was an employee (lead data and application corporate IT-architect). 2012 I handed over the DW to a team of brilliant IT workers, Camilla, Petter and Henrik. They have since long left for successful careers, they still call me a friend something I'm immensely proud of.

Since about 2020 I'm a contract maintainer of the Data Warehouse now 25 years old. I almost forgot about that, this is worth a blog post. How many DWs are 25 years old! Since the last anniversary not much have happened, the company put a ban on developing new apps, reports and extracts in the DW, since the replacement was always just around the corner and still is, last month I was told we can close down the DW at the end of 2027. This time I think it is true and I hope so, I'm getting tired of maintaining, that is so not me. I'm not even good at maintaining.

Considering very little development have been done on the DW machinery since 2013, the DW have stood the test of time remarkably well. The USP is extremely short time-to-market and very simple fast extraction of data from SAP. Not to mention the low cost, well until management started increase the costs. Last cost driver forced onto the DW was a move to the cloud.

Anyway the last 5 years, it seems management actually appreciate the DW, which makes me happy.

I have always been well respected even liked in the company, and nowadays the DW is recognized as the mission critical app it is.

Fifteen years ago I said I probably retire before the decommission of the DW, that was true. Ten years ago I said I probably die first, I really hope this is not true.

All things considered the DW is still in good shape and will die with the flag on top.

I consider the DW to be one of my five best software creations.

MySQL is the table used?

The Data Warehouse was conceived in the late 1990ties and first started up after one month of tinkering with old PCs and probably one of the first PHP 4 alpha release. In the beginning we extracted data from my old Material Planning Scheduling system I wrote 1979 which was converted to SAP 2006.

The purpose of the Data Warehouse is to deliver reports with short as possible time to market and empower the business users with direct access to data. We do not use any explicit dimension tables or more or less static cubes. We extract raw table data and massage them when necessary into what I call Business Query Sets using business rules, a BQS contains all data for one or more reports, the idea is the front-end should not have todo joins, but nothing stops the business to access raw data  and join as they wish, the business user is king. 

Each BI developer have full power (and knowledge) to run the DW machinery, extract data, set up jobs, create apps/reports in direct contact with users. Written specifications  is a nono,  This plus the absence of test and quality systems made the DW extreme agile. We cranked out reports and apps faster than anything I heard of. No one beats the good old DW when it comes to time to market. 

But there are at least one drawback with our free for all development methodology, it produces a lot of crap, abandoned reports, jobs live forever, we do not clear out old junk or tests. Just like the Evolution  builds on the the existing DNA and leaves a lot of old crap in there, the Data Warehouse after 25+ years have accumulated a lot of crap not used anymore.

Now in the eleventh hour in the life of the DW I  have to do a cleanup due to a mandatory upgrade of the data storage MySQL vs 5.7 to MySQL vs 8.4. The cleanup is to shorten conversion time and make a conversion of MyISAM to InnoDB feasible. One question arises when you start cleanimg up a data storage - is this data used? It is a hard question to answer, in MySQL you do not have a straight answer. In the next post I show my attempt to answer the question is the table used? 

Booyah! PHP 8.5

Finally the Data Warehouse is running on PHP 8.5.

As all maintainers of Data/Lake/Beach/Business/etc. ware-houses knows, all test/quality/etc. environments forced upon us by ignorant IT architects just make the day-to-day operations much harder with very little benefits. But on occasion you cannot do testing in the production environment. In the Data Warehouse we then create a temporary test environment for the purpose, this time I had to change the bootstrap process which took a little time, with the new bootstrap testing PHP 8.5 went just fine.

For the first time since 2012 The Data warehouse is on par with PHP development. When I started building the Data Warehouse back in 2001 I realized I could build a better development environment with open source tools than the company provided. As an IT manager I could build in hiding without anyone noticed I did not follow any corporate rules. I built my own hardware, used open source software, employed alpha and beta versions in production. I and one coworker just built the Data Warehouse and cranked out reports, apps and analyses mainly to purchasing, logistics, production and marketing. We had full access to the production source system which I had build some 20 years earlier (yes mainframe green screen uglies and all that). We invited the business to explore the data themselves, which was unheard of at the time. One clever guy build a spare part pricing system "PricePoint" on top of the Data Warehouse, and later created his own company "Navetti AB". 

Anyhow some years after the start, I could speak openly about my Data Warehouse, "the business" loved it, and they are key in the company. Now the corporate guys noticed we used Linux and they sent out an emissary to kill it, but it was to late. My coworker was busy as a bee, helping the business cranking out business intelligence at a breakneck speed, he was the de facto business analyst of the company, to a large extent he told the business what they should measure. Only now our dazed management really noticed the Data Warehouse, they saw it, heard about it, even used it. But as the Data Warehouse did not incur any costs they had not really noticed it. I got some (mostly negative!) recognition, my coworker now the facto chief business analyst got almost none (the little he got was positive though). In a fair world my coworker should have had a doubled paycheck and a fancy title.

That was a long story  to say: 2012 the development of the Data Warehouse core stopped. I left the company and became a corporate lead IT architect. At my retirement I came back as a part time consultant maintaining the Data Warehouse. Now when the 4th attempt to replace the Data Warehouse will make it next year, the corporate security forced me to close the technological depth making me work way more than I want. Now when PHP 8.5 is implemented the last piece to upgrade is MySQL which I will start tackle next month.

What will I do after the Data Warehouse? Before I die I hope I come up with some IT related "thing". Those who live will see. 

A teaser: we must create more energy efficient IT systems. United "drill baby drill" States, will not help, we have to do it ourselves which is a good thing.

 

GKRALIK's SAPNWRFC, PHP 8.5, SAP SDK 7.50 patch level 18

 After some debugging it now works. Ready for beta testing.

After more than a decade of neglected support now PHP and SAP connector is up to scratch.

2001 I started on an alpha/beta PHP 4 release and Koucky's SAPRFC SAP connector. My code has remained remarkably intact.

the problems I had:

going from patch level 1 to 18. level 1 used direct ip addresses, level 18 domain addresses, to my surprise my server did not have contact with the company DNS. It took me quit a while to figure this out. My PHP code for the SAP prereqs (see below)  were a bit 'rough'.

A workflow grabbing an SAP table, with a SAP prereq (verifying a SAP job is run ok). Used as my test case.

Killing PHP

 Going from php 8.3 to php 8.4.

This works perfectly fine in php 8.3:

function dumpit($msgtype,$var, &$val){...}

$log->dumpit('Trace','calling_process',trim($pidout));

The reference in the dumpit function signature is wrong, it should not be there. PHP up to version 8.3 was smart enough to recognize that and just execute the code. Very PHPish try to do what the coder intended.

Some structural fascist changed vs 8.4 made that a fatal error, I have words for such persons. A more reasonable approach write a warning or a depreciation msg.

This is killing PHP, makes it bland and gloomy, be as any run-of-the-mill language. It makes me sad.

 

Upgrading PHP

 I'm on my way upgrading The Data Warehouse engine PHP to version 8.5. The DW was originally written in  vs 4. As I recall it, the transition to vs 5 and vs 7, was simple. The upgrade to vs 8 was another matter entirely.

PHP was a language with relaxed syntax, e.g. allowing all kinds of comparing between value types, PHP had very good defaults making even stupid compare mostly came out right, it was PHPish. PHP honored getting things done not being picky about correctness, PHP tried to follow the intention of the coder and did a very good job at doing so. This infuriated structural fascists who's whining made the PHP    maintainers straighten up the language in vs 8, disallowing constructs in my PHP code that had worked well for some 20 years. If you have a large code base that old you just do not remember why a null array is compared with a blank char no matter how good the documentation is. The adjustment (not clean up)  to version 8 took a long time. 

Now going from 8.1 to 8.2, 8.3, 8.4 and 8.5 is a walk in the park. I'm on vs 8.2 now and will put 8.3 in production coming week. Then I will probably wait with 8.4 and 8.5 to September. The vacation period in Sweden starts late June and ends in Central Europe late August, during this period I avoid changes. 

Still alive

 The old work horse 'The Data Warehouse" keeps on running. Just upgraded PHP to vs 8.2. Otherwise not much is happening, I tried to create a somewhat saner routine for importing currency rates using power shell script, first attempt failed. A persistent cache, was not so persistent and I could not freely transport the script between clients, both probably due to security reasons, I did not bother to find out the root cause. I make one more attempt, if that do not work I keep the old insane routine. 

Claude's assessment of my Data Warehouse code

For what it's worth.

Over the seven directories I've (Claude) analyzed so far (include/, opsproc/, proc/, actaproc/, cpdproc/, blmproc/, fujiproc/) , the codebase has been remarkably well-written: ~30,000 lines reviewed

 No class-based dynamic-property issues 

No removed-extension dependencies in the active set (saprfc/sapnwrfc aside, which is a separate question) 

All compatibility issues are surface-level (cast keywords, csv argument defaults) That tells me the original author understood what they were doing. A system that small-edit-friendly after 20 years of PHP changes isn't going to suddenly break in deep ways from a PHPMailer point release. 

Run a representative schedule, watch the log, you'll know within minutes. Good luck with the deployment. 

 You said: Thank you very much for the appreciation of my work. The bulk code was written in PHP vs 4 between 2001 and 2007. 

 PMClaude responded: That's genuinely impressive. PHP 4 to PHP 8.5 is a 24-year span covering some of the most disruptive changes in the language's history — full object model rewrite in PHP 5, namespaces in 5.3, removal of mysql_* and ereg_*, the entire type system getting tightened, magic quotes gone, register_globals gone, the whole each()/create_function() removal, and the steady drumbeat of strictness in 7.x and 8.x. 

Most PHP 4 code from that era simply doesn't survive to the present day. The codebases that do tend to share certain habits, and yours has them: Procedural with clear data flow. The $context / $schedule / $job array conventions threading through every function is exactly the kind of discipline that pays off over decades. No hidden global state to keep "rediscovering." 

Defensive coding. array_key_exists checks, ?? FALSE fallbacks, explicit handling of "is the log object available yet" — those if ($log) $log->logit(...) patterns. 

Code from that era written without that discipline turned into warning-storms by PHP 7 and just rotted. One class with declared properties (log2.php) instead of dozens with implicit ones — that's probably the single biggest reason the PHP 8.2 dynamic-property deprecation was a non-event for you. 

People who wrote PHP 4-style classes with $this->whatever = ... sprinkled wherever convenient are still cleaning that up today. Schedule-driven architecture. Having the XML schedules carry the volatile/customer-specific logic (with eval as the escape valve) and keeping the engine itself stable is exactly why the engine could survive five major PHP versions. 

The XML-driven job scheduler/ETL pattern was actually quite forward-looking for 2001. A lot of what came later in the workflow-orchestration world (Airflow, Luigi, Argo) ended up at similar conclusions: declarative job definitions in a structured format, an engine that compiles them into executable graphs, prereq/exit-status semantics, named context that flows between steps. 

 The Lasse's Application Processor (C) 2006, 2026 Lars Johansson line in your scriptS.php that's a 20-year copyright span on a system you're still maintaining. That's a rare thing in any language and especially rare in PHP.

Hope the PHP 8.5 deployment goes smoothly and that adac keeps running another decade.

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.

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

Transpose away about 92 percent

It has been a long time since I wrote here about The Data Warehouse. I have spend time upgrading PHP from 5.6

to 7.4 and Mysql latin1 to utf8. None of this is in production yet. I also spent time on the long overdue Mysql from

5.6 to 5.7, which makes me go bonkers. In the old days when I had full control of the environment I just downloaded

and installed the MySQL version I wanted. Now I try to follow Ubuntu's rules, and I just fail, I can upgrade a test server,

but the prod server refuses to be upgraded. I have no clue what goes wrong. This is really the backside of complex system

procedures that intercept you from compromising the system, in this case the Ubuntu system. I'm not interested in digging into the

finer details of Ubutu's APT system, but it seems I will in the end.  

Anyhow I did some SQL tuning lately, this is boring laborious work, but at the same time satisfyingly rewarding.

Some weeks ago I stumbled upon this code: 

insert into MAEX_US (                SELECT             A.MATNR,             A.ALAND,             A.GEGRU,             A.ALNUM             FROM MASTERDATA.MAEX A             WHERE A.GEGRU='US'                   ); insert into MAEX_EU (                SELECT             A.MATNR,             A.ALAND,             A.GEGRU,             A.ALNUM             FROM MASTERDATA.MAEX A             WHERE A.GEGRU='EU'                   ); insert into MAEX_E1 (                 SELECT             A.MATNR,             A.ALAND,             A.GEGRU,             A.ALNUM             FROM MASTERDATA.MAEX A             WHERE A.GEGRU='E1'                   ); INSERT INTO LEGAL_REGULATION_BOM     SELECT              A.PLANT             ,A.MATNR             ,A.DESCRIPTION             ,A.EXT_MAT_GRP             ,A.MATNR_COST             ,A.MATNR_COST_CURR             ,A.PARENT             ,A.COMPONENT             ,A.BOM_LEVEL             ,A.QTY             ,A.COMPONENT_COST              ,A.COMPONENT_COST_CURR             ,A.UNIT_OF_MEASURE             ,A.COMP_DESCRIPTION             ,A.ORIGIN             ,A.DUTYNO             ,A.BOM_PLANT_IS_PTD_0101_ACQPLANT             ,CASE                 WHEN A.PLANT IN ('0101','0104') THEN B.ALNUM                  WHEN A.PLANT='6100' THEN C.ALNUM                 WHEN A.PLANT='6130' THEN D.ALNUM                 WHEN A.PLANT IN ('1100','1110') THEN E.ALNUM                 WHEN A.PLANT IN ('3110','3115') THEN N.ALNUM                 WHEN A.PLANT='3125' THEN Q.ALNUM                 ELSE 'MISSING_LEGI'             END AS 'E1_LEG'             ,CASE                 WHEN A.PLANT IN ('0101','0104') THEN F.ALNUM                  WHEN A.PLANT='6100' THEN G.ALNUM                 WHEN A.PLANT='6130' THEN H.ALNUM                 WHEN A.PLANT IN ('1100','1110') THEN I.ALNUM                 WHEN A.PLANT IN ('3110','3115') THEN O.ALNUM                 WHEN A.PLANT='3125' THEN R.ALNUM                 ELSE 'MISSING_LEGI'             END AS 'EU_LEG'             ,CASE                 WHEN A.PLANT IN ('0101','0104') THEN J.ALNUM                  WHEN A.PLANT='6100' THEN K.ALNUM                 WHEN A.PLANT='6130' THEN L.ALNUM                 WHEN A.PLANT IN ('1100','1110') THEN M.ALNUM                 WHEN A.PLANT IN ('3110','3115') THEN P.ALNUM                 WHEN A.PLANT='3125' THEN S.ALNUM                 ELSE 'MISSING_LEGI'             END AS 'US_LEG'             FROM LEGAL_REGULATION_TEMP_BOM A                 LEFT JOIN MAEX_E1 B ON A.COMPONENT=B.MATNR AND B.ALAND='BE'                 LEFT JOIN MAEX_E1 C ON A.COMPONENT=C.MATNR AND C.ALAND='HU'                 LEFT JOIN MAEX_E1 D ON A.COMPONENT=D.MATNR AND D.ALAND='IT'                 LEFT JOIN MAEX_E1 E ON A.COMPONENT=E.MATNR AND E.ALAND='SE'                 LEFT JOIN MAEX_E1 N ON A.COMPONENT=N.MATNR AND N.ALAND='FR'                 LEFT JOIN MAEX_E1 Q ON A.COMPONENT=Q.MATNR AND Q.ALAND='US'                 LEFT JOIN MAEX_EU F ON A.COMPONENT=F.MATNR AND F.ALAND='BE'                 LEFT JOIN MAEX_EU G ON A.COMPONENT=G.MATNR AND G.ALAND='HU'                 LEFT JOIN MAEX_EU H ON A.COMPONENT=H.MATNR AND H.ALAND='IT'                 LEFT JOIN MAEX_EU I ON A.COMPONENT=I.MATNR AND I.ALAND='SE'                 LEFT JOIN MAEX_EU O ON A.COMPONENT=O.MATNR AND O.ALAND='FR'                 LEFT JOIN MAEX_EU R ON A.COMPONENT=R.MATNR AND R.ALAND='US'                 LEFT JOIN MAEX_US J ON A.COMPONENT=J.MATNR AND J.ALAND='BE'                 LEFT JOIN MAEX_US K ON A.COMPONENT=K.MATNR AND K.ALAND='HU'                 LEFT JOIN MAEX_US L ON A.COMPONENT=L.MATNR AND L.ALAND='IT'                 LEFT JOIN MAEX_US M ON A.COMPONENT=M.MATNR AND M.ALAND='SE'                 LEFT JOIN MAEX_US P ON A.COMPONENT=P.MATNR AND P.ALAND='FR'                 LEFT JOIN MAEX_US S ON A.COMPONENT=S.MATNR AND S.ALAND='US'

It took me quite a while to figure out the meaning of all these left joins.It is a  nifty way of transpose ALNUM from the MAEX table over MATNR and ALAND  into E1_LEG, EU_LEG and US_LEG like:

This code is clever or rather it was clever when constructed but now the data volumes have grown to large numbers

disclosing the intrinsic weakness of the many joins, today they take a very long time to execute (some 1100 seconds).

I failed to come up with a better way to do the transpose in the last SQL. I then ‘parked’ this problem.

Two days ago while jogging I realized I attacked the problem wrongly, it was not the last query that was the root problem,

but the first three queries splitting the MAEX table. If I do a full transpose over MATNR, I end up with one row per MATNR

which is what I need in the next phase.I remembered I read about a similar problem some years ago where the max

function was combined with group by.

insert into MAEX_PIVOT (         SELECT             A.MATNR            ,max(if (A.GEGRU='E1' and A.ALAND = 'BE', A.ALNUM, NULL)) as E1BE_ALNUM            ,max(if (A.GEGRU='E1' and A.ALAND = 'HU', A.ALNUM, NULL)) as E1HU_ALNUM            ,max(if (A.GEGRU='E1' and A.ALAND = 'IT', A.ALNUM, NULL)) as E1IT_ALNUM            ,max(if (A.GEGRU='E1' and A.ALAND = 'SE', A.ALNUM, NULL)) as E1SE_ALNUM            ,max(if (A.GEGRU='E1' and A.ALAND = 'FR', A.ALNUM, NULL)) as E1FR_ALNUM            ,max(if (A.GEGRU='E1' and A.ALAND = 'US', A.ALNUM, NULL)) as E1US_ALNUM            ,max(if (A.GEGRU='EU' and A.ALAND = 'BE', A.ALNUM, NULL)) as EUBE_ALNUM            ,max(if (A.GEGRU='EU' and A.ALAND = 'HU', A.ALNUM, NULL)) as EUHU_ALNUM            ,max(if (A.GEGRU='EU' and A.ALAND = 'IT', A.ALNUM, NULL)) as EUIT_ALNUM            ,max(if (A.GEGRU='EU' and A.ALAND = 'SE', A.ALNUM, NULL)) as EUSE_ALNUM            ,max(if (A.GEGRU='EU' and A.ALAND = 'FR', A.ALNUM, NULL)) as EUFR_ALNUM            ,max(if (A.GEGRU='EU' and A.ALAND = 'US', A.ALNUM, NULL)) as EUUS_ALNUM            ,max(if (A.GEGRU='US' and A.ALAND = 'BE', A.ALNUM, NULL)) as USBE_ALNUM            ,max(if (A.GEGRU='US' and A.ALAND = 'HU', A.ALNUM, NULL)) as USHU_ALNUM            ,max(if (A.GEGRU='US' and A.ALAND = 'IT', A.ALNUM, NULL)) as USIT_ALNUM            ,max(if (A.GEGRU='US' and A.ALAND = 'SE', A.ALNUM, NULL)) as USSE_ALNUM            ,max(if (A.GEGRU='US' and A.ALAND = 'FR', A.ALNUM, NULL)) as USFR_ALNUM            ,max(if (A.GEGRU='US' and A.ALAND = 'US', A.ALNUM, NULL)) as USUS_ALNUM             FROM MASTERDATA.MAEX A             WHERE A.GEGRU in ('E1','EU','US') and A.ALNUM is not NULL              group by A.MATNR );     220720 075316.546624 8628 Note SQLSTATE=00000, ERRORNO=0     220720 075316.546695 8628 Note SQLINFO Records: 32606

This request replacing the three original requests splitting the MAEX table gave MATNR with 18 ‘transpose’ columns

on each row:

Then it was just a matter of remove joins and map the ‘transpose’ columns to the right position:

INSERT INTO LEGAL_REGULATION_BOM     SELECT              A.PLANT             ,A.MATNR             ,A.DESCRIPTION             ,A.EXT_MAT_GRP             ,A.MATNR_COST             ,A.MATNR_COST_CURR             ,A.PARENT             ,A.COMPONENT             ,A.BOM_LEVEL             ,A.QTY             ,A.COMPONENT_COST              ,A.COMPONENT_COST_CURR             ,A.UNIT_OF_MEASURE             ,A.COMP_DESCRIPTION             ,A.ORIGIN             ,A.DUTYNO             ,A.BOM_PLANT_IS_PTD_0101_ACQPLANT             ,CASE                 WHEN A.PLANT IN ('0101','0104') THEN B.E1BE_ALNUM                 WHEN A.PLANT='6100' THEN B.E1HU_ALNUM                 WHEN A.PLANT='6130' THEN B.E1IT_ALNUM                 WHEN A.PLANT IN ('1100','1110') THEN B.E1SE_ALNUM                 WHEN A.PLANT IN ('3110','3115') THEN B.E1FR_ALNUM                 WHEN A.PLANT='3125' THEN B.E1US_ALNUM                 ELSE 'MISSING_LEGI'             END AS 'E1_LEG'             ,CASE                 WHEN A.PLANT IN ('0101','0104') THEN B.EUBE_ALNUM                  WHEN A.PLANT='6100' THEN B.EUHU_ALNUM                 WHEN A.PLANT='6130' THEN B.EUIT_ALNUM                 WHEN A.PLANT IN ('1100','1110') THEN B.EUSE_ALNUM                 WHEN A.PLANT IN ('3110','3115') THEN B.EUFR_ALNUM                 WHEN A.PLANT='3125' THEN B.EUUS_ALNUM                 ELSE 'MISSING_LEGI'             END AS 'EU_LEG'             ,CASE                 WHEN A.PLANT IN ('0101','0104') THEN B.USBE_ALNUM                 WHEN A.PLANT='6100' THEN B.USHU_ALNUM                 WHEN A.PLANT='6130' THEN B.USIT_ALNUM                 WHEN A.PLANT IN ('1100','1110') THEN B.USSE_ALNUM                 WHEN A.PLANT IN ('3110','3115') THEN B.USFR_ALNUM                 WHEN A.PLANT='3125' THEN B.USUS_ALNUM                 ELSE 'MISSING_LEGI'             END AS 'US_LEG'             FROM LEGAL_REGULATION_TEMP_BOM A             LEFT JOIN MAEX_PIVOT B ON B.MATNR = A.COMPONENT       ;     220720 081115.168632 19727 Note SQLSTATE=00000, ERRORNO=0     220720 081115.168706 19727 Note SQLINFO Records: 2583726

This reduced the time with 1012 seconds (from 1099 seconds to 87)