Thursday, October 31, 2013

ODI Repositories Introduction, Creation and Administration

ODI Repositories Introduction, Creation and Administration

ODI Repositories and Types


Oracle data integrator provides two types of repositories. They are as follows.
      1. Master repository
                 - Basically all secured information goes to master repository.
                 - It stores topology source, target jdbc url, username, password and schema information.
                 - LDAP connectivity information
                 - ODI internal security informations like ODI user, password, privileges and profiles.
                 - In addition to secured information, it stores versions for all odi objects.
      2. Work Repository
                 - Basically stores all development and execution related objects
                 - Stores data models
                 - Stores project, folders, interfaces, procedures, packages, sequences, variables, KMs, functions etc
                 - Stores execution information of scenarios, load plan etc.
                 - There are two types of repositories are available in work repositories.
                                - Development Work Repository --- Will have development and execution information. Used in devlopment and testing environments
                                - Execution Work Repository      --- Will have only execution information. Used in UAT and Production environments

 Repositories Design in Environments

Oracle recommends the following kind of repository setup in dev, testing, uat and production environments.


Development environment and Hot fix environment will have development work repositories and only for hot fix purposes hot fix environment will be used since it will have the same code base as in production. Once hot fix is done, the the changes will be synced from hot fix environment to development environment. The sync of development objects is basically controlled through source control system and track which version objects are going to which environment by labeling them.

Due to firewall constraints and for security reasons, Master repositories are separated for Dev/Test, Test and production. At each environment, the topology information will be pointing to the respective source, target technologies.

Repository Creation

Master repository and work repository will be kept in separate schemas in oracle database. I will create two schemas here one for master repository and the other for work repository with connect, resource grants. we need a sysdba user role privilege to create the repository. If we are using Oracle XE database we use the system user and if it is enterprise database, you may need to contact DBAs to create the repository or get the sysdba privilege.

create user odimaster identified by odimaster;
grant connect,resource to odimaster ;
 create user odiwork1 identified by odiwork1;
grant connect,resource to odiwork1;



Once the oracle schemas are created for master and work repository, we can use the ODI client to create the master and work repository. Oracle also provides RCU (repository creation utility) which will create respoitory for all Oracle Fusion Middleware products including ODI. Here we will use only the ODI client to create the respository.

ODI client is part of odi companion generic and can be download from oracle downloads for ODI. Once you extract the download zip file, ODI client odi.exe is available in studio\oracledi-studio\oracledi\client folder. Select File->New->Master Repository creation wizard

Provide master repository schema details and sysdba user details.
Enter password for supervisor user which has all privileges in ODI. super will be used to create other user and provide privileges.
Please select internal password storage and click submit. You can select external store and provide LDAP details here if needed. ODI client will take some time to create the objects required for the repository in master repository. Once master repository is created successfully, confirmation will be displayed.

Please select File->New->Create a new ODI repository login. Provide the login name and provide details for the supervisor login. After logging in as supervisor, we can create other users and provide correct privileges.Select master repository only option since we havnt created any work repository yet.

 Once login is created, select ODI->Connect to connect to the master repository. After logging in, there are four tabs which are displayed in the left hand side. 

1. Designer - will show objects only when connected to development work repository. When connected to work repository, it shows project, folder, odi objects, KMs, models etc.
2. Operator - will show objects when connected to development and execution work repository. When connected to work repository, it will show all execution information.
3. Topology - will show information from master repository. All technology physical and logical connections and agents information
4. Security - will show information from master repository on ODI users, profiles and objects.

Lets us now create a work repository so that we can get to work on development. Select Topology tab and expand on repositories accordion. Here it will show master repository and work repositories. If we need to know about master repository we connected, just double click on master repository. 

To create work repository, right click on work repository and select create new work repository.
Provide the schema details for work repository.
Select the type of work repository you want to create. Select Development repository since we want to do development. If it is for production where we dont want development objects to be stored, select execution repository. Complete the wizard and odi will take sometime to create the work repository.

Click yes for the prompt to create work repository login and provide a name for the work repository login. Now in the topology, you will see the new work repository created under work repositories. double click on the work repository to see more details about the repository.

Now click on ODI->disconnect and select ODI->connect to login into the new work repository.
Select the login name created for work repository and provide supervisor user and password to login to ODI work repository.

Now, you will be able to see designer and operator enabled and showing objects from work repository.

In the next blogs, I will show how to create table to table and file to table loading in ODI.
Please let me know if there any question. I will be happy to help.



6 comments:

  1. Hi,
    Is there a way to do this from command line? via script?

    Regards,
    Suraj.

    ReplyDelete
  2. Your readers may be interested in the following ebook on the ODI 11g repository

    ReplyDelete
    Replies
    1. Here is the link https://www.amazon.com/Discover-Oracle-Integrator-Repository-Model-ebook/dp/B00H8O6H1W

      Delete
  3. what happened if we only create either master or work repository..
    Will odi work??
    plz clear me as i need the ans

    ReplyDelete

  4. Thanks for the nice blog here.I was searching this one for a long time.This blog is very helpful for my studies..I got another one site also,which is same as yours Oracle ODI .Check this one also Oracle Fusion Manufacturing .Sure it will be helpful for you too..Once more iam thanking you for your creative blog.

    ReplyDelete