Data Lakes Explained

Last month, I wrote an explainer on AI and it was well-received, so here is one on data lakes.  If you already know the concepts, you may still find this framing helpful in client discussions.  Our audience this time is the CFO, or maybe the CMO, and our motivation is that their analytical needs are not well-served by the transactional database.

Transactional Processing with a Relational Database

The data that runs your business – most of it, anyway – is probably stored in a relational database like Microsoft’s venerable SQL Server.  Without going into details about the “relational” structure, the key is that this database is optimized for the daily operations of the business.

New policies are booked, premiums collected, and claims paid.  These are transactions that add, change, or delete records.  There are also “read only” operations, like producing invoices, but the database is designed primarily for transaction processing.

A well-designed transactional database will resist anomalies

A well-designed transactional database will resist anomalies, like a line item with no invoice, or two sales of the same item.  The database designer will have used a technique called normalization, breaking the data up into smallish tables with relationships that enforce integrity.

Think of how your chart of accounts is organized.  Everything you need to account for is broken down to the lowest relevant level, and then rolled up for reporting.  Every journal entry hits two accounts, debit and credit, so that they’re kept in balance.  Your meticulously normalized database is kind of like that.

When a customer places an order, a row is added to the Order table.  You don’t need to open the Customer table unless there’s a change to the customer.  Built around these normalized tables is the machinery of indexes, clusters, and triggers, which support speed and integrity.

Pro Tip: Take time to confirm that the transactional database is stable and supporting the business satisfactorily.  You don’t want to start building pipelines and then discover there’s a problem with your data source.

Analytical Processing with a Data Warehouse

Transaction processing involves adding and changing data, with carefully limited scope.  Analytical processing, by contrast, is mostly reading data – not changing it – and holistic in scope.  To support this, the data must be copied into a separate database and denormalized.

Let’s say you want to know whether Dent protection sells better as a standalone product, or as part of a bundle – corrected for the number of dealers who don’t offer the bundle, and segmented by the vehicle’s make and price range.

You could run this query against the transactional database, but it would be difficult.  The query is complicated enough without having to piece together data from multiple tables.  The normalization which served so well for transaction processing is now an obstacle.

Confession: I am a normalization bigot.  I bought C.J. Date’s textbook, read the original papers in the ACM journal, and even coded Bernstein’s algorithm.  To me, organized data is normalized data, and de-normalizing is like leaving your clothes on the floor.

So, this is a good guide to denormalization.  Everything we learned not to do in relational databases – wide tables, nested data, repeating groups – is useful here.

Analytical data is stored in cubes, stars, snowflakes, hearts, and clovers

Analytical work requires not only a new database design, but a new database system.  Out goes SQL Server and now we have Big Query, Redshift, and Snowflake.  You may hear this buzzword, OLAP, which means “online analytical processing.”  This concept was invented for marketing purposes, to describe the new category of software.

Analytical data is stored in cubes, stars, snowflakes, hearts, and clovers (see sidebar).  Just kidding about the hearts and clovers.  Also, while your transactional database may be running SQL Server “on premise,” the analytical database will almost certainly be on a cloud service from Amazon, Microsoft, or Google.

To be honest, not everyone needs an OLAP database.  As CIO for BMW Financial Services, I did not recommend one because our analytical workload was small, at the time, and could be served adequately without a lot of new gear and expensive consultants.  Since then, I have gone over to the side of the consultants.

Sidebar: What’s an OLAP Cube?

In the early days of analytical processing, software vendors thought it would be a good idea to use a multidimensional data structure called a hypercube. Think of a typical spreadsheet, with rows representing an income statement and one column for each month. That’s two dimensions. Now, add a stack of spreadsheets, one for each region. That makes three dimensions, like a cube. I put myself through grad school working at Comshare, one of the first OLAP software vendors. It supported seven dimensions. That’s a hypercube. Nowadays, there are better data structures, and this leads to some confusion. Older analysts may assume that if they’re doing OLAP, then they must be using a cube. They may use the term “OLAP cube” to mean any analytical database, even though cubes have largely been replaced by newer structures.

