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

shiva,

can you please explain how the data is compared between the source and the lookup tables.

for Ex: Source Tables

Customer_id

1

2

3

4

5

lookup table:

1

2

3

i want only values 4,5 in my target table.

can you please help

thanks

naveena

hi,

Before you load you data to your warehouse, you need to check the same version of the data is available in target,

To compare your ODS and data warehouse/data mart you are using the lookup transformation

Cheers,

Sithu, sithusithu@hotmail.com

thanks seethu,

i am getting the error when i use flat file as my source table. the same is working with oracle source

thanks

nayana

Even though u import flat file properly, in the Work Flow Manager, i.e, in Task Developer, when u double click the session, in the Mapping Tab, for the Source

Give the Source File Directory say C:FLATFILE

(where C-Drive, Flatfile-Directory)

And Source Filename say ITEM.TXT

I hope this is the only solution for ur pb. 

Cheers

ry

We use Look up transfermations to implement the SCD to Implement the CDC(Change Data Capture).

The following tasks can be implemented with look up transfermation.

1.Get a Related value.

2.SCD

3.For calculations.

Get a related Value Means tack a simple example when we are using the aggregater transfermation for calculation of avg sal,sum sal by dept wise (in scott table).we get the ans deptno wise.deptno     sumsal       avg sal

10             29000       5555

20             39999         8733

Because emp doesnot containe deptname and location.

This not give u the complete analysis data if we include Department Name and Location wise  sum and avg sal then the information good for analysis.

Hi,

Lookup is used to look into some other table to get the details for the related information of the current table. These tables have a common column and we get the related information if the condition satisfies.

Pallavi