Oracle Sources on x64 opteron w/64 bit OS & 64 bit SSAS

Hello all..

here is a brief description of my problem.

HP DL585 64 bit Server 2003 R2
x64 (opteron)

Most of our data is housed in Oracle.

When i have a project that sources data from oracle (.net/OracleClient Data Provider) I cannot process the cube.

I found this Document that outlined how to workaround the basic oracle connectivity problem, however it has not fully addressed what I've seen. I worked out how to get the connection to work from within BIDS (by starting from the batch file outlined in the linked page), however I cannot process the cube. I receive the following Error:

Errors in the high-level relational engine. The following exception occurred while the managed IDbConnection interface was being used: Attempt to load Oracle client libraries threw BadImageFormatException. This problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed..

This looks to me as if the 64 bit SSAS service is trying to use the 32bit oracle client installed on the system, however cannot, since service is 64 bit. That makes sense to me. however both the 64 bit and 32 bit clients are installed. So the questions is:

How to i get BIDS to use the 32 bit client, and get the MSAS service (64bit) to use the 64bit oracle client?

Is this approach totally invalid?

Someone please help.

I haven't ever tried building a cube off an Oracle DB, but I have built SSIS packages off Oracle DBs on x64. The following article helped tremendously:

http://stevemchugh.blogspot.com/2007/02/adventures-of-ssis-and-oracle-in-64bit.html

I won't even try to answer your question other than mentioning that article as something to checkout. Not sure if it will be helpful or not.

|||Thanks for the article reference... I learned a little bit about it.

The article deals with SSIS predominantly, and the issues with SSAS seems related but different. The article also describes using the Oracle OLEDB driver, and in my instance i'm using the .net provider, since the microsoft and oracle oledb driver both don't handle numerics with no defined precision, something which, while explicitly state by oracle is not a best practice, seems to occur quite frequently in our environment.

I tried using the oracle oledb driver just to check and no joy . when i use that, i get the following message when i process the cube on the server. again, everything seems to work (locally) from BIDS, just not when processing.

OLE DB or ODBC error: Class not registered.

So I'll have to wait and see if anyone chimes in who has worked through this problem.

-Eric