Unconnected Lookup

Unconnected Lookup


In this example, we understand the following:
* How to configure the Unconnected Lookup?
* How to combine the multiple tables using the Source Qualifier?
* How to define and Initialize Mapping Parameters?
A mapping parameter represents a constant value that can be defined before a session taskis run. A mapping parameter retains the same value throughout the entire session and provides an alternative to hard coding values within logical expressions. ―$$‖ is the required
prefix for user-defined mapping parameter and variable names.
Sources: – INVOICE (SDBU)
PRODUCT (SDBU)
LINE_ITEM (SDBU)
Target: – ODS_LINE_ITEM (TDBU)
Lookup Table: – ODS_CUSTOMER_ACTIVE (TDBU)
1. Import the Source Definition LINE_ITEM by the Source Analyzer from SDBU schema
2. Import the Target Definition ODS_LINE_ITEM by the Target Designer from TDBU schema
3. Create a Mapping ―m_ods_line_item‖ by using the mapping designer. Click Tools > Mapping Designer to open the Mapping Designer.
4. Drag the INVOICE, PRODUCT and LINE_ITEM Source Definitions to the mapping designer workspace.
5. If created automatically, delete all of the Source Qualifier Transformations.
6. Create a new Source Qualifier transformation by selecting all three sources (PRODUCT, INVOICE and LINE_ITEM)Click Transformation > Create to create Source Qualifier transformation. Select Expression, enter name as SQ_ LINE_ITEM
7. Create an Expression Transformation EXP_ODS_LINE_ITEM. Link the columns from SQ_LINE_ITEM to EXP_ODS_LINE_ITEM.
* INVOICE.CUST_NO
* LINE_ITEM.LINE_ITEM_NO
* LINE_ITEM.INVOICE_NO1
* LINE_ITEM.PRODUCT_CODE
* LINE_ITEM.QUANTITY
* LINE_ITEM.DISCOUNT
* PRODUCT.PRICE
* PRODUCT.COST
8. Add the sql query in the Source qualifier
SELECT INVOICE.CUST_NO, LINE_ITEM.LINE_ITEM_NO,
LINE_ITEM.INVOICE_NO, LINE_ITEM.PRODUCT_CODE,
LINE_ITEM.QUANTITY, LINE_ITEM.DISCOUNT,
PRODUCT.PRICE, PRODUCT.COST
FROM INVOICE, LINE_ITEM, PRODUCT
WHERE INVOICE.INVOICE_NO=LINE_ITEM.INVOICE_NO
AND PRODUCT.PRODUCT_CODE=LINE_ITEM.PRODUCT_CODE
9.In the Mapping Designer, click Mappings > Parameters and Variables. Click the Add button and Initialize the parameters
$$MIN_RATE=0.00
$$MAX_RATE=0.35
10. Create an output port in expression transformation EXP_ODS_LINE_ITEM,DISCOUNT_RATE_OUT decimal (10, 2)
11. Create a Lookup transformation and select ODS_CUSTOMER_ACTIVE from the target location as the table to be used for the lookup. Name it as
LKP_ODS_CUSTOMER_ACTIVE
12. Add a port (input only) called CUST_ID_in with the same data type, precision and scale as CUST_ID in Lookup Transformation
13. Unselect the O (Output) attribute for ports CUST_NAME, FIRST_CONTACT, CUSTOMER_STATE, CUSTOMER_ZIP and CUSTOMER_TYPE. Select the R (Return) attribute for the DISCOUNT_RATE port.
14. In the Condition tab, add the condition CUST_ID = CUST_ID_in
15. Open the Expression Editor for the DISCOUNT_RATE_out port.Enter the Following Formula
IIF (ISNULL (DISCOUNT) OR DISCOUNT < $$MIN_RATE OR DISCOUNT >
$$MAX_RATE, :LKP.lkp_ODS_CUSTOMER_ACTIVE(CUST_NO), DISCOUNT)
16. Drag the Target, Link the columns from LKP_CUSTOMER to ODS_CUSTOMER_ACTIVE Click Repository > Save to save the new mapping to the repository. Validate the mapping.
vkni (3)
In the Workflow Manager Navigator, create the session ―s_m_ods_line_item‖ that uses mapping ―m_ods_line_item‖ using the Task Developer.
Menu->ToolsTask Developer
Task Developer ->MenuTasksCreate
18. Provide the connection for Integration service to communicate with your source,Lookup and Target system
Session–>edit–>mapping Sources (LS) -> SQ_LINE_ITEMOLTP_SOURCE
Session–>edit–>mapping Transformations (LS) -> LKP_ODS_CUSTOMER_ACTIVE->OLAP_TDBU
Session—>edit–>mapping  Target (LS) ->ODS_LINE_ITEMOLAP_TDBU
19. Choose the Target load Normal Mode and check ―truncate target table‖ option
20. Create the workflow ―w_ods_line_item‖ using the workflow designer. Assign the appropriate INT service.
21. Click Tasks > Link Task. Drag from the Start task to the Session task. Click Repository > Save to save the workflow in the repository.
22. Execute the workflow and verify the data in Target.
 

Connected Lookup Unconnected Lookup
Connected lookup participates in dataflow and receives input directly from the pipeline Unconnected lookup receives input values from the result of a LKP: expression in another transformation
Connected lookup can use both dynamic and static cache Unconnected Lookup cache can NOT be dynamic
Connected lookup can return more than one column value ( output port ) Unconnected Lookup can return only one column value i.e. output port
Connected lookup caches all lookup columns Unconnected lookup caches only the lookup output ports in the lookup conditions and the return port
Supports user-defined default values(i.e. value to return when lookup conditions are not satisfied) Does not support user defined default values

Leave a Reply

Your email address will not be published. Required fields are marked *