Hi,

We are trying to measuer the performance of our cube. It has more than
50 dimensions.
For every day we create a new partition. After creating partitions for
2 days, the cube processing time increases. e.g for first 2 days day it
took around 15 mins and for the third day it's taking around 40 mins.
The number of rows in the fact table for each of the partitions is
same.

But still on third day onwards the cube processing performance is
degrading. Even after using the optimize schema option, the problem
remains.

We analyses the Analysis services logs. The SQL query is taking more
and more time on subsequent runs.

Can anybody suggest some solution for this problem?

regards,
dattatray

Hi,

What the box do you have?
What version of AS?
How many rows in you fact table?
Do you make separate table for every cube partition or you make view?

Vladimir Chtepa

<dattatrayhkulkarni (AT) gmail (DOT) com> schrieb im Newsbeitrag
news:1138206163.859897.127390 (AT) g49g2000cwa (DOT) googlegroups.com...

also, do you use incremental update?
or full partition process?
how many new members are added in the dimensions each day?
are you in MOLAP or ROLAP mode?

have you identify if its the execution of the SQL generated by AS the
problem?
or the aggregation process slow down?

"Vladimir Chtepa" <vc.nospam (AT) diacom-systemhaus (DOT) nospam.de> wrote

Hi,

The version of Analysis services is 8.00
We are using incremental update.
We are using MOLAP mode. We create a seperate a fact table for each
partition and there are around 3 Million rows in each fact table.

The analysis services logs show that the actual relational SQL query is
taking longer and longer time. We are using HP's hingh end machine
with xeon dual processor 8 GB RAM and 200 GB RAID hard disk array.

regards,
dattatray.



Jéjé wrote:

Hi,



It is pretty strange that SQL query for processing take more and more time.

If you use the schema one partition - one fact table, without any partition
filter, you should have almost constant processing time per partition.



Could you give a bit more information?

What for a query will be used? I would recommend you using of SQL profiler,
if you get troubles with SQL queries.



Vladimir Chtepa



<dattatrayhkulkarni (AT) gmail (DOT) com> schrieb im Newsbeitrag
news:1138281321.767966.257100 (AT) z14g2000cwz (DOT) googlegroups.com...
Hi,

The version of Analysis services is 8.00
We are using incremental update.
We are using MOLAP mode. We create a seperate a fact table for each
partition and there are around 3 Million rows in each fact table.

The analysis services logs show that the actual relational SQL query is
taking longer and longer time. We are using HP's hingh end machine
with xeon dual processor 8 GB RAM and 200 GB RAID hard disk array.

regards,
dattatray.



Jéjé wrote:

Hi,

The queries I am referring are captured from the Analysis services
logs. So while doing the cube processing Analysis services has used
these queries.
I analysed the execution plan for queries on both fact tables.
For the first fact table it's using the "Hash Join" and for the second
fact table it uses the "nested loop" join. The nested loop join query
is executing much slower.
There are no indices on any of the fact and dimension tables.

Is there any way by which we can tell the analysis services that while
doing the cube processing use the "Hash Join" method?

regards,
dattatray.


Vladimir Chtepa wrote:

Try to create indexes on key fields.

<dattatrayhkulkarni (AT) gmail (DOT) com> schrieb im Newsbeitrag
news:1138290202.774055.117310 (AT) f14g2000cwb (DOT) googlegroups.com...
Hi,

The queries I am referring are captured from the Analysis services
logs. So while doing the cube processing Analysis services has used
these queries.
I analysed the execution plan for queries on both fact tables.
For the first fact table it's using the "Hash Join" and for the second
fact table it uses the "nested loop" join. The nested loop join query
is executing much slower.
There are no indices on any of the fact and dimension tables.

Is there any way by which we can tell the analysis services that while
doing the cube processing use the "Hash Join" method?

regards,
dattatray.


Vladimir Chtepa wrote:

also,
verify that your statistics are updated in the database.
bad statistics information result in bad query plan.

"Vladimir Chtepa" <vc.nospam (AT) diacom-systemhaus (DOT) nospam.de> wrote