March 22, 2015 at 4:50 pm
Hi,
I've got a user who is building a cube that has both SQL Server & Oracle data sources. Tables from both sources are added to the cube and measures from both are added, you can Explore data from tables in both data sources and everything appears to be working fine but when you attempt to Process the cube it fails with the following error ...
21 Internal error: The operation terminated unsuccessfully
22 Errors in the high-level relational engine. The following exception occurred while the managed IDbCommand interface was being used: ORA-0942: table or view does not exist.
23 Errors in the OLAP storage engine: An error occurred while processing the 'SQLServerTableName' partition of the 'SQLServerTableName' measure group for the 'CubeName' cube from the BlahBlah database.
24 Server: The current operation was cancelled because another operation in the transaction failed.
It appears to be attempting to query the SQL Server table on the Oracle connection and is returning an Oracle error.
I've run a trace on the SQL Server side and I can see SSAS connecting to SQL Server but it doesn't attempt to query the table.
We can build and process cubes from each source individually but not from both sources in the same cube. I've tried simplifing the cube itself, just including a single table from each connection and we're having the same problem.
Any ideas?
Edit: Additional System Info
SSAS 2012
Oracle 11
March 22, 2015 at 6:28 pm
Smells a little like a security issue - have you checked the identity that will be used to query the Oracle datasource during cube build? It's been a while since I looked but pretty sure the view source data function uses *current user* credentials (ie you) but the build process users a specific credential (typically user the service is running under).
Steve.
March 22, 2015 at 6:43 pm
Ok, I think I've tracked down part of the problem, it appears the user set the Primary Data Source as the Oracle Connection.
Apparently SSAS will perform a remote query from the Primary Data Source to the Secondary using OPENROWSET to get the Secondary Data Source data.
'Add a Secondary Data Source' section in the link here ... https://technet.microsoft.com/en-us/library/ms174600(v=sql.110).aspx
This explains why when Processing the cube it was returning an Oracle Error when trying to query the SQL data.
The Primary data source has been changed to the SQL Server connection and now we're getting a different but equally frustrating error. :hehe:
Cheers
Edit: New Errors
Internal error: The operation terminated unsuccessfully
Errors in the high-level relational engine: The 'OracleDataSource' secondary data source is not a relational data source, or does not use an OLE DB provider.
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'DimBlah', Name of 'DimBlah' was being processed.
Both data sources are currently setup using .Net Providers, from the error above it sounds like they can't be used and we need to revert to using OLE DB. We don't currently have OLE DB Drivers for Oracle setup on the server so we're currently battling with getting that to work, then attempting to Process the cube once that is working.
Can anyone confirm that using OLE DB Providers is required for this?
June 10, 2016 at 2:06 am
We are having the same exact issue :
We switch to : OLEDB Provider for Oracle , but now we are having this new error message:
Error3MSDORA.1 is not registered
Were you guys able to solve the problem ? Thanks for your help !
June 10, 2016 at 3:24 am
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply