Seriously, I cannot process any new data, and the customer cannot see what
has already been processed, so, soon they will just give up.
I'm reposting the problems because of no response to previous posts.

Here's the situation:
OLAP server (sp3) running on W2K3, 4 proc, 4GB RAM, plenty of space.
Data warehouse is Oracle 9i, and I'm using the MS OLEDB Provider for Oracle
driver.
The cube is divided into partitions, one per day. About 45 partitions
currently exist.
The fact table for the cube has about 364,000,000 rows.

Here are the problems:
1. I cannot process new partitions. When I try, Analysis manager becomes
unresponsive after doing the query to the Oracle db for data. It never
recovers. Same result when run through DTS. This seemed happen more
frequently starting after 20-30 days of data went in. Now it happens 100% of
the time.

2. I cannot connect via http to the OLAP machine. IIS is installed on the
machine and everything is set up correctly, I can see msolap.asp from another
machine, but when I use http://xx.xx.x.xxx as the data source in the MDX
sample app becomes unresponsive and never connects.

This is pretty much my last chance. After this, we'll probably move to
Oracle's version of OLAP which I do not want to do.

Thanks,
ADAM

Just a sanity check: the fact table should be indexed on the date that
is used for partitioning, else processing per partition may take
linearly longer as more data is added
(which is expected with a table scan)...


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Yes, the table is indexed (& partitioned) on that date column.
One thing I did notice, by accident:
I process partitions by day, I last night I accidently removed the date
portion of the filter clause. Processing took 13 minutes (as opposed to 45
minutes when things worked well). The data appears correct. So this leads
me to this question: If the partition has slice values indicating date, do I
have to specify the date in the filter clause as well? Or is specifying in
both places causing the query to take forever?

"Deepak Puri" wrote:

You don't need to specify the date in the partition filter, if the slice
already specifies it, since the Analysis Server will constrain the date
in the fact table query.

Why not specifying the filter would speed up processing - maybe the
addition of a redundant "where" condition slows down the query - I'm not
sure. But you can examine the SQL trace for this query when you process
a cube partition.


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

One of the best practices for Analysis Services it to turn on the
system-wide processing log file, see:
http://www.microsoft.com/technet/pro.../anservog.mspx
Using it, you can capture the exact SQL statement which is issued to your
data source.

From the processing log file, you will see that if you set the partition
data slice then you do not also have it in the WHERE clause as a filter.
Analysis Services automatically adds it for you. If you don't want it to,
there is a registry setting you can set to disable it, but by-default it is
always added for you.
--
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.

"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote

Thanks for the tip, Dave - I'll try using the processing log in future,
to determine the fact table SQL query...


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!