All,

I'd appreciate a few words of advice if anyone's trod this path before.

We've got an Oracle 9i database which contains the data for our cube.
There are many reasons for this and we'd rather not have to re-write
various modules to populate a SQL Server instance.

We can connect easily from Analysis Services (2000 SP4) into this mart
and it works well. The problem we have is that if you want to point at a
different Oracle schema (e.g. from dev to production) MSAS stores the
schema name in the cube definition and getting this updated is a real
struggle. We've got a couple of sub-optimal solutions for this (based
around utilities written in C# using the DSO library) but they don't
work particularly well.

Another alternative that we've come up with is to define the various
Oracle tables as views in a SQL Server database via a linked server
(using MSDAORA). This way the cube is built with reference to the SQL
Server views, but we can easily repoint the linked server to different
Oracle schemas with one easy update.

So the deployment problem is solved but we are suffering a serious
performance hit - going through SQL Server on to Oracle is much, much
slower than direct to Oracle.

Has anyone any suggestions as to what we might like to consider to speed
up this solution?

thanks,


- chris.

Two things.

1) Have you tried using the OPENQUERY() function in your views?
OPENQUERY() does a pass through query to a linked server so you may find
it quicker and you will also find that you can use PL-SQL functions in
your query if you need to.

2) Cubes don't link directly to Oracle, they link to a datasource, which
in turn links to Oracle. Although it is annoying that you cannot change
it easily, the name of the data source is irrelevant. You should be able
to change the underlying schema that the data source is pointing to from
Analysis Manager without having to resort to DSO. So even though it is
named after the original schema you can point it at any other schema you
like (even using a different OLEDB provider if you like)

--
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

In article <43283f03$0$38043$bed64819 (AT) news (DOT) gradwell.net>,
unsliced (AT) gmail (DOT) com says...