Pooling Data in a Data Lake

You can think of the data lake as a way station between the transactional database and the data warehouse.  We want to collect all the data into a common repository before loading it into the data warehouse.

Why not simply extract, transform, and load data straight from the transactional database?  Well, we could, but it would be brittle.  Any change on either side would require an update to the pipeline.  The data lake decouples the OLTP and OLAP data stores.

The data lake serves the very important function of storing all the data, in whatever format, whether or not it’s amenable to organization.  The term’s originator, James Dixon, wanted to suggest a large volume of data with no preconceived organization.

The key thing is to collect all the data in one place, and think about organization later.  This calls for an “object data store,” like Google Cloud Storage.  GCP and AWS both use “buckets.”  You get the idea – this is where you leave your clothes on the floor.

Most of your data will indeed be structured data coming from the transactional database, and on its way into the OLAP database – but not all of it.  Here are some real-life examples I have encountered:

    • Logs of API traffic. Details of who is using our ecommerce API, including copies of the payload for each request and response.
    • Text snippets. A file of the several paragraphs that make our standard Texas contract different from the one in Wisconsin, so that we can produce new contracts automatically.  Same goes for product copy on the web site.
    • Telephone metadata. A list of timestamps, durations, phone numbers, and extensions for all calls in the call center, both inbound and outbound.

These examples are better served by special-purpose databases like Hadoop, Bigtable, and Mongo.  It’s best to take stock of all the data your analysts might need, broadly speaking, and start collecting it before you go too far with designing the OLAP database.

What is Accuracy?

Suppose you have tested positive for a rare and fatal disease, and your doctor tells you the test is 90% accurate.  Is it time to put your affairs in order?  Fortunately, no.  “Accuracy” means different things to different people, and it’s surprisingly easy to misinterpret.

What the 90% means to your doctor is that if ten people have the disease, then the test will detect nine of them.  This is the test’s “sensitivity.”  Sensitivity is important because you want to detect as many cases as possible, for early treatment.

On the other hand, like Paul Samuelson’s joke about the stock market having predicted nine of the last five recessions, sensitivity doesn’t tell you anything about the rate of false positives.  

If you’re into machine learning, you probably noticed that sensitivity is the same as “recall.”  Data scientists use several different measures of accuracy.  For starters, we have precision, recall, naïve accuracy, and F1 score.

There are many good posts on how to measure accuracy (here’s one) but few that place it in the Bayesian context of medical testing.  My plan for this article is to briefly review the standard accuracy metrics, introduce some notation, and then connect them to the inference calculations.

Accuracy Metrics for Machine Learning

First, here is the standard “confusion matrix” for binary classification.  It shows how test results fall into four categories: True Positives, True Negatives, False Positives, and False Negatives.  Total actual positives and negatives are P and N, while total predicted are and .

These are not only definitions, they’re numbers that express probabilities like the sensitivity formula, above.  This notation will come in handy later.  The standard definition of accuracy is simply the number of cases which were labeled correctly – true positives and true negatives – divided by the total population.

Unfortunately, this simple formula breaks down when the data is imbalanced.  I care about this because I work with insurance data, which is notoriously imbalanced.  The same goes for rare diseases, like HIV infection – which afflicts roughly 0.4% of people in the U.S.  Doctors use a metric called “specificity.”

The FP term in the denominator penalizes the model for false positives.  You can think of specificity as “recall for negatives.”  Doctors want a test with high sensitivity for screening, and then a more specific test for confirmation.  A good explainer from a medical perspective is here.

In a machine learning context, you want to optimize something called “balanced accuracy.”  This is the average of sensitivity and specificity.  For more on imbalanced data and machine learning, see my earlier post.

Bayes Theorem and Medical Testing

Bayes’ Theorem is a slick way to express a conditional probability in terms of its converse.  It allows us to convert “is this true given the evidence?” into “what would be the evidence if this were true?”

