Creation of Header, Detail, and Trailer Records

Version 7

    Purpose

    Creating header, detail, and trailer records.

    Usage

    This mapping creates a header, detail, and trailer records that can be used by other applications. The trailer record contains an extract date and row count that can be used for reconciliation purposes.

     

    Overview

    An aggregator transformation is used in the mapping to get the row count for the trailer record. Similar logic could be used to capture additional information from the detail records.

    This mapping will create three types of output: MYCUSTOMERS_HEADER, MYCUSTOMERS_DETAIL and MYCUSTOMERS_TRAILER. Using post session

    mappings these three tables can be joined together to get a desired output file.

     

    creating header detail and trailer records.png

    Challenge Addressed

    This mapping template illustrates how to  create records that have a header, trailer, and detail records from a  flat file source that contains only detail records. This is often a  requirement of external applications that require header, detail and  trailer records for audit purposes. For example, applications often  require a trailer record that summarizes the information contained in  the detail records.

     

    Pros

    • Easy process to create a records containing header, detail and trailer information.
    • The output gives meaningful detailed information about the contents of the source file.
    • Using  the same mapping, post process mappings can be used to create a  combined report from Header, Detail, and Trailer attributes.

     

    Cons

    • Post processing may be required to fulfill specific audit requirements.

     

    Download

    • PowreCenter 9.6.x Mapping XML (See Attachments section below)
    • PowerCenter 9.1.0 Mapping XML (See Attachments section below)
    • PowerCenter 8.6.1 Mapping XML (See Attachments section below)

     

    Implementation Guidelines

    The key concepts illustrated by the mapping can be found in the following transformation objects:

     

    • The Expression transformation exp_HDR_TLR creates the information contained in the header and trailer records;
    • The Aggregator transformation agg_ROW_COUNT contains the logic that calculates the number of rows used in the trailer record and removing duplicate records;
    • The Aggregation transformation agg_HDR_RECORD removes duplicate rows and creates one header record.

     

    Please refer to the metadata descriptions in the mapping m_TT_CREATE_HEADER_DETAIL_TRAILER_RECS for more details on this mapping’s functionality.

     

    Note:

    • The Mappings were originally built using PowerCenter Designer. If you are using PowerCenter Express (PCX), all the mappings cannot be imported as PCX includes only the Informatica Developer tool and not PowerCenter Designer tool.  For example, Informatica Developer doesn’t support mappings that use “Sequence Generator” transformation.
    • The objects are based on “UTF-8” codepage. If you have changed you codepage, XML may need to be edited.