dwhs
TRANSCRIPT
8/13/2019 DWHS
http://slidepdf.com/reader/full/dwhs 1/13
Testing a Business Datawarehouse – an industrial challenge
Harry M. Sneed
ANECON GmbH
Vienna, Austria August, 2006
8/13/2019 DWHS
http://slidepdf.com/reader/full/dwhs 2/13
Testing a Business Datawarehouse
Project Background• The Raiffeisenbank, Austria‘s largest financial institution has
acquired 16 daughter banks throughout Eastern Europe.
• According to the Basel banking convention, banks are required tomake regular reports on the credit status of their customer loans.
• To accomplish that, the bank in Vienna has designed a financial
datawarehouse for answering inqueries and generating requiredreports.
• The datawarehouse has to be fed data by the eastern Europeandaughter banks.
• 132 files were sent to Vienna monthly to be loaded into thewarehouse database.
• The goal of this particular project was to map the incoming data ontothe datawarehouse data according to the rules written by thebusiness analysts.
• The goal of the test project was to ensure that the data was mappedcorrectly.
• There were 5 managers, 3 analysts, 26 developers & 8 testers.
8/13/2019 DWHS
http://slidepdf.com/reader/full/dwhs 3/13
Datawarehouse Test Process
Original
Rule
Specs
Reformulating
& Enhancing
of the rules
Formal
Rule
Specs
Assertion
Generator
Tester
formalizes
the rules
Tester
sets up
Test data
Source
Data
System
under
Test
Tester
executes
test
Target
data
DataTest
Test Result
Validator
Assertion
Procedures
Testerchecks
results
ExceptionReport
Tester
reports
errors
Error
Reports
Test Casesassign old.Attributif<condition>
in semi-formaler
prose
Written by the
bank analyst
1 per DB-Entity
If (new.key=old.key)
assert new.Attribut=old.Attribut if (<condition>);
assert new.Attribut=old.Attribut + wert*wert;
8/13/2019 DWHS
http://slidepdf.com/reader/full/dwhs 4/13
Sample 1: An extract from the rule table before the rule has been converted
DR_INTEREST_ID;"Link to TB0_ACCOUNT_INTEREST.
Debit interest conditions applicable to the account.";
If REIACD in field DICT(debit) has a value other than 0,
account is linked to interest group.
The following then applies: REINTD / KEY (Position 3-4) (Interest Type) 2 Alpha
REINTD / KEY (Position 5-9) (Interest Subtype) 5 AlphaREINTD / KEY (Position 10-12)(Currency) 3 Alphas
The above Key fields are concatenated in ID.
If in REIACD the DICT values are zeroes,
the account interest condition has to be extracted from the ACCNTAB:
ACCNTAB / DRIB (debit Base Rate Code) ACCNTAB / DRIS (debit Spread Rate)
If both of <> 0 value, extract ACCOUNT_IDIf ACCNTAB / DRIS is available (<> 0),
extract the same as for ACCOUNT_ID
If only DRIB of <> value, extract DRIB
8/13/2019 DWHS
http://slidepdf.com/reader/full/dwhs 5/13
Sample 2: An extract from the rule table after the rule has been converted
DR_INTEREST_ID;"Link to TB0_ACCOUNT_INTEREST.
Debit interest conditions applicable to the account.";
" ? assign REIACD/DICT | REIACD/DCST | ACCNTAB/CCY | 'D'
if REIACD/DICT (debit) <> '0',
assign ACCNTAB/CONCAT
if ACCNTAB/DRIS <> '0',
assign ACCNTAB/CCY|ACCNTAB/DRIB
if ACCNTAB/DRIB <> '0',
assign '*nomap*'
if REIACD/DICT = '00' and ACCNTAB/DRIS = '0' and ACCNTAB/DRIB = '00'
assign ACCNTAB/CNUM|CCY|ACNO|BRCAif other.
(18-digit account Id made up of CNUM length 6,
leading zeros length 4, leading zeros +ACSQ length 2,
leading zeros +BRCA concatenated).";
8/13/2019 DWHS
http://slidepdf.com/reader/full/dwhs 6/13
Sample 3: A generated assertion procedure
file: ACCOUNT;
// This comparison procedure assumes that the old ACCOUNT file contains the following attributes:
// from ST_ACCOUNT Table ,ACCOUNT_ID;ATYP;STYP;RETB;CRIB;CRIS;DRIB,DRIS;PRFC;OD;FACT;FCNO// from REIACD Table REIACD.CICT;REIACD.DICT;REIACD.DACP;REIACD.CACP
// from FCLTYFM Table FCLTYFM.RVDT
if ( new.ACCOUNT_ID = old.concat_acct_id );
assert new.GROUP_ACCOUNT_TYPE_ID = old.atyp if (old.atyp = "G");
assert new.GROUP_ACCOUNT_TYPE_ID = "S" if (old.atyp = "R" & old.styp = "S");
assert new.GROUP_ACCOUNT_TYPE_ID = "C" if (old.atyp = "R" & old.styp = "C");
assert new.GROUP_ACCOUNT_TYPE_ID = "L" if (old.atyp = "R" & old.styp = "L");
assert new.STATUS_BLOCKED = "1" if (old.retb = X"04");
assert new.STATUS_BLOCKED = "2" if (old.retb = X"02");assert new.STATUS_BLOCKED = "3" if (old.retb = X"06");
assert new.STATUS_BLOCKED = "*n.a.*" if (other);
assert new.CR_INTEREST_ID = old.crib if (old.cict = "0");
assert new.DR_INTEREST_ID = old.drib;
assert new.DR_INTEREST_LIQU_ACCT = old.dacp;
assert new.CR_INTEREST_LIQU_ACCT = old.cacp;
assert new.PROFIT_CENTRE_DESCRIPTION = old.prfc;
assert new.OVERDRAFT_START_DATE = "2005.05.15" if (old.od <> "0");
assert new.OVERDRAFT_REVIEW_DATE = old.rvdt if (old.fact <> "0" & old.fcno <> "0") ;assert new.OVERDRAFT_REVIEW_DATE = "NULL" if (old.fact = "0") ;
assert new.PRIMARY_ACCOUNT_INDICATOR = "P" ! "S" ! "*n.a.*";
assert new.STATUS_INDICATOR = "inactiv" if (old.retb = X"3") ;
assert new.STATUS_INDICATOR = "inactiv" if (old.retb = X"1") ;
assert new.STATUS_INDICATOR = "closed" if (old.reci = "C") ;
assert new.STATUS_INDICATOR = "active" if (other);
end;
8/13/2019 DWHS
http://slidepdf.com/reader/full/dwhs 7/13
Sample 4: A section from the assertion generation log
+-------------------------------------------------------------------+
|&New Assert Script: Asrt-011 = ST_ACCOUNTING_STANDARD
+-------------------------------------------------------------------+| New Attribute ACCOUNTING_STANDARD_CODE has no rule!
| New Attribute APPLICATION_ID has a rule that was not processable
| Rule = text file
| New Attribute QUALITY_INDICATOR has no rule!
| New Attribute SOURCE_SYSTEM_ID has a rule that was not processable
| Rule = text file+-------------------------------------------------------------------+
| Number of new Attributes = 013
| Number of Rules = 011
| Number of Basel Rules = 003
| Number of complex Rules = 005
| Number of complexBasel Rules = 002
| Number of generated Keys = 002
| Number of generated Asserts = 009
| Number of unrecognized Rules = 002
+-------------------------------------------------------------------+
8/13/2019 DWHS
http://slidepdf.com/reader/full/dwhs 8/13
Sample 5: The generation statistics+-------------------------------------------------------------------+
| Assertion Generation completed !
| Number of E/R Rule Lines processed = 11384
| Number of new Tables processed = 266
| Number of new Attributes processed = 10910| Number of old Tables processed = 132
| Number of old Attributes processed = 00891
| Number of new Attributes without a rule = 05573
| Number of Transformation Rules specified = 05337
| Number of Transformation Rules processed = 04742
| Number of Basel-II Rules recognized = 00890
| Number of Basel-II Rules processed = 00852| Number of Complex Rules recognized = 00853
| Number of Complex Rules processed = 00562
| Number of Complex Basel Rules recognized = 00157
+-------------------------------------------------------------------+
| Number of Assert Scripts generated = 00266
| Number of Assertions generated = 04897
| Number of Table Selectons generated = 00181
| Number of Assert Keys generated = 00023
| Number of Assert Conditions generated = 00308
| Number of Assert Concatenates generated = 00103
| Number of Assert Alternates generated = 00365
| Number of Assert Arithmetic generated = 00009
| Number of Test Cases generated = 05337+-------------------------------------------------------------------+
8/13/2019 DWHS
http://slidepdf.com/reader/full/dwhs 9/13
Sample 6: A generated select procedure for the incoming data
// SQL Select for Stage File: ST_ACCOUNT_INTEREST_RATESELECT
// SQL Select for MiDas File:REINTD
SELECT
ACCOUNT_ID,
KEY,
BAL2,FROM REINTD JOIN
// SQL Select for MiDas File:SDBANKPD
SELECT
BJRDNB,
FROM SDBANKPD JOIN
// SQL Select for MiDas File:ACCNTABSELECT
LCD,
FROM ACCNTAB JOIN
END SELECT;
// End of Select for: ST_ACCOUNT_INTEREST_RATE
8/13/2019 DWHS
http://slidepdf.com/reader/full/dwhs 10/13
Sample 7: A section of the data validation report+-------------------------------------------------------------------------------------------------------------------------------------+
| File/Table Comparison Report |
| Key Fields of Record(new,old) |
+-------------------------------------------------------------------------------------------------------------------------------------+
| New:ACCOUNT_ID |
| Old:concat_acct_id |+-----------------------------------------------------------------+------------------------------------------------------------------+
| RecKey:100000USD114601001 | duplicate key in old File/Table |
+-----------------------------------------------------------------+------------------------------------------------------------------+
| RecKey:100000ATS104501001 | |
| New: GROUP_ACCOUNT_TYPE_ID | G |
| Old: Constant_Value | L |
+-----------------------------------------------------------------+------------------------------------------------------------------+
| RecKey:100000ATS104501001 | |
| New: CR_INTEREST_ID | XXX00 || Old: crib | 0 |
+-----------------------------------------------------------------+------------------------------------------------------------------+
| RecKey:100000ATS104501001 | |
| New: DR_INTEREST_LIQU_ACCT | 0 |
| Old: dacp | 1 |
+-----------------------------------------------------------------+------------------------------------------------------------------+
| RecKey:100000ATS104501001 | |
| New: OVERDRAFT_START_DATE | NULL |
| Old: Constant_Value | 2005.05.15 |+-----------------------------------------------------------------+------------------------------------------------------------------+
| RecKey:100000ATS104501001 | |
| New: OVERDRAFT_REVIEW_DATE | 9999-08-01_00:00:00 |
| Old: Constant_Value | NULL |
+-----------------------------------------------------------------+------------------------------------------------------------------+
| RecKey:100000ATS104501001 | |
| New: APPLICATION_ID | GL |
| Old: Constant_Value | RE |
+-----------------------------------------------------------------+------------------------------------------------------------------+
8/13/2019 DWHS
http://slidepdf.com/reader/full/dwhs 11/13
Data Validation
TAIC
Assertion
Script
ASSERTION Assert
Table FILECOMP
TXTSCAN
COMPARE
Comparison
Report
XMLSCANCSVSCANCSV File XML File
Old
Data
NewData
From
SQL Table
From
XML Output
Text File
From Reports
8/13/2019 DWHS
http://slidepdf.com/reader/full/dwhs 12/13
Test Statistics
• The test project lasted only 2,5 months
• The author worked 216 hours on the project,developing two new tools, converting 562 rules
and writing some 30 pages of documentation• There were 266 DB tables with 10910 attributes
and 5337 rules to be tested
• By the end 88% of the rules had been verified
• Due to lack of time 12% of the rules were neverconverted
• 208 errors were reported by the testers
8/13/2019 DWHS
http://slidepdf.com/reader/full/dwhs 13/13
Summary
• The greatest problem in the IT-world is the lack ofeducation
• Business analysts are unable to write requirements in aformal language like OCL
• Austrian and German companies try to use English as arequirement language, but the English language skill ofthe employees is insufficient for complex texts
• Testers have no formal training in testing methods, theycan only perform routine tasks
• IT-Management is more often totally incapable, they rushinto projects with neither process nor tools.
• There is a tremendous need for more and bettereducation of IT personnel in industry.