Working with Targets in Informatica PowerCenter 10.x

[box type=”note” align=”” class=”” width=””]This article is an excerpt from a book by Rahul Malewar titled Learning Informatica PowerCenter 10.x. The book harnesses the power and simplicity of Informatica PowerCenter 10.x to build and manage efficient data management solutions.[/box]

This article guides you through working with the target designer in the Informatica’s PowerCenter. It provides a user interface for creation and customization of the logical target schema.

PowerCenter is capable of working with different types of targets to load data:

• Database: PowerCenter supports all the relations databases such as Oracle, Sybase, DB2, Microsoft SQL Server, SAP HANA, and Teradata.
• File: This includes flat files (fixed width and delimited files), COBOL Copybook files, XML files, and Excel files.
• High-end applications: PowerCenter also supports applications such as Hyperion, PeopleSoft, TIBCO, WebSphere MQ, and so on.
• Mainframe: Additional features of Mainframe such as IBM DB2 OS/390, IBM DB2 OS/400, IDMS, IDMS-X, IMS, and VSAM can be purchased
• Other: PowerCenter also supports Microsoft Access and external web services.

Let’s start!

Working with Target relational database tables – the Import option

Just as we discussed importing and creating source files and source tables, we need to work on target definitions.

The process of importing the target table is exactly same as importing the Source table, the only difference is that you need to work in the Target Designer.

You can import or create the table structure in the Target Designer. After you add these target definitions to the repository, you can use them in a mapping.

Follow these steps to import the table target definition:

1. In the Designer, go to Tools | Target Designer to open the Target Designer.
2. Go to Targets | Importfrom Database.
3. From the ODBC data source button, select the ODBC data source that you created to access source tables. We have already added the data source while working on the sources.
4. Enter the username and password to connect to the database.
5. Click on Connect.
6. In the Select tables list, expand the database owner and the TABLE heading.
7. Select the tables you wish to import, and click on OK.

The structure of the selected tables will appear in the Target Designer in workspace.

As mentioned, the process is the same as importing the source in the Source Analyzer. Follow the preceding steps in case of some issues.

Working with Target Flat Files – the Import option

The process of importing the target file is exactly same as importing the Source file, the only difference is that you need to work on the Target Designer.

Working with delimited files

Following are the steps that you will have to perform to work with delimited files.

1. In the Designer, go to Tools | Target Designer to open the Target Designer.
2. Go to Target | Importfrom File.
3. Browse the files you wish to import as source files.
4. The flat file import wizard will come up.
5. Select the file type — Delimited. Also, select the appropriate option to import the data from second row and import filed names from the first line as we did in case of importing the source. Click on Next.
6. Select the type of delimiter used in the file. Also, check the quotes option — No Quotes, Single Quotes, and Double Quotes — to work with the quotes in the text values. Click on Next.
7. Verify the column names, data type, and precision in the data view option. Click on Next.
8. Click on Finish to get the target file imported in the Target Designer.

We now move on to fixed width files.

Working with fixed width Files

Following are the steps that you will have to perform to work with fixed width Files:

1. In the Designer, go to Tools | Target Designer to open the Target Designer.
2. Go to Target | Import from File.
3. Browse the files you wish to use as source files.
4. The Flat file import wizard will come up.
5. Select the file type — fixed width. Click on Next.
6. Set the width of each column as required by adding a line break. Click on Next.
7. Specify the column names, data type, and precision in the data view option. Click on Next.
8. Click on Finish to get the target imported in the Target Designer.

Just as in the case of working with sources, we move on to the create option in target.

Working with Target – the Create option

Apart from importing the file or table structure, we can manually create the Target Definition. When the sample Target file or the table structure is not available, we need to manually create the Target structure. When we select the create option, we need to define every detail related to the file or table manually, such as the name of the Target, the type of the Target, column names, column data type, column data size, indexes, constraints, and so on. When you import the structure, the import wizard automatically imports all these details.

1. In the Designer, go to Tools | Target Designer to open the Target Designer.
2. Go to Target | Create.
3. Select the type of Target you wish to create from the drop-down list.
4. An empty target structure will appear in the Target Designer.
5. Double-click on the title bar of the target definition for the T_EMPLOYEES table.

This will open the T_EMPLOYEES target definition.

1. A popup window will display all the properties of this target definition. The Table tab will show the name of the table, the name of the owner, and the database type. You can add a comment in the Description section. Usually, we keep the Business name empty.
2. Click on the Columns tab. This will display the column descriptions for the target. You can add, delete, or edit the columns.
3. Click on the Metadata Extensions tab (usually, you keep this tab blank). You can store some Metadata related to the target you created. Some personal details and reference details can be saved. Click on Apply and then on OK.
4. Go to Repository | Save to save the changes to the repository.

Let’s move on to something interesting now!

Working with Target – the Copy or Drag-Drop option

PowerCenter provides a very convenient way of reusing the existing components in the Repository. It provides the Drag-Drop feature, which helps in reusing the existing components.

Using the Drag-Drop feature, you can copy the existing source definition created earlier to the Target Designer in order to create the target definition with the same structure.

Follow these steps:

Step 1: In the Designer, go to Tools | Target Designer to open the Target Designer.

Step 2: Drag the SRC_STUDENT source definition from the Navigator to the Target Designer workspace as shown in the following screenshot:

Step 3: The Designer creates a target definition, SRC_STUDENT, with the same column definitions as the SRC_STUDENT source definition and the same database type:

Step 4: Double-click on the title bar of the SRC_STUDENT target definition to open it and edit properties if you wish to change some properties.

Step 5: Click on Rename:

Step 6: A new pop-up window will allow you to mention the new name. Change the target definition name to TGT_STUDENT:

Step 7: Click on OK

Step 8: Click on the Columns tab. The target column definitions are the same as the SRC_STUDENT source definition. You can add new columns, delete existing columns, or edit the columns as per your requirement.

Step 9: Click on OK to save the changes and close the dialog box.

Step 10: Go to Repository | Save

Creating Source Definition from Target structure

With Informatica PowerCenter 10.1.0, now you can drag and drop the target definition from target designer into Source Analyzer. In the previous topic, we learned to drag-drop the Source definition from Source Analyzer and reuse it in Target Designer. In the previous versions of Informatica, this feature was not available. In the latest version, this feature is now available.

Follow the steps as shown in the preceding section to drag-drop the target definition into Source Analyzer.

In the article, we have tried to explain how to work with the target designer, one of the basic component of the PowerCenter designer screen in Informatica 10.x. Additionally to use the target definition from the target designer to create a source definition.

If you liked the above article, checkout our book, Learning Informatica PowerCenter 10.x. The book will let you explore more on how to implement various data warehouse and ETL concepts, and use PowerCenter 10.x components to build mappings, tasks, workflows, and so on.