Oracle Business Intelligence Application Architect
☎ +91 9994883085
Don't miss

Sunday, 3 August 2014

Flat File Validation Using Informatica


By on 10:16:00


Summary:

The intention of this case study is to give an idea about validation of a flat file source using information in file trailor. File will contain total record count in trailor and same will be verified with actual record count received in file before loading to target. If file is found to be valid, loading will be done else a record will be inserted in status table indicating that file to be processes is invalid.

1. Objective

The intention of this case study is to give an idea about validation of a flat file source using information in file trailor. File will contain total record count in trailor and same will be verified with actual record count received in file before loading to target. If file is found to be valid, loading will be done else a record will be inserted in status table indicating that file to be processes is invalid.

Other scenarios:



2. Source Definition:

Sample flat file source is a comma separated values file having employee records. In addition file is having a header (starting with H) and a trailor (starting with T). Header record contains file sequence number and trailor record contains total number of records in file (including header and trailor record).

In sample file total records in file are 14 (including header and trailor). File trailor is “T14”.
Data records are having columns empno, ename, job, mgr, hiredate, sal, comm, and deptno.

3. Target Definition

3.1 Target T_FILE_PROCESS_STATUS
First target is relational table (oracle) T_FILE_PROCESS_STATUS and has below structure.



This table will be used to load file information when the file is not valid. In case of a valid file nothing will be loaded in this table. Example data in this table for invalid & valid file will be as shown below.



Process_Date
Header_SeqNo
Trailor_count
File_Record_count
Is_File_Valid
01/12/2010
1500
12
14
N
02/12/2010
1500
14
14
Y



3.2 Target EMPLOYEE
Second target is EMPLOYEE table with below structure.


4. Mappings

4.1 Mapping to validate the source file: m_ValidateSourceFile

Below shown is a prototype of the mapping to check if total record count of file matches with record count specified in trailor. If there is a mismatch then a status record will be inserted in T_FILE_PROCESS_STATUS. For a valid record count in trailor, no record will be inserted in target table.




4.1.1 Transformations used

a) exp_Header_Trailor:

This expression is used after Source Qualifier to identify file header and file trailor. In this expression total number of records in file is also calculated.




In expression define following variable ports and output ports.

Eg;

Input column; empno



Please see the below expression used in different Variable ports:


Port Name


Expression



v_HeaderSeqNo


IIF (SUBSTR (Empno, 1, 1) ='H', SUBSTR (Empno, 2), NULL)



v_TrailorRecCnt


IIF SUBSTR (Empno, 1, 1) ='T', SUBSTR (Empno, 2), NULL)



v_FileRecCnt


v_FileRecCnt + 1



O_ProcessDate


SYSDATE



Variable to extract header sequence number is v_HeaderSeqNo, variable to extract trailor record count is v_TrailorRecCnt and variable to count total number of records in file is v_FileRecCnt.

b) AGG_HEADER_TRAILOR_ROWCOUNT

Aggregator is used to make a single row for header sequence number, trailor record count and file record count.

Port Name
Datatype
Precision
Scale
I
O
V
Expression
Group By
O_ HeaderSeqNo

string
10
0
Y
Y



O_TrailorRecCnt

string
10
0
Y
Y



O_File_Rec_Cnt
interger
10
0
Y
Y



O_Prosess_date
Date/time
29
0
Y
Y


Y
O_File_header
interger
10
0

Y

Max(O_ HeaderSeqNo)


O_file_trailor
interger
10
0

Y

Max (O_TrailorRecCnt
)

O_File_rec_count
interger
10
0

Y

Max(O_File_Rec_Cnt)



Port o_ProcessDate is specified as Group By port.

C) exp_VALIDATE_ROCCOUNT

This expression is used to compare trailor record count with file record count.




Output port o_IsFileValid uses below expression to check if trailor record count matches with file record count. If there is a mismatch then o_IsFileValid flag is set to ‘N’.

