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

Oracle connection from a SQL2005 Ent. Edt. X64 cluster

Hi
Does anybody know if there is a problem/issue with retrieving data from
Oracle on a SQL2005 Cluster? My problem is that I don't seems to have
the MSDAORA provider like I have on my other servers.
I don't recall having ever seen this not being present in a SQL
installation, so I'm wondering if this has something to do with the fact
that it's a Cluster install?
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator""Steen Schlüter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote in
message news:uAWzvoJYHHA.4232@.TK2MSFTNGP05.phx.gbl...
> Hi
> Does anybody know if there is a problem/issue with retrieving data from
> Oracle on a SQL2005 Cluster? My problem is that I don't seems to have the
> MSDAORA provider like I have on my other servers.
> I don't recall having ever seen this not being present in a SQL
> installation, so I'm wondering if this has something to do with the fact
> that it's a Cluster install?
>
There is no 64bit version of the MSDAORA provider. You will need the OleDB
Provider from Oracle. Get version 10.2.0.2.
David|||David Browne wrote:
>
> ""Steen Schlüter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote
> in message news:uAWzvoJYHHA.4232@.TK2MSFTNGP05.phx.gbl...
>> Hi
>> Does anybody know if there is a problem/issue with retrieving data
>> from Oracle on a SQL2005 Cluster? My problem is that I don't seems to
>> have the MSDAORA provider like I have on my other servers.
>> I don't recall having ever seen this not being present in a SQL
>> installation, so I'm wondering if this has something to do with the
>> fact that it's a Cluster install?
> There is no 64bit version of the MSDAORA provider. You will need the
> OleDB Provider from Oracle. Get version 10.2.0.2.
> David
Hi David,
Thanks for the info. I'll try to get it working with the Oracle OLEDB
provider. I've never used that one, so do you by any change have any
links that tells about how to install/configure it?
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||""Steen Schlüter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote in
message news:e1CkGmMYHHA.4232@.TK2MSFTNGP05.phx.gbl...
> David Browne wrote:
>>
>> ""Steen Schlüter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote
>> in message news:uAWzvoJYHHA.4232@.TK2MSFTNGP05.phx.gbl...
>> Hi
>> Does anybody know if there is a problem/issue with retrieving data from
>> Oracle on a SQL2005 Cluster? My problem is that I don't seems to have
>> the MSDAORA provider like I have on my other servers.
>> I don't recall having ever seen this not being present in a SQL
>> installation, so I'm wondering if this has something to do with the fact
>> that it's a Cluster install?
>>
>> There is no 64bit version of the MSDAORA provider. You will need the
>> OleDB Provider from Oracle. Get version 10.2.0.2.
>> David
> Hi David,
> Thanks for the info. I'll try to get it working with the Oracle OLEDB
> provider. I've never used that one, so do you by any change have any links
> that tells about how to install/configure it?
>
Oracle Provider for OLE DB
http://www.oracle.com/technology/tech/windows/ole_db/index.html
David|||David Browne wrote:
>
> ""Steen Schlüter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote
> in message news:e1CkGmMYHHA.4232@.TK2MSFTNGP05.phx.gbl...
>> David Browne wrote:
>>
>> ""Steen Schlüter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk>
>> wrote in message news:uAWzvoJYHHA.4232@.TK2MSFTNGP05.phx.gbl...
>> Hi
>> Does anybody know if there is a problem/issue with retrieving data
>> from Oracle on a SQL2005 Cluster? My problem is that I don't seems
>> to have the MSDAORA provider like I have on my other servers.
>> I don't recall having ever seen this not being present in a SQL
>> installation, so I'm wondering if this has something to do with the
>> fact that it's a Cluster install?
>>
>> There is no 64bit version of the MSDAORA provider. You will need the
>> OleDB Provider from Oracle. Get version 10.2.0.2.
>> David
>> Hi David,
>> Thanks for the info. I'll try to get it working with the Oracle OLEDB
>> provider. I've never used that one, so do you by any change have any
>> links that tells about how to install/configure it?
> Oracle Provider for OLE DB
> http://www.oracle.com/technology/tech/windows/ole_db/index.html
> David
>
Thanks David - I'll give it a try.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator

