Skip Ribbon Commands
Skip to main content
Navigate Up
Sign In

Quick Launch

Average Rating:

(6 Ratings)
facebook Twitter
Email
Print Bookmark Alert me when this article is updated

Feedback

Handling Multiple Matches from Lookups in PowerCenter mappings
Content

Lookups find a value based on the conditions you set in the Lookup transformation.
If your lookup condition is not based on a key, or your lookup source is denormalized, the Server might find multiple matches. When this happens you need to decide what you want the Server to do.

The Lookup transformation offers three choices:

  • Use the value in the first matching record in the lookup cache
  • Use the value in the last matching record in the lookup cache
  • Return an error

To illustrate this situation, this example uses a mapping that converts currencies from local to US dollars (for invoice received during March).
This mapping, uses a surfboard company that also imports sports wear from Australia, England, and Japan.
The sportswear vendors invoice the surfboard shop in their local currencies.
Since the surfboard shop is a small business, these companies let the shop pay in US dollars.

The surfboard shop has an operational database that stores invoice data in the INVOICES table. It also has a table that stores exchange rates for the Australian dollar, British pound, and Japanese yen. This table is updated with current exchange rates twice a week, on Wednesdays and Fridays.
Our mapping needs to look up an exchange rate based on a currency code and the invoice date. When the Server finds a matching exchange rate, it converts the invoice total to US dollars and writes the results to a target. Since our lookup source includes more than one exchange rate for a given date, we'll need to decide how we want to handle multiple matches.

Sample Data

If you want to create the mapping described in this article, you can download the attached SQL file that creates all the sources corresponding to the RDBMS platform you use for your source database:

  • Oracle - lkp2_ora_sql.zip
  • Microsoft SQL Server - lkp2_ms_sql.zip
  • Sybase SQL Server - lkp2_syb_sql.zip
  • Informix - lkp2_inf_sql.zip

The surfboard shop stores all of its invoice transactions in a table called INVOICES and the exchange rates for each currency in the EXCHANGE_RATES table.

If you want to create these tables, run the SQL script through the Warehouse Designer. (Select Targets-Generate/Execute SQL. Then select Execute SQL file.) Make sure that you connect to a source database, not a target database, before running the script.
When you finish, you can import the source definition for the INVOICES table into the Source Analyzer. However, you don't need to create a source definition for the EXCHANGE_RATES table. We'll import it in the Mapping Designer when we create our Lookup transformation.

Source Table

The INVOICES table includes the following columns:

Column Datatype
INVOICE_NO Integer/Number
INVOICE_DATE Date/Datetime
CURRENCY_CODE Varchar(10)
INVOICE_TOTAL Decimal(24,2)

This table contains 6 records: 5 records have a matching date in the lookup source, one record does not.

Lookup Source

The lookup source is any table, view, or synonym in a database from which the Server looks up values. (You cannot create a lookup source from a flat file.)

To calculate the payment amount in US dollars for each invoice, we need data from the EXCHANGE_RATES table. This table stores the exchange rates for each currency during the month of March, and serves as our lookup source table. The EXCHANGE_RATES table includes the following columns:

Column Datatype
CURRENCY_CODE Varchar(10)
CURRENCY_NAME Varchar(38)
EXCHANGE_RATE Decimal(19,4)
EFFECTIVE_DATE Date/Datetime

The EXCHANGE_RATES table is stored in the same database as the INVOICES table.

Target Table

In the Warehouse Designer, we'll design a target, T_PAYMENT_SUMMARY, for storing the calculated payment amounts. After designing this target definition, you can tell the Designer to create the actual target table in your target database.

Column Datatype
INV_NO Integer/Number
INV_DATE Date/Datetime
CURR_NAME Varchar(38)
INV_TOT Decimal(24,2)
EXC_RATE Decimal(19,4)
EFF_DATE Date/Datetime
PAYMENT_AMT Decimal(24,2)

Mapping

