Suppose a bank has come up with a new scheme where the customers will be provided with extra interest rate . For this the bank requires the customer details from the customer reference table. We will map the customer reference table into the new scheme table so as to get all the required details of the customers.
For this all the data has to be loaded into the new empty table of the scheme.
- Go to Start->All Programs and search for PowerCenter Designer.
- Right click on the PowerCenter Designer and click on Open as administrator.
- A pop up window appears asking whether the program can make changes to the computer.
- Click on Yes and proceed further.
- This will open the Designer Screen which looks as follows.
- NAVIGATOR: The Navigator is indicated by the symbol 1 on the screen. It consists of the Repositories and the folders under it. We can connect to a particular Repository and also connect to its folder. It helps in using existing components.
- WORKSPACE: It is the space where the actual work is done. We can edit different repository objects in this space. It consists of different tabs such as Source Analyzer,Target Designer,Transformation Developer,Mapplet Designer and Mapping Designer.
- TOOLBAR: It consists of different tools which are used in the Designer Window.
- STATUS BAR: It gives the status of the current operation that is being executed.
- Right click on the Repository that you want to connect to.
- A Connect To Repository window appears on the screen.
- Enter the Username and Password and click on Connect.
- The Repository along with its folders appears on the Designer Screen which looks as follows
- Go to Folder option and click on Create.
- A Create Folder dialog box appears on the screen. Give a folder name for eg. X_NEW and Click on OK.
- A window pops showing the successful creation of the new folder X_NEW.
- Click on OK.
- We find our new created folder in the Navigator under the Repository which is connected.
- If the New Folder does not appear under the Repository then disconnect the Repository and reconnect it.
- This appears on the left hand side of the window in the toolbar.
- R stands for Repository Manager.
- D stands for Designer.
- W stands for Workflow Manager.
- M stands for Workflow Monitor.
- Go to the X_NEW Folder which has been created.
- Right Click on it and click on Connect.
- This will open all the tools menu under the new folder X_NEW.
- Open Toad For Oracle.
- Connect to the target database and also connect to the source database by entering the username and password.
- Go to the source database editor and write the sql code : grant connect,resource,dba to targetdatabasename;
- Run the code.
- Go to the source database.
- Go to Database and click on Schema Browser. This will open the Schema Browser.
- The page looks as follows. Now double click on the table which consist of the customer account reference details and which is to be mapped into the target table.
- Click on the Script tab.
- Copy the Create Table code of the CUST_ACCT_XREF by selecting and right clicking and selecting Copy.
- Now open the target table editor and paste the code there.
- Change the table name to CUST_ACCT_XREF as CUST_ACCT_XREF_T as the source table name cannot be same as the target table name.
- Run the code.
- This will create a table with same number of columns as in CUST_ACCT_XREF.
- This creates the table CUST_ACCT_XREF_T.
- Open the schema browser of the target database.
- We see that the table with the name CUST_ACCT_XREF_T is created.
- Click on the Data tab.
- We see that all the columns of the table are created and there is no data.
- Return back to the Design Window in Informatica.
- In Design Window , click on SourceAnalyzer under the Tools option.
- Go to Sources and click on Import from Database.
- Import Tables window appears on the screen.
- Select the ODBC Datasource for source by browsing and enter the username,owner name and password.
- Then click on Connect.
- Expand the Source Table that appears on the Import Tables window.
- Select the table ie the CUST_ACCT_XREF that you want to map and click on OK.
- We see that the CUST_ACCT_XREF table appears on the screen.
- Go to Target Designer and click on it.
- In the Target Designer window ,go to Targets and click on Import from Database.
- Select the ODBC Data Source for target.
- Enter the required username,owner name and password .
- Then click on Connect.
- Now expand the tables in target database and select the table to which the source table is to be mapped.
- Select CUST_ACCT_XREF_T.
- Then click on OK.
- Go to the Mapping Designer window by selecting the Mapping Designer.
- Expand the Sources Folder.
- Drag the source table CUST_ACCT_XREF from the source folder into the workspace.
- This will ask for a new mapping name.
- Give the mapping name as cust_ref_s.
- Then click on OK.
- The Mapping Designer window looks like this.
- The source definition along with the source qualifier appears on the screen.
- Expand the Targets folder and drop the target table CUST_ACCT_XREF_T on the Mapping Designer window.
- Select all the columns in the Source qualifier.
- Go to Layout and click on Autolink by Name.
- Drag the selected columns from the Source qualifier to the Target Table.
- We find that all the columns from the Source qualifier are mapped to the Target Definition.
- Now press Ctrl+S to save the changes made.
- Now expand the Mappings Folder on the left.
- Right click on the cust_ref_s and then click on Versioning.
- Then click on Check In.
- The Check In dialog box appears on the screen.
- Give comment as V1 and then click on Apply to all.
- After this no changes can be made.
- Right click on the cust_ref_s under the Mappings Folder.
- Click on Generate Workflow.
- The Workflow Generation window appears on the screen.
- Click on Next.
- Now change the Connection Object of the source and target
- Then Click Next.
- Again click Next.
- Then click on Finish.
- Go to Workflow Manager by clicking on its icon.
- Expand the Workflows Folder on the left hand side under the X_NEW Folder.
- Drag the workflow and drop it on the workspace.
- Right click the workflow on screen and click on Edit.
- The Edit Tasks dialog box appears on the screen.
- Click on the Properties tab.
- Change the Source connection value and Target connection value by browsing.
- Go the Mapping tab.
- Click on Targets Folder and change the Target load type to Normal.
- Check the Truncate target table option.
- Click on Apply.
- Now right click on the wf_cust_ref_s.
- Click on Start Workflow.
- A window appears showing that the workflow is succeeded or not.
- This completes the process of mapping the source table into target table .