Lot Genealogy (Part 2): Automating Testing of a Genealogy

If you are going to roll out any caching of your lot genealogy data the one clear thing you have to get right is testing. If, like us, you are working in a highly regulated environment it's necessary to ensure that you've got your testing right.

To this end I've created a new table called LOT_GENEALOGY_WHEREUSED_TESTS;

Lot_Genealogy_WhereUsed_Tests Table Description

The purpose of this new table is to hold copies of the lot genealogy data from Lot_Genealogy_WhereUsed. By holding these "snapshots" we can check after each rebuild of the cache that the data that was there and we had previously validated as correct is still there.

To create tests the following SQL will insert an existing Lot Genealogy into the testing table;

INSERT INTO LOT_GENEALOGY_WHEREUSED_TESTS
  SELECT 'ATR002', -- test_Ref
         'Product lot (166449) consists of two items (038003 and 038001)', -- test_description
         -- data from Lot Genealogy
         MASTER_LOT_NUMBER,
         INGRED_LOT_NUMBER,
         INGRED_ITEM_NUMBER,
         INGRED_ITEM_DESCRIPTION,
         INGRED_ITEM_TYPE_CODE,
         BATCH_NUMBER,
         PRODUCT_LOT_NUMBER,
         PRODUCT_ITEM_NUMBER,
         PRODUCT_ITEM_DESCRIPTION,
         PRODUCT_ITEM_TYPE_CODE
    FROM LOT_GENEALOGY_WHEREUSED LGW
   WHERE 1 = 1
     AND LGW.MASTER_LOT_NUMBER = '0490/0002';

This SQL is taking the lot genealogy for Master Lot Number 0490/0002 and copying it into the testing table, adding a test reference (ATR002) and a test description so we know what the test is supposed to be checking for.

That, as they say, was the easy bit. Now we need to create some SQL that is capable of running a test and returning a result. Our reporting tool (in case you can't tell from my other blog posts!) is SQL Server Reporting Services (SSRS) hence I'm going to split the SQL into two pieces, one to give me a list of all the tests and the other to run an individual test - SSRS will allow me to embed the latter as a sub-report into a report driven by the former.

List of Tests SQL

This was by far the easiest of the two;

SELECT DISTINCT LGWT.TEST_REF VALUE,
                LGWT.Test_Ref || ' (Lot ' || LGWT.MASTER_LOT_NUMBER || ')' LABEL
  FROM LOT_GENEALOGY_WHEREUSED_TESTS LGWT
 ORDER BY 1, 2

This returns something similar to;

List of Tests Generated Using SQL

I've added the bit of additional detail (for the Label) just so that in addition for being able to use this in the main report to get a list of tests I can also use it in the sub-report as a source for a picker on the "Test_Ref" parameter.

Run A Test SQL

This is slightly larger but here's the code then I'll try and explain it;

SELECT :Test_Ref "Test Ref",
       (SELECT TEST_DESCRIPTION
          FROM LOT_GENEALOGY_WHEREUSED_TESTS LGWT1
         WHERE LGWT1.TEST_REF = :Test_Ref
           AND ROWNUM = 1) "Test Description",
       TR.Test_Row_Count "Test Row Count",
       TR.Cache_Row_Count "Cache Row Count",
       TR.Union_Row_Count "Union Row Count",
       CASE
         WHEN TR.Test_Row_Count = TR.Cache_Row_Count AND
              TR.Cache_Row_Count = TR.Union_Row_Count THEN
          'PASS'
         ELSE
          'FAIL'
       END "Test Result"
  FROM (SELECT (SELECT COUNT(*)
                  FROM LOT_GENEALOGY_WHEREUSED_TESTS LGWT1
                 WHERE LGWT1.TEST_REF = :Test_Ref) Test_Row_Count,
               (SELECT COUNT(*)
                  FROM LOT_GENEALOGY_WHEREUSED LGW
                 WHERE LGW.MASTER_LOT_NUMBER =
                       (SELECT MASTER_LOT_NUMBER
                          FROM LOT_GENEALOGY_WHEREUSED_TESTS
                         WHERE TEST_REF = :Test_Ref
                           AND ROWNUM = 1)) Cache_Row_Count,
               (SELECT COUNT(*)
                  FROM (SELECT MASTER_LOT_NUMBER,
                               INGRED_LOT_NUMBER,
                               INGRED_ITEM_NUMBER,
                               INGRED_ITEM_DESCRIPTION,
                               INGRED_ITEM_TYPE_CODE,
                               BATCH_NUMBER,
                               PRODUCT_LOT_NUMBER,
                               PRODUCT_ITEM_NUMBER,
                               PRODUCT_ITEM_DESCRIPTION,
                               PRODUCT_ITEM_TYPE_CODE
                          FROM LOT_GENEALOGY_WHEREUSED_TESTS LGWT1
                         WHERE LGWT1.TEST_REF = :Test_Ref
                        UNION
                        SELECT MASTER_LOT_NUMBER,
                               INGRED_LOT_NUMBER,
                               INGRED_ITEM_NUMBER,
                               INGRED_ITEM_DESCRIPTION,
                               INGRED_ITEM_TYPE_CODE,
                               BATCH_NUMBER,
                               PRODUCT_LOT_NUMBER,
                               PRODUCT_ITEM_NUMBER,
                               PRODUCT_ITEM_DESCRIPTION,
                               PRODUCT_ITEM_TYPE_CODE
                          FROM LOT_GENEALOGY_WHEREUSED LGW
                         WHERE LGW.MASTER_LOT_NUMBER =
                               (SELECT MASTER_LOT_NUMBER
                                  FROM LOT_GENEALOGY_WHEREUSED_TESTS
                                 WHERE TEST_REF = :Test_Ref
                                   AND ROWNUM = 1))) Union_Row_Count
          FROM DUAL) TR

As you can see it takes a single parameter, the Test Reference Number. How it works is it counts the number of records in the cache, counts the number of records in the test table, and then does a select of all the records in the test table and, using a straight UNION, all the records in the cache. Because of the way UNIONs work (stripping out duplicates) the COUNT of the number of records returned by the UNION should be the same as the number of records in each of the other two queries. If they are all the same the TEST_RESULT is 'PASS' otherwise it's 'FAIL'.

NOTE: I'm sure this could be done a lot more efficiently but to be honest given the relative sizes of the tables I don't think you'll be sitting round too long for a result. On our system it takes less then .02 of a second. Your mileage will vary, but probably not by much!

Now that I've got the SQL I've setup a simple SSRS report to display the result for a single test back to me;

SSRS Report Showing A Single Test Result

You'll also notice that the test result includes a listing of all the records included in the test. This is a simple SELECT * ... WHERE TEST_REF = ... so I'm not going to give you the SQL for it.

The master report looks like this (in Report Builder 3);

SSRS Master Reporting Showing All Tests

I've included text boxes (on each page as part of the footer) to record who the Tester and Checker are and the Date. For our internal quality purposes we need this level of detail, you might not but what's the harm?

When executed the report appears like this;

SSRS Testing Report - Final Result

Hopefully you will find this useful.