LAB 5: USING JOINER TRANSFORMATION

BUSINESS REQUIREMENT:

Suppose that we are trying to keep the Customers address information. For that we need the mentioned address as well as the geographical location of that place. A geographic dimension is already created which has to be populated first. The geographical information is a normalized structure whereas the dimension is a denormalized table. The records hence needs to be flattened to be loaded into the dimension.

 CHALLENGES:

  • All the data regarding the location needs to be flattened without the loss of data.
  • A joiner transformation is to be used to join the geographical location to the states and cities mentioned by the customer . This will give the exact address.

SOLUTION:

STEP 1:

  • Go to the PowerCenter Designer and Open as administrator.
  • Connect to the required repository and also connect to the folder under the repository.
  • Go to Tools->Source Analyzer.
  • Import GEO_CITIES,GEO_STATES and GEO_COUNTRIES source tables from the source database.

1

  • Go to the  Target Designer and import GEO_DIM_T table from the target database.

2

STEP 2:

  • Now go to the Mapping Designer.
  • Create a new mapping by going to Mappings->Create.

3

  • Name the mapping as M_GEO_DATA.

4

STEP 3:

  • Drag the GEO_CITIES,GEO_STATES and GEO_COUNTRIES source tables from the Sources in the Navigator and drop them on the workspace.
  • Similarly drag the GEO_DIM_T target table from the Targets and drop it on the workspace.

5

STEP 4:

  • Create a Joiner Transformation by clicking on the Joiner Transformation icon in the toolbar and then clicking on the workspace.

6

  • Rename the Joiner Transformation as JOIN_CITY_COUNTRY.

7

  • Drag all the ports(except the ACTIVE port) from SQ_GEO_CITIES Source Qualifier into the JOIN_CITY_COUNTRY.
  • Then drag all the ports (except the ACTIVE port ) from SQ_GEO_COUNTRIES into the joiner.

8

  • Double click on the JOIN_CITY_COUNTRY and Edit the joiner.
  • Go to the Properties tab and check whether the Join Type is Normal Join.

9

  • Go to the Condition tab and create a new condition by clicking on its icon.
  • Set the Condition as CNTRY_ID1=CNTRY_ID.

10

  • Click on OK to save the changes.

STEP 5:

  • Create another Joiner Transformation.

11

 

  • Rename it as JOIN_CITY_STATES.

12

  • Drag all the ports from the JOIN_CITY_COUNTRY (except CNTRY_ID1) into the JOIN_CITY_STATES.

13

 

  • Drag the STATE_ID,STATE_NAME and STATE_CODE from the SQ_GEO_STATES into the JOIN_CITY_STATES.

15

  • Double click on the JOIN_CITY_STATES and go to the Properties tab.
  • Set the Join Type as Master Outer Join.
  • Now go to the Condition tab and create a new condition.
  • Set the condition as STATE_ID1=STATE_ID.

14

  • Click OK to save the changes.

STEP 6:

  • Add a new Expression Transformation and rename it as EXP_PLACEHOLDER.

16

  • Drag the CITY_NAME,STATE_NAME,STATE_CODE,CNTRY_NAME,CNTRY_CD and CURR_CD ports from JOIN_CITY_STATES into the expression.

17

STEP 7:

  • Create a new Sequence Generator and rename it as SEQ_GEO_ID.

18

  • Connect NEXTVAL of the SEQ_GEO_ID to the GEO_ID of the target definition.
  • Connect all other ports in the expression to the target definition.

19

  • Save the mapping using CTRL+S.
  • Perform versioning so that no further changes could be made to the mapping.
  • Right click on the Mapping name in the Navigator and Generate Workflow.
  • Follow the steps as explained before.

STEP 8:

  • Go to Workflow Manager.
  • Drag the workflow, wf_M_GEO_DATA from the Navigator and drop it on the workspace.
  • Edit the Session .
  • In the Properties tab, set the Source Connection Value and Target Connection Value.
  • Go to the Mapping tab.Select Targets.
  • Set the Target Load Type as Normal and check the Truncate Target Table option.
  • Click on OK to save the changes.
  • Press CTRL+S to save the changes made.
  • Right click on the workflow in the Navigator and Start Workflow.

20

  • This shows the successful execution of the mapping.

21

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

Leave A Reply

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