Port Name
Expression


O_isFile_Valid

IIF (o_FileTrailor != o_FileRecCount,'N')



d) FIL_INVALID_FILE

This filter will pass the row only when o_IsValidFile flag is ‘N’ i.e. trailor record count does not match with file record count.
Filter condition is o_IsFileValid = 'N'.


4.2 Mapping to load source file: m_LoadFile

This is simple source to target mapping with a filter in place to skip file header and trailor.


4.2.1 Transformations used

FIL_HEADER_TRAILOR

Filter is used to skip file header and trailor. Only data records will be passed and loaded to target T_EMPLOYEE.

Filter condition used is SUBSTR (Empno, 1, 1)! = ‘H’ AND SUBSTR (Empno, 1, 1) != 'T'


5. Sessions

Session tasks will be created corresponding to each mapping. Session s_ValidateSourceFile is for mapping m_ValidateSourceFile.

Session s_LoadFile is for mapping m_LoadFile. As date in source file is in DD-MON-YY format, DateTime Format String is changed from default to DD-MON-YY. All other settings are default.


6.  Workflow (wf_LoadFile):

Simple workflow as shown below is created using a start task and two session tasks.

Condition to execute session wf_LoadFile is defined as below expression.

IIF ($s_ValidateSourceFile.TgtSuccessRows = 0,
$s_ValidateSourceFile.Status = succeeded,
$s_ValidateSourceFile.Status = failed)

With this condition is place, session s_LoadFile will execute only when no row is being inserted to target by session s_ValidateSourceFile. As per the mapping logic, m_ValidateSourceFile will insert the row in target only when file is failed for validation. So no row inserted in target means file is valid and should be loaded to EMPLOYEE table using m_LoadFile mapping.
Using sample flat file to execute this workflow will insert no row in T_FILE_PROCESS_STATUS. 12 rows will be inserted to EMPLOYEE table.

0 comments:

Post a Comment

Labels

1z0-482 Dumps (2) 1Z0-525 Dumps (1) BI Apps Installation (1) BI Publisher Interview Questions (1) BICS (2) Business Intelligence (1) DATA WAREHOUSE ADMINISTRATOR CONSOLE (18) Data Warehousing (48) E-BIZ R12 (1) E-BIZ R12 INSTALLATION (1) Essbase (1) hmailserver (1) Hyperion (1) Hyperion Essbase (2) Hyperion Essbase Interview Questions (1) Hyperion financial management (1) Informatica (163) Informatica Installation 9.6.1 (2) Informatica Interview Question (46) Informatica Online Training (1) Informatica Training (1) Informatica Training Chennai (1) JAVA (1) linux (1) Normalization in Oracle (1) OBIA 11.1.1.7.1 (1) OBIA 7.9.6.3 (3) OBIA Installation (33) OBIEE (1) OBIEE 10G (8) OBIEE 11G (137) OBIEE 11g dumps (2) OBIEE 11g Interview Questions (1) OBIEE 12c (20) OBIEE 12c Architecture (1) OBIEE 12C TRAINING (1) OBIEE certification (2) OBIEE Interview Question (1) OBIEE Publisher Interview Questions (1) OBIEE TRAINING CHENNAI (1) OBIEE TRAINING ONLINE (1) ODI (Oracle data integrator) (2) odi 11g certification (1) ODI dumps (2) Oracle Administrator (20) Oracle Business Intelligence Application (3) Oracle Data Integrator (2) Oracle Data Integrator Interview questions for experience (1) Oracle Database (3) Oracle Database 12c Installation steps (3) Oracle Database Installation in Linux 6 (2) Oracle E-Business R 12.3 (1) Oracle Hyperion Planning and Budgeting Questionnaire (2) oracle Joins (1) RCU INSTALLATION (1) scorecard in obiee 11g (1) SQL (3) SQL Interview Questions (1) sql server 2008 (2) Sql server installation (1) WinSCP (1)