This kind of reasoning is obviously important for interpreting medical test results, and most people are bad at it.  I’m one of them.  I can never apply Bayesian reasoning without first making the diagram:

In this diagram, A is the set of people who have the disease and B is the set of people who have tested positive.  U is the universe of people that we’ve tested.  We have to make this stipulation because, in real life, you can’t test everyone.

We might assume that the base rate of disease in the wide world is A/U, but we only know about the people we’ve tested.  They may be self-selecting to take the test because they have risk factors, and this would lead us to overestimate the base rate.

Even within our tidy, tested universe, we can only estimate A by means of our imperfect test.  This is where some probability math comes in handy.  The true positives, people who tested positive and in fact have the disease, are the intersection of sets A and B.  Here they are, using conditional probability:

That is, the probability of testing positive if you’re sick, P(B|A), times the base probability of being sick, P(A).  Again, though, P(A) can be found only through inference – and medical surveillance.  Take a moment and think about how you would obtain these statistics in real life.

Mostly, you are going to watch the people who tested positive, set B, to see which ones develop symptoms.  The Bayesian framework gives you four variables to play with – five, counting the intersection set itself – so you can solve for P(A) in terms of the other ones:

That is, the probability of being sick if you’ve tested positive, P(A|B), times the probability of testing positive, P(B).  We know P(B) because we know how many people we’ve tested, U, and how many were positive.  Now that we’re in a position to solve for P(A) let’s bring back the other notation.

Accuracy Metrics and Bayes Theorem

Machine learning people use the accuracy metrics from the first section, above, while statistics people use the probability calculations from this second section.  I think it’s useful, especially given imbalanced medical (or insurance) data, to combine the two.

Now, we can rewrite the two conditional probability calculations, above, in terms of accuracy.  Set A = P, set B = P̂ , and the various metrics describe how they overlap.

And:

Giving our sick group as:

Finally, since you’re still worried about your positive test result … let’s assume the disease has a base rate of 1% – twice as virulent as HIV.  Recall that we never said what the test’s specificity was.  Since the test has good sensitivity, 90%, let’s say that specificity is weak, only 50%.

You are among 504 patients who tested positive.  Of these, only nine actually have the disease.  Your probability of being one of the nine is P(A|B).  This is the test’s precision, which works out to 1.8%.

Claims Prediction with BQML

Did you know you could develop a machine learning model using SQL?  Google’s cloud data warehouse, Big Query, includes SQL support for machine learning with extensions like CREATE MODEL – by analogy with SQL DDL statement CREATE TABLE.

If you’re like me, you’re probably thinking, “why on Earth would I ever use SQL for machine learning?”  Google’s argument is that a lot of data people are handy with SQL, not so much with Python, and the data is already sitting in a SQL-based warehouse.

Big Query ML features all the popular model types from classifiers to matrix factorization, including an automated model picker called Auto ML.  There’s also the advantage of cloud ML in general, which is that you don’t have to build a special rig (I built two) for GPU support.

In this article, I am going to work a simple insurance problem using BQML.  My plan is to provide an overview that will engage both the Python people and the SQL people, so that both camps will get better results from their data warehouse.

  1. Ingest data via Google Cloud Storage
  2. Transformation and modeling in Big Query
  3. Access the results from a Vertex AI notebook

By the way, I have placed much of the code in a public repo.  I love grabbing up code samples from Analytics Vidhya and Towards Data Science, so this is my way of giving back.

Case Study: French Motor Third-Party Liability Claims

We’re going to use the French car insurance data from Wüthrich, et al., 2020.  They focus on minimizing the loss function (regression loss, not insurance loss) and show that decision trees outperform linear models because they capture interaction among the variables.

There are a few ways to handle this problem.  While Wüthrich treats it as a straightforward regression problem, Lorentzen, et al. use a composition of two linear models, one for claim frequency and a second for claim severity.  As we shall see, this approach follows the structure of the data.

