process for etl tesing

55
1 ETL Informatica Testing ETL Informatica Testing By, Peter L Annis This Demo is based on ETL Informatica Testing and back end Testing

Upload: gullipalli

Post on 10-Nov-2014

367 views

Category:

Documents


0 download

DESCRIPTION

special process for ETL testinginformtica testing

TRANSCRIPT

Page 1: Process for ETL Tesing

1

ETL Informatica Testing

ETL Informatica Testing

By,

Peter L Annis

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

Page 2: Process for ETL Tesing

2

Content

ETL Informatica Testing

• 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

Page 3: Process for ETL Tesing

3

Source Database Configuration Details

ETL Informatica Testing

• 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

Page 4: Process for ETL Tesing

4

Start Run odbcad32

Source database

ETL Informatica Testing

Page 5: Process for ETL Tesing

5

Source database

ETL Informatica Testing

To create the Test Connection for Source DB

Page 6: Process for ETL Tesing

6

Source database

ETL Informatica Testing

Page 7: Process for ETL Tesing

7

Source database

ETL Informatica Testing

Test connection created Successfully

Page 8: Process for ETL Tesing

8

Source database

ETL Informatica Testing

In Data Source Administrator

• We can able to see the source database Name

Page 9: Process for ETL Tesing

9

Target Database

ETL Informatica Testing

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

Page 10: Process for ETL Tesing

10

Target Database

ETL Informatica Testing

Get the Target base connection details

We must know

• Server Name

• UserID

• Password

• Database Name

Page 11: Process for ETL Tesing

11

Target Database

ETL Informatica Testing

Target base connection is successful

We can able to see the

• Database and

• Target Tables also

Page 12: Process for ETL Tesing

12

Source Database

ETL Informatica Testing

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

Page 13: Process for ETL Tesing

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

Page 14: Process for ETL Tesing

14

Source Database

ETL Informatica Testing c

Enter the Login Details

UserName:xxxxx

Password: xxxxx

Database: xxxxx

After Successful Login

Page 15: Process for ETL Tesing

15

Source Database

ETL Informatica Testing

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

Page 16: Process for ETL Tesing

16

Table information for Source

ETL Informatica Testing

Check the Data is available in the Source Table

Page 17: Process for ETL Tesing

17

Table information for Target

ETL Informatica Testing

• Check the whether Target Table has the Data

Page 18: Process for ETL Tesing

18

Common ETL Testing Procedure

Page 19: Process for ETL Tesing

19

ETL Testing Process

ETL Informatica Testing

• 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

Page 20: Process for ETL Tesing

20

ETL Testing Scope general

ETL Informatica Testing

• 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

Page 21: Process for ETL Tesing

21

ETL Testing Procedure

ETL Informatica Testing

• 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

Page 22: Process for ETL Tesing

22

ETL Testing Procedure

ETL Informatica Testing

• 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

Page 23: Process for ETL Tesing

23

Data warehousing Architecture

Page 24: Process for ETL Tesing

24

Data Warehousing

ETL Informatica Testing

Page 25: Process for ETL Tesing

25

Data Warehouse Model

ETL Informatica Testing

Developer

Metadata

End User

Operational

Sources

Data Warehous

e

Extract Transform

Load

Page 26: Process for ETL Tesing

26

Uses of Informatica in Data warehousing

ETL Informatica Testing

Page 27: Process for ETL Tesing

27

Power Centre Architecture

ETL Informatica Testing

Page 28: Process for ETL Tesing

28

Informatica Power center Details

ETL Informatica Testing

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

Power center 7.x

Page 29: Process for ETL Tesing

29

Informatica Configuration Details

ETL Informatica Testing

Page 30: Process for ETL Tesing

30

Repository Details

ETL Informatica Testing

Creating the Repository Details

• Enter the Repository Name : Test/Stage/Dev

• User Name : xxxx

Page 31: Process for ETL Tesing

31

Repository Details

ETL Informatica Testing

• Enter the Repository Name : Test/Stage/Dev

• User Name : xxxx

• Password :xxxxx

Repository Details

Host Name :

Port Number :

Try to Connect

Page 32: Process for ETL Tesing

32

Workflow Monitor

ETL Informatica Testing

Page 33: Process for ETL Tesing

33

Workflow Monitor

ETL Informatica Testing

• Right Click Test Repository Folder

• Enter Username : XXXX

• Password : XXXX

• Click on Connect

Page 34: Process for ETL Tesing

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

Page 35: Process for ETL Tesing

35

Workflow Details

ETL Informatica Testing

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

Page 36: Process for ETL Tesing

36

Workflow Details

ETL Informatica Testing

• 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

Page 37: Process for ETL Tesing

37

Time Stamp Validation

ETL Informatica Testing

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

Page 38: Process for ETL Tesing

38

Record Count Validation

ETL Informatica Testing

• Target table Count is 663

Rules should be followed while taking source count alsoMicrosoft Excel

Worksheet

Page 39: Process for ETL Tesing

39

Record Count Validation

ETL Informatica Testing

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

• Right now target count also matching 663

Page 40: Process for ETL Tesing

40

Data validation

ETL Informatica Testing

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

Page 41: Process for ETL Tesing

41

Data Type validation

ETL Informatica Testing

Data type verification for both Source and Target table

Page 42: Process for ETL Tesing

42

Data Type validation

ETL Informatica Testing

Page 43: Process for ETL Tesing

43

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

Data Type validation

ETL Informatica Testing

• Compare with ETL Mapping Document for Source

Page 44: Process for ETL Tesing

44

Data type Verification

ETL Informatica Testing

• 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

Page 45: Process for ETL Tesing

45

Data Validation

ETL Informatica Testing

Source Table

Page 46: Process for ETL Tesing

46

Data Validation

ETL Informatica Testing

• Export the Source table data and

• Save the file as “Source.Xls”

Page 47: Process for ETL Tesing

47

Data Validation

ETL Informatica Testing

Page 48: Process for ETL Tesing

48

Data Validation

ETL Informatica Testing

• Open New Excel Sheet Paste

• Save ”Target.Xls”

Page 49: Process for ETL Tesing

49

Data Comparison Using Beyond Compare Tool

Beyond Compare Tools

Page 50: Process for ETL Tesing

50

Data Compare for Source & Target table

ETL Informatica Testing

Page 51: Process for ETL Tesing

51

ETL Informatica Testing

• Click Data Compare

• Browse the Source and Target File.xls

• Click on “Open as New”

Page 52: Process for ETL Tesing

52

Data compare using Tool

ETL Informatica Testing

Page 53: Process for ETL Tesing

53

Data compare using Tool

ETL Informatica Testing

There is no-mis matched data is available

Page 54: Process for ETL Tesing

54

Overview

ETL Informatica Testing

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

Page 55: Process for ETL Tesing

55

GOOD LUCK For ETL TESTING

ETL Informatica Testing