Wednesday, January 27, 2010

OBIEE: ORA-12154 could not resolve connect identifier

I'm playing around with OBIEE using the SH schema (sample schema). I've imported the tables successfully, created the logical and presentation layer when I decided to change the driver from ODBC 3.5 to the OCI driver. When I went to run a simple report to test the Times dimension, I received this:

OK, no worries.

I tried to update the row count through the Administration tool and got this:

First I went to Google. Nothing really exciting there, but it did remind me of the OBIEE forums (which I posted to for the first time yesterday). So I did a search there.

Reading through a couple of posts confirmed what I had done.1. tnsping my local database. Check.2. Connect to the local database via SQL*Plus. Check.3. Check environment variables. TNS_ADMIN is setup to point to my one and only tnsnames.ora file on the machine.

Hold up...what about the PATH variable? I've seen before, in my millions of local installations in Windows, where the order of paths will affect how certain things work. Here's what mine looked like (hard returns added for readability):

Hmmm...I wonder if having the database path first is causing this? Why would it though? OBIEE doesn't have it's own tnsnames.ora...so it shouldn't, right? I moved c:\oracle\bin behind the final OBIEE path. Guess what? I was able to connect.

I'm not sure exactly why this happened, I wasn't able (or was just too lazy) to find the exact reason in the forums (I'm sure Christian or Robin will leave me a note), but it worked. Yeah for me.

6 comments:

Is it possible something else changed too and the reboot/restart to pick up the changed PATH also picked up the other thing?

The PATH problem normally shows itself in OBIEE through not being able to find the OCI library files (The specified module could not be found / Failed to load the DLL), which makes sense. But you're getting an ORA error, so it's got to the client it just can't resolve the DSN that you've got in your connection pool with what it's got in its tnsnames.

How exactly is your TNS_ADMIN set? According to TFM, it looks for a registry setting, not an environment variable first.

The ODBC driver uses the OCI driver internally (see MOS 781054.1, which points at 66403.1, so) for 10.2, some software (like patch sets) assumes the ODBC driver is in the oracle home directory. There are dependencies between the ODBC and the sqlnet dll's, which are very version specific.

So I speculate when you switched drivers, you are now using incompatible dll's with the oracle bin first in the path. Moving the bin to after the other directories allow it to find the correct dll first. So that's why you don't get the library not found error, and instead get the wtf error - sqlnet isn't smart enough to give a version mismatch error.

I have two colomuns in bmm layer one is student count and another rfstudent count.

student count gives all the students for the year 2010 whose composite score is 3 and rf student count gives count of all the students whose composite score is in 3,2,1 ,,,,ineed to calculate percentage of these two coloumns