Lorentzen focus on the Gini index as a measure of fitness.  This is supported by Frees, and also by the Allstate challenge, although it does reduce the problem to a ranking exercise.  We are going to follow the example of Dal Pozzolo, and train a classifier to deal with the imbalance issue.

Ingesting Query Data via Google Cloud Storage

First, create a bucket in GCS and upload the two CSV files.  They’re mirrored in various places, like here.  Next, in Big Query, create a dataset with two tables, Frequency and Severity.  Finally, execute this BQ LOAD script from the Cloud Shell:

bq load \
--source_format=CSV \
--autodetect \
--skip_leading_rows=1 \
french-cars:french_mtpl.Frequency \
gs://french_mtpl2/freMTPL2freq.csv

The last two lines are syntax for the table and the GCS bucket/file, respectively.  Autodetect works fine for the data types, although I’d rather have NUMERIC for Exposure.  I have included JSON schemas in the repo.

It’s the most natural thing in the world to specify data types in JSON, storing this schema in the bucket with the data, but BQ LOAD won’t use it!  To utilize the schema file, you must create and load the table manually in the browser console.

Wüthrich specifies a number of clip levels, and Lorentzen implements them in Python.  I used SQL.  This is where we feel good about working in a data warehouse.  We have to JOIN the Severity data and GROUP BY multiple claims per policy, and SQL is the right tool for the job.

BEGIN
SET @@dataset_id = 'french_mtpl'; 
 
DROP TABLE IF EXISTS Combined;
CREATE TABLE Combined AS
SELECT F.IDpol, ClaimNb, Exposure, Area, VehPower, VehAge, DrivAge, BonusMalus, VehBrand, VehGas, Density, Region, ClaimAmount
FROM
    Frequency AS F
LEFT JOIN (
  SELECT
    IDpol,
    SUM(ClaimAmount) AS ClaimAmount
  FROM
    Severity
  GROUP BY
    IDpol) AS S
ON
  F.IDpol = S.IDpol
ORDER BY
  Idpol;
 
UPDATE Combined
SET ClaimNb = 0
WHERE (ClaimAmount IS NULL AND ClaimNb >=1 );
 
UPDATE Combined
SET ClaimAmount = 0
WHERE (ClaimAmount IS NULL);
 
UPDATE Combined
SET ClaimNb = 1
WHERE ClaimNb > 4;
 
UPDATE Combined
SET Exposure = 1
WHERE Exposure > 1;
 
UPDATE Combined
SET ClaimAmount = 200000
WHERE ClaimAmount > 200000;
 
ALTER TABLE Combined
ADD COLUMN Premium NUMERIC;
 
UPDATE Combined
SET Premium = ClaimAmount / Exposure
WHERE TRUE;
 
END

Training a Machine Learning Model with Big Query

Like most insurance data, the French MTPL dataset is ridiculously imbalanced.  Of 678,000 policies, fewer than 4% (25,000) have claims.  This means that you can be fooled into thinking your model is 96% accurate, when it’s just predicting “no claim” every time.

We are going to deal with the imbalance by:

  • Looking at a “balanced accuracy” metric
  • Using a probability threshold
  • Using class weights

Normally, with binary classification, the model will produce probabilities P and (1-P) for positive and negative.  In Scikit, predict_proba gives the probabilities, while predict gives only the class labels – assuming a 0.50 threshold.

Since the Allstate challenge, Dal Pozzolo and others have dealt with imbalance by using a threshold other than 0.50 – “raising the bar,” so to speak, for negative cases.  Seeking the right threshold can be a pain, but Big Query supplies a handy slider.

Sliding the threshold moves your false-positive rate up and down the ROC curve, automatically updating the accuracy metrics.  Unfortunately, one of these is not balanced accuracy.  You’ll have to work that out on your own.  Aim for a model with a good, concave ROC curve, giving you room to optimize.

