Issue –

My BI developer informed me; They are no longer able to connect oracle data sources they have created in their cube.

Troubleshooting – As I am aware; In order to connect to Oracle; you should have oracle client installed properly.

As it was working earlier hence my first impression was; they definitely have changed something on server which causing this issue but their response was none.

So, without getting into IT blame game I decided to troubleshoot.

I checked Linked Server but it was not working.

I tried to create OLEDB connection with native oracle client and .Net oracle provider but no luck

As it was development environment there were many Oracle client was installed and after various troubleshooting attempts; I am able to resolve the issue.

Though during 12 hr. of troubleshooting I have encountered various error messages so thought to list down all so if any of you facing these error messages you might need to do what I did to resolve the issue.

Error messages 

Test connection failed because of an error in initializing provider. System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.

Test connection failed because of an error in initializing provider. 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.

Test connection failed because of an error in initializing provider. The ‘OraOLEDB.Oracle.1’ provider is not registered on the local machine.

Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of xxx
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of ‘xxx’, Name of ‘xxx’ was being processed.
Errors in the OLAP storage engine: An error occurred while the ‘xxxx’ attribute of the ‘Dim Product’ dimension from the ‘xxx’ database was being processed.
Server: The current operation was cancelled because another operation in the transaction failed

 Resolution – In simple word you need both 32 bit oracle client and 64 bit oracle client installed on your SQL Server machine but in my case it only worked when

1. I have removed all the clients;
2. Cleaned the registry entry
3. Removed all the Client entry desktop icons 
4. Delete all the previous installed dirs.

Once done – Reboot the server; After reboot

1. Install 64 bit oracle client ( in my case I installed oracle 12.2.0)
2. Install 32 bit oracle client ( ensure version is same i.e 12.2.0 in my case )
3. Update the tnsname.ora file ( I copied from different oracle server )
4. Reboot the server

You can install oracle client from below location –

Oracle Database 12c Release 2 Client (12.2.0.1.0) for Microsoft Windows (32-bit)

Oracle Database 12c Release 2 Client (12.2.0.1.0) for Microsoft Windows (x64)

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/oracle12c-windows-3633015.html