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 published anywhere in 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.

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 extracts in the DW, since the replacement was just around the corner and it 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 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. 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 in 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.