The best way to deal with imbalanced data is to oversample the minority class.  In Scikit, we might use random oversampling, or maybe synthetic minority oversampling.  BQML doesn’t support oversampling, but we can get the same effect using class weights.  Here’s the script:

CREATE OR REPLACE MODEL`french-cars.french_mtpl.classifier1`
    TRANSFORM (
        ML.QUANTILE_BUCKETIZE(VehAge, 10) OVER() AS VehAge,
        ML.QUANTILE_BUCKETIZE(DrivAge, 10) OVER() AS DrivAge,
        CAST (VehPower AS string) AS VehPower,
        ML.STANDARD_SCALER(Log(Density)) OVER() AS Density,
        Exposure,
        Area,
        BonusMalus,
        VehBrand,
        VehGas,
        Region,
        ClaimClass
    )
OPTIONS (
    INPUT_LABEL_COLS = ['ClaimClass'], 
    MODEL_TYPE = 'BOOSTED_TREE_CLASSIFIER',
    NUM_PARALLEL_TREE = 200,
    MAX_TREE_DEPTH = 4,
    TREE_METHOD = 'HIST',
    MAX_ITERATIONS = 20,
    DATA_SPLIT_METHOD = 'Random',
    DATA_SPLIT_EVAL_FRACTION = 0.10,
    CLASS_WEIGHTS = [STRUCT('NoClaim', 0.05), ('Claim', 0.95)]
    )  
AS SELECT
  Area,
  VehPower,
  VehAge,
  DrivAge,
  BonusMalus,
  VehBrand,
  VehGas,
  Density,
  Exposure,
  Region, 
  ClaimClass
FROM `french-cars.french_mtpl.Frequency`
WHERE Split = 'TRAIN'

I do some bucketizing, and CAST Vehicle Power to string, just to make the decision tree behave better.  Wüthrich showed that it only takes a few levels to capture the interaction effects.  This particular classifier achieves 0.63 balanced accuracy.  Navigate to the model’s “Evaluation” tab to see the metrics.

The OPTIONS are pretty standard.  This is XGBoost behind the scenes.  Like me, you may have used the XGB library in Python with its native API or the Scikit API.  Note how the class weights STRUCT offsets the higher frequency of the “no claim” case.

I can’t decide if I prefer to split the test set into a separate table, or just segregate it using WHERE on the Split column.  Code for both is in the repo.  BQML definitely prefers the Split column.

There are two ways to invoke Auto ML.  One is to choose Auto ML as the model type in the SQL script, and the other is to go through the Vertex AI browser console.  In the latter case, you will want a Split column.  Running Auto ML on tabular data costs $22 per server-hour, as of this writing.  The cost of regular BQML and data storage is insignificant.  Oddly, Auto ML is cheaper for image data.

Don’t forget to include the label column in the SELECT list!  This always trips me up, because I am accustomed to thinking of it as “special” because it’s the label.  However, this is still SQL and everything must be in the SELECT list.

Making Predictions with Big Query ML

Now, we are ready to make predictions with our new model.  Here’s the code:

SELECT
    IDpol,
    predicted_ClaimClass_probs,
FROM 
    ML.PREDICT (
    MODEL `french-cars.french_mtpl.classifier1`,
    (
    SELECT
      IDpol,
      BonusMalus,
      Area,
      VehPower,
      VehAge,
      DrivAge,
      Exposure,
      VehBrand,
      VehGas,
      Density,
      Region
    FROM
      `french-cars.french_mtpl.Frequency`
    WHERE Split = 'TEST'))

The model is treated like a FROM table, with its source data in a subquery.  Note that we trained on Split = ‘TRAIN’ and now we are using TEST.  The model returns multiple rows for each policy, giving the probability for each class:

This is a little awkward to work with.  Since we only want the claims probability, we must UNNEST it from its data structure and select prob where label is “Claim.” Support for nested and repeated data, i.e., denormalization, is typical of data warehouse systems like Big Query.

