OBIEE TNSNames Revisited

Following on from my previous article on OBIEE and the tnsnames file.

If for some reason you have no access to the OBI server, and you have a tnsnames.ora file do not despair.

It is entirely possible to configure a database connection within the connection pool settings. But bear in mind this is not very portable, especially with regards to promoting repositories to production - you will have to edit these details manually, and should be used sparingly. There is a "higher" risk of mistyping details.

So how does it work?

To configure the Oracle BI repository:

1. In the Administration Tool, open the repository in online mode. Online mode is strongly recommended for performing data access security tasks.

2. In the Physical layer, right-click and select New Database. The Database dialog is displayed.

3. For Name, enter a name of your choice.

4. For Database, select the type of database that you have installed for use with Oracle Business Intelligence (typically Oracle 11g).

5. Select the Connection Pool tab and click the Add button. The Connection Pool dialog is displayed.

6. For Name, enter the connection pool name you require.

7. Select the Call interface appropriate for the database (for example, OCI 10g/11g for Oracle Database). For Data source name, provide the information that is appropriate for the database that you have installed and configured for use with Oracle Business Intelligence. For example, for Oracle Database, enter a connection string similar to the following:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.130)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=BIOracle)(SID=BIOracl)))

When connecting to an Oracle Database data source, you can include the entire connect string, or you can use the net service name defined in the tnsnames.ora file. As we have no tnsnames file to provide a net service name we use the method above.If you examine a tns entry in the tnsnames.ora file you should see that it is a fairly straightforward job to build the required string above.

8. Select Shared logon and enter values for User name and Password. In this step, you provide the user/schema name and password that you require to access the physical tables/views/synonyms.

9. Click OK in the Connection Pool dialog.

10. Click OK in the Database dialog.