Suppose that a bank wants to open a new branch for its personal customers. The Customers Table consists information about all types of customers, personal as well as business customers. The Accounts Table has information about the accounts held by all types of customers. For opening a new branch,we need to have information about the personal customers only. It might happen that one customer has many accounts or many customers have a shared account. So we see a many to many relationship from Customers to Accounts. We have to maintain this many to many relationship in a table.
- We need to load information about the personal customers into the target table.
- The Customers and the Accounts table has to be joined to the newly created reference table to get proper association.
- Right click on the PowerCenter Designer and Open as administrator.
- Connect to the required repository in the navigator.
- Provide the Username and Password and then Click on Connect.
- Connect to the X_NEW Folder under the repository.
- Go to Tools and then Click on Source Analyzer.
- Go to the Sources option and click on Import from Database.
- The Import Tables dialog box appears on the screen.
- Enter the Username and Password for the source database.
- Click on Connect.
- A set of tables of the source database appears.
- Select the CUSTOMERS,ACCOUNTS,CUST_ACCT_XREF tables and click on OK.
- Three tables appears in the Source Analyzer.
- Go to Target Designer.
- Go to Targets and then Click on Import from Database.
- Connect to the Target Database and import the ACCOUNTS_T and CUST_ACCT_XREF_T tables.
- The Tables appear in the Target Designer.
- Go to the Mapping Designer and Create a new mapping.
- Name the mapping as LOAD_ACCOUNTS and then click on OK.
- Drag the CUSTOMERS,ACCOUNTS and CUST_ACCT_XREF tables from the Sources in the Navigator and drop them in the workspace.
- The three Source Definition along with its Source Qualifiers of the tables appears on the screen.
- Delete the Source Qualifiers by selecting them and press the DEL key.
- When prompted Click Yes to Confirm Delete.
- All the Source Qualifiers are deleted.
- Now create a new Source Qualifier by clicking on the Source Qualifier icon in the Toolbar and then clicking in the workspace.
- A Select Sources for Source Qualifier Transformation dialog box appears on the screen.
- Select all the three sources and then click on OK.
- All the Source Definitions are connected to a single Source Qualifier.
- Double Click on the Source Qualifier.
- A Edit Transformations dialog box appears on the screen.
- Click on Rename and change the name of source qualifier to SQ_CUST_ACCT_XREF.
- Click on OK.
- Now go to the Properties tab.
- Click on the down arrow next to the User Defined Join attribute.
- Change the SQL Condition to ACCOUNTS.ACCT_NR=CUST_ACCT_XREF.ACCT_NR AND CUSTOMERS.CUST_ID=CUST_ACCT_XREF.CUST_ID
- Now click the down arrow next to the Source Filter attribute.
- Change the SQL condition to CUSTOMERS.CUST_TYPE=1 and click on OK.
- Check the Select Distinct Attribute and click on Apply.
- Then Click on OK to save the changes made.
- Create a Sequence Generator by clicking on its icon present in the toolbar and then clicking on the workspace.
- Double click on the Sequence and rename it to SEQ_ACCT_KEY.
- Click on OK to save the changes.
- Drag the ACCOUNTS_T Target table into the Mapping Designer workspace.
- Connect the NEXT_VAL of the SEQ_ACCT_KEY to the ACCT_KEY of the target table.
- Connect the ACCT_NR, ACCT_TYPE,ACCT_OPEN_BRANCH,CREATE_DATE and ACTIVE of the Source Qualifier to the ACCT_NR,ACCT_TYPE,ACCT_OPEN_BRANCH,CREATE_DATE and ACTIVE of the Target Definition respectively.
- Save the mapping by using CTRL+S.
- Right click on the mapping LOAD_ACCOUNTS in the navigator and perform versioning.
- Again right click on the LOAD_ACCOUNTS and click on Generate Workflow.
- Click on Next and then change the Connection Object .
- Click on Next->Finish.
- Go to the Workflow Manager and drag and drop the wf_LOAD_ACCOUNTS from the navigator to the workspace.
- Edit the Session.
- The Edit Tasks window appears on the screen.
- Go to the Properties tab and then change the Source Connection Value and Target Connection Value.
- Go to the Mapping tab.
- Click on the Targets Folder.
- Change the Target Load Type to Normal and check the Truncate Target Table option.
- Click on Apply.
- Then click on OK to save the changes.
- Right click on the workflow wf_LOAD_ACCOUNTS in the Navigator and then click on Start Workflow.
- The Workflow Monitor window appears on the screen showing whether the workflow succeeded or not.
- To verify whether the data is loaded, right click on the session s_LOAD_ACCOUNTS and click on Get Session Log.
- This gives the Log Events for Session:s_LOAD_ACCOUNTS. This verifies whether all the rows are affected or not.
- This creates a new distinct Target Table with the required information.