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.
July 27, 2011 at 6:04 pm |
Thanx.. i solved it though
September 13, 2011 at 3:07 pm |
I wish all IT folks had the patience to offer solutions with such clarity. Thanks a bunch!
September 13, 2011 at 4:33 pm |
I was unable to solve the issue .I am using 11.1.1.5 version.
September 13, 2011 at 4:56 pm |
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.
November 18, 2011 at 8:20 pm |
good one….thanks dude
December 15, 2011 at 10:13 pm |
If this solution doesn’t work out, create the TNS entry in the following location C:\OBI-Install\Oracle_BI1\network\admin.
December 27, 2011 at 12:07 pm |
This solution worked for me…I am surprised how you actually found this solution..well done!
March 8, 2012 at 12:04 pm |
nice job
June 21, 2012 at 7:22 pm |
Good job David. I’m able to solve this issue now.
June 29, 2012 at 4:44 am |
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.
July 2, 2012 at 3:08 pm |
Thank you for your comment. Can you please describe which issues you have encountered during your visit on our blog?
July 5, 2012 at 5:28 pm |
You are awesome. Thanks so much!
August 27, 2012 at 1:49 pm |
Great job. Thanks for sharing. Got the BI Administration connected to my data source.
September 4, 2012 at 7:36 pm |
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.
September 26, 2012 at 7:28 am |
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
September 24, 2012 at 8:15 pm |
Thank You so very much. You have no idea how badly i was stuck at the very step of OBIEE
October 13, 2012 at 5:39 am |
Thank You so very much. your tips solve my issue in OBIEE metadata connection
November 28, 2012 at 6:28 pm |
Thank you so much … !!
December 5, 2012 at 10:56 am |
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.