Start your work in ODI....

Before you start your work,There are pre-requisite steps that need to follow.
First, you should have data base installed  in your machine/should have Creadentials of other database.


Orace XE is a very light database,we can use it to start a work.


Creating Master Repository
Security information and Topology information(Physical Schema OR Logical Schema information)
are kept in master repository.


I have installed XE in my local machine and has a schema SYSTEM.


To create master repository,
Start menu-----> All Programme---->Oracle------>Oracle data integrator---->
Repository Management    ---->Master Repository Creation. Enter details,
Click on test connection,It should successful.Click OK,it will be created in few seconds.

   

      

Once Master Repository created ,We have to create Work Repository.


Creating Work Repository
Your work(We will see that ProcedureInterface/Package) will be stored into work repository.


To create Work Repository,
Start menu-----> All Programme---->Oracle------>Oracle data integrator---->
Topology manager.


     




***Physical and Logical schema are like body and soul,they are link to each other.
Physical Schema


Logical Schema

ODI – Loading data into Hyperion Essbase

ODI – Loading data into Hyperion Essbase


We are going to see how to load data into Hyperion Essbase using Oracle data Integrator.

Before we start with, let’s assume a scenario. Hyperion Essbase database is identified with application-database pair.  i.e. Academic application – Workforce database. So to connect with Essbase we need IP, Username and password. Just like to connect with database we need IP, Schema, password.

We will load data from table (source) to Hyperion Essbase(target).

Below is the source table.

Hyperion Essbase always store period(which is PERIOD_YEAR in source) as a FY04,FY05,FY06…..etc.

So, if in source  PERIOD_YEAR is 2008 then in Hyperion Essbase it will go as FY08 in period dimention. Thats why we need mapping file(simple text file) for PERIOD_YEAR and same is the case of department  which will store as a code in hyperion Essbase .Below is the mapping file for PERIOD_YEAR and Department.

Always next step will be to reverse table and file. Before we do this we will need Physical and logical architecture information for source and target. So, we are going to create physical and logical architecture for source(database table, file) and target(Hyperion Essbase).

Go to Topology manageràPhysical architectureàTechnologiesàOracle

Give appropriate name to Data server. Also give username, password and JDBC details.

Similarly, Go to Topology manageràPhysical architectureàTechnologiesàFile

Give appropriate name to Data server and JDBC details.

Now, Go to Topology manageràPhysical architectureàTechnologiesàHyperion Essbase

Again give appropriate name to Data server. Also give username, password. Please note that there will be No details we have to provide for JDBC as Hyperion don’t have that. So, you cannot Test connection by using Test button.

Next step is to create logical schema for all 3 technology (Hyperion Essbase, Oracle, File) to point out physical schema that we have created.

Very Next step is to create model for all Hyperion Essbase, Oracle and File.

So, Go to DesigneràModelàClick on Insert Model,

In Definition tab give appropriate name to Model, select Technology as a hyperion Essbase, select Logical schema that you have created for Hyperion Essbase. In Reverse tab select Customized, Context to Global or other if you have created and RKM as RKM Hyperion Essbase.  If you want to transfer data on multiple account of Hyperion then select MULTIPLE_DATA_COLUMN as Yes and specify that account in DATA_COLUMN_MEMBER else keep it default.

After this click on Reverse button and check in Operator whether Model reverse successfully or not.

So, you will able to see reversed Model like below.

Similarlly, do it for Oacle and File technology and reverse model so that you will be having source table and 2 mapping file that i have shown you in the Model.

Next step is to create Interface which will load data from Oracle table to Hyperion Essbase using that mapping file.

Now, Go to DesigneràProjectsàRight click on InterfaceàInsert InterfaceàGo to diagram tab,

As you can see drag Hyperion Essbase model account onto target Datastore and source table, 2 mapping file onto sources. You may observe on target that Period (in target) is directly coming from PERIOD_MONTH (in source). Adjust, Entity, Scenario, Version, EmployeeGrade and account (ST02020400) has fixed value. You can process/transform source as required and use it into targer as I did for Employee. Department and Year in target is coming from mapping files and these mapping file has joined with Oracle table.

Now, last step remaining in Flow tab. Go to Flow tabàSelect Targetàselect IKM SQL to Hyperion Essbase(Data) as a IKM and Save it.

Now we are all set to load data into Hyperion Essbase. Click on Execute and check it in operator.

Congratulation, Data is loaded into Hyperion Essbase from Oracle table.

You can check the data by retrieving through Excel file in particular combination.

Load text file into oracle table

Load text file into oracle table

To load data from text to oracle table we have to create new project. Inside project ,we have to design program which will do actual text to table loading. This all activity will be done inside Designer

To open designer,

Start menu-----> All Programme---->Oracle------>Oracle data integrator---->Designer

Create new project as shown below,

Give appropriate name to project, and click OK.

Now,if you will observe,Project tree will be having First Folder which have Package,Interface,Procedure.

To create Interface,right click on Interface---->Insert Interface.Give name to it as shown below.

