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.

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

STEP 2:

Now go to the Mapping Designer.

Create a new mapping by going to Mappings->Create.

Name the mapping as M_GEO_DATA.

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.

STEP 4:

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

Rename the Joiner Transformation as JOIN_CITY_COUNTRY.

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.

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.

Go to the Condition tab and create a new condition by clicking on its icon.

Set the Condition as CNTRY_ID1=CNTRY_ID.

Click on OK to save the changes.

STEP 5:

Create another Joiner Transformation.

Rename it as JOIN_CITY_STATES.

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

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

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.

Click OK to save the changes.

STEP 6:

Add a new Expression Transformation and rename it as EXP_PLACEHOLDER.

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

STEP 7:

Create a new Sequence Generator and rename it as SEQ_GEO_ID.

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.

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.