Thursday, 8 March 2012

Connecting Oracle Data Source with OBIEE 11g

In my working environment windows Server R2, I couldn’t connect with oracle database using DSN name (Which we are creating using Administrative Tool -> Data Source (ODBC) -> System DSN in OBIEE 10g) of the schema.

I am sharing the workaround that how I have imported the tables into Administration tool in OBIEE 11.1.1.5.0 in Oracle database environment.
I went to below directories with my installed OBIEE 11g version and copied the oracle credential in the file of tnsnames.ora into this folder.
1. Drive:\<BIHome>\Oracle_BI1\network\admin\tnsnames.ora
    E.g.: C:\OBIEE11g\Oracle_BI1\network\admin\tnsnames.ora

2. C:\OBIEE11g\oracle_common\network\admin\tnsname.ora
     E.g: Drive:\<BIHOME>\oracle_common\network\admin\tnsname.ora
I have opened the Administration Tools and created the Test.rpd and selected ‘Import Metadata’ from file Menu.
Here I have given the following details to connect with oracle database.

Import Type: Local Machine
Connection Type: OCI 10g /11g
DSN: ORCL ( Oracle Service Name)
User Name: SH (The schema that you want to import into the physical layer)
Password: Schema password
You can view the below screenshot for your reference.
 
By clicking 'Next', the schema has to be connected and imported into the physical layer of Administration Tool.

Note:
If you want connect with the remote server oracle database system then we have add the corresponding machine name, service name and IP address in tnsnames.ora file. You can view the below screen shot for you reference.
                               
But MySQL database is connecting with DSN Name with the procedure we are following to import the metadata in OBIEE 10g

7 comments:

  1. Thank you. It helped me out.

    ReplyDelete
  2. Hi,
    Pls. could you clarify:
    a) OBIEE11g is your installation directory... right ?
    b) oracle_common should be created ? (since it is not there... right ?)
    c) Oracle_BI1 - this should be created also ? (since it is not there... right ?)

    txs

    ReplyDelete
    Replies
    1. Hi Above,

      My experience with the above post is with BI Enterprise Edition 11g. After successful installation of OBIEE 11g we will get all the above directory.
      a) Yes.. OBIEE 11g is installation directory only.
      b) Oracle_Common directory will be available with the installation of OBIEE 11g, no need
      to create manually
      c) Orace_BI1 also will be available with the installation of OBIEE 11g.

      But in OBIEE 11.1.1.5 and above version are supporting creating DSN and import the schema in the physical layer of the repository.

      so After copying the oracle credentials into the tnsnames.ora file to the above mentioned directories we can import the tables using OCI or System DSN.

      Hope it helps!
      Bose

      Delete
    2. I had the same problem. But, if you install only client then the NETWORK folder will not be created automatically. Only two folders will be created.
      ORAHOME
      ORAINST
      To resolve the above issue, i.e 'connection has failed' error you need to update the bi_init.bat file with actual oracle home folder.
      i.e
      set ORACLE_HOME=

      note: you can find bi_init file oraclebi\orahome\bifoundation\server\bin

      Delete
    3. Hi Anand,

      I am facing the same problem. It helped me alot.

      Thanks
      Krishna

      Delete
  3. thank you. you are a top man!

    ReplyDelete
  4. I couldn't find
    \oracle_common\network\admin\tnsname.ora
    since it showing upto network folder not even admin folder also
    Thanks in advance

    ReplyDelete

OBIEE 11.1.1.9 Released and available for download

Hi All, OBIEE 11.1.1.9 is released and available for download. Here is the link you can download the software. http://www.oracle.co...