Objectives

Analyze a delimited flat file. Configure a connected lookup transformation. Use a filter transformation to exclude records from the transformation pipeline.

Background

The Marketing department is holding a special promotion for stores targeted as potential customers. They have purchased an industry listing from the Nielsen Research Company of their target market, which includes potential new customers as well as many of their current customers. Because this promotion is extended to new customers only, they must first exclude existing customers from this listing before they send out the promotional mailing.

Informatica Solution

The purchased industry listing is a flat file, nielsen.dat, which has been placed on the Informatica server machine. After analyzing the purchased listing file, you see that a state tax ID for each store has been included.  Our Company also uses a state tax ID to keep track of its customers, making comparison very easy. In our case, this is done with the CUSTOMER_ID. In your Informatica mapping, create a lookup transformation based on the CUSTOMERS table. Next, compare the state tax ID from the purchased listing to the CUSTOMER_ID in the CUSTOMERS table via the lookup. Use a filter transformation to test the result of the lookup and filter out matches. When no match is found for a given state tax ID, the filter allows the potential customer record into a NEW_CUST table which can be used for the promotional mailing.

Hands On

Ø     Analyze the Source File

What to do…

Use the Source Analyzer to analyze the delimited flat file NIELSEN (from the directory your instructor provides.)  The flat file contains a header row. Define the layout of the flat file as below:

STATE_TAX_ID             NUMERIC             28,0

COMPANY_NAME        TEXT                     50

ADDRESS1                       TEXT                     72

ADDRESS2                       TEXT                     72

CITY                                   TEXT                     30

STATE                               TEXT                     2

POSTAL_CODE               TEXT                     10

Ø     Design the target schema

What to do…

Use the Warehouse Designer to create an automatic target definition named NEW_CUST using the NIELSEN source definition. Then physically create the table in the target database using your student ID and password. The table columns should look like the ones below:

HINT

·         This example shows an Oracle table. Remember that Informatica will change the native datatypes to reflect the type of database that you are using.

Ø     Create the transformations and mapping

What to do…

Use the Mapping Designer to create a mapping from the NIELSEN flat file source to the NEW_CUST target table.

Name the mapping mNew_Customer_x. The lookup transformation matches the state tax ID from the NIELSEN flat file to the customer ID in the CUSTOMERS table. For each match, filter out the corresponding record from the transformation pipeline. This will allow only records that did not match an existing CUSTOMER_ID through to the target table.

HINTS

·         When you create your look-up transformation you will be importing a table.

·         In the lookup transformation you must specify a lookup condition.

·         Remember that your filter transformation is filtering the results of the lookup transformation.

can you please how the data will be stored int he Lookup table(ie the oracle table) and how it is passed onto the filter condition .

thanks

nayana