Hi,
Iam trying to build a cube in AS with all the data in
Oracle.
Iam able to create dimensions but not process them. Also
able to select a Fact table and link with the dimension
tables. When I process the cube, Analysis manager hangs
and I have to kill the process.
Iam using Oracle 8i, Windows XP, SQL 2000 service pack
3a. SQL Server and Oracle are installed in the same
machine. The same problem is faced by one of my friends
too and he too has the same configuration except that SQL
and Oracle are on different machines.
If the data is on SQL Server or Excel, I dont have any
problems at all creating a cube.
Has anyone faced this problem before. All help is welcome.

Thanks in advance
Karthik

hi,
did you use the correct relations for all the key columns
in the dimensions ? and one more thing is whichversion of
sql server r u using ? better to use sqlserver enterprise
edition. chk with the datasource connection also. is the
data source connecting ? is it working properly ?

Are you sure all statistics in oracle are up to date? We've found the
same behaviour when Oracle maintains indexes on the tables used for AS
and they database has not rerun analyse tables after loading new data.

HTH,

Coen

Hi,
Since the cube was not getting created, I finally tried
with one dimension table and one fact table.
The Key column was perfect. There was a match for all the
rows with the Key column.
Iam using SQL Server 2000 Enterprise Edition.
Data source is connecting because Iam able to see the
tables in Oracle and select them and create a dimension
(Not process it). I can see the tables in the Cube editor
and see that the join is correct.
Its only when I process the cube that it hangs.
The no of rows in the dimension table and fact table is
less than 1000.
There are no indexes on the 2 tables.
I created the 2 tables in oracle and tried to use AS. So
statistics I dont know whether its up to date. Is it
neccessary to run it because the data is very small (less
than 1000 rows).

-Karthik

I suggest that you try to reproduce the problem interactively. Turn on the
system-wide processing log file (see a section in the Operations Guide for
how to do this
http://www.microsoft.com/technet/pro.../anservog.mspx
) -- this should give you the SQL statement which is issued to Oracle.

Log into the domain account which you've configured the msmdsrv service to
use. Using the Oracle tools, connecct using the same connection string as in
the AS data source. Now issue the SQL statement. What is the result?? You
are in-effect doing the same thing that Analysis Services does when it
processes the partition.

One side note -- you should be careful to run the Optimize Schema tool in
the Cube Editor In both the Operations Guide and in the Performance Guide
(http://www.microsoft.com/technet/pro...n/ansvcspg.msp
x )
, we talk about how important this is for processing performance. This is
how you simplify the SQL statement which AS will issue for processing.
Without running the Optimize Schema tool, there is one join for each
dimension in the cube -- if you have 15 dimensions, then you have a 16-way
inner join that will be issued by default. A best practice is to always run
the Optimize Schema tool to minimize this. This side note is important in
your case because, besides impacting performance, it could also be causing
the system to appear to "hang" as Oracle attempts to process a very complex
query.

Hope this helps.

--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

"Karthik" <anonymous (AT) discussions (DOT) microsoft.com> wrote