Creating Target Definitions and Tables

You can import target definitions from existing target tables, or you can create the definitions and then generate and run the SQL to create the target tables. In this session,we shall create a target definition in the Warehouse Designer, and then create a target table based on the definition.

Creating Target Definitions

The next step is to create the metadata for the target tables in the repository. The actual table that the target definition describes does not exist yet.

Target definitions define the structure of tables in the target database, or the structure of file targets the PowerCenter Server creates when you run a workflow. If you add a target definition to the repository that does not exist in a relational database, you need to create target tables in your target database. You do this by generating and executing the necessary SQL code within the Warehouse Designer.

In the following steps, you will copy the EMPLOYEES source definition into the Warehouse Designer to create the target definition. Then, you will modify the target definition by deleting and adding columns to create the definition you want.

1. In the Designer, switch to the Warehouse Designer.

2. Click and drag the EMPLOYEES source definition from the Navigator to the Warehouse Designer workspace.

The Designer creates a new target definition, EMPLOYEES, with the same column definitions as the EMPLOYEES source definition and the same database type.

Next, you will modify the target column definitions.

3. Double-click the EMPLOYEES target definition by to open it.

The Edit Tables dialog box appears.

4. Click Rename and name the target definition T_EMPLOYEES.

Note: If you need to change the database type for the target definition(like if ur source is oracle and target in teradata), you can select the correct database type when you edit the target definition.

5. Click the Columns tab.

The target column definitions are the same as the EMPLOYEES source definition.

target-defn.jpg

6. Select the JOB_ID column and click the delete button.

7. Delete the following columns:

ADDRESS1

ADDRESS2

CITY

STATE

POSTAL_CODE

HOME_PHONE

EMAIL

When you finish, the target definition should look similar to the following target definition:

edittable_targetdefn2.jpg

Note that the EMPLOYEE_ID column is a primary key. The primary key cannot accept null values. The Designer automatically selects Not Null and disables the Not Null option. You now have a column ready to receive data from the EMPLOYEE_ID column in the EMPLOYEES source table.

Note: If you want to add a business name for any column, scroll to the right and enter it.

8. Click OK to save your changes and close the dialog box.

9. Choose Repository-Save.

Creating Target Tables

You can use the Warehouse Designer to run an existing SQL script to create target tables.

Note: When you use the Warehouse Designer to generate SQL, you can choose to drop the table in the database before creating it. To do this, select the Drop Table option. If the target database already contains tables, make sure it does not contain a table with the same name as the table you plan to create. If the table exists in the database, you lose the existing table and data.

To create the target T_EMPLOYEES table:

1. In the workspace, select the T_EMPLOYEES target definition.

2. Choose Targets-Generate/Execute SQL.

The dialog box to run the SQL script appears.

3. In the Filename field, enter the following text:

C:\[your installation directory]\MKT_EMP.SQL

If you installed the client software in a different location, enter the appropriate drive letter and directory.

4. If you are connected to the source database from the previous lesson, click Disconnect, and then click Connect.

5. Select the ODBC data source to connect to the target database.

6. Enter the necessary user name and password, and then click Connect.

dbobjgen.jpg

7. Select the Create Table, Drop Table, and Primary Key options.

8. Click the Generate and Execute button.

The Designer runs the DDL code needed to create T_EMPLOYEES. If you want to review the actual code, click Edit SQL file to open the MKT_EMP.SQL file.

9. Click Close to exit.

 

Creating Source Tables and Definitions

Creating Source Tables

Most of the data warehouses already have existing source tables or flat files. Before you create source definitions, you need to create the source tables in the database. In this lesson, you run an SQL script in the Warehouse Designer to create sample source tables. The SQL script creates sources with table names and data.

Note: These SQL Scripts come along with Informatica Power Center software.

When you run the SQL script, you create the following source tables:

CUSTOMERS

DEPARTMENT

DISTRIBUTORS

EMPLOYEES

ITEMS

ITEMS_IN_PROMOTIONS

JOBS

MANUFACTURERS

ORDERS

ORDER_ITEMS

