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?
