Hi all,

I am using AS 2000 SP3. I hit a performance issue. The processing time of
one of the cube needs more than 2 hours. After investigation, the critical
path is in the record transfering. The records for this cube is more than 40
million. As I saw, the number of records that AS read is 1000 per batch. Can
anyone give me some advice?

Thanks and best regards,
Francis

Have you looked at all of the best practices outlined in the AS Performance
Guide:
http://www.microsoft.com/technet/pro.../ansvcspg.mspx
it is also discussed heavily in the AS Operations Guide:
http://www.microsoft.com/technet/pro.../anservog.mspx
The general rule of thumb that I use is that a server-class machine using an
optimized schema (using the optimize schema tool in the cube editor) with a
reasonable number of effective aggregates should be able to process about a
million-rows per minute. As with any rule of thumb, your mileage may vary.
Based on the numbers below, you are running about 1/2 of the general number
that I like to see . . . so you may go faster, but you may not. If you have
the available CPUs, you might consider processing in parallel.
--
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.

"francis" <francis (AT) microsoft (DOT) com> wrote

Hi Dave,

Thanks for your advices. The acticle is very useful and I will try to apply
it in my cube design.

On the other hand, I would like to ask is there any option that can change
the batch size in processing? Because AS will retrieve fact and dimension
data from the relational tables. On my situation, the data from the
relational tables is more than 40 millions records. However, the reading
batch size that I can see is only 1,000. Under my observation, AS read
10,000 records per second. So it take around 4 hours to read 40 millions
records. On this situation, the best way to improve the processing
performance is to increase the batch size. So it will be nice if there is
option to tune the batch size.

Thanks and best regards,
Francis




"Dave Wickert [MSFT]" <dwickert (AT) online (DOT) microsoft.com> wrote

AS simply opens up the connection and starts reading. It reads record by
record. There is no similar concept as "batch size". The nearest thing that
we have is a segment size which is 64K records... that is the storage
interval with MOLAP structures.
--
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.

"francis" <francis (AT) microsoft (DOT) com> wrote