1 / 13

Testing a Business Datawarehouse – an industrial challenge

Testing a Business Datawarehouse – an industrial challenge. Harry M. Sneed ANECON GmbH Vienna, Austria August, 2006. Testing a Business Datawarehouse Project Background. The Raiffeisenbank, Austria‘s largest financial institution has acquired 16 daughter banks throughout Eastern Europe.

chloe
Télécharger la présentation

Testing a Business Datawarehouse – an industrial challenge

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Testing a Business Datawarehouse – an industrial challenge Harry M. Sneed ANECON GmbH Vienna, Austria August, 2006

  2. 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 to make 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 required reports. • The datawarehouse has to be fed data by the eastern European daughter banks. • 132 files were sent to Vienna monthly to be loaded into the warehouse database. • The goal of this particular project was to map the incoming data onto the datawarehouse data according to the rules written by the business analysts. • The goal of the test project was to ensure that the data was mapped correctly. • There were 5 managers, 3 analysts, 26 developers & 8 testers.

  3. Datawarehouse Test Process Tester executes test Original Rule Specs Written by the bank analyst Tester sets up Test data Reformulating & Enhancing of the rules System under Test Tester formalizes the rules Source Data Assertion Procedures DataTest Test Result Validator Formal Rule Specs Assertion Generator Target data 1 per DB-Entity Exception Report Tester checks results assign old.Attribut if<condition> in semi-formaler prose Test Cases Tester reports errors If (new.key=old.key) assert new.Attribut=old.Attribut if (<condition>); assert new.Attribut=old.Attribut + wert*wert; Error Reports

  4. 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 Alpha REINTD / 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_ID If ACCNTAB / DRIS is available (<> 0), extract the same as for ACCOUNT_ID If only DRIB of <> value, extract DRIB

  5. 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|BRCA if other. (18-digit account Id made up of CNUM length 6, leading zeros length 4, leading zeros +ACSQ length 2, leading zeros +BRCA concatenated).";

  6. 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;

  7. 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. 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 +-------------------------------------------------------------------+

  9. Sample 6: A generated select procedure for the incoming data // SQL Select for Stage File: ST_ACCOUNT_INTEREST_RATE SELECT // 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:ACCNTAB SELECT LCD, FROM ACCNTAB JOIN END SELECT; // End of Select for: ST_ACCOUNT_INTEREST_RATE

  10. 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 | +-----------------------------------------------------------------+------------------------------------------------------------------+

  11. TAIC Data Validation Assertion Script Assert Table From Reports ASSERTION FILECOMP Text File From SQL Table From XML Output CSVSCAN XMLSCAN TXTSCAN CSV File XML File COMPARE Old Data New Data Comparison Report

  12. 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 never converted • 208 errors were reported by the testers

  13. Summary • The greatest problem in the IT-world is the lack of education • Business analysts are unable to write requirements in a formal language like OCL • Austrian and German companies try to use English as a requirement language, but the English language skill of the employees is insufficient for complex texts • Testers have no formal training in testing methods, they can only perform routine tasks • IT-Management is more often totally incapable, they rush into projects with neither process nor tools. • There is a tremendous need for more and better education of IT personnel in industry.

More Related