Finacle Core and ODS Integration Data warehouse ETL Testing Process

1.    Introduction

In banking environment every day customer, product and transection added from different business sources which create a lot of data that are stored in backend database.

To transform and store that data to a centralized database so that discussion making, information gathering can be easy done through data warehouse.

Fincale introduces ODS (Operational Data Store) to achieve the above thing by using ETLs.

By ETLs data from Finacle core is transferred to ODS database at End of particular day by running batch.

2.    Why Database testing and Data warehouse needed?

Database testing is test data which we are entering from frontend of an application whether same data is populating at backend table properly or not .To check the data base integrity with frontend we need this testing . Typical tests involved in Database Testing:

·         Data Validation

·         Data base Upgrade Test

·         Database Migration Test

·         Data Security

·         Data performance Test

·         Data Restore

Always we need to perform database testing after that we can start with data warehouse testing which reduces the testing time and issues can be found in the beginning of the stage.

Data warehouse testing is more appropriate approach to testing database as it involves more complex business logic for data transformation, translation on data from multiple sources. Data warehouse applications are typically based on principle of ETL (Extract Transform and Load). It deals with Mapping, Transformation and aggregation requirements. Typical steps involved in Data warehouse Testing:

·         Staging job testing

·         Homogenization  job testing

·         Load job testing

3.    Data warehouse  at FINACLE ODS Integration Overview

In above pictorial representation shows how data passes form source to target by ETL jobs.

ETL is an acronym that stands for ‘Extract – Transform – Load’.

·         Extraction of data from one or more sources.

·         Transforming [e.g. cleansing, reformatting, standardization, aggregation, or the application of any   number of business rules] it to business needs.

·         Loading of the resulting data set into specified target systems or file formats.

While creating ETL jobs in Finacle and ODS integration project at target database we have static tables and dynamic tables.

Static Tables are in ODS:

·         BATCH_DETAIL_TABLE

·         JOB_DETAIL

·         PARAMETER_DETAIL

·         MD_ENTITY_DETAIL

·         ERROR_TYPE

All these table data are set while creating project for bank according to the business requirement which is constant throughout the project.

Dynamic Tables are in ODS:

·         BATCH_RUN_DETAIL

·         JOB_RUN_DETAIL

·         ALL TARGET TABLES

These are the tables where data value can be Insert, Update, and Modified also can be deleted as required by business functionality.

<1>STAGING JOB:

In this step Data from source is transfer to staging table by running the ETL. Here all the data transferred as per source view also parameter set data get loaded with data after which job id, batch id created for that ETL.

<2> HOMOGENIZATION JOB:

Here Data or table form different source is getting transformed as per natural keys, that creates temporary homogenization table which is get deleted after whole batch execution occur successfully.

Also transform occurred from source column to target column but datatype transformation not occur. (P.N.: Nvarchar2 (38) will change Varchar(38) which  because of datastage  not because of transformation).

<3>LOADING JOB:

It will check whether temporary homogenization table having data or not, depending upon that it will proceed with other process. If there is no data will be there then job will get aborted.

If data will be there then it will be execute LOOKUP JOB followed by ERRCHECK.

After that LOAD Process execute .So that Data can be moved target.

If there any of the steps will not work may be functional issue or network issue the job will get aborted in particular step status, for which reason we can find in log of particular step.

How each ETL job runs in Bank?

For each bank one project created where all ETL’s staging,homogenization and load jobs are clubbed into one ETL so that while we run that ETL all etls getting executed parallel sequence .

4.    Data warehouse  Testing process at FINACLE ODS Integration

Types of data warehousing testing:

Checklist while performing ETL testing for (table to table testing for Source and target)

1.    Row count of Source & target should match before & after loading.

2.    Identification of source and target columns, their primary keys, foreign keys, and other check constraints.

3.    Hash count on unique key / primary key should match before & after loading.

4.    Source query minus target query should be zero for all the straight move columns.

5.    If columns are of not straight move then check the transformation logic

And test accordingly.

6.    Follow data sampling technique for SCD type-2 columns

7.    Error Type Testing should be done for each natural key and RI columns.

5.    Commonly faced issues For which ETL may fail

·         Due to incorrect source directory path or connection details/source file not available

·         Incorrect table name or  mismatch in the number of table columns compared to that in mapping or vice versa

·         Cache issues

·         Insufficient table space may also lead to the job failure

·         Due to unique, primary, foreign , null constraints violation

·         When the number of rows rejected are more than the threshold count defined

·         SQL/database table error

8. Conclusion

From above we can come to know what the importance of Data warehousing testing is and where and how it is perform at Finacle product. Also what are common failed issues faced while ETL testing.

Posted in: ETL Testing