SELECT IDpol, probs.prob 
FROM pred, 
UNNEST (predicted_ClaimClass_probs) AS probs
WHERE probs.label = "Claim"

Now that we know how to use the model, we can store the results in a new table, JOIN or UPDATE an existing table, etc.  All we need for the ranking exercise is the probs and the actual Claim Amount.

Working with Big Query Tables in Vertex AI

Finally, we have a task that requires Python.  We want to measure, using a Gini index, how well our model ranks claims risk.  For this, we navigate to Vertex AI, and open a Jupyter notebook.  This is the same as any other notebook, like Google Colab, except that it integrates with Big Query.

from google.cloud import bigquery
client = bigquery.Client(location="US")
sql = """SELECT * FROM `french_mtpl.Combined_Results` """ 
df = client.query(sql).to_dataframe()

The Client class allows you to run SQL against Big Query and write the results to a Pandas dataframe.  The notebook is already associated with your GCP project, so you only have to specify the dataset.  There is also a Jupyter magic cell command, %%bigquery.

Honestly, I think the hardest thing about Google Cloud Platform is just learning your way around the console.  Like, where is the “New Notebook” button?  Vertex used to be called “AI Platform,” and notebooks are under “Workbench.”

I coded my own Gini routine for the Allstate challenge, but the one from Lorentzen is better, so here it is.  Also, if you’re familiar with that contest, Allstate made us plot it upside down.  Corrado Gini would be displeased.

The actual claims, correctly sorted, are shown by the dotted line on the chart – a lot of zero, and then 2,500 claims.  Claims, as sorted by the model, are shown by the blue line.  The model does a respectable 0.30 Gini and 0.62 balanced accuracy.

Confusion Table:
       Pred_1 Pred_0 Total Pct. Correct
True_1   1731    771  2502     0.691847
True_0  29299  36420 65719     0.554178
Accuracy: 0.5592
Balanced Accuracy: 0.6230

Now that we have a good classifier, the next step would be to combine it with a severity model.  The classifier can predict which policies will have claims – or the probability of such – and the regressor can predict the amount.  Since this is already a long article, I am going to leave the second model as an exercise.

We have seen how to make a simple machine learning model using Big Query ML, starting from a CSV file in Google Cloud Storage, and proceeding through SQL and Python, to a notebook in Vertex AI.  We also discussed Auto ML, and there’s a bunch of sample code in the repo.

Paying Bills for American Motors

My first Big Six consulting engagement, right out of MBA school, was solving a catastrophic failure in the Accounts Payable system of American Motors Corp.  You may recall AMC, they produced the Gremlin and the original Jeep.  This was right around the time of their acquisition by Chrysler, a sensitive time for the company.  The building still wore the red, white, and blue AMC logo, but the Chrysler employee newspaper was on the stand in the cafeteria.

It was on me to figure out what in hell had caused this popular and bulletproof software to fail. 

They were also just about to launch two new assembly plants in Canada, at Brampton and Bramalea, Ontario.  The launch, and maybe even the acquisition, was jeopardized because AMC had suddenly lost the ability to pay its suppliers’ invoices.  They had devolved to a purely manual process, paying months late, and their suppliers were threatening to cut them off.  Without a functioning A/P system, there would not be many parts shipping to the new plants in Canada.

The classical A/P function revolves around the “three-way match.”  Starting with the invoice, you must locate the purchase order for the goods and the slip from the receiving department showing that the correct goods had arrived.  As you can imagine, a giant manufacturing company cannot possibly perform this task on paper.  American Motors had been running the McCormack & Dodge suite of accounting software, and that was the proximate cause of the failure.  My assignment was to diagnose and fix the failure.

The Director of the A/P department had collected all of the invoices, receivers, and purchase orders into file boxes on tables in a huge room.  This had been a big conference room, maybe, or a gymnasium, and he had hired a platoon of “account temps” to run around the room looking for three-way matches.  Once someone found a match, they would run down the hall to the cashier and authorize payment of the invoice.  It was like a demented Chuck Barris TV game show.

