Understand Informatica PowerCenter Target Designer

  Level1 Training

We spoke about the Source Analyzer in one of our previous posts.  After we create source definition using Source Analyzer, target schema should be created. This can be designed in the Target Designer tool in PowerCenter. The Target Designer provides a user interface for creating and customizing the logical target schema. Target definitions can be customized as needed and when the customization is complete, we can create the physical targets in the database from SQL scripts executed from the Target Designer.

We have different options to create target definition in Target definition. Lets see the different ways in detail.

Try Creating your Target Definition.

I. Understand Target Designer

1. Select TOOLS | TARGET DESIGNER to open the Target Designer or click on the Target Designer button as shown below.
2. Make sure you are in the Workbook mode, by selecting VIEW | WORKBOOK. You should see the workbook tab.
   
3. Click on the Source Analyzer button. The tab now displays the Source Analyzer icon as shown below.
4. Select WINDOW | NEW WINDOW. A new Target Designer Window is created.
5. In the Workbook Tab section, notice that there are now 3 tabs.
   
6. If you need to switch back and forth between the tools, you can click on the appropriate workbook tab, or select WINDOW | WINDOWS and select the window.

II. Design the target schema (Physical target not existing in Database)

1. Select TARGETS | CREATE. The Create Target Table dialog box appears.
2. Enter the name of the target, TGT_EMPLOYEES_X.
3. Select the database type for the target table, i.e. Oracle.
4. Click on Create and Done.
5. The new table definition appears in the Target Designer workbook.
6. The target schema definition has also been added to your folder’s Targets section, or node.
7. Double-click on the TGT_EMPLOYEES_X table. The Edit Tables dialog box appears.
8. Click on the Columns tab.
   • Add the following columns using icon.
     
   • Once the table has been defined, close the Edit Tables dialog box by clicking on OK.
9. Select REPOSITORY | SAVE to save the newly designed schema to the repository.

III. Design the target schema (Physical target existing in Database)

1. Select TARGETS | IMPORT FROM DATABASE.
2. Enter login details as shown below. Follow the steps for ODBC Creation Here
3. Click on Connect, expand TRGX and Tables and select the target table.
4. Click on OK.
   
5. The new table definition appears in the Target Designer workbook.
6. The target schema definition is added to your folder’s Targets section, or in the node in the Designer’s Window.
   
7. Double-click on the TGT_EMPLOYEES_X table. The Edit Tables dialog box appears. Click on Rename under the Table tab.
8. Click on the Columns tab and note the columns in the table.
9. Select REPOSITORY | SAVE to save the newly designed schema to the repository.

IV. Create the physical target in the Database

1. Click on the TGT_EMPLOYEES_X table to select it.
2. Select TARGETS | GENERATE/EXECUTE SQL from the menu. The Database Object Generation dialog box appears.
   
3. In the Filename entry box, accept the default script file name, MKTABLES.SQL.
4. Select the Selected tables radio button.
   Note : Selecting the All tables radio button will write the code for all tables, which are in the Target Designer workspace window.
5. Under Generation options, make sure the Create Table, Primary Key, Foreign Key and Drop Table options are checked.
6. Click on  the Connect button.
7. Log in to the target database, using the proper ODBC connect string.
8. Click the Generate and Execute button.
9. If you receive a prompt asking if you want to overwrite the contents of the MKTABLES.SQL file, click on OK.
10. The MKTABLES.SQL containing the DDL script to create the TGT_EMPLOYEES_X table is created and runs against the target database.
11. Scroll through the contents of the Output Window to verify that the table was successfully created.
12. Click on the Close button.

Video Tutorial.

In this video tutorial we will see different ways to create target definition.

Hope you enjoy this tutorial, Please let us know if you have any difficulties in trying out these exercise.

Share with your friends