Skip Ribbon Commands
Skip to main content
Navigate Up
Sign In

Quick Launch

Average Rating:

facebook Twitter
Email
Print Bookmark Alert me when this article is updated

Feedback

HOW TO: Reduce the number of rows cached by Lookup Transformation to improve session performance
Solution
When developing a mapping to load a fact table in a data warehouse environment, a lookup transformation is used to look up dimension keys.

Example

If there are 10 million rows in the Product_Dimension product dimension table then the lookup will cache all the rows when loading Sales_Order_Fact .

Usually fact tables are loaded incrementally and not all product rows are needed in the dimension lookup cache. In some cases we can use a SQL override in the lookup transformation to select only the product rows that need to be cached. This can improve session performance by reducing the time to build the lookup cache and also improve lookup performance.

Example

In a scenario where Sales_Order_Fact is loaded from a staging table Sales_Order_Stage which contains only the incremental rows, we can use the following query to filter out unwanted Product_Dimension rows:
SELECT PRODUCT_DIMENSION.PRODUCT_KEY as PRODUCT_KEY, PRODUCT_DIMENSION.PRODUCT_CODE as PRODUCT_CODE
FROM
PRODUCT_DIMENSION, SALES_ORDER_STAGE
WHERE
PRODUCT_DIMENSION.PRODUCT_CODE = SALES_ORDER_STAGE.PRODUCT_CODE
More Information
Reference
Applies To
Product: PowerCenter
Problem Type:
User Type: Developer
Project Phase:
Product Version: PowerCenter
Database:
Operating System:
Other Software:
Attachments
Last Modified Date:9/29/2008 3:16 AMID:22863
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)