In my last tutorial I talked about the installation of Oracle Data Integrator 11gR1 on a Windows machine. In this post I will explain how to go about creating and then connecting to master and work repositories!
Pre-requisites: Oracle 10g Express Edition with *SQLPlus, Oracle Data Integrator (ODI) version 11gR1.
First, open Command Prompt and type sqlplus. Connect to your system schema by providing the valid username and password.
Now, you need to create an RDBMS schema for your master repository. For that execute the following SQL commands.
SQL> create user ODIMR identified by password default tablespace users temporary tablespace temp;
SQL> grant connect, resource to ODIMR;
1. Creating a Master Repository
Step 1: Open ODI Studio. Open the New Gallery by choosing File –> New. In the New Gallery, in the Categories tree, select ODI and then click on Master Repository Creation wizard.
Step 2: In the wizard, select Oracle JDBC Driver. Add the JDBC URL as jdbc:oracle:thin:localhost:1521:orcl. Enter the User as ODIMR and the Password as password. Click the Test Connection button and verify successful connection. DBA User and Password will be the same you entered while logging in to *SQLPLUS. Click Next to proceed.
Step 3: In the Authentication window, enter Supervisor Password as password. Click Next. In the Password storage window, select Internal password storage and click Finish.
2. Connect to master repository
Step 1: Open the New Gallery by choosing File –> New. In the New Gallery, in the Categories tree, select ODI. Select Create a New ODI Repository login.
Step 2: Enter the JDBC URL and other information fields and select jdbc:oracle:thin:@”host”:”port”:”sid” in order to enter the proper URL.
Step 3: Finally, click on Connect to Repository on the main screen in ODI. Enter the credentials with login name ODIMR.
3. Create Work repository
Step 1: Create a schema for the work repository by executing the below SQL commands. I have given name as ODIWR for my schema.
SQL> create user ODIWR identified by password default tablespace users temporary tablespace temp;
SQL> grant connect, resource to ODIWR;
Step 2: Go to Topology Navigator tab. Select Repositories. Under that right click Work Repository and select New Work Repository. This will begin the process of creating a new work repository.
Step 3: In the following screen enter the required parameters once again. Enter User as ODIWR and Password as password. Make sure you test the connection before proceeding further.
Step 4: In the Specify Work Repository properties set the ID to “121”. Set the Name to WORKREP121. Enter Password as password. For Work Repository Type, leave Development. Click Finish.
Step 5: When asked for login name, enter name as ODIWR23. View the newly created repository under the work repositories tree view.
Finally, disconnect from the master repository and try connecting to work repository now created. Select user as ODIWR23 and Password as password.