This mapping includes the following components:

  • Source definition for the INVOICES table. This table stores the invoice number, invoice date, currency code, and invoice total.
  • A Source Qualifier so the Server can read the source records.
  • A Lookup transformation. Since the currency exchange rates vary from day to day, we need to import the EXCHANGE_RATES table that stores the currency exchange rate for each currency. The Server compares the currency code from the INVOICES table with the currency code in the EXCHANGE_RATES table. If there's a match, it then compares the invoice date with the effective date for each exchange rate. If it finds a match, it returns an exchange rate. If the Server doesn't find a match, it uses the output port's default value any time it encounters an error or fails to find a matching value. Valid default values include constants and constant expressions (that is, one that does not include values from input or input/output ports).
  • An Expression transformation that multiplies the invoice total by the exchange rate to calculate the payment amount in US dollars.
  • A target definition for the target table that stores the results of the payment in US dollars calculation, as well as the exchange rate and effective date for each invoice.

In this mapping, we'll connect the Source Qualifier transformation to the Lookup transformation and the Lookup transformation to the Expression transformation, making it part of the data flow.

Here's a quick preview of how the final mapping looks:

Your first step is to create a new mapping and add the source INVOICES. You can either have the Designer add the Source Qualifier or you can add it yourself. You don't need to change any of the default settings in the Source Qualifier.

Lookup an Exchange Rate

Next we need to add the Lookup transformation. To calculate the payment amount in US dollars for each invoice, we need data from the EXCHANGE_RATES table.

The EXCHANGE_RATES table is stored in the same database as the INVOICES table. Therefore, when you create the Lookup transformation you'll import the EXCHANGE_RATES table from the source database. The lookup source does not have to reside in the source or target database. You can import a table, view, or synonym from another database by selecting the Import button on the dialog that displays when you first create a Lookup transformation.

Next, you need to configure the transformation to look up rates read from the source table RENTALS:

  • Rename the Lookup transformation to lkpExchangeRates and add any comments about the transformation. Comments can help you see at a glance which tasks each transformation performs. If you switch the mapping to the Icon view (Layout-Iconize), you can move the mouse over each transformation and the comments display in a text callout window.
  • Configure the ports within the transformation.
  • Define the lookup condition.
  • Set the properties for the transformation.

Configuring Ports in the Lookup Transformation

You need to configure the Lookup transformation to receive the currency code and invoice date from the Source Qualifier, compare the currency code to the one stored in the EXCHANGE_RATES table, compare the invoice date to the exchange rate effective date, and return an exchange rate for each invoice.

Therefore you need to create two input ports: IN_CURRENCY_CODE and IN_INVOICE_DATE. These ports are input ports (I) only since you are not passing the values out and they are not part of the EXCHANGE_RATES table.

To make our target data as easy to read as possible, we'll include the currency name and effective date for each exchange rate the lookup finds.

You can configure the CURRENCY_NAME, EXCHANGE_RATE, and EFFECTIVE_DATE ports as output (O) and lookup ports (L). You need to pass the currency name, exchange rate, and effective date to the Expression transformation, which calculates the payment in US dollars for each invoice.

The CURRENCY_CODE port is a lookup port (L), since we are only using it in the lookup condition and not passing it to the Expression transformation.

Here's how the Lookup transformation looks with IN_CURRENCY_CODE and IN_INVOICE_DATE added:

Since this Lookup transformation is part of the data flow (a connected Lookup transformation), you do not need to specify a Return port (R). You use Return ports with unconnected Lookup transformations.

Lookup Condition

Next, you need to configure the lookup condition to find an exchange rate for each invoice. You'll create a lookup condition that compares the currency codes in the INVOICES table with those in the lookup source, EXCHANGE_RATES. You also need to create a second condition that compares the invoice date to the effective date for the exchange rate. When you create a lookup condition with multiple conditions, the Server evaluates each condition as an AND, not an OR. The currency code in the INVOICES table must match the currency code in the EXCHANGE RATE table AND the invoice date must be greater than or equal to one of the effective dates.

