I've decided that from now on, consulting project estimates will be padded for an

additional week to get Oracle connectivity working on my dev. machine. Designing an

Analysis Services solution with an Oracle data source should be easy if you are using

the right data provider and have Oracle's drivers installed and configured correctly.

There are three Oracle connectivity options in the BIDS 2008 SSAS project designer:

the .NET provider and two native providers; one from Microsoft and the other from

Oracle. I have yet to see the Oracle provider work -- that leaves two.

If I use the .NET OracleClient provider, everything seems to work just fine. In fact

the only reason I'm not using it is because I learned from Chris

Webb's blog and a chat with Chris on the MSDN forum that Microsoft has discontinued

support and future development. Using the .NET provider, I have no issues designing

the DSV, using relationships or editing named queries. When I switch to the Native

Microsoft provider for Oracle, all hell breaks loose and the DSV designer gives itself

a lobotomy. After making any table definition or named query changes, relationships

to that table are dropped and it starts complaining about data types for no good reason.

Apparently the problem is due to some confusion about the compatibility between the

Oracle Number data type and the Int64 data type used in the DSV.

I can design the DSV using the .NET provider and then switch to the Native provider

with no issues as long as I don't mess with the DSV design again. After an email exchange

with Chris, he confirmed that he and others have come to this same conclusion: use

the unsupported .NET provider for design and then switch to the native provider for

deployment. Thanks to Chris Webb for helping with this.

Weblog by Paul Turley and SQL Server BI Blog.