process for etl tesing

1

ETL Informatica Testing


By,

Peter L Annis

• This Demo is based on • ETL Informatica Testing and back end Testing

2

Content


• Set up the Test Environment Ready as per as per following details

• Source database configuration details • Target database configuration details • ETL Informatica details and which Version has to be used• Complete understanding of Functional requirement System

(FRS)• ETL Testing Procedure • Data Validation using Beyond Compare Tool• CDC Functionality is required of the current Project

Note : Depends up on client need

3

Source Database Configuration Details


• Install Oracle 9i Software after successfully installed • Add the TNS Names” tnsnames.ora” file as per below path • “C:\ORACLE\ora92\network\ADMIN\”• ODBC Configuration Details for Source database

4

Start Run odbcad32

Source database


5

Source database


To create the Test Connection for Source DB

6

Source database


7

Source database


Test connection created Successfully

8

Source database


In Data Source Administrator

• We can able to see the source database Name

9

Target Database


Our Target base will be SQL Server 2008

We can get trial version

Try to download from the below:- http://www.sqldbtools.com/Downloads.aspx?ProductId=2

Or

From Microsoft site

10

Target Database


Get the Target base connection details

We must know

• Server Name

• UserID

• Password

• Database Name

11

Target Database


Target base connection is successful

We can able to see the

• Database and

• Target Tables also

12

Source Database


Connecting to source

Database (Oracle)

But I’m using

PL/SQL Developer

We can install Toad or PL SQL Developer Tool these are GUI interface and user friendly

13

Source Database Tools

• Source Database Connection

We can use – Toad or – PL SQL Developer Tool

These are user-friendly GUI Interface

Free downloadable tools from web site

ETL Informatica Testing c

14

Source Database

ETL Informatica Testing c

Enter the Login Details

UserName:xxxxx

Password: xxxxx

Database: xxxxx

After Successful Login

15

Source Database


File -- > SQL Script --> New Query window will be opened

16

Table information for Source


Check the Data is available in the Source Table

17

Table information for Target


• Check the whether Target Table has the Data

18

Common ETL Testing Procedure

19

ETL Testing Process


• Common Testing Procedure/Process:-• Following things should be in place before test case design /

Execution– Requirements– Source to Target Column mapping,– Selection criteria ( Business Rules ) in Transformation

mappings – Look up condition– Source and Target Database– Flow of ETL

20

ETL Testing Scope general


• Verify the data and server refresh is done as per Design document

• Verify the data is available for Customer On time • Verify the ETL workflow is successfully completed • Verify the Session Log file is created and check the

updated Date timestamp.• Verify the ETL data’s control fields is populated in the

target table as per current ETL workflow• ROW_ADD_STP,• ROW_ADD_USER_ID,• ROW_UPDATE_STP, • ROW_UPDATE_USER_ID

21

ETL Testing Procedure


• Verify the row count of source and target table should be equal • Verify that the total row count in source and target row count

should be matched.• Verify the values of source column should be matched with

target value• Verify the Data Type & Size of each field existed in source and

Target Table as per Design Document• Verify the Data Integrity as per design document

– Entity integrity– Referential integrity

22

ETL Testing Procedure


• verify that the any Transformation logic rules have been implement between the Source to Target as per design document

• (i.e.) Aggregate • Sequence generator • Sorter • Router • Filter • Lookup

23

Data warehousing Architecture

24

Data Warehousing


25

Data Warehouse Model


Developer

Metadata

End User

Operational

Sources

Data Warehous

e

Extract Transform

Load

26

Uses of Informatica in Data warehousing


27

Power Centre Architecture


28

Informatica Power center Details


• Install the Informatica Power center 7.x• version no will be depends on the Client • After Successful initialization of Informatica

Power center 7.x

29

Informatica Configuration Details


30

Repository Details


Creating the Repository Details

• Enter the Repository Name : Test/Stage/Dev

• User Name : xxxx

31

Repository Details


• Enter the Repository Name : Test/Stage/Dev

• User Name : xxxx

• Password :xxxxx

Repository Details

Host Name :

Port Number :

Try to Connect

32

Workflow Monitor


33

Workflow Monitor


• Right Click Test Repository Folder

• Enter Username : XXXX

• Password : XXXX

• Click on Connect

34

Work Flow monitor

• Uses of Work flow monitor • Workflow monitor play’s important role in the ETL

Testing• To Check the whether ETL workflow is successfully

executed or failed • To verify the session log information• To Check the ETL run time for Date timestamp and Load

summary details for – Source and – Target Table data count

Suppose if the workflow is failed we can able figure out the failed reason

35

Workflow Details


Information need to get the session log • Folder Name : PRESRC_KIT_TEST• Worklet Name : wl_daily_Bo_trg_kit_Master• session Log : s_m_stg_trn_sql_KIT_SALES_PLANT.log

36

Workflow Details


• Check whether the ETL Run Timestamp and Target table Timestamp should be matched

• Source & Target Table Count in session load summary

• Check whether ETL Workflow Session is successfully completed or failed

Log File Checking

37

Time Stamp Validation


• Session Log and Target table data loading timestamp should be matched

38

Record Count Validation


• Target table Count is 663

Rules should be followed while taking source count alsoMicrosoft Excel

Worksheet

39

Record Count Validation


• Check whether the Target Table count is should be matched with the Source count

• Right now target count also matching 663

40

Data validation


As of now we had checked • Whether the ETL Work flow is succeeded or

failed • Log File Date time Stamp Verification • Session load summary details for source and

target table total no. record count• Source and Target table record count

41

Data Type validation


Data type verification for both Source and Target table

42



43

1. Desc <Table_Name> to view the Data type Details



• Compare with ETL Mapping Document for Source

44

Data type Verification


• Compare the Dataype details with the ETL Mapping document and Target Table

• Out Target Database is SQL Server 2008

• By using SP_HELP <Table_Name> command

• We can view the data type details

45

Data Validation


Source Table

46

Data Validation


• Export the Source table data and

• Save the file as “Source.Xls”

47

Data Validation


48

Data Validation


• Open New Excel Sheet Paste

• Save ”Target.Xls”

49

Data Comparison Using Beyond Compare Tool

Beyond Compare Tools

50

Data Compare for Source & Target table


51


• Click Data Compare

• Browse the Source and Target File.xls

• Click on “Open as New”

52

Data compare using Tool


53

Data compare using Tool


There is no-mis matched data is available

54

Overview


As of now we had checked • Whether the ETL Work flow is succeeded or failed • Log File Date time Stamp Verification • Session load summary details for source and target table total

no. record count• Source and Target table record count • Data type Compare with ETL Mapping Document and

Database level • Data validation for source and target table

55

GOOD LUCK For ETL TESTING


process for etl tesing

Documents