Source Analyzer and Target Designer in Informatica

In ETL/Data Warehouse, you will encounter different sources and targets.

A Source in ETL is an entity from where you pull the records, and then you store these records in temporary tables (staging tables) or Informatica transformation caches. On the basis of mapping design document/business requirement you make a change in these data records (transform the data) and then you load the transformed data in another tables structures, called target tables.

In every Informatica mapping, there will always be a source and a target. To manage different sources and targets in Informatica, you have to use source analyzer and target designer. These tools are integrated into Powercenter designer tool and can be launched from there itself.

With the help of Source analyzer, you can create or import different types of sources in Informatica. Similarly by using target designer you can create or import different types of targets. Informatica provides you the feature to either create source/target from scratch by designing its structure or you can create source/target by importing the definitions. When you Import Source from the database, the following metadata gets imported.

• Source (Table) name
• Database location
• Name of Columns
• Columns Data types
• Constraints

You can also define the key relationships in the tables, which is applicable only at Informatica level and is stored in the repository.

The source or target created/imported in Informatica can be reused any no of times in different mappings. Every mapping must have at least on loadable target. Otherwise mapping will be invalid.

Note- When we create source/target in source analyzer/target designer, structures are only created in Informatica. At the database level, there is no object created. So, you have to create Database objects having the same structure as you have created in Informatica.

You can import following type of sources using source analyzer

• Relation tables ( database tables), views and synonyms
• Flat files
• Cobol files
• XML files

Performance tip – To improve the performance of Relational Source tables, use indexes on the source database tables. On the target, tables disable or remove constraints and indexes for performance.

In this Tutorial - you will learn

• How to open Source Analyzer
• How to import Source table in Source Analyzer
• How to open Target Designer & import target in target designer
• How to create folder in Informatica

How to open Source Analyzer

Step 1 - Open Informatica PowerCenter designer tool

Step 2 – In next screen

1. Double click on the repository which you want to connect
2. Enter username
3. Enter Password
4. Click on Connect button

   

After Successful login, the folders of the user will be listed under the repository name

Note –

1. If login is not successful, check if the user has privileges of connecting to repository
2. If no folder is visible under repository name, check if the folder is created.
3. If a folder was created recently then disconnect from the repository and reconnect.

Step 3 – In the next step

1. Right click on the folder
2. Select open option

When a folder is open, subfolders of that folder will be listed.

Step 4 – Click on the Source analyzer menu, as shown in the figure.

How to import Source table in Source Analyzer

Step 1 - In source analyzer

1. Click on tab "sources" from the main menu
2. Select import from database option, after this ODBC Connection box will open.

   

Step 2 – We will now create ODBC connection (If you already have ODBC connection, move to step 3)

1. Click on the button next to ODBC data Source.

2. On the next page, Select user DSN tab and click Add button.

A DSN (Data Source Name) is basically a data structure, and it contains information about a specific details (database type, location, user details, etc.). This information is required by ODBC driver so that it can connect to that database.

3. When you click on the add button, you will see a list of drivers for various databases (Oracle, SQL Server, Sybase, Microsoft Access, Excel, etc.) The driver which you select depends on what database you want to connect. Select the driver for the data source. Here in gtupapers you will be using oracle database tables. Select Oracle wire protocol driver.

   

4. On the next page, select the general tab and enter database details. Then Click on test connect.

   Note – Database details are specific to your installation depending on hostname, port and SID you selected during database installation. You can get this details by opening tnsnames.ora file located in your oracle installation folder.

5. Once you made a Test Connect, enter database username, password and then select "OK" button

   

6. If the connection is successful, it will show a message box.

   

7. Select OK for test connect window and ODBC Driver setup window. The Created ODBC Data Source will be added to user data sources. Now we are set with ODBC Data source.

Step 3 - In gtupapers, you will be using oracle's Scott/Tiger schema tables. If you do not have these tables in the database, create those using this script.

Download the above Scott.sql File

Step 4 – In the import tables window.

1. Select ODBC data source for the oracle database.
2. Enter database username
3. Enter database password
4. Click on connect/reconnect button. This will show tables for the database user.
5. Expand the tree under tables folder and select EMP table
6. Select OK button.

   

Step 5- The table will be imported in the Informatica Source Analyzer. Use "ctrl+s" keys to save changes to the repository.

Similarly, you can import any other database tables in source analyzer.

How to import target in target designer

In this tutorial, you will create target table emp_target, which will be having the identical structure of the source EMP table. To import target table, the target table must be present in the database. To create target table use the below script. Types of targets available in Informatica are relational, XML and flat files.

Download the above emp_target.sql File

Note – here in this tutorial, both source and target table are present in the same database schema (gtupapers). However, you can also create a target in a different schema.

Step 1- In the Informatica Designer, click target designer icon to launch target, designer.

Step 2- In next step

1. Select option "Targets" from the main menu
2. Select "import" from database option

Step 3 - In the import table window.

1. Select ODBC data source for the oracle database.
2. Enter database username
3. Enter database password
4. Click on connect/reconnect button. This will show tables for the database user.
5. Expand the tree under tables folder and select EMP_TARGET table
6. Select OK button

This will import target in Target Designer of Informatica Powercenter. Use "ctrl+s" key to save the changes.

In this tutorial, you have imported sources and targets. Now, you are all set to create your first mapping.

How to create folder in Informatica

Informatica folders are logical containers, which keeps the objects that you create in Informatica designer/ Workflow manager (mappings, mapplets, sources, targets, etc.)

To create folder

Step 1 – Open Repository Manager

Note - If Informatica Designer is already open, then repository manager can be opened using the shortcuts available in the toolbox.

This will open Informatica Repository Manger in a separate window.

Step 2 - Follow these steps

1. Click on folder menu
2. Select Create option

Step 3 – In next step

1. Enter Desired folder name
2. Select ok

This will create a new folder and a message will appear saying "The folder has been successfully created."

The newly created folder will be listed under the respective repository.

This completes the tutorial. See you in the next one!