Uitzonderingsrapportage

Hoe vaak komt het niet voor dat er data in operationele systemen wordt vastgelegd die weliswaar niet in strijd is met de invoercontrole van de diverse bedrijfsapplicaties, maar jij, of je baas, zou toch willen dat het ‘verboden’ was om dergelijke data in te voeren. Je kunt hierbij denken aan zaken als:

• verkeerd ingevoerde postcodes
• niet toegestane combinaties van grootboekrekening / kostenplaats / kostendrager
• ontbrekende gegevens bij een bestelling

Maar ook aan:

• een uitzonderingsrapportage voor bestellingen boven de € 5.000,-
• de rapportage van de kerncijfers uit de 6 backend-databases van je bedrijf
• de openstaande klachten voor customer support per medewerker

Enfin, laat je fantasie maar de vrije loop …

Het onderstaande artikel werkt een generieke oplossing uit met gebruikmaking van SQL 2005, Reporting Services en Integration Services om de uitzonderingsrapportage ‘s-nachts automatisch te genereren en als pdf te mailen naar de diverse medewerkers van je organisatie.

De business case van het artikel is: het op generieke wijze genereren van signaleringsrapporten van divers pluimage om de bestaande bedrijfsprocessen te monitoren, te ondersteunen en te verbeteren.

Het uitzonderingsrapportage-systeem (US) in hoofdlijnen

In figuur 1 is het hele US proces opgenomen. Rechts staan de gebruikers en/of machines die de diverse productiedatabases muteren. Periodiek, b.v. ’s nachts of iedere 5 minuten, start Integration Services (SSIS) het proces op wat door middel van het uitvoeren van SQL-statements alle te rapporteren gegevens uit de productiedatabases haalt. Deze gegevens worden weggeschreven in de ExceptionDB. Vervolgens wordt ReportingServices (SSRS) aan het werk gezet om op basis van data driven subscription, waarover later meer, de diverse collega’s te bedienen met de op maat gesneden uitzonderingsrapportages.

Fig. 1: Het uitzonderingsrapportage-systeem in hoofdlijnen

Het databaseschema

Om de grote diversiteit aan rapportages te kunnen ondersteunen heb ik het volgende eenvoudige databaseschema in elkaar gezet (zie figuur 2).

Fig. 2: Databasediagram van het US

De “magie” van het hele systeem zit eigenlijk in de tabel ExceptionOutput. Elk record uit een productiedatabase heeft een waarde die uniek is voor dat record. Deze KeyValue, zoals ik die unieke waarde benoemd heb, kan bijvoorbeeld zijn: het OrderNr, het RelatieNr, het MutatieNr of iets dergelijks. In het geval van een aggregatie, bijvoorbeeld de totale weekomzet van een verkoper, neem je voor de KeyValue het MedewerkersNr.

De “magie” van het hele systeem zit eigenlijk in de tabel ExceptionOutput

Een ander belangrijk veld is het veld TextValue. In het TextValue-veld wordt de inhoud van het KeyValue-veld weergegeven, evt. aangevuld met andere gegevens.

Voor de toelichting op de functie van de overige velden verwijs ik naar de tabellen 1a, 1b en 1c.

Tabel 1a: Tabeldefinitie van ExceptionMain

Tabel 1b: Tabeldefinitie van ExceptionOutput

Tabel 1c: Tabeldefinitie van ExceptionProcess

In listing 1 is een voorbeeld opgenomen van een check op de aanwezigheid van niet-numerieke huisnummers in de Contact-tabel. In de frontend applicatie mogen niet-numerieke huisnummers worden ingevoerd. Echter in een andere applicatie worden de huisnummers gecast naar een integer en wordt er een fout gegenereerd als er een alfa-numeriek karakter voorkomt in het huisnummerveld. Door deze toekomstige fout nu tijdig te rapporteren kan de productieafdeling deze invoer herstellen en zal het proces van de 2^e applicatie doorlopen kunnen worden zonder fouten.

