Integration Testing

After unit testing is complete; it should form the basis of starting integration testing. Integration testing should

Test out initial and incremental loading of the data warehouse.

Integration testing will involve following

1. Sequence of ETL jobs in batch.
2. Initial loading of records on data warehouse.
3. Incremental loading of records at a later date to verify the newly inserted or updated data.
4. Testing the rejected records that don’t fulfill transformation rules.
5. Error log generation.

Integration Testing would cover End-to-End Testing for DWH. The coverage of the tests would include the below:

Count Validation

Record Count Verification: DWH backend/Reporting queries against source and target as an initial check.

Control totals: To ensure accuracy in data entry and processing, control totals can be compared by the system with manually entered or otherwise calculated control totals using the data fields such as quantities, line items, documents, or dollars, or simple record counts

Hash totals: This is a technique for improving data accuracy, whereby totals are obtained on identifier fields (i.e., fields for which it would logically be meaningless to construct a total), such as account number, social security number, part number, or employee number. These totals have no significance other than for internal system control purposes.

Limit checks: The program tests specified data fields against defined high or low value limits (e.g., quantities or dollars) for acceptability before further processing.

Dimensional Analysis

Data integrity between the various source tables and relationships.

Statistical Analysis

Validation for various calculations.

• When you validate the calculations you don’t require loading the entire rows into target and Validating it.
• Instead you use the Enable Test Load feature available in Informatica Power Center.

Property	Description
Enable Test Load	You can configure the Integration Service to perform a test load. With a test load, the Integration Service reads and transforms data without writing to targets. The Integration Service generates all session files, and performs all pre- and post-session Functions, as if running the full session. The Integration Service writes data to relational targets, but rolls back the data when the session completes. For all other target types, such as flat file and SAP BW, the Integration Service does not write data to the targets. Enter the number of source rows you want to test in the Number of Rows to Test field. You cannot perform a test load on sessions using XML sources. You can perform a test load for relational targets when you configure a session for normal Mode. If you configure the session for bulk mode, the session fails.
Number of Rows to Test	Enter the number of source rows you want the Integration Service to test load. The Integration Service reads the number you configure for the test load.

Data Quality Validation

Check for missing data, negatives and consistency. Field-by-Field data verification can be done to check the consistency of source and target data.

Overflow checks: This is a limit check based on the capacity of a data field or data file area to accept data. This programming technique can be used to detect the truncation of a financial or quantity data field value after computation (e.g., addition, multiplication, and division). Usually, the first digit is the one lost.

Format checks: These are used to determine that data are entered in the proper mode, as numeric or alphabetical characters, within designated fields of information. The proper mode in each case depends on the data field definition.

Sign test: This is a test for a numeric data field containing a designation of an algebraic sign, + or - , which can be used to denote, for example, debits or credits for financial data fields.

Size test: This test can be used to test the full size of the data field. For example, a social security number in the United States should have nine digits

Granularity

Validate at the lowest granular level possible

Other validations

Audit Trails, Transaction Logs, Error Logs and Validity checks.

Note: Based on your project and business needs you might have additional testing requirements.