Oracle connection from a SQL2005 Ent. Edt. X64 cluster

Hi
Does anybody know if there is a problem/issue with retrieving data from
Oracle on a SQL2005 Cluster? My problem is that I don't seems to have
the MSDAORA provider like I have on my other servers.
I don't recall having ever seen this not being present in a SQL
installation, so I'm wondering if this has something to do with the fact
that it's a Cluster install?
Regards
Steen Schlter Persson
Database Administrator / System Administrator""Steen Schlter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote in
message news:uAWzvoJYHHA.4232@.TK2MSFTNGP05.phx.gbl...
> Hi
> Does anybody know if there is a problem/issue with retrieving data from
> Oracle on a SQL2005 Cluster? My problem is that I don't seems to have the
> MSDAORA provider like I have on my other servers.
> I don't recall having ever seen this not being present in a SQL
> installation, so I'm wondering if this has something to do with the fact
> that it's a Cluster install?
>
There is no 64bit version of the MSDAORA provider. You will need the OleDB
Provider from Oracle. Get version 10.2.0.2.
David|||David Browne wrote:
>
> ""Steen Schlter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote
> in message news:uAWzvoJYHHA.4232@.TK2MSFTNGP05.phx.gbl...
> There is no 64bit version of the MSDAORA provider. You will need the
> OleDB Provider from Oracle. Get version 10.2.0.2.
> David
Hi David,
Thanks for the info. I'll try to get it working with the Oracle OLEDB
provider. I've never used that one, so do you by any change have any
links that tells about how to install/configure it?
Regards
Steen Schlter Persson
Database Administrator / System Administrator|||""Steen Schlter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote in
message news:e1CkGmMYHHA.4232@.TK2MSFTNGP05.phx.gbl...
> David Browne wrote:
> Hi David,
> Thanks for the info. I'll try to get it working with the Oracle OLEDB
> provider. I've never used that one, so do you by any change have any links
> that tells about how to install/configure it?
>
Oracle Provider for OLE DB
http://www.oracle.com/technology/te...e_db/index.html
David|||David Browne wrote:
>
> ""Steen Schlter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote
> in message news:e1CkGmMYHHA.4232@.TK2MSFTNGP05.phx.gbl...
> Oracle Provider for OLE DB
> http://www.oracle.com/technology/te...e_db/index.html
> David
>
Thanks David - I'll give it a try.
--
Regards
Steen Schlter Persson
Database Administrator / System Administrator

Oracle and unicode

Since the .net oracle providers are the only ones that work in x64 it is extremely annoying that it insists on mapping ALL varchar columns in oracle to wstring. Is there a way to fix this? The oledb providers don't have this problem but I don't like being forced to use the 32 bit runtime as a workaround.I remember that this has already been discussed:
I was told, that all .NET providers return UNICODE text. You have to do an explicit data conversion with ALL your fields.

@.Donald Farmer: Since you've said, that when developing SSIS it was meant to avoid implicit data conversion due to performance issues you've had with DTS, I'm wondering how this paradigm copes with this?

@.Brent: Sorry I cannot help...|||

Sorry, I didn't see that post.

I'm just having a hard time explaining to my developers why there's 18 different workarounds to get oracle to work in the first place. The last thing they want to do is explicitly map 150 different columns.

|||

Brent Mills wrote:

Since the .net oracle providers are the only ones that work in x64 it is extremely annoying that it insists on mapping ALL varchar columns in oracle to wstring. Is there a way to fix this? The oledb providers don't have this problem but I don't like being forced to use the 32 bit runtime as a workaround.

i'm not sure if this will help, but openlink has a 64 bit ole db to obdc provider: http://uda.openlinksw.com/oledb/st/oledb-odbc-st/