[Hyperion] Creating ODBC Driver

 Creating ODBC DRIVER  to Connect Hyperion Essbase

ODBC(Open Database Connectivity) It is a program that stores all information about the database and directly retrieve the data from source system.ODBC is system default adapter to connect all databases.This can be used as translation layer in between application and database.An application that can communicate through ODBC is referred to as ODBC-compliant. Any ODBC-compliant application can access any DBMS that has a corresponding driver.

ODBC implementations run on many operating systems, including Microsoft Windows, Unix, Linux, OS/2, OS/400, IBM i5/OS, and Mac OS X. Hundreds of ODBC drivers exist, including drivers for enterprise DBMS such as Oracle, DB2, Microsoft SQL Server, Sybase, Pervasive SQL, IBM Lotus Domino, MySQL, PostgreSQL, OpenLink Virtuoso, and desktop database products such as FileMaker, and Microsoft Access

Creating ODBC to Connect Hyperion Essbase

Creating ODBC link is nothing but creating a connection from your application to Database you can update data from through this ODBC.

1.Goto ADMINISTRATIVE tools

2.Select DATA SOURCE(ODBC)

3.Now USER DSN TAB CLICK on ADD

 4. Select  a  driver  for  which  you want  to  connect  to  a  database.Here  i  Selected Data Direct 6.0 Oracle Wire protocol

  

  

5.Click FINISH

6. Driver  setup  

Data source Name: Name for  data source

Description : About the Data source  why you are  using for which purpose

Standard Connection :

These  are  the server  details  to which  server  you are  connecting.

 

Host :  Server  name  or  IP Address 

Port Number: 

Oracle Net Listener / Enterprise Manager Repository port

1521
Oracle Application Server / Oracle Database   

Edit listener.ora and restart listener

SID: Service Identifier

After  all  values  given then  

Click  for  TEST Connection

It  shows whether it  is  connection  setting are  working  and  the  state  of  connection

After  successful connection  passed  .  Then  select  APPLY . 

Now  creating  TNS Listner  setting  

 goto  

C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN   path  then  add the  Server details   with  previous  TNS details.

then  save  the  tnsname.ora  and  close  .

Connect  ODBC from  Hyperion Essbase

Step1:

1.      From  start menu then  All programs the On  from Oracle EPM system select Essbase  from  this  select start  Essbase administrative services console.

2.      Login with  user name  /Password

3.      Select  an  application  From  essbase  server

4.      select  Application  from  server

5.      Select  File  and Click  on Open SQL

  

 

 Step 2

1.      Select the application 

2.      select  database 

3.      Select OK

Step 3 

 

1.      Select SQL data source             Ex: Demo

2.      select  columns statement which  gives the data  values  from  the  selected  table  . The  table  contents  contain  the  dimension  related  details 

3.      select  From  here  i  select  ITEM_DIM is  table  for  items  dimension with  hierarchy

4.      Where  is  the  class  for  statement to  retrieve  the  data  from  the  oracle  tables  for essbase  application.

5.      After  giving  related  information select save  

6.      then  again  select  File  menu  and  then  select  open sql data source  

7.      Then  select OK/Retrieve button .

  

 

Step 4:

1.      Give  the  username  that username  must  have  a  access  privileges  to  SQL data  table  .

2.      Give  the  password for  the  same  

3.      Select Essbase  server

4.      select Application  for creating  rule  file

5.      select  Database  for  the  same

6.      After  all  information  select  OK 

Now  the  either data  or Table   from  SQL/Oracle  will be  automatically  uploaded  to particular  rule  file  

then  map with  the  members  for  hierarchy  dimension  /Data load