Cube with oracle + sql server data

Hello,

I have a cube with 1 fact table from SQL Server and 1 dimension from Oracle.

when I process the cube I get this error:

OLE DB error: OLE DB or ODBC error: Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle.1" for linked server "(null)".; 42000; The OLE DB provider "OraOLEDB.Oracle.1" for linked server "(null)" reported an error. The provider did not give any information about the error.; 42000.

when I remove the fact table of SQL Server and replaced it with a fact from oracle, the cube will process fine.

The system specs is:

Windows 2003 sp1 X64 (AMD CPU)

SQL Server 2005 SP1

Oracle 10.02g with support to 64 bit.

Thanks in advance,

Yoav.

My guess about your situation:

When you trying to create a cube that is based on 2 datasources at some point Analysis Server is trying to figure out better way to construct SQL queries against the relational database.

In some complex situations it might decide it cannot send queries separately to each data source. At this moment Analysis Server constructs a query using OPENROWSET semantics. So it would send a query to SQL Server asking for partial results brought from Oracle. You can trace these SQL queries in your processing dialog. And you'd see OPENROWSET that is failing for you.

There are several solutions. For one you can define linked server in SQL Server pointing to Oracle table/s. This way you can make sure you test connectivity to Oracle ahead of time instead of relying on OPENROWSET. This solution also eliminates the need for 2 datasources.

Second, you can look at your cube design, and try to simplify your measure group-dimension relationships so Analysis Server wouldnt be sending OPENROWSET.

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