Last week I encountered an issue when trying to import metadata as part of creating a new RPD in the OBI 11.1.1.7 admin tool: The Connection has failed. Not terribly useful, that.
TL;DR Solution: edit your bi_init.bat file to include the following statement:
set TNS_ADMIN=Drive:\Path\to\tnsnames.ora\
Where:
- it’s the path only (don’t include the file name), and
. - the path is correct for your environment
E.g., you might set it like so:
set TNS_ADMIN=C:\Oracle\product\client\network\admin
It should also go without saying that the tnsnames.ora file needs to exist and be properly configured. For reference, here’s mine (be sure to substitute for the correct host name (IP works too) and DB service name):
orcl = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = MyDBserver.MyDomain.com) (PORT = 1521) ) ) (CONNECT_DATA = (SERVICE_NAME = MyRepo) ) )
Your tns configuration can be tested with tnsping.
In an alternate RPD (the one we normally use), this error never occurred. Some searching quickly turned-up several references to the TNS_ADMIN variable, which in fact was the culprit – but it was a bit of a windy path to get there.
So first, the well-documented fix is to set TNS_ADMIN as part of your BI Admin Tool startup (which, in 11.1.1.7, is handled by default by bi_init.bat which is probably located in a path similar to
C:\Program Files\Oracle Business Intelligence Enterprise Edition Plus Client Tools\oraclebi\orahome\bifoundation\server\bin ).
The initial hurdle for me was that the documenation I found indicated that TNS_ADMIN should be set in user.cmd. The trick with that is this: user.cmd isn’t created by default by the Oracle BI Client Installer (the BI Admin Tool installer) in 11.1.1.7. In fact, the call in bi_init.bat is commented-out:
rem call %ORACLE_INSTANCE%\bifoundation\OracleBIApplication\%ORACLE_BI_APPLICATION%\setup\user.cmd
Fortunately, this is a red herring; all that’s necessary is that the variable be set, even if you do it by hand at the command line where you invoke the bi_init.bat script. Adding the variable declaration directly to bi_init.bat works just fine. So have fun with that and now get to work!
Turning back to my situation, so if this wasn’t set, how the heck were things working in my primary RPD? As it turns out, the TNS_ADMIN variable was being set – as a Static Repository variable inside the RPD!
I don’t recommend going this route (I inherited this environment), but it’s worth noting that if you don’t have access to the server and need to define connections there, you can use this same approach to circumvent the need to engage a system admin to create the definitions for you.
-j