Click on Diagram tab. On diagram tab you have to design/implement your logic.

Defination 1: Load text data to table,Lets say that Oracle table has a 2 column.

Following is textvalues.txt files at oracledi\demo\file.

 

In SYSTEM schma create HSP_TEXT_CELL_VALUE table.

CREATE TABLE HSP_TEXT_CELL_VALUE (TEXT_ID NUMBER(3),VALUE VARCHAR2(20));

To represent Table and Text file in ODI,We need to create Model in ODI.

So,Model is a Meta data(All information about table/Text,column name,length,....etc).

We need to create model for HSP_TEXT_CELL_VALUE table and Textvalues.txt file.

Before we create model,We have to create physical and logical architecture for oracle and file technology.
Lets say for  SYSTEM schema, we created Oracle_10G_XE as a physical and logical architecture in oracle and FILE_DEMO_SRC for file.

To create Model, click on Model on in designer.

Give name to Model and select schema in which you have created HSP_TEXT_CELL_VALUE table.
On Reverse tab,select Context as a Global.

Now,click on Selective Reverse tab,It will disply all table that are exist in SYSTEM schema.
select HSP_TEXT_CELL_VALUE table and click Reverse.

Then it should shown in Model tree as shown below,

Similarly,create model for textvalues.txt. Here is a help,

Give name to model,Select File technology, select File logical schema.click OK

Now,follow step as shown below.

On Defination tab,

On Files tab,

On Columns tab,

Click OK.

Now,we have model for both table as well as text file.

Double click on interface that you have created. Go to Diagram tab.

Drag HSP_TEXT_CELL_VALUE table model on Target Data source and,

textvalues.txt files model on Source as shown below.

If,it will ask for automatic mapping click yes otherwise map it manually.

This mappings describe that on target column what should be insert.

Manual mapping is also drag and drop process.

Click on Flow tab,for source select LKM File to SQL lkm.

For Target data source select IKM Oracle Incremental Update, as shown below.

On Diagram tab,click on Execute  button and check it in Operator to know success/failure of your interface.

Congratulation,your text file is loaded into table.

Start work with ODI.Creating Master Repository,Work Repository,Physical Schema,Logical Schema

Before you start your work,There are pre-requisite steps that need to follow.
First, you should have data base installed  in your machine/should have Creadentials of other database.


Orace XE is a very light database,we can use it to start a work.


1)Creating Master Repository
    Security information and Topology information(Physical Schema OR Logical Schema information)
    are kept in master repository.


    I have installed XE in my local machine and has a schema SYSTEM.


    To create master repository,
    Start menu-----> All Programme---->Oracle------>Oracle data integrator---->Repository Management-      
    --->Master Repository Creation. enter details,click on Test Connection,it should success.


    click OK,Master repository will be created in few seconds.


      

   








2)Creating Work Repository
     Your work(We will see that ProcedureInterface/Package) will be stored into work repository.
 
     To create work repository,
     Start menu-----> All Programme---->Oracle------>Oracle data integrator---->Topology Manager.


     Create new connection,
   

           Login Name:Give any Login name you want.
           Login Name:SUPERVISOR
           password:SUNOPSIS.


           Enter others detail and Test connection by clicking Test button,it should success.then click OK.


           You will see window which has shown below,Insert Work repository
          
            I am using same schema SYSTEM for both master and work repository,you can create new shema
            for work repository if you want.Also enterJDBC details in new Data server.

      Now,to start Designer,
      Start menu-----> All Programme---->Oracle------>Oracle data integrator---->Designer.


      You will have,
    

           Login Name:Give any Login name you want.
           Login Name:SUPERVISOR
           password:SUNOPSIS.


           In Work Repository section,click on find button.It will show Work Repository that you have 
           created. Click OK,you will be having Desiner window.

          
 

      ***Physical and Logical schema are like body and soul,they are link to each other.
3)Physical Schema
     To Create Physical shema,Go to Topology manager---->physical schama
     
     If you want to connect ODI with Oracle database,In technology right click on Oracle,

      Give name to physical schema,enter credentials of database you want to connect,give JDBC parameter
      and Test it.it should success.I have connectd with localhost PC,with SYSTEM schema.


4)Logical Schema
      Its a logical name given to Physical schama


     To Create Logical Schema ,Go to Topology manager---->Logical Schema.

            Give any name that you want.Click OK.


   If you have followed upto this successfully,then you can start working on Examples/Practice..... 

Installing Oracle data integrator.

Installing Oracle data integrator is a very simple process,Just click next,next and you have done!!
Below is a first screen when you installing ODI.

Getting Started with an ETL tool,Oracle Data Integrator.

Oracle Data Integrator is a Extract,transform,load tool from Oracle.In a simple way,It can extract data from one technology(Database/Application) and can load to another technology.


To extract/transform/load data by ODI,only 1 PC need to have install ODI.We can connect this ODI installed PC to different PC/database/application(Oracle apps or any...) and work can be done.