Created a new repository (RPD) using the Oracle Business Intelligence Enterprise Edition (OBIEE) Admin Tool. When trying to import data in offline mode, the following error occurs with both ODBC and OCI connection:
Error
The Connection has failed.
You may also notice all or some of the following errors in NQSAdminTool.log:
[2011-02-07T11:26:19.000+00:00] [OracleBIServerComponent] [nQSError: 17003] Oracle gateway error: OCIEnvNlsCreate or OCIEnvInit failed to initialize environment. Please check your Oracle Client installation and make sure the correct version of OCI libraries are in the library path.
…
[2011-02-08T15:42:58.000+00:00] [nQSError: 17014] Could not connect to Oracle database. [[
[nQSError: 17001] Oracle Error code: 12154, message: ORA-12154: TNS:could not resolve the connect identifier specified
at OCI call OCIServerAttach
…
]][2011-02-08T15:46:08.000+00:00] [nQSError: 17014] Could not connect to Oracle database. [[
[nQSError: 17001] Oracle Error code: 12705, message: ORA-12705: Cannot access NLS data files or invalid environment specified at OCI call OCISessionBegin
…
The issue is not reproducible while in Online mode.
This issue would be due to Multiple Oracle Homes exist which can cause confusion to the OBIEE Admin Tool for where to reference OCI libraries, NLS and the tnsnames.ora entries.
Additional configuration steps are required to ensure the desired files are used.
To prevent this error, you need to complete the following configuration tasks:
NOTE: In all cases, the OBIEE Admin Tool should be started by using the Start -> Programs -> Oracle Business Intelligence -> Administration Tool menu item and not by double-clicking the RPD file itself. Starting the Admin Tool via the menu item is required to initialize required environment variables.
Setting the TNSNAMES.ORA entry to use the same name as the Oracle Database SID is recommended. The TNS_ADMIN environment variable needs to point to the physical location of the TNSNAMES.ORA file in the local environment (whether this be the OBIEE server machine or Admin Tool machine).
1. For the Admin Tool installed with the complete OBIEE server installation:
1.1. Edit registry under HKEY_LOCAL_MACHINESOFTWAREORACLE to make sure NLS_LANG key is set to a valid characterset for the Oracle Client used as the DSN.
1.2. Please edit the file %MiddleWare_Home%instancesinstance1bifoundationOracleBIApplicationcoreapplicationsetupbi-init.cmd to set %ORACLE_HOME%bin as first entry in the PATH.
e.g.
set PATH=%OBIEE_HOME%bin;%OBIEE_HOME%bifoundationserverbin;%OBIEE_HOME%bifoundationwebbin;C:OracleBIEE_11gjrebin;%windir%;%windir%system32;%PATH%
1.3. Please edit the file %MiddleWare_Home%instancesinstance1bifoundationOracleBIApplicationcoreapplicationsetupuser.cmd to set TNS_ADMIN to your Oracle client tnsnames.ora home directory path (in case exists) or to your OBIEE tnsadmin %OBIEE_Client_Home%Oracle_BI1networkadmin.
e.g.
set TNS_ADMIN=C:oracleproduct11.2.0client_1networkadmin
~ OR ~
set TNS_ADMIN=C:OracleOracleBIEE11gOracle_BI1networkadmin
2. For the Admin Tool installed with the new standalone installer:
2.1. Edit registry under HKEY_LOCAL_MACHINESOFTWAREORACLE to make sure NLS_LANG key is set to a valid characterset for the Oracle Client used as the DSN.
2.2. Please edit the file %OBIEE_Client_Home%oraclebiorahomebifoundationserverbinbi_init.bat to set %ORACLE_HOME%bin as first entry in the PATH.
2.3. Please add TNS_ADMIN entry to the bi_init.bat file to point to your Oracle client tnsnames.ora home directory path.
e.g.
set TNS_ADMIN=C:oracleproduct11.2.0client_1networkadmin
NOTE: While connecting from your standalone BI Admin Tool client in online mode, the Admin Tool will use the TNSNAMES.ORA file on the BI server. Ensure that your connection pool has the correct entries for your Oracle DB and that the TNSNAMES.ORA file on the BI server has the corresponding TNS entry.
You may instead use the short format for database connection string in the Data Source Name field e.g. dbservername:1521/orcl
Reference Metalink Note : 1296370.1
