Using Oracle data with Microsoft Business Intelligence

Before I joined Microsoft, I was working on a large project to build SQL Server 2005 analysis services cubes from data in Oracle 10g.  The fun we had trying to get the data out and setup the servers! 

Getting the OLEDB drivers and the Oracle client tools working in a 64-bit environment is at the heart of this issue and so I was very pleased to see a simple post by Jorge Perz Campo (a Spanish Microsoft support engineer) explaining what needs to be done.  Don’t be confused that this talks about Integration Services as they both use the same drivers to access Oracle data.

The core of the problem is that you need to use the 32-bit drivers to develop, because the BI Development studio uses them, and 64-bit drivers to run in production.  It all gets properly sorted out at runtime as the appropriate driver is used automatically by integration and analysis services.

One thing to note is that in any 64 bit version of Windows 32 bit apps get put into Program Files (x86) while the true 64 bit apps install to Program Files.  Some Oracle  software doesn’t like paths with brackets so the 32 bit version needs to be installed to something like Program files X86 instead. This issue is discussed on the Oracle forums here.

Technorati Tags: Oracle,SQL Server,Buisness Intelligence,Integration Services. DTS,Analysis Services