Home | Contact Us | Sitemap
DWHlabs provides Data Warehouse education services.This web portal is very useful to the Database professionals,
Data Warehouse professionals, UNIX professionals etc.,
  About Us Advertise Support us Tell your Fren Testimonial Contact Us  
 
 
Please help us continue development of this website and it's services!
 
 

 

 
DWHlabs- ETL TOOL
TUTORIALS
MAPPINGS
VIDEOS
BOOKS
QUESTIONS
Sponsered Links
 
 
MAPPINGS PAGE - REAL TIME MAPPINGS - GENERAL MAPPINGS
 
Mapping : How to find the number of success , rejected and bad records in the same mapping.
Explanation :
In this Mapping we will see how to find the number of success , rejected and bad records in one mapping.
 
  • Source file is a flat file which is in .csv format . Click here to download the source file.The table appears like as shown below..
    EMPNO

    NAME

    		 HIREDATE SEX
    100 RAJ 21-APR M
    101 JOHN 21-APR-08 M
    102 MAON 08-APR M
    103   22-APR-08 M
    105 SANTA 22-APR-08 F
    104 SMITHA 22-APR-08 F
    106     M
  • In the above table it shows few values are missing in the table .ANd also the date format of few records are improper.This must be considered as invlaid records and should be loaded into Bad_records table ( target table which is relational).
  • Other than 2 , 3 , 5, 6 records ,remaining all are invalid records because of NULL values or improper DATE format or both .
  • INVALID & VALID RECORDS ::
  • First we seperate this data using Expression transformation.Which is used to flag the row for 1 or 0 .The condition as follows ..
  • IIF(NOT IS_DATE(HIREDATE,'DD-MON-YY') OR ISNULL(EMPNO) OR ISNULL(NAME) OR ISNULL(HIREDATE) OR ISNULL(SEX) ,1,0)
  • FLAG =1 is considered as invalid data and FLAG =0 is considered as valid data .This data will be routed into next transformation using router transformation .Here we added two user groups one as FLAG=1 for invalid data and the other as FLAG=0 for valid data.
  • FLAG=1 data is forwarded to the expression transformation .Here we take one variable port and trwo ouput ports .One for increament purpose and the other for flag the row ...
  • INVALID RECORDS
  • INCREAMENT ::
    PORT
    EDIT EXPRESSION
    COUNT_INVALID
    V_PORT ( output port )
    V_PORT
    V_PORT+1 ( variable)
  • INVALID DATE ::
    PORT
    EDIT EXPRESSION
    INVALID_DATE
    IIF( IS_DATE(O_HIREDATE,'DD-MON-YY'), O_HIREDATE, 'INVALID DATE')
  • This data will be moved to the BAD_RECORDS table.Look at the below table::
    EMPNO

    NAME

    		 HIREDATE SEX COUNT
    100 RAJ INVALID DATE M 1
    102 MAON INVALID DATE M 2
    103 NULL  22-APR-08 M 3
    106  NULL  NULL M 4
  • VALID RECORDS ::
  • In this we will have the valid records.But here we dont want the Employee ,who is 'F' (Female).So our goal is to load MALE employee info., into the SUCCESS_RECORDS target table.
  • For this we need to use a Router transformation and declare the user group as follows \
  • IIF( sex='M',TRUE,FALSE)
  • And the defined group will capture teh rejected records which are nothing but employee who is FEMALE .
  • This data passed to the REUSABLE Expressiona transformation.Where the Increamental logic is applied to get the count value for the the no., of success and rejected records which are passing it.And loaded into the target table.
  • Look at the below tables :::
  • SUCCESS_RECORDS::
    EMPNO

    NAME

    		 HIREDATE SEX COUNT
    101 JOHN 22-APR-08 M 1
  • REJECTED_RECORDS::
    EMPNO

    NAME

    		 HIREDATE SEX COUNT
    105 SANTA 22-APR-2008 F 1
    106 SMITHA 22-APR-2008 F 2
 
 
Download the mapping and execute it in your lab .
 
Download :
XML FILE  
m_bad_success_reject_records  
   
DWH 3Colors
CERTIFICATION GUIDE
DATA DICTIONARY
ALL ARTICLES
   
DWH CONCEPTS

DWH Architecture
Types of Schema
Normalization
VS LINKS

OLAP Vs OLTP
Dimension Table Vs Fact Table
Datamart Vs Data Warehouse
Data Mining Vs Web Mining

SHARE KNOWLEDGE

Now viewers can share their information with DWHlabs , please Click Here.
Please send your feedback Click Here .
Tell you friend about DWHlabs Click Here .


Data Warehouse Lab

- ETL TOOL
INFORMATICA
OTHER TOOLS

- REPORTING TOOL
BUSINESS OBJECTS
OTHER TOOLS

Database Lab

- ORACLE
SQL
PL/SQL

- DB2
OTHER TOOLS

Unix Lab

Project Lab

DWH Schools

Books Store



 
  About Us Advertise Support us Tell your Fren Testimonial Contact Us  
© 2008 DWHlabs
Privacy Policy | Terms & Conditions