We have a problem that the processing time of the cubes increments a
lot within short time
We have 10 cubes with data of 3 years in it. When we first scheduled
all dimensions/cubes to process (about 3 weeks ago); it lasted about 5
hours to process them.

Cubes and dimensions haven't changed since, but every day the
processing time becomes half an hour to 1 hour longer. Now it takes 13
hours to process all dimensions!! This means when we schedule the job,
it is not finished the next day when people start working.
The amount of data is not increased much.

Does anybody have an idea what might be the cause?

Maurice Ars, Pulse

I'd check and see if you're duplicating the data in your underlying
data warehouse tables with some sort of script. I've unknowingly done
this a few times and ended up with too much data.

-Joe

do you do a full process of your dimensions?
do you use partitions in your cubes?
have you added some aggregations in the cubes? (like using the usage based
optimization)

does the problem come from the source database or from the olap server?
I mean, does the slow time come from the read step process or the wrting
process?

for dimensions, generally the problem is related to the source tables in the
database.
do you update your statistics and indexes in the source database?
what's appends if you manually execute the select statement generated by AS
in the database?
for a dimension, the select statement is like:
select DISTINCT column1, column2, column3, .... from Dimensiontable

do you use any RAID? does your disks are ok?
sometimes we can loose a disk but we don't quickly see this failure.


<maurice.ars (AT) planet (DOT) nl> wrote

Thanks for all your suggestions, I surely can do sonething with them in
future.
For now, it seems that the cause is fragmentation of the disk. A
colleague of mine figured that out and cleaning up seems to be the
solution.
The source database was not the problem. We do a full process of the
dimensions and an incremental update of the cubes.
We can't use partitions because the customer of us has the standard
edition; in this edition you can create only 1 partition.

We use RAID and disks are OK (they only were fragmented)