Case Sensitivity

I'm sure this can be changed by your friendly SQL Server DBA but worth noting that, by default, SQL Server databases are case insensitive whereas with Oracle you do need to distinguish between your P's and your p's!

For example when searching for the word 'Tower', the following line

SELECT *
FROM MyTable
WHERE name LIKE '%ToWeR%'

will bring back the row in SQL Server but NOT in Oracle.

Analysis Services for Federated BI

Having struggled on my first attempt I tried a second time to build an AS cube over Oracle and SQL server data and get it all to knit together. There are a few gotchas but here is the basic step by step guide:
1. Create a SQL server data source
2. Create an Oracle Data Source (I used Oracle OLEDB)
3. Create a DSV for the SQL server objects
4. Create a seperate DSV for the Oracle objects

* Note that putting both sets of objects in a single DSV seems to create an issue where the cube tried to access the Oracle objects through the SQL server database using a linked server - slightly defeating the overall purpose..

5. Create 2 cubes - one from each data source and get your dimensions how you'd like them
6. Identify an Oracle dimension that would be useful, and will join, to a measure group in the SQL server cube
7. Open up the SQL server cube and go to 'dimension usage'
8. Right click to add a linked objects and choose the Oracle cube
9. A linked measure group will appear and you should now be able to join the SQL server measure group to the Oracle dimension

* Note that this does not seem to work the other way round (ie adding the SQL server measures group as a linked measure group to the Oracle cube)
* Also note you may battle with type when joining but change types in DSV named queries if you have to

10. You may want to input any missing relationships between the cubes as 'many to many' relationships
11. Process the cube and query both measures groups using either dimensions