I have a data cube which originally contains data starting from 2004/1/1 up
to yesterday. The processing time is about 3 hours. Recently, the time
required for processing is increased dramatically. It required about 9 to 10
hours to process. For me, it is not acceptable. I have not changed any
setting in my fact table or data cube.
Does anyone encounter this problem? How to solve it? Please help me! Thanks!

do you use AS2000 or 2005?
do you have the std or ent edition? (so do you have partitions?)

have you try to reaggregate the cube based on the new volume?

AS2000: what's appends if you stop/restart the service before the loading?

have you identify if the processing time come from the cube aggregation or
from the database reading step?
maybe the problem is related to the source database and not the aggregation
process.
(if its the case, defrag the indexes, and recalculate the statistics of the
source database can help you)


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

Hi! Jeje,

Thanks for your quick response.
We are using AS2000 Enterprise edition.

We only got one partition. Since past 1 month data is updated by users, we
always full process the dimension and data cube. I am not sure whether we can
create paritions for cube and then only full process past 1 month data
partition.

We have tried to reaggregate the cube based on the new volume. However, it
doesn't work.

I think that the problem may not be related to data source coz I have tested
different periods of data. It works fine when the data set consists of 1.5
years of data. If the data set has data more than 1.5 years, the processing
time is increased dramatically.

"Jeje" wrote:

Polly wrote:

It would be good if you do partitions. There is no need to partition
whole data every time.

Regards
Amish Shah

Hi! Amish,

We wanted to do partition a few months ago. However, we don't know how to
automate the process. We would like to keep 1 month data for full process.
The remaining data is kept unprocessed. We do not want to do merging and
partiiton process manually daily. Do you have any information on how to do
that?

"amish" wrote:

before going into partitions etc...
you have to identify the performance issue problem. (loading time or
aggregation time)

if you do a full process of the dimensions then partitions will not really
helps you (or just a little) because you have to do a full process of all
the partitions too!!!
but you can create partitions on other axis then the time axis
like 1 partition by store (or any relevant dimension)

if you create partitions on the most common and used axis you'll optimize
the query time too.

do you have a distinct count cube?
we have found a problem with cubes using a DCount measure, if we add
aggregations in this cube (using the usage based optimization wizard for
example) this can cause a processing issue.
a process which takes few minutes jump to hours!!!!
in fact, the server CPU do this: 100% for 10sec. then 5% for 2 minutes, then
100% for 10sec. and again 2-3minutes at 5% etc.... instead-of 100% during
the complete processing time. and the "2minutes slow part" increase as the
processing advance... (so the slow part is few second at the beginning of
the process and growth to 10minutes near the end of the process)
The solution was to recreate the aggregations of the cube.
(maybe the real issue is not related to the added aggregations; but this can
help you)

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

Hi! Jeje,

Thanks for your reply.

In my cube, there is no distinct count measures or calculated member.

Based on my reply to Amish, do you think that we can do update in olap cube
instead of full process?

I have collected some information from package log.

Before slow processing preformance, information in the package log is
"Writing data of Partition 'Item Sales' (segment 1). Row processed: 3532000.
Writing data of Partition 'Item Sales' (segment 2). Row processed: 3404000.
Writing data of Partition 'Item Sales' (segment 3). Row processed: 3106969.
Writing data of Partition 'Item Sales' (segment 4). Row processed: 0.........
Writing aggregation and indexes of Partition 'Item Sales' (segment
1)....Writing aggregation and indexes of Partition 'Item Sales' (segment
150).....Duration:0:26:28. Row Processed: 10042969...."

After slow processing preformance, information in the package log is
"Writing data of Partition 'Item Sales' (segment 1). Row processed: 3270000.
Writing data of Partition 'Item Sales' (segment 2). Row processed: 3270000.
Writing data of Partition 'Item Sales' (segment 3). Row processed: 3269000.
Writing data of Partition 'Item Sales' (segment 4). Row processed:
255771.........
Writing aggregation and indexes of Partition 'Item Sales' (segment
1)....Writing aggregation and indexes of Partition 'Item Sales' (segment
151).....Duration:7:41:47. Row Processed: 10064771...."

Do you think that there is problem in segment 4? How do I control that
segment?

you can't control this directly
but you can play with the memory config of your server.
if you improve the processing memory option, maybe you can improve the
performance.

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

Where can I set processing memory option? Thanks!

"Jeje" wrote:

take a look at the property page of the server himself
(right click on the server then properties)

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