Creating and Connecting to ODI Master and Work Repositories

Purpose

This tutorial walks you through the steps that are needed to create and connect to the ODI Master Repository and the ODI Work Repository.

Time to Complete

Approximately 20 minutes

Topics

This OBE tutorial covers the following topics:

	Overview
	Scenario
	Examples
	Verifying the Prerequisites
	Example 1: Creating and Connecting to the Master Repository
	Example 2: Creating and Connecting to the Work Repository
	Summary

Viewing Screenshots

Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: Because this action loads all screenshots simultaneously, the response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over each individual icon in the following steps to load and view only the screenshot associated with that step.

The screenshots will not reflect the specific environment that you are using. They are provided to give you an idea of where to locate specific functionality in Oracle Data Integrator.

Overview

The first steps to setting up Oracle Data Integrator are to create the Master repository, connect to the Master repository, create the Work repository, and then connect to the Work repository. This OBE provides a step-by-step walkthrough of the process of creating and connecting to the ODI Master Repository and the ODI Work Repository.

Note: The steps to create and connect to the master and work repositories are also outlined in the ODI installation guide.

 

Back to Topic List

Scenario

Linda works as a database administrator for Global Enterprise. In Global Enterprise, Linda is responsible for performing database management and integration tasks on the various resources within the organization. In particular, Linda is responsible for data loading, transformation, and validation. To begin working on her projects (such as exporting a relational table to a flat file), she needs to create the new Master repository and Work repository.

Examples

In this OBE, students learn the scenario through two examples.

Example 1: Creating and Connecting to the Master Repository
This example walks you through the steps needed to create and connect to the ODI Master repository.

Example 2: Creating and Connecting to the Work Repository
This example walks you through the steps needed to to create and connect to the ODI Work repository.

 

Verifying the Prerequisites

Before you start the tasks, make sure that your system environment meets the following requirements:

Software Requirements

The system should include the following installed products:

• Oracle Database 10g XE
• Oracle Data Integrator 10g (10.1.3.4)

If not done before, start the services and components for Oracle Database 10g XE and Oracle Data Integrator 10g (10.1.3.4)

Back to Topic List

Example 1: Creating and Connecting to the ODI Master Repository

This example walks you through the steps needed to create and connect to the ODI Master repository.

A relational schema must be created for each repository. The following steps walk you through creating the relational schema for the ODI Master repository.

	Connecting to the RDBMS (Oracle 10g XE)
	Creating the RDBMS Schema/User (Oracle 10g XE) for the Master Repository
	Creating the ODI Master Repository
	Connecting to the ODI Master Repository

 

Connecting to the RDBMS (Oracle 10g XE)

The Relational Database Management System (RDBMS) used in this tutorial is Oracle 10g XE. To connect to your RDBMS, perform the following steps:

1.	Start the Oracle 10g XE database Home page by selecting Start > All Programs > Oracle Database 10g Express Edition > Go To Database Home Page.
2.	The Login screen appears. Log in to Oracle XE Database as system. The default password is oracle1. Click Login.

 

Back to Topic List

 

Creating the RDBMS Schema/User (Oracle 10g XE) for the Master Repository

To create the schema and user in the RDBMS (Oracle 10g XE), perform the following steps:

1.	On the Oracle Database Express Edition Home screen, select SQL Commands > Enter Command from the SQL drop-down list.
2.	Create the schemas by executing the following SQL commands: create user <MY_SCHEMA> identified by <MY_PASS> default tablespace <MY_TBS> temporary tablespace <MY_TEMP>; grant connect, resource to <MY_SCHEMA>; Where: <MY_SCHEMA> corresponds to the name of the schema that you want to create <MY_PASS> corresponds to the password that you gave <MY_TBS> corresponds to the Oracle tablespace where the data will be stored <MY_TEMP> corresponds to the temporary default tablespace To create the user, enter the following command. Click Run. create user snpm1 identified by password default tablespace users temporary tablespace temp;
3.	Verify that User snpm1 was successfully created.
4.	Grant connect privileges to the newly created user by executing the following SQL command: grant connect, resource to snpm1; Verify that this statement processed successfully.

Back to Topic List

Creating the ODI Master Repository

To create the ODI Master repository, perform the following steps:

1.	Start the ODI Master Repository Creation program: Start > All Programs > Oracle > Oracle Data Integrator > Repository Management > Master Repository Creation. In the Master Repository Creation Wizard, click the button next to the Driver field. Select Oracle JDBC Driver and click OK.
2.	In the URL field, enter the following URL: jdbc:oracle:thin@localhost:1521:XE, and then enter the User as snpm1 and Password as password. In the Technology field, select Oracle. Click the Test Connection button and verify successful connection. Click OK. Click OK on Master Repository Creation Wizard screen.
3.	The SQL command is executed in the command window. If the Master repository creation is successful, you will see the following message. Click OK. The ODI Master repository is now created.

Back to Topic List

Connecting to the ODI Master Repository

To connect to the ODI Master repository, perform the following steps:

1.	Start Topology Manager: Start > All programs > Oracle > Oracle Data Integrator > Topology Manager. Click the New icon to create a new connection to the Master repository.
2.	Configure Repository Connections with the parameters provided in the following table. Click the Test button. Verify successful connection and click OK. Click OK to save the connection. Notes: Don't copy and paste in the JDBC Url field! This may cause problems with entering a valid URL string. You may need to enter the appropriate driver and URL for your RDBMS. Oracle Data Integrator Connection Parameter Value Login Name Master Repository User SUPERVISOR Password SUNOPSIS Database Connection (Master Repository) Parameter Value User snpm1 Password password Driver List Oracle JDBC Driver Driver Name oracle.jdbc.driver.OracleDriver Url jdbc:oracle:thin@localhost:1521:XE
3.	Select the newly created repository connection (Master Repository) from the drop-down list. Click OK. The ODI Topology Manager starts. You are now successfully logged in to the ODI Topology Manager. Click the Repositories tab in left panel of the Topology Manager. Verify that your newly created Master repository is in the Repositories window.

 

Back to Topic List

 

Example 2: Creating and Connecting to the ODI Work Repository

This example walks you through the steps needed to create and connect to the ODI Work repository.

A relational schema must be created for each repository. The following steps walk you through creating the relational schema for the ODI Work repository.

Connecting to the RDBMS (Oracle 10g XE)

	Creating the RDBMS Schema/User (Oracle 10g XE) for the Work Repository
	Creating the ODI Work Repository
	Connecting to the ODI Work Repository

 

Connecting to the RDBMS (Oracle 10g XE)

To connect to the RDBMS, perform the following steps:

1.	If not opened, open the Oracle XE Database Home page: Start > All Programs > Oracle Database 10g Express Edition > Go to Database Home page.
2.	The Login screen appears. Log in to Oracle XE Database as system. The default password is oracle1. Click Login.

Back to Topic List

Creating the RDBMS Schema/User (Oracle 10g XE) for the Work Repository

To create the RDBMS schema for the Work repository, perform the following steps:

1.	On the Oracle Database Express Edition Home screen, select SQL Commands > Enter Command from the SQL drop-down list.
2.	Create the schemas by executing the following SQL commands: create user <MY_SCHEMA> identified by <MY_PASS> default tablespace <MY_TBS> temporary tablespace <MY_TEMP>; grant connect, resource to <MY_SCHEMA>; Where: <MY_SCHEMA> corresponds to the name of the schema that you want to create <MY_PASS> corresponds to the password that you gave <MY_TBS> corresponds to the Oracle tablespace where the data will be stored <MY_TEMP> corresponds to the temporary default tablespace To create the user, enter the following command. Click Run. create user snpw1 identified by password default tablespace users temporary tablespace temp;
3.	Verify that user snpw1 was successfully created.
4.	Grant connect privileges to the newly created user by executing the following SQL command: grant connect, resource to snpw1; Verify that this statement processed successfully.

Back to Topic List

 

Creating the ODI Work Repository

To create the ODI Work repository, perform the following steps:

1.	If not started, start the ODI Topology Manager: Start > All Programs > Oracle > Oracle Data Integrator > Topology Manager. Choose the newly created Master repository from Example 1 (Master Repository). Click OK.
2.	Click the Repositories tab in the left panel of the Topology Manager. Right-click Work Repositories and select Insert Work Repository.
3.	In the Data Server: New window, enter the parameters shown in the following table. Click the JDBC tab. Parameter Value Name WORKREP Technology Oracle User snpw1 Password password
4.	In the JDBC window, select Oracle JDBC Driver and enter the URL from the table below. Click the Test button. In the Test Connection for: Work Repository dialog box, click Test. Verify successful connection and click OK. Click OK again. Notes: Don't copy and paste in the JDBC Url field! This may cause problems with entering a valid URL string. You may need to enter the appropriate driver and URL for your RDBMS. Parameter Value Driver Name oracle.jdbc.driver.OracleDriver Url jdbc:oracle:thin@localhost:1521:XE
	In the Work Repository: New dialog box, set the ID to 1. Set the Name to WORKREP. Click OK. Verify that the newly created Work repository is now in the work repositories tree view.

Back to Topic List

Connecting to the ODI Work Repository

To create a new project for your BPEL process, perform the following steps:

1.	Start ODI Designer: Start > All Programs > Oracle > Oracle Data Integrator > Designer. Click the New icon to create a new connection to the Work repository.
2.	Configure Repository Connections with the parameters provided in the following table. In the Work Repository section, click the icon next to Repository Name field. This displays the list of defined work repositories for the Master repository. Select WORKREP and click OK. Click OK again to save the connection. Oracle Data Integrator Connection Parameter Value Login Name WORKREP User SUPERVISOR Password SUNOPSIS   Database Connection (Master Repository) Parameter Value User snpm1 Password password Driver List Oracle JDBC Driver Driver Name oracle.jdbc.driver.OracleDriver Url jdbc:oracle:thin@localhost:1521:XE
3.	Select Work Repository from the Login Name drop-down list if not already selected. Click OK. The following screen appears. You have now successfully created and connected to the ODI Work repository.