ETL_INATICA_POWERCENTER_数据去重.doc

MAPPING TEMPLATE FOR:Removing duplicate source recordsUSAGE:Remove duplicate records from a source when the source records contain a functional key. TEMPLATE OBJECTS:m_LoadNielsenData_RemoveDuplicates_v61.XMLCHALLENGE ADDRESSED: Duplicate records are occasionally found in source data. Due to primary key constraints on a target database, only one “version” of a duplicate source record should be loaded into the target. This mapping illustrates one alternative for removing duplicate records when the source has a functional key that can be used for grouping. OVERVIEW: The mapping m_LoadNielsenData_RemoveDuplicates illustrates the concept of using the functionality within an Aggregator transformation to remove duplicate records from a source and load this data into a target table of the same structure. IMPLEMENTATION GUIDELINES:The PowerMart / PowerCenter Aggregator transformation can be used to “group” records based on a logical key. In this example mapping, the source data is Nielsen company data that resides in a relational table that does not contain a primary key (to allow duplicates). The functional key to each source record is the STATE_TAX_ID field. The key concept illustrated by this mapping can be found in the following transformation objects: The Source Qualifier object SQ_NIELSEN is used to retrieve data from the NIELSEN raw data table. The select statement orders the rows by STATE_TAX_ID (the group-by column in the Aggregator transformation), so that the Sorted Ports option can be used in the mapping. In Agg_RemoveDuplicateRecords, the STATE_TAX_ID port is selected as the “Group By” port in the transformation (the number of “Group By” ports must correspond to the “Number of Sorted Ports” indicated in the Source Qualifier object). The Informatica server, by default, returns the last row in a group if no aggregate function is specified. If two records with the same value for STATE_TAX_ID enter the Aggregator, only one record will be returned by the Aggregator. As a result, duplicate source records are eliminated.Note that the SORTED INPUT option is chosen in the aggregator so that only one groups worth of data will be cached in memory by the Informatica server (dramatic increases in Aggregator performance).Please refer to the metadata descriptions in