Setting up OBIEE11g Admin Tool for ODBC Access

When you see the "The connection has failed" error in OBIEE 11g Admin tool, you would need to follow the below steps to overcome this, atleast
this what was done on Windows 7:

1. Copy the tnsnames.ora from the Oracle's Database location to the OBIEE 11g Install location for e.g.:  Oracle_HOME_LOCATION: C:\app\sripen\product\11.2.0\dbhome_1\NETWORK\ADMIN to OBIEE_11g_LOCATION: C:\OracleFMW\oracle_common\network\admin
2. Open up Control Panel --> System and Security --> System --> Advance System Settings --> Advanced --> Enviroment Variables and add the following in the System Variables section:
   Variable Name: TNS_ADMIN
   Variable Value: C:\OracleFMW\oracle_common\network\admin
3. Then logoff the current user and login again.
4. Now try connecting using the Connection Type as OCI 10g/11g and give the corresponding values to see if it will connect to the database.
   

While using other tools for importing follow the above procedures as well this below:

1. Update the user.cmd or user.sh file depending on your Windows or Unix OS as seen below:

For Windows:

set TNS_ADMIN=C:\OracleFMW\oracle_common\network\admin

For Unix:

TNS_ADMIN=/home/sripen/OracleFMWoracle_common/network/admin
export TNS_ADMIN


The user.cmd file can be found under for e.g.: C:\OracleFMW\instances\instance1\bifoundation\OracleBIApplication\coreapplication\setup