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.