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)
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.
8. Add the sql query in the Source qualifier
SELECT INVOICE.CUST_NO, LINE_ITEM.LINE_ITEM_NO,
FROM INVOICE, LINE_ITEM, PRODUCT
9.In the Mapping Designer, click Mappings > Parameters and Variables. Click the Add button and Initialize the parameters
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
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.
In the Workflow Manager Navigator, create the session ―s_m_ods_line_item‖ that uses mapping ―m_ods_line_item‖ using the Task Developer.
Task Developer ->MenuTasksCreate
18. Provide the connection for Integration service to communicate with your source,Lookup and Target system
Session–>edit–>mapping Sources (LS) -> SQ_LINE_ITEMOLTP_SOURCE
Session–>edit–>mapping Transformations (LS) -> LKP_ODS_CUSTOMER_ACTIVE->OLAP_TDBU
Session—>edit–>mapping Target (LS) ->ODS_LINE_ITEMOLAP_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|