PROMOTIONS

STORES

Generally, you use the Warehouse Designer to create target tables in the target database. The Warehouse Designer generates SQL based on the definitions in the workspace. However, we will use this feature to generate the source tutorial tables from the tutorial SQL scripts that ship with the product.

To create the sample source tables:

1. Launch the Designer, double-click the icon for your repository, and log into the

repository.

Use your user profile to open the connection.

2. Double-click the Tutorial_yourname folder.

3. Choose Tools-Warehouse Designer to switch to the Warehouse Designer.

4. Choose Targets-Generate/Execute SQL.

The Database Object Generation dialog box gives you several options for creating tables.

5. Click the Connect button to connect to the source database.

6. Select the ODBC data source you created for connecting to the source database.

7. Enter the database user name and password and click the Connect button.

You now have an open connection to the source database. You know that you are connected when the Disconnect button displays and the ODBC name of the source database appears in the dialog box.

8. Make sure the Output window is open at the bottom of the Designer.

If it is not open, choose View-Output.

9. Click the browse button to find the SQL file.

Note : The SQL file is installed in the Tutorial folder in the PowerCenter Client installation directory.

10. Select the SQL file appropriate to the source database platform you are using. Click Open.

Alternatively, you can enter the file name and path of the SQL file.

Platform File

Informix SMPL_INF.SQL

Microsoft SQL Server SMPL_MS.SQL

Oracle SMPL_ORA.SQL

Sybase SQL Server SMPL_SYB.SQL

DB2 SMPL_DB2.SQL

Teradata SMPL_TERA_SQL

11. Click Execute SQL file.

The database now executes the SQL script to create the sample source database objects and to insert values into the source tables. While the script is running, the Output window displays the progress.

12. When the script completes, click Disconnect, and then click Close. 

Creating Source Definitions

Now we are ready to create the source definitions in the repository based on the source tables created in the previous session. The repository contains a description of source tables, not the actual data contained in them. After you add these source definitions to the repository, you can use them in a mapping.

To import the sample source definitions:

1. In the Designer, choose Tools-Source Analyzer to open the Source Analyzer.

2. Double-click the tutorial folder to view its contents.

Every folder contains nodes for sources, targets, schemas, mappings, mapplets, and

reusable transformations.

3. Choose Sources-Import from Database.

4. Select the ODBC data source to access the database containing the source tables.

5. Enter the user name and password to connect to this database. Also, enter the name of

the source table owner, if necessary.

In Oracle, the owner name is the same as the user name. Make sure that the owner name

is in all caps (for example, JDOE).connect-to-db.jpg

6. Click Connect.

7. In the Select tables list, expand the database owner and the TABLES heading.

If you click the All button, you can see all tables in the source database.

You should now see a list of all the tables you created by running the SQL script in addition to any tables already in the database.

8. Select the following tables:

CUSTOMERS

DEPARTMENT

DISTRIBUTORS

EMPLOYEES

ITEMS

ITEMS_IN_PROMOTIONS

JOBS

MANUFACTURERS

ORDERS

ORDER_ITEMS

PROMOTIONS

STORES

Tip: Hold down the Ctrl key to select multiple tables. Or, hold down the Shift key to

select a block of tables. You may need to scroll down the list of tables to select all tables.

9. Click OK to import the source definitions into the repository.

Creating Repository Users

Creating Repository Users and Groups

You can create a repository user profile for everyone working in the repository, each with a separate user name and password. You can also create user groups and assign each user to one or more groups. Then, grant repository privileges to each group, so users in the group can perform tasks within the repository (such as use the Designer or create workflows).

The repository user profile is not the same as the database user profile. While a particular user might not have access to a database as a database user, that same person can have privileges to a repository in the database as a repository user.

Informatica tools include two types of security:

Privileges. Repository-wide security that controls which task or set of tasks a single user or group of users can access.

Permissions. Security assigned to individual folders within the repository.

PowerCenter uses the following privileges:

Use Designer

Browse Repository

Use Repository Manager

Use Workflow Manager

Workflow Operator

