LAB 4:WORKING WITH LOOKUP AND UNION TRANSFORMATIONS

BUSINESS REQUIREMENT:

Suppose a customer has more than one account in a bank. It might happen that some accounts are held by more than one customer as in case of joint accounts,etc. So this many to many relationship between the Customers and Accounts table can be resolved using a cross reference table. This will help in keeping the records and updating them from time to time.

CHALLENGES:

  • The cross reference data is stored in two tables and merged and loaded into the Datawarehouse.
  • We need to add a union transformation to merge the tables. The outputs of both the ports is passed into two lookups, one for Customer resolution and the other for Accounts resolution.The inputs will be Customer id and Account id and the outputs will be Customer key and Account key.This data is then merged and loaded into the datawarehouse.

SOLUTION:

STEP 1:

  •  Go to PowerCenter Designer and Open as administrator. Connect to the required repository and then connect to the folder under the repository in the Navigator window.
  • Go to Tools->Mapping Designer.
  • Create a new mapping by going to Mappings in the toolbar and selecting the Create option.

1

  • Give the mapping name as INIT_LOAD_CUSTACCTXREF and click on OK.

2

STEP 2:

  • Import the CUST_ACCT_XREF and CUST_ACCT_XREF_ARC source tables if not imported.
  • Drag the CUST_ACCT_XREF and CUST_ACCT_XREF_ARC tables from the Sources in the Navigator to the Mapping Designer workspace.

3

  • Import the CUST_ACCT_XREF_T  target table if not imported.
  • Drag the target table into the Mapping Designer workspace.
  • The following shows the icon to create the Union Transformation present in the toolbar.
  • Create a Union Transformation by clicking on its icon in the toolbar and then clicking on the workspace.

4

 

STEP 3:

  • Drag all the columns from the SQ_CUST_ACCT_XREF Source Qualifier to the Union Transformation.

5

  • All ports are created and linked to the Source Qualifier.

STEP 4:

  • Double click on the Union Transformation.
  • The Edit Transformations dialog box appears on the screen.
  • Go to the Groups tab. A new group named NEWGROUP is created.
  • Rename the NEWGROUP to ACTIVE.

6

  • The above shows the icon to add a new group.
  • Create a new group by clicking on it and name it as ARCHIVE.

7

  • Go to the Group Ports tab and check whether all the ports of the CUST_ACCT_XREF table are present or not.
  • Click on Apply.
  • Then click on OK to save the changes made.

8

 

STEP 5:

  • Drag all the ports from the other Source Qualifier into the Union Transformation.
  • Create a new expression by clicking on the Expression Transformation icon in the toolbar and then clicking on the workspace.

9

  • Drag all the output ports of the Union Transformation into the Expression.

10

  • Edit the Expression Transformation and rename it as EXP_INFO.
  • Then click on OK to save the changes.

11

STEP 6:

  • Create a new Lookup Transformation by clicking on its icon in the toolbar and then clicking on the workspace.

LOOKUP

  • A Select Lookup Table for Lookup Transformation dialog box appears on the screen.
  • Select the Target button at the top and then select the CUST_ACCT_XREF_T table and then click on OK.

12

  • A Lookup Transformation named LKPTRANS appears in the workspace.

13

  • Drag the CUST_ID field from EXP_INFO into the lookup.
  • It is copied as CUST_ID1  at the bottom.

14

STEP 7:

  • Edit the Lookup Transformation by double clicking on it.
  • Rename the Transformation as LOOKUP_CUST and then click on OK.

15

  • Go to the Ports tab and rename CUST_ID1 as IN_CUST_ID.
  • Make it an input only port by unchecking the output ‘O‘ column.

16

  • Remove all the ports except IN_CUST_ID, CUST_KEY and CUST_ID using the delete icon.

17

  • Make CUST_ID as lookup only port by unchecking the output column.

STEP 8:

  • Go to the Condition Tab and click on the new condition icon.

18

  • Select CUST_ID in the Lookup Table Column and IN_CUST_ID in the Transformation Port.
  • Click on OK to save the changes.

STEP 9:

  • Create a new Lookup Transformation and select the Target table as ACCOUNTS.
  • From EXP_INFO expression drag the ACCT_NR port into the new lookup transformation.This port will be created as ACCT_NR1.

19

  • Edit the Lookup Transformation.
  • Rename it as LOOKUP_ACCOUNTS.

20

  • Rename ACCT_NR1 as IN_ACCT_NR by going to the Ports tab.
  • Delete all the ports except for IN_ACCT_NR,ACCT_NR and ACCT_KEY.
  • Make ACCT_NR as the lookup only port.

21

  • In the Condition Tab, create a new condition row with ACCT_NR as Lookup Table Column and IN_ACCT_NR as the Transformation Port.

22

  • Click on OK to save the changes.

STEP 10:

  • Create a new expression by clicking on the Expression Transformation icon in the toolbar and then clicking on the workspace.
  • Rename the expression as EXP_METADATA.

23

  • Drag the CA_ID from LOOKUP_CUST into the EXP_METADATA.
  • Drag the EFF_DT and END_DT from the EXP_INFO into the EXP_METADATA.

24

  • Edit the EXP_METADATA and create three output only ports.
  • Create the first output port with name LAST_UPD_DT with Date/Time as Datatype with expression value as SYSDATE.
  • Create the second output port with name LAST_UPD_DESC with String Datatype having Precision value as 255 and  expression value as ‘Initial Load’.
  • Create the third output port ACTIVE as String Datatype with Precision value as 1 and expression value as IIF(IsNull(END_DT),’Y’,’N’).
  • Then click on OK to save the changes.

25

STEP 11:

  • Create a new Sequence Generator by clicking on the Sequence Generator icon in the toolbar and then clicking on the workspace.
  • Rename the Sequence Generator as SEQ_CA.

26

  • A new Sequence Generator gets created.

27

  • Connect the NEXTVAL of the SEQ_CA expression to CA_ID in the target definition.
  • Connect all other attributes from EXP_METADATA to the target definition.
  • Save the mapping using CTRL+S.
  • Perform versioning so that no further changes could be made in the mapping.
  • Right click on the mapping in the Navigator and Generate Workflow and follow the steps as explained in the previous labs.

STEP 12:

  • Go to the Workflow Manager and drag the created workflow from the Navigator into the workspace.
  • Edit the Session and make necessary changes as explained before.
  • Save using CTRL+S.
  • Then right click on the mapping in the Navigator and click on Start Workflow.

31

 

  • The Workflow Monitor appears on the screen showing the successful mapping.

32

  • Go to the Session Log to verify the data is loaded.

34

Leave A Reply

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