When you create multiple lookup conditions, always place conditions with equal signs first to optimize session performance.

To get the most accurate conversion, you want to find the exchange rate closest to the invoice date. Since the EXCHANGE_RATES table does not store daily exchange rates, but rather biweekly exchange rates, we need to set the condition to exclude effective dates that occur after the invoice date. In other words, we want to return an exchange rate when the effective date of the exchange rate is less than or equal to the invoice date. If the effective date is after the invoice date, the lookup will not find a matching exchange rate. For example, if an invoice date is Jan 15, 1998, and our EXCHANGE_RATES table stores dates for March 1998 only, the Server won't find a matching exchange rate in the lookup cache. In this situation, the Server returns the default value for that port

Therefore, we need our lookup condition to read, EFFECTIVE_DATE<= IN_INVOICE_DATE as shown in the following dialog.

If the currency codes match AND the invoice date is later in the month than the exchange rates, the Server returns an exchange rate for use in the payment calculation.

Also, the datatypes for each port included in a lookup condition must exactly match. This means the datatype for CURRENCY_CODE must match the datatype for IN_CURRENCY_CODE. Likewise, the datatype for EFFECTIVE_DATE must match the datatype for IN_INVOICE_DATE. You can double-check the datatypes by clicking the Ports tab. Both CURRENCY_CODE and IN_CURRENCY_CODE should be set to String(10) and EFFECTIVE_DATE and IN_INVOICE_DATE should be set to Datetime.

If you have privileges to modify the database containing a lookup source, you can improve lookup initialization time by adding an index to the lookup source. (This is important for very large lookup sources.) Since the Server needs to query, sort, and compare values in these columns, the index should include every column used in a lookup condition.

Handling Multiple Matches

Depending on the type of lookup source table, your lookup might produce multiple return values. If this happens, you have three choices:

  • Use the first value
  • Use the last value
  • Report an error

Before you choose which option to use in the mapping, you need to understand the lookup cache.

Understanding the Lookup Cache