Administer Repository

Administer Server

Super UserYou can perform various tasks for each privilege. Privileges depend on your group membership. Every repository user belongs to at least one group. For example, the user who administers the repository belongs to the Administrators group. By default, you receive the privileges assigned to your group. While it is most common to assign privileges by group, the repository administrator, who has either the Super User or Administer Repository privilege, can also grant privileges to individual users.

An administrator can perform the following tasks

Create groups.

Assign privileges to groups.

Create users and assign them to groups.

In the following steps, you will perform the following tasks:

1. Connect to the repository as an Administrator. If necessary, ask your administrator for the user name and password. Otherwise, ask your administrator to complete the lessons in this chapter for you.

2. Create a group called Group1. To do this, you need to log in to the repository as the

Administrator.

3. Assign privileges to the Group1 group.

4. Create a new user.

Connecting to the Repository

To perform the following tasks, you need to connect to the repository. If you are already

connected to the repository, disconnect and connect again to log in as the Administrator.

Otherwise, ask your administrator to perform the tasks in this chapter for you.

To connect to the Repository,

1. Launch the Repository Manager.

A list of all repositories appears in the Navigator.

2. Double-click on ur repository .

3. Enter the repository user name and password for the Administrator user. Click Connect.

The dialog box expands to enter additional information.

4. Enter the host name and port number needed to connect to the repository database.

5. Click Connect.

You are now connected to the repository as the Administrator user.

Introduction to Informatica PowerCenter 7.1

  • Informatica is one of the most popular ETL(Extraction, Transformation and Loading) tools in the market today. Informatica Power Center provides an environment to load data into a centralized location such as an Operational data Store (ODS) or a data mart or a data warehouse. U can extract the data from various data sources such as flat files, or any Database or even COBOL files, Transform the data based on ur business logic and load data into different types of targets including files and relational databases.

Informatica Provides the following components:

  • Informatica Repository
  • Informatica Client
  • Informatica Server

Informatica Repository: The Repository is the core of the informatica suite. Repository database contains a set of metadata tables that the Informatica tools and applications access. The Informatica Client and server access the repository to save and retrieve metadata.Informatica Client:  The PowerCenter Client is comprised of applications that you use to manage the repository, design mappings and mapplets, create sessions and workflows to load the data, and monitor workflow progress.The Informatica Client consists of three client applications.

  • Repository Manager
  • Designer
  • Server Manager

In this tutorial, you use the following applications and tools:

Repository Manager. Use the Repository Manager to create and administer the metadata repository.  You use the Repository Manager to create a repository user and group. You create a folder to store the metadata you create in the lessons.Repository Server Administration Console. Use the Repository Server Administration console to administer the Repository Servers and repositories.Designer. Use the Designer to create mappings that contain transformation instructions for the PowerCenter Server. Before you can create mappings, you must add source and target definitions to the repository. Designer comprises the following tools:

  •  Source Analyzer. Import or create source definitions.
  •  Warehouse Designer. Import or create target definitions.
  •  Mapping Designer. Create mappings that the PowerCenter Server uses to extract, transform, and load data.
  •  Workflow Manager. Use the Workflow Manager to create and run workflows and tasks. A workflow is a set of instructions that describes how and when to run tasks related to extracting, transforming, and loading data.
  •  Workflow Monitor. Use the Workflow Monitor to monitor scheduled and running workflows for each PowerCenter Server.

Informatica Server: The Informatica Server extracts the source data, performs the data transformation and loads the transformed data into the targets. Sources accessed by Powercenter

  • Relational: Sybase, Oracle, IBM DB2, Informix, MS SQL Server and Teradata.
  • File: Fixed and delimited flat files, COBOL files and XML files
  • Extended: If u use Power Center,  u can purchase additional Powerconnect products to connect to other business sources such as SAP R/3, Siebel, etc.
  • Mainframes: If u use Power Center,  u can purchase additional Powerconnect products to connect to IBM DB2 on MVS
  • Others: MS Access and MS Excel
Follow

Get every new post delivered to your Inbox.