Friday, 25 March 2011

Creating and Connecting to ODI Master and Work Repositories

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

Step:1) Connecting to the RDBMS (Oracle 10g XE)

Step:2) Connecting to the RDBMS (Oracle 10g XE)

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

i.On the Oracle Database Express Edition Home screen, select SQL Commands > Enter Command from the SQL drop-down list.

Create the schemas by executing the following SQL commands:
create user identified by ; grant dba, connect, create session to ;

SQL> create user bispmaster identified by password;
SQL> grant dba,connect,create session to bispmaster;

Step:4) Creating the ODI Master Repository



In the URL field, enter the following URL: jdbc:oracle:thin:@bispcognos:1521:xe, and then enter the User as bispmaster and Password as password(This is in our condition). In the Technology field, select Oracle. Click the Test Connection button and verify successful connection. Click Next. Click OK on Master Repository Creation Wizard screen.




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.


Step:5) Connecting to the ODI Master Repository

Configure Repository Connections with the parameters provided in the following screen. Click the Test button. Verify successful connection and click OK. Click OK to save the connection.


Select the newly created repository connection (Master Repository) from the drop-down list. Click OK. The ODI Topology Manager starts.


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


Step:1)Connecting to the RDBMS


If not opened, open the Oracle XE Database Home page: Start > All Programs > Oracle Database 10g Express Edition > Go to Database Home page.


The Login screen appears. Log in to Oracle XE Database as system. The default password is password. Click Login.

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


On the Oracle Database Express Edition Home screen, select SQL Commands > Enter Command from the SQL drop-down list.


Create the schemas by executing the following SQL commands:
create user identified by ; grant dba, connect, create session to ;

SQL> create user bispwork identified by password;
SQL> grant dba,connect,create session to bispwork;

Step:3) Creating the ODI Work Repository

In ODI, click the Topology Navigator tab and then click to the Repositories panel. Right-click the Work Repositories node and select New Work Repository. The Create Work Repository Wizard opens.


In the screen that follows, enter the parameters shown in the following screen. Click the Test button. Verify successful connection and click OK. Click Next.


In the Create Work Repository Login window, click Yes. Enter the Login name: WORK_REPO as shown on the screenshot. Click OK. Verify that the newly created Work repository is now in the work repositories tree view



Now you disconnect from the Master repository and connect to the Work repository. Click ODI menu and select Disconnect "Master Repository".


Click Connect to Repository. Select “WORK_REPO” from the Login Name drop-down list. Enter Password: SUNOPSIS. Click OK. Click the Designer tab. The following ODI Designer screen appears.



Done !

3 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. SYSTEM has dba privileges still i am getting this error. Can you help me with that. Thanks in advance
    and what is the meaning of-"
    Caused by: java.sql.SQLRecoverableException: Closed Connection"



    error:23049-
    oracle.odi.setup.RepositorySetupException: Error while updating Schema Version Registry Entry for ODI. Check if user SYSTEM has DBA permissions.
    at oracle.odi.setup.support.SchemaVersionUtil.setComponentInvalid(SchemaVersionUtil.java:234)
    at oracle.odi.setup.support.MasterRepositorySetupImpl.createMasterRepository(MasterRepositorySetupImpl.java:278)
    at oracle.odi.ui.gallery.support.MasterCreateRepositoryPage.invoke(MasterCreateRepositoryPage.java:231)
    at oracle.odi.ui.gallery.support.AbstractMasterRepositoryWizard$2.run(AbstractMasterRepositoryWizard.java:168)
    at oracle.ide.dialogs.ProgressBar.run(ProgressBar.java:655)
    at java.lang.Thread.run(Thread.java:662)
    Caused by: java.sql.SQLRecoverableException: Closed Connection
    at oracle.jdbc.driver.OracleStatement.ensureOpen(OracleStatement.java:5470)
    at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1672)
    at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:446)
    at oracle.ias.version.SchemaVersion.executeVersionQuery(SchemaVersion.java:2440)
    at oracle.ias.version.SchemaVersion.hasRegistryEntry(SchemaVersion.java:2033)
    at oracle.ias.version.SchemaVersion.setComponentInvalid(SchemaVersion.java:1951)
    at oracle.odi.setup.support.SchemaVersionUtil.setComponentInvalid(SchemaVersionUtil.java:232)

    ReplyDelete