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.
6. Select the JOB_ID column and click the delete button.
7. Delete the following columns:
When you finish, the target definition should look similar to the following target definition:
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.
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.