I’m looking to use BO to access a number of different data sources (all Oracle db’s) but present only one universe to the user. Unfortunately there is no opportunity to use a data mart etc and synchronising data within reports from multiple sources will be too complex for most users. Therefore the problem is how to access multiple data sources from one universe.
The solution I’m looking at is using Oracle views from one db to access the required tables from the other db’s. I can then build the universe based on the tables from my core db and the views on the other db’s thus presenting a universe to the users which looks like the data is all coming from the one source.
Has anyone used this approach before ? What are your experiences ? I’m concerned about the performance of the views. I realise they will have to be tuned and the appropriate db tables indexed accordingly to achieve optimum performance. Are there any other issues to look out for ?
Thanks in anticipation.
Neil
Neil Bain
EDS,
Royfold House,
Hill of Rubislaw,
Aberdeen,
AB15 6GZ.
In a message dated 98-07-16 12:52:01 EDT, you write:
[ snip ]
The solution I’m looking at is using Oracle views from one db to access
the required tables from the other db’s. I can then build the universe based on the tables from my core db and the views on the other db’s thus presenting a universe to the users which looks like the data is all coming from the one source.
Has anyone used this approach before ? What are your experiences ? I’m concerned about the performance of the views. I realise they will have to be tuned and the appropriate db tables indexed accordingly to achieve optimum performance. Are there any other issues to look out for ?
Thanks in anticipation.
Neil
Neil:
You need to be very careful to investigate the performance of this solution. I am assuming that you are using database links in your views to access these other data sources?
In my last experience with this strategy (Oracle 7.1, so things may have changed some) a “remote” query does not make effective use of “remote” indexes. Put another way: the Oracle optimizer that evaluates the SQL does not have access to the system tables across the database link, and therefore does not necessarily do an appropriate query plan. I should point out that this problem was when a single view included tables from both the “local” and a “remote” database, so that contributed to the problem.
If your views are constructed solely from the “remote” database, then hopefully the remote database will process (optimize) the view SQL and perform accordingly. Just be aware that joins between local and remote data could be painful.
Let us know how it works! I would certainly be interested in hearing about it.
Regards,
Dave Rathbun
Integra Solutions www.islink.com See you in Orlando in '98!
In a message dated 98-07-16 12:52:01 EDT, you write:
[ snip ]
The solution I’m looking at is using Oracle views from one db to access
the required tables from the other db’s. I can then build the universe based on the tables from my core db and the views on the other db’s
thus
presenting a universe to the users which looks like the data is all coming from the one source.
Has anyone used this approach before ? What are your experiences ? I’m concerned about the performance of the views. I realise they will have to be tuned and the appropriate db tables indexed accordingly to
achieve
optimum performance. Are there any other issues to look out for ?
Thanks in anticipation.
Neil
Neil:
You need to be very careful to investigate the performance of this solution. I
am assuming that you are using database links in your views to access these
other data sources?
In my last experience with this strategy (Oracle 7.1, so things may have changed some) a “remote” query does not make effective use of “remote” indexes. Put another way: the Oracle optimizer that evaluates the SQL does not
have access to the system tables across the database link, and therefore does
not necessarily do an appropriate query plan. I should point out that this problem was when a single view included tables from both the “local” and a “remote” database, so that contributed to the problem.
If your views are constructed solely from the “remote” database, then hopefully the remote database will process (optimize) the view SQL and perform
accordingly. Just be aware that joins between local and remote data could be
painful.
[Charles Killam] It is my experience that views are slower for response times to begin with. I have been involved with the same type of effort using synonyms and the response time was also slow. This is something you MUST consider. There are lots of solutions for a problem, how do he solutions weigh against ALL of the requirements.
Another option that can work well is snapshots - this is of course dependant on whether the requirements are for immediately current data and/or how you trigger the refresh of the snapshot.
We’ve used this approach a number of times (Oracle 7.3) and the optimization of remote queries is a lot better then with Oracle 7.1.
Only one little warning up front. Don’t use synonyms i.s.o. views on the remote tables, since you won’t be able to see the columns in designer then. BO uses Oracle API calls to fetch this information and there isn’t a way to tell Oracle that you are looking for information from a remote table.
Greetings,
Marianne Wagt-van Loenen
IDETA, the Netherlands