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.
- 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.
- 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.
- Now go to the Mapping Designer.
- Create a new mapping by going to Mappings->Create.
- Name the mapping as M_GEO_DATA.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- This shows the successful execution of the mapping.
- Go to Session Log to verify whether the data is loaded correctly.