OBIEE 11G – Error in Importing Metadata

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.

Import Metadata

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.

Edit User Command

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.

About these ads

20 thoughts on “OBIEE 11G – Error in Importing Metadata

    • 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.

  1. If this solution doesn’t work out, create the TNS entry in the following location C:\OBI-Install\Oracle_BI1\network\admin.

  2. 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?

  3. 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.

  4. 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.

  5. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s