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
Saturday, October 16, 2010
Oracle 10g Express Edition
Have been installing Oracle 10g server and client on my laptop so I can play around with developing an Analysis Services Cube that can join SQL Server and Oracle tables together.
All going well, though hit a problem that BIDS couldn't connect to the XE database through an Oracle connector or Oracle OLEDB. After a good while googling I gave up but then had a thought. I found the registry setting for the Oracle XE Client and create a TNS_ADMIN entry that pointed to the server install TNSNAMES directory. I'd installed both server and client on my D drive so this worked fine.
All going well, though hit a problem that BIDS couldn't connect to the XE database through an Oracle connector or Oracle OLEDB. After a good while googling I gave up but then had a thought. I found the registry setting for the Oracle XE Client and create a TNS_ADMIN entry that pointed to the server install TNSNAMES directory. I'd installed both server and client on my D drive so this worked fine.
Subscribe to:
Posts (Atom)