Oracle tricks

Hi there,

We have a BI application that uses SSAS 2005 cubes. This application needs to work with either an Oracle 9i or SQL Server 2000 data mart. Unfortunately, when connecting to Oracle, the Data Source Views in the SSAS project start complaining because of the way Oracle references tables (Schema.TableName etc., compared to SQL which is just TableName).

Are there any tricks to getting a single SSAS 2005 project to work against either an Oracle 9i data mart or a SQL 2000 data mart?

We would really like to avoid having to create and maintain two SSAS projects.

Thanks,

--Phil

I have not tried this, but if you could set up a "dbo" schema in oracle it might work as SQL2000 has a "default schema" of dbo, so dbo.<tablename> should work in both.

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 Provider Issue

Hi all,

On the dev server, I'm using SSAS 2005 (32-bit) + Oracle provider for OLE DB to access my Oracle Database and it works perfectly.

On the production server, SSAS 2005 is running on a 64-bit platform.

The Oracle client 10.2.0.1.0 (64 bit) has been installed containing the Oracle provider for OLE DB but unfortunately the Oracle provider for OLE DB does not appear in the list in BIDS when trying to create a date source.

Could someone help ?

Regards,

Jean-Luc

I remeber a similar problem. It just could be just a problem of showing provider in tools.

Try authoring your connection on 32 machine and deploy it to 64bit machine. See if Analysis Server can connect to Oracle.
If not try using .NET Oracle client.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Oracle DSV: Problems creating relationships with reported inconsistent datatypes.

I am adding tables to the DSV and adding the relationships but SSAS keeps complaining that the data types of the FK and PK tables do not match, even though I can see that they are in fact the same, ie NUMBER with no scale/precision set. Presumably this means Oracle uses a default?

Any ideas as to the fix? I have created a NamedQuery as a SELECT * FROM <table> and this seems to work but does seem to be a hack and is very annoying!

Searching connect, I found at that this is a bug. however, it was reported as fixed in SP1 but I am using SP2 CTP.. so I have added a new bug report for this under SP2.

the only workaround is to go back and edit the XML file. (View Code in the solution explorer context menu).

Oracle Data Source Problem: Cannot connect during deployment. Help!

I am developing an SSAS project which looks at an Oracle database but I cannot for the life of me get it to process.

This is one of those awful Oracle connectivity problems!

Now, in the DataSource, I can edit and the test connection works perfectly. Every other application can connect to the Oracel server correctly. The problem comes when I try to deploy my project. I get errors left right and centre. They even change between attempts! Most common is TNS name resolution error:

Error 1 OLE DB error: OLE DB or ODBC error: ORA-12154: TNS:could not resolve the connect identifier specified. 0 0

Yet, as I have said, when I test the connection everything is OK. And I can use the same connection details in every other application. What is so different about the deployment operation that means it cannot find the connection configuration?

I have tried all the providers, impersonation etc. FYI: the main method for getting the Oracle connection details is through LDAP.

OK.. I found a work around.. I put the connection details directly into the tnsnames.ora file and everything went smoothly again!

So, why does the deployment processing not look at sqlnet.ora when everything else does, including the UI/designer? I am just worried things might get difficult in a production environment.

The other interesting thing is that this has worked from my other machine, so their must be some Oracle config somewhere? Any Oracle connectivity experts out there?

|||

If you running 64 bit be aware of this thread as well --> http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=159581&SiteID=1