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.