This example was reviewed at the February meeting, comments incorporated, and 'signed off' as a completed example at the meeting in April 2003.

0       Configuration

Matrix reference: Conversion test for a database system

Author: Isabel Evans     Version 0.3      April 2003

Replaces v0.2 February 2003, from comments received at meeting April 2003 and via web site

1       Introduction

Two financial service providers (FSP1 and FSP2) have merged and, as part of that merger, management and servicing of customer accounts for a particular investment type is to be moved from FSP1 to FSP2.   As a result, a data migration project is needed: the data held in the existing IT systems of FSP1 about those accounts must be extracted, and loaded onto the existing IT systems at FSP2.  Some data items are held in a different format in the two IT systems, and this data must be converted from one format to the other during the data migration.  This includes the data held on the customer database used to provide information to Marketing Planning.

1.1   Scope and limitations of this example:

This example only covers field to field data conversion. This is simple example with a database that only has 5 fields.  In a larger data conversion, the tester would need to make a risk assessment about whether to cover all or some of the fields and would concentrate testing in the high-risk areas.  The example is based on typical data conversion problems encountered in real projects, but have been much simplified.

Note for this version: further examples and the guidelines will illustrate other aspects of conversion testing not covered in this example.

During a data migration, many activities (IT and non-IT) are carried out, and both the IT and non-IT activities will need to be tested or rehearsed leading up to the live migration.  Types of testing that might be required include:

§          Performance testing of the speed of the extract, conversion and load processes

§          Procedure testing of the manual processes around the migration and their intersection with the IT processes

§          Security testing of the security processes around the migration, for example data protection, security of data from corruption during transfer, security of data from illegal access during transfer

§          Functional testing of control processes built to support the migration for example conversion control reporting

§          Functional testing of the extract and load processes

§          Additionally during a data conversion or data migration, the conversion testing itself has several aspects, which will not be covered in this example, but will be covered in other examples or in the guidelines.  As these have not yet been written, notes are added at the end of this example.

2        Requirements/Specification of System under Test

2.1   Customer Accounts Marketing Database (CAMD):

The FSP1 CAMD (the source system) holds customer information, which is to be transferred to the FSP2 CAMD (the target system).  The format of the two databases is similar but not identical. Some of the fields are outlined in the table below.

The data migration specification states that:

§          During the data migration, if a field value cannot be converted, a line will be written to the Conversion Reject Report, and the field value in the converted data will be set to a standard value, to be updated post migration.

§          If a field value can be converted, it will be loaded as the converted value and a line written to the Conversion Load Report.

§          Under certain circumstances a whole record will be rejected and then the record will be written to the Rejection File, and a line written to the Conversion Reject Report.

§          The data migration specification states that if the age value is not within one of the bands, it will be set to D in the target system and a line written to the Conversion Reject Report. (add to spec at front – and for other bits of spec)

§          The data migration specification states that as FSP1 has only traded within the UK , all target records will have a Country value of “ UK ” added to them. There is no need to test other country values, as the source data has no country field. The data cannot be partitioned. Put in spec.

§          The data migration specification states that the definitions for the sources and target fields is the same for the Surname field, so the surnames should be transferred without any change. The data cannot be partitioned.

§          If the gender in the source system is set to M it will be converted and loaded as M and a line written to the Conversion Load Report

§          If the gender in the source system is set to F it will be converted and loaded as F and a line written to the Conversion Load Report

§          If the gender in the source system is set to C this is a corporate customer not an individual and the details must not be loaded to the target Marketing system (because it will be converted to another system which holds corporate customers). It will be loaded to the Rejection File and a line will be written to the Conversion Reject Report.

§          If the gender in the source system is set to any other value, including Null, it will be loaded as M, and a line written to the Conversion Reject Report.

3       Test Design

Definition of the test case design technique and how to apply it.

3.1   Data conversion testing stage 1: review of field formats and uses.

Software life cycle stage: part of the requirements and design stages for the data migration

Testing methods: review / inspection, static analysis (supported by tools) for example data audit tools that  check relationships between data.

Purpose of the test: check that the data conversion design correct, identify records in the source system for data cleaning (e.g. adding information mandatory in the target database)

Description of the tests:

1.        Use static test methods (e.g. code inspection, static analysis tools) for the following:

§          Check that the field descriptions in the specification match the actual definitions in the code

§          Identify which fields require conversion, and which transfer with no change in value or format – an example of a field where the value is transferred unchanged is Surname

§          Identify which of the source database fields require a data conversion during the migration – an example is the Age field, which in the target database is replaced by the Age-band field. Analysis of the two fields reveals that in the source system the field may be empty (null) or hold a value between 0 and 120 (an age) or have incorrect values in some cases. The target field is defined as a 1 character alphabetic field.

§          Identify where the target database may require changes to accommodate source data – an example is the Title field. Error guessing and knowledge of the source system / real life tells us that it is possible for titles to be longer that 5 characters. Analysis of the title field in the source database reveals a range of titles such as “Major General” which is 13 characters including the space.

2.        In the specification review, check that the data conversion design correctly describes these data transfers, conversions and changes.

3.2   Data conversion testing stage 2: design of dynamic tests

Software life cycle stage: part of the dynamic testing stages for the data migration

Testing methods: dynamic running of the conversion, using test data designed using techniques from BS7925-2:, equivalence partitioning, random testing, syntax testing , boundary value analysis, plus error guessing.

Purpose of the test: check that the data conversion design is correct and that it has been correctly implemented.

4        Actual Test Cases

4.1   Equivalence class partitioning and boundary value analysis

The data migration specification states that if the age value is not within one of the bands, it will be set to D in the target system and a line written to the Conversion Reject Report.

Tests are drawn up as per BS 7925-2, with the following partitions and boundaries identified:

For Age:

§          Input partitions: 0-17, 18-64, 65 – maximum

§          Output partitions: A, B, C, D

§          Boundaries: 0, 18, 65, maximum age (check value)

§          Invalid input partitions: non-numeric, null, less than 0, above maximum age (not specified – need to check)

§          Invalid output partitions: E, null, numeric (not specified, based on tester’s perceptions of what might go wrong, this list may vary, as different testers may choose different examples)

For Customer Surname:

§          Boundaries: 1 character surname, 30 character surname

Note: These test cases could be minimised by testing more partitions in one test case; for example, test case 1 and test case 6 could be combined.

4.2   Random testing

The data migration specification states that as FSP1 has only traded within the UK , all target records will have a Country value of “ UK ” added to them. There is no need to test other country values, as the source data has no country field. The data cannot be partitioned.  For this field, all data records are going to be treated in the same way. To test that the loaded records on the target database are set correctly to Country = UK , test data will be derived in two ways:

1)        All tests derived by other techniques will have a check that the country field has been correctly updated to UK as part of the conversion and load.

2)       A database of test data will be generated which reflects the operational profile of the live source database. A random selection of test source records will be converted and loaded, and the Country field checked to see that the value has been correctly updated to “ UK ”. The selection will be made by a random number generator, selecting by record number.

4.3   Syntax testing plus Error Guessing