Recently I start creating a repository (11g version) in offline mode and I want to import metadata which failed by the error ‘The connection has failed’.. I have searched for a solution and I will explain this in the next sections.
We can import some metadata from the Oracle BI Administration Tool > File > Import > from Database … In the Import dialog box, we have to select a connection type, in my case ‘OCI 10g/11g’. Next step is to enter a Data Source Name (orcl) and a username and password from which you want to import the tables.
By clicking on the ‘next’-button it troughs an error ‘The connection has failed..’ This was very weird because my Oracle DB and Listener were up-and-running. I had checked this before to make a connection via SQLdeveloper.
At this point I had to find a solution before I could go further on my repository modeling. On Oracle forums I found a nice tip which solves the problem.
The root cause can be found in the following directory:
<biee11>\instances\instance1\bifoundation\OracleBIApplication\coreapplication\setup
There you will find a file named ‘user.cmd’ respectively ‘user.sh’. When you open the .cmd file you will see that you can set a TNS_ADMIN. This was not done earlier and this causes the problem of importing metadata through the wizard.
Solution: you have to set the TNS_ADMIN to an appropriate path such as <biee11>\Oracle_BI1\network\admin. This is the location where your tnsnames.ora is stored.
Afterwards, you have to save the .cmd file and try again to import some metadata in the Administration Tool.
Note: maybe you have to close the Administration Tool or restart your BI-services. For me it was sufficient to restart the Administration Tool and everything was working fine!
Important remark: when you are creating Dashboards & Answers or you are working in Online mode and you want to retrieve some data for a certain table by selecting the option ‘View Data’, you will also get the error: ‘The connection has failed..’. This problem will only occur when your tnsnames.ora is not stored in the Oracle_BI1 directory.
Thanx.. i solved it though
I wish all IT folks had the patience to offer solutions with such clarity. Thanks a bunch!
I was unable to solve the issue .I am using 11.1.1.5 version.
This workaround should also work for the 11.1.1.5 version. Please make sure you have also copied the TNSnames.ora to the BI-directory.
good one….thanks dude
If this solution doesn’t work out, create the TNS entry in the following location C:\OBI-Install\Oracle_BI1\network\admin.
This solution worked for me…I am surprised how you actually found this solution..well done!
nice job
Good job David. I’m able to solve this issue now.
Hi, Neat post. There is an issue along with
your web site in web explorer, would check this… IE
still is the market leader and a big component of
people will omit your wonderful writing because of this problem.
Thank you for your comment. Can you please describe which issues you have encountered during your visit on our blog?
You are awesome. Thanks so much!
Great job. Thanks for sharing. Got the BI Administration connected to my data source.
Hi, would this apply to OBIEE client desktop? I am using the RPD on my desktop to connect to the database schema but only have the client version of OBIEE 11g on my desktop.
Since, it is only a client version, I can’t locate the ‘user.cmd’ or ‘user.sh’ files.
For client desktop installation you can set the TNS_ADMIN via %OBIEE_Client_Home%\Oracle_BI1\network\admin.
e.g.
set TNS_ADMIN=C:\oracle\product\11.2.0\client_1\network\admin
OR
set TNS_ADMIN=C:\Oracle\OracleBIEE11g\Oracle_BI1\network\admin
You can find more information on following support link: https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=PROBLEM&id=1296370.1
Thank You so very much. You have no idea how badly i was stuck at the very step of OBIEE
Thank You so very much. your tips solve my issue in OBIEE metadata connection
Thank you so much … !! 🙂
I am using the OBIEE client tools. I resolved the same issue. But after that i am not able to see the tables listed under the scheam. Please help me out.
Hi,
I’m able to import metadata, but for some tables if i try to view the data its giving error in connection (for other tables its working)… Also after uploading the rpd i’m getting the error
odbc driver returned an error (SQLExecDirectW).
Error Details
Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 42016] Check database specific features table. Must be able to push at least a single table reference to a remote database (HY000)
Pls help me in this regard.