Seeking Oracle

Getting two mega vendors to work together – always fun. On a positive note, the experience further reinforced my love for SQL Server.

Scenario: Installing Oracle 11g 64-bit and 32-bit clients on Windows Server R2 64-bit machine for both development and testing. As you would recall, BIDS (aka SSDS in SQL Server 2012) is a 32-bit application. So, is PowerPivot if it runs in 32 bit Excel. On the other hand, the server products, such as SSRS and SSAS, are x64. So, the changes are that you’ll need both the 32-bit and 64-bit OLE DB Oracle providers since Microsoft discontinued its Oracle OLE DB provider support.

Issue 1: It looks like this deployment scenario wasn’t on the Oracle support radar and probably was never tested. First of all, based on previous experience, you need to install both the 64 bit and 32 bit full Oracle clients with the Administrator option. Other options didn’t work for me. However, the 64-bit client install completes fine but the 32-bit client fails. Once the 32-bit setup goes to the Finish step, it just vanishes into a thin air, probably as a result of an unhandled exception. No errors in the Oracle inventory logs or anywhere else.

Resolution: How do we get the 32-bit client to actually install SxS with the 64-bit client? A registry hack of course, because it tries to use the 64-bit inventory folder (C:\Program Files\Oracle\Inventory) and this is not allowed. So, open regedit and navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE. Change the inst_loc setting from C:\Program Files\Oracle\Inventory to C:\Program Files (x86)\Oracle\Inventory, as follows:

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE]

inst_loc=C:\Program Files (x86)\Oracle\Inventory

Issue 2: Now that we got the 32-bit client installed, what do we get in BIDS when we attempt to set up a data source to Oracle? The Oracle OLE DB provider is missing.

Resolution: Run the Oracle 32-bit client a second time but choose the Custom option. Then, select the Oracle OLE DB provider to get it registered properly.

Now we get the Oracle OLE DB Provider to show in BIDS and we should be able to connect in both x32 and x64 clients and everything should just work nicely, right?

Issue 3: After all of this setup extravaganza, what speed do you get when reading rows from Oracle in SSAS or PowerPivot? About 20,000 rows/sec. Now, SSAS is capable of processing 100K+ rows/sec on a single thread so something is wrong here. I got the same pathetic speed with different customers so the issue is probably with the OLE DB provider. I tried a few things but I couldn’t get better performance. I’m hearing that Oracle drivers from other vendors (DataDirect and Attunity were specifically mentioned) have better performance but I haven’t tried them. Let me know if you have a better luck.