The mad rush to pay months-old invoices was destroying any organization that might once have existed.

For me, as a programmer, this provided a stunning visualization of what this work must have looked like in the dark days before computers.  Of course, in those days, they would have been prepared for it.  Here, the mad rush to pay months-old invoices was destroying any organization that might once have existed in the file boxes.  The A/P director’s job was on the line and, over the weeks of my engagement, he aged ten years.  This poor devil was my client.  I could see the dark circles and the grey hair progressing as I greeted him each morning.

I should note that a new consultant doesn’t get a big job like this on his own but, as “senior schmuck onsite,” I was running the engagement.  Occasionally, higher-ranking consultants would show up to offer an opinion, not do any actual work, and bill four hours to the job.  Also, as the only one with any computer skills, it was on me to figure out what in hell had caused this popular and bulletproof software to fail.

Our method had two prongs of attack.  First, we brought in several junior, not yet CPA, staffers from our audit practice, and put them to work matching invoices.  This was basically the same process as in the gym downstairs, only our people were going to be smarter and look for patterns that might provide some clues.  Plus, we could bill for them at 100% realization.

Meanwhile, I would learn everything I could about the failing A/P system and its friends, the Purchasing system and the General Ledger system.  I read all three APRMs (Application Programmer’s Reference Manual, pronounced “A-Parm”) from cover to cover.  I read all the Job Control Language, the job streams, and much of the COBOL source code.

The only people dumber than the A/P department are these consultants!

I also got invited to defend our work at an executive meeting on the top floor of the AMC building, where I met the Vice President of Purchasing.  This was a big bull of a man, obviously some kind of ex-jock with a lot of red meat in his diet.  He pounded my guy mercilessly, and the preliminary stats from our auditors were no defense.  “The only people dumber than the A/P department,” he roared, “are the consultants hired by the A/P department!”

Eventually, I traced the failure to one specific job running one specific program, P1X030, the “matching module” itself.  All data flowing into, out of, or around this module were good, except that something like 90% of invoices went unmatched.  I called my manager up from Detroit and we went over the results.

I enjoyed working with Ken.  Back in those days, computer skills were considered déclassé.  I was the only consultant who could write a lick of code, and Ken was our only “technical” manager.  Eventually, the firm would get rid of Ken, and then me, in favor of a more golf-oriented practice.

“What about the exception report?” Ken asked, “is it dummied out?”  I checked the JCL.  Systems programmers would often streamline an implementation by suppressing some of its printouts but, no, P1X030 was faithfully printing a list of its reasons for rejecting 90% of the invoices.  “Let’s go for a walk,” Ken said.

We walked about half a mile, the length of the big, mainframe computer facility.  There, lying on the output table, was P1X030’s exception report.  Ken rapped on the window of the control room and spoke with the operator.  The report spooled off his printer every night, and then lay unclaimed on the table.  The operator had been collecting the old reports, and he was relieved to the be rid of them.  This was line-printer paper, boxes of it.  I waited while Ken went to find a hand truck.

The problem, printed mechanically line after line, was that the Purchasing department had been neglecting the important task of generating proper purchase orders.  They had been ordering the suppliers, probably in the same tones I had heard in the boardroom, simply to ship now and worry about the numbers later.

Purchasing had evidently instructed the suppliers to invent random P.O. numbers.  Our auditors had found a few clinkers, like 12345678 and 00000000, but mostly we had no clue.  If anyone had thought to ask a supplier, they would have been afraid to admit it and, anyway, it would have been the Purchasing department doing the asking.

I wrote up our findings and Ken presented them to AMC management.  He wheeled his hand truck into the boardroom and, for dramatic effect, read off the first few variants of “missing or invalid purchase order number.”  We included a report from P1X030, tabulating the various ways in which its safety features had been defeated.

There was no system failure for me to fix, so that concluded our engagement.  As to the failure we did find, management seemed eager to fix that one on their own.