The Server produces the same results whether or not you enable the lookup cache (select Lookup caching enabled on the Lookup transformation's Properties panel). However, using the lookup cache increases session performance. If you choose not to enable the lookup cache, the Server performs a separate query for each lookup. For our mapping, we'll enable the lookup cache.

The first and last values are the first values and last values found in the lookup cache that match the lookup condition. When the Server creates the lookup cache, it includes only the lookup source columns in the lookup condition and lookup source columns used in the mapping (that is, lookup source columns designated as output and linked to another transformation). If you designate a lookup source column as an output port, but do not link the port to another transformation, the Server does not include that port in the lookup cache.

The Server determines which record is first and which is last is that it generates an ORDER BY clause for each column in the lookup cache, then sorts each column in the lookup condition in ascending order. The Server sorts string columns alphabetically (A to Z), numeric columns in ascending numeric order (such as 0 to 10), and date/time columns from January to December and from the first of the month to the end of the month.

This means that the lookup cache for our mapping contains the following columns with the CURRENCY_CODE and EFFECTIVE_DATE columns sorted in ascending order:

CURRENCY_CODE EFFECTIVE_DATE CURRENCY_NAME

EXCHANGE_RATE

AUST Mar 4 1998 12:50PM Australian Dollar 0.0671
AUST Mar 6 1998 12:50PM Australian Dollar 0.0650
AUST Mar 11 1998 12:50PM Australian Dollar 0.0662
AUST Mar 13 1998 12:50PM Australian Dollar 0.0655
AUST Mar 18 1998 12:50PM Australian Dollar 0.0661
UK Mar 4 1998 12:50PM British Pound 1.6733
UK Mar 6 1998 12:50PM British Pound 1.6010
UK Mar 11 1998 12:50PM British Pound 1.6529
UK Mar 13 1998 12:50PM British Pound 1.6612
UK Mar 18 1998 12:50PM British Pound
1.6702
YEN Mar 4 1998 12:50PM Japan Yen 0.0077
YEN Mar 6 1998 12:50PM Japan Yen 0.0074
YEN Mar 11 1998 12:50PM Japan Yen 0.0072
YEN Mar 13 1998 12:50PM Japan Yen 0.0079
YEN Mar 18 1998 12:50PM Japan Yen 0.0080

You can also see which columns the Server includes in the lookup cache, by opening the session log file. Look for an entry similar to the following:

Default SQL to create lookup cache:

SELECT CURRENCY_NAME,EXCHANGE_RATE,CURRENCY_CODE,EFFECTIVE_DATE FROM EXCHANGE_RATES
ORDER BY CURRENCY_CODE,EFFECTIVE_DATE,CURRENCY_NAME,EXCHANGE_RATE

Although the ports IN_CURRENCY_CODE and IN_INVOICE_DATE are part of the lookup condition, they are not part of the lookup source table, and therefore, are not included in the lookup cache.

Understanding the lookup cache helps you decide how to handle multiple matches. Since our lookup condition is based on currency code and invoice date, you can see that we have multiple matches for any invoice between March 4 and March 31. Therefore, you need to determine which exchange rate you want the Server to return. If you select Use First Value, the Server returns the exchange rate on March 4 for every invoice that falls between March 4 and March 31.
Since we want the most accurate conversion possible, this might not be the most accurate exchange rate. If, however, you select Use Last Value, the Server matches the last date in the cache for the corresponding currency code.
The result: the Server returns the exchange rate closest to the invoice date.

If any invoice falls before March 4, the Server will not find a matching exchange rate and returns the default value defined for each output port in the Lookup transformation.

The Properties tab should have the following settings:

Saving the Lookup Transformation

Now you can save the transformation. At this point, you'll connect the INVOICE_DATE port from the Source Qualifier transformation to the IN_INVOICE_DATE in the Lookup transformation. The mapping looks like this:

Notice that the Lookup transformation displays only the input and output ports. The Lookup ports used to find the rate are not part of the data flow and do not display.

Now we need to calculate the payment in US dollars.

Calculating the Payment

To convert the local currency to US dollars, you'll add an Expression transformation. Since we want our target table to include the invoice number, invoice date, currency name, exchange rate, and effective date, include these columns in the Expression transformation as input/output ports. To calculate the payment in US dollars, create an output port, OUT_PAYMENT, and add the expression:

INVOICE_TOTAL * EXCHANGE_RATE

The Ports panel now looks like the following:

Now you can link the columns to the target and save the mapping. Here's a peek at the final mapping:

Running the Session

When you run the session, you don't need to add any special settings. Just make sure the target and source database settings are correct when you create the session. The source database must be the same database in which both the lookup source table EXCHANGE_RATES and the actual source table INVOICES reside. In the future, if you use $Source or $Target as the database name in a Lookup transformation, be sure to point the session to source or target database where the lookup source table resides.

When you run the session, the Server processes the source data, creates a lookup cache (from the CURRENCY_CODE, CURRENCY_NAME, EXCHANGE_RATE, and EFFECTIVE_DATE in the EXCHANGE_RATES lookup source), looks up each exchange rate, calculates the payment amount in US dollars, and writes each record to the target.

Here's how the source, lookup source, and target data look:

Source Table: INVOICES

INVOICE_NO

INVOICE_DATE CURRENCY_CODE INVOICE_TOTAL
999
Mar 1 1998 5:12AM YEN
1000.00
1000 Mar 7 1998 10:13AM AUST 5000.00
1010 Mar 10 1998 8:10AM UK 3500.00
1102 Mar 11 1998 5:15AM YEN 4200.00
1213
Mar 12 1998 9:57AM
UK
7000.00
1424 Mar 20 1998 2:23PM AUST 8900.00

Lookup Source: EXCHANGE_RATES

CURRENCY_CODE CURRENCY_NAME EXCHANGE_RATE EFFECTIVE_DATE
AUST
Australian Dollar
0.0671
Mar 4 1998 12:50PM
AUST
Australian Dollar
0.0650
Mar 6 1998 12:50PM
AUST
Australian Dollar
0.0662
Mar 11 1998 12:50PM
AUST
Australian Dollar
0.0655
Mar 13 1998 12:50PM
AUST
Australian Dollar
0.0661
Mar 18 1998 12:50PM
UK
British Pound
1.6733
Mar 4 1998 12:50PM
UK
British Pound
1.6010
Mar 6 1998 12:50PM
UK British Pound
1.6529
Mar 11 1998 12:50PM
UK British Pound
1.6612
Mar 13 1998 12:50PM
UK
British Pound
1.6702
Mar 18 1998 12:50PM
YEN
Japan Yen
0.0077
Mar 4 1998 12:50PM
YEN
Japan Yen
0.0074
Mar 6 1998 12:50PM
YEN
Japan Yen
0.0072
Mar 11 1998 12:50PM
YEN
Japan Yen
0.0079
Mar 13 1998 12:50PM
YEN Japan Yen 0.0080
Mar 18 1998 12:50PM

T_PAYMENT_SUMMARY

Use Last Value

Since we chose Use Last Value, the results from our session look like this:

INV_NO INV_DATE CURR_NAME
INV_TOTAL EXCH_RATE EFF_DATE PAYMENT
1000 Mar 7 1998 10:13AM Australian Dollar 5000.00 0.0650
Mar 6 1998 12:50PM 325.00
1010
Mar 10 1998 8:10AM British Pound 3500.00
1.6010
Mar 6 1998 12:50PM 5603.50
1102
Mar 11 1998 5:15AM Japan Yen 4200.00 0.0074 Mar 6 1998 12:50PM 31.08
1213
Mar 12 1998 9:57AM British Pound 7000.00 1.6529 Mar 11 1998 12:50PM 11570.30
1424 Mar 20 1998 2:23PM Australian Dollar 8900.00 0.0661 Mar 18 1998 12:50PM 588.29

Using First Value and Report Error

The results vary depending on whether you select Use First Value, Use Last Value, or Report Error.

Use First Value

If we had chosen Use First value, these results would have looked like this:

INV_NO INV_DATE CURR_NAME INV_TOTAL EXCH_RATE EFF_DATE PAYMENT
1000 Mar 7 1998 10:13AM
Australian Dollar
5000.00
0.0671
Mar 4 1998 12:50PM
335.50
1010
Mar 10 1998 8:10AM
British Pound
3500.00
1.6733
Mar 4 1998 12:50PM
5856.55
1102
Mar 11 1998 5:15AM
Japan Yen
4200.00
0.0077
Mar 4 1998 12:50PM
32.34
1213
Mar 12 1998 9:57AM
British Pound
7000.00
1.6733
Mar 4 1998 12:50PM
11713.10
1424
Mar 20 1998 2:23PM Australian Dollar
8900.00
0.0671
Mar 4 1998 12:50PM
597.19

Because the Server creates an ORDER BY in ascending order for each column in the lookup condition (CURRENCY_CODE and EFFECTIVE_DATE), the first value is the earliest date, and the last record is the latest date in the EXCHANGE_RATES.EFFECTIVE_DATE column. Therefore, the Server returns the exchange rate for the first date for each matching currency code in the EXCHANGE_RATE table. Since the Server does not find a match for Invoice 999 on Mar 1, it returns the default value.

Report Error

If we had selected Report Error, the Server would have found multiple matches for each source row, and would have returned the default values for the output ports.

INV_NO INV_DATE CURR_NAME INV_TOTAL EXCH_RATE EFF_DATE PAYMENT


More Information


Reference


Attachments


Applies To
Product: PowerCenter
Problem Type:
User Type: Administrator
Project Phase:
Product Version: PowerCenter
Database:
Operating System:
Other Software:

Last Modified Date: 10/7/2010 2:46 AM ID: 15174
People who viewed this also viewed

Feedback

Did this KB document help you?



What can we do to improve this information (2000 or fewer characters)