A company always keeps the core data but sometimes we need to have metadata to make changes and update them on the tables. Expressions help in keeping and updating this metadata. Let us take the Accounts information of some customers and make changes to the metadata.
- We have to make changes to the expressions of the Accounts ports to see the changes.
- Go to PowerCenter Designer and Open as administrator.
- Connect to the Repository in the Navigator window to which you want to connect.
- Connect to the required folder under the Repository.
- Go to Tools->Mapping Designer and open the previously created mapping LOAD_ACCOUNTS.
- Check out the Mapping by right clicking on the mapping.
- Click on Versioning->Check Out.
- The Check Out dialog box appears n the screen. Write the Comment and Click on Apply to All or OK to save the changes.
- Now we can make changes to the Mapping.
- This is the Expression Transformation icon available in the toolbar.
- Create an Expression Transformation by clicking on the Expression Transformation icon and then clicking on the workspace.
- Drag and drop ACCT_TYPE,LAST_UPDATE_DATE,LAST_UPDATE_DESC and ACTIVE from the Source Qualifier to the Expression Transformation.
- Double click on the Expression Transformation.
- The Edit Transformations dialog box appears on the screen.
- Rename the expression by clicking on Rename and change the name to EXP_METADATA.
- Then click on OK to save the changes.
- Go to the Ports tab and add a prefix IN_ to all the ports.
- Change these ports to input ports only by unchecking the ‘O‘ box.
- A Create New Button is on the screen . Click on it to create a new port.
- Now make 4 variable ports.
- Make a new port V_ACCT_TYPE. Set its Datatype as String and set the Precision as 1.Change the expression by clicking on the down arrow and change it to DECODE(IN_ACCT_TYPE,1,’SAV’,2,’CHK’,3,’CRO’,’UNK’).
- Select the Functions and ports from the Navigator and to apply them double click on them.
- Click on Validate to check whether the Expression parsed successfully . Then click on OK.
- Again click on OK to save the changes.
- Check the ‘V‘ box to convert the port into a variable port.By doing this the input and the output port automatically gets unchecked.
- The variable ports are used to compute values.
- Similarly make another variable port V_LAST_UPDATE_DATE with Datatype as Date/time and expression as SYSDATE.
- Create another variable port V_LAST_UPDATE_DESC with Datatype as String, Precision as 255 and set the expression as ‘Initial Load’.
- Create another variable port V_ACTIVE with Datatype as String,Precision as 5 and set the expression as IIF(IN_ACTIVE=1,’Y’,’N’).
- The 4 variable ports are created.
- Create 4 new output ports.
- Create an output port O_ACCT_TYPE with Datatype as String ,Precision as 5 and set the expression as V_ACCT_TYPE.
- Create the second output port O_LAST_UPDATE_DATE with Datatype as Date/Time and set the expression as V_LAST_DATE.
- Create the third output port O_LAST_UPDATE_DESC with Datatype as String ,Precision as 255 and set the expression as V_LAST_UPDATE_DESC.
- Create the fourth output port O_ACTIVE with Datatype as String,Precision as 1 and set the expression as V_ACTIVE.
- Click on Apply and the Click on OK to save the changes.
- Connect the respective output ports to the ports on the target table.
- It will ask to remove existing link if any. Click on Yes.
- Perform versioning by right clicking on the mapping->Versioning->Check In.
- Set the Comment and click on OK.
- Then save the workflow.
- Generate the Workflow by right clicking on the workflow name and selecting Generate Workflow.Perform the steps as said in the previous labs.
- Go to the Workflow Manager and drag the workflow wf_LOAD_ACCOUNTS from the Navigator and drop it on the workspace.
- Right Click on the Session and Refresh Mapping.
- Now right click on the mapping and Start Workflow.
- The Workflow Monitor window appears n the screen showing the successful mapping.
- This using of expression helps in changing the metadata of the Accounts table.