INSERT INTO
  [SVR-DWHDB01\DWHPROD].[StagingArea].[dbo].
    [ExceptionOutput]
  (
    CreationDate,
    ExceptID,
    KeyValue,
    Amount,
    Quantity,
    TextValue
  )
  SELECT DISTINCT
    GETDATE() AS creationdate,
    25 AS ExceptID,
    c.no_ AS KeyValue,
    0 AS Amount,
    1 AS Quantity,
    'niet numeriek huisnummer: ' + [House No_]
      AS TextValue
  FROM [SVR-DBYES01].[Yesterday].[dbo].[VEH$Contact] c
  WHERE ISNUMERIC(ISNULL([House No_], 0)) = 0
    AND LEN([House No_]) > 0

Listing 1: Voorbeeld van een exception-systeem SQL-statement

Indien de fout veelvuldig voorkomt en/of erg storend is voor de business, zal er uiteraard een bugfix moeten komen voor de applicatie’s. Maar ook het bugfixproces heeft een bepaalde doorlooptijd en tot het zover is, zal het uitzonderingsrapportage-systeem zijn diensten bewijzen.

Maar ook het bugfixproces heeft een bepaalde doorlooptijd en tot het zover is, zal het uitzonderingsrapportage-systeem zijn diensten bewijzen.

Integration Services

Het past niet in het kader van het artikel om een volledige beschrijving van het SSIS-deel van het US te geven. Ik wil hier volstaan met een afbeelding (fig 3.) van het SSIS-package. Uiteraard is een en ander ook te schrijven in een SQL-batch.

Fig 3: Het exception-package in SSIS

Reporting Services

De functionaliteit die gemaakt is binnen SSRS valt uiteen in twee delen: de layout van het uitzonderingsrapport en de scheduling met DataDrivenSubscription.

De layout

Voor de layout van het uitzonderingsrapport binnen SSRS heb ik de volgende keuze gemaakt (zie figuur 4). Uiteraard is de layout afhankelijk van het doel en publiek van het rapport. In dit voorbeeld gaat het om een uitzonderingsrapport dat wordt getoond in de browser.

Fig 4. Voorbeeldrapportage in SSRS

De DataDrivenSubscription

In listing 2 is het SQL-statement opgenomen om de e-mailadressen te selecteren van medewerkers die een rapport gaan ontvangen. Let op dat alleen een mailtje wordt gestuurd als er informatie te versturen is. Immers, als er geen records aanwezig zijn voor de medewerker, via de ProcessID-JOIN, worden er voor die medewerker ook geen mailtjes verstuurd.

SELECT ExceptionProcess.EMail, ExceptionMain.ExceptID
FROM ExceptionMain
  INNER JOIN ExceptionOutput ON
    ExceptionMain.ExceptID = ExceptionOutput.ExceptID
  INNER JOIN ExceptionProcess ON
    ExceptionMain.ProcessID = ExceptionProcess.ProcessID
  WHERE (CAST(CONVERT(varchar(10),
      ExceptionOutput.CreationDate, 102) AS datetime) =
    CAST(CONVERT(varchar(10), GETDATE(), 102) AS
      datetime))
  GROUP BY ExceptionProcess.EMail, ExceptionMain.ExceptID

Listing 2: Het SELECT-statement voor de DataDrivenSubscription in SSRS

Conclusie

Met relatief eenvoudige structuren is een heel krachtige uitzonderingsrapportage tool te bouwen die flexibel inzetbaar is. Met gebruikmaking van de standaard componenten van SQL2005 is hele systeem binnen een dag in de lucht.

Het omvormen van operationele gegevens naar de fingerprint van het exception-systeem (CreationDate, ExceptID, KeyValue, Amount, Quantity, TextValue) is de sleutel tot het generiek vastleggen en rapporten van gegevens uit diverse operationele databases.