I have a historical table that is over 100 million records in size.
Processing this table in AS takes an extremly long time (about 2 hours). Is
there any way to speed this up in AS?

is it 2000 or 2005?
As for the processing, are your cubes optimized (keys from dimensions read
from fact?). In another words, if you look at the select query does it have
any join statements?

MC


[quoted text, click to view]

do you have a distinct count measure in the cube?
(this cause the SQL statement to be sorted by this column)

have you created partitions?
have you optimized the schema in the cube (AS2000)? (the select statement
used use less inner join commands between your fact table and your
dimensions)

does your SQL Server is on another server or the same server?
how many aggregation have you in your cube?
what is your server? (CPU, Memory...)


[quoted text, click to view]

The general rule of thumb is that a common server-class machine with a
reasonable I/O subsystem will do about 1 million rows per minute. That
varies based on many issues, such as the number of aggregates, storage type.
Your experience is a bit low, but not unreasonable.

For hints on how to improve, look at the AS Performance Guide at:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ansvcspg.mspx

Hope that helps.
--
Dave Wickert [MSFT]
dwickert@online.microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


[quoted text, click to view]