Thanks for the response. That Performance Guide was quite helpful for
understanding many aspects of Analysis Server. Unfortunately, I
wasn't able to use it to improve the performance of our system. We
are constantly creating new partitions (with new data) and merging
them in. Essentially doing the incremental cube updates ourselves.
So in our case, we need to worry about three times: process time,
merge time, and query time. It seems like anything I do to speed up
one of those will slow down another one of those times. Using
aggregations is great for query times, but processing and merging of
the new partitions became prohibitively slow. Any suggestions for
changes that might increase performance across the board?

OnlyForPostingToNewsgroups (AT) hotmail (DOT) com (Chris Webb) wrote in message news:<48d9285e.0403180153.5c0f512e (AT) posting (DOT) google.com>...

Hi David,

Out of curiosity, why are you merging the partitions after processing them?
Typically, with larger systems, I leave the partitions in place and process
the partitions directly which will result in faster processing performance
(e.g. if partition by day, only process one day of data) and faster query
performance (i.e. more threads are utilized across multiple partitions which
helps query performance - this is especially true with distinct count
queries). Saying this, to improve a pivot table performance:
1. This is a pretty tricky thing to do and setting the execution location to
the server does not make it faster (from experience, it is often slower as
you have seen as well).

2. One of the ways to improve the performance of queries (outside the above
statements) is to re-organize your dimensions. i.e. instead of having 3 or
4 separate dimensions, if its possible, place the dimensions into different
levels of one dimensions. This will help speed up performance quite a bit
for Excel.

These are somethings to help - but with some additional info I may be able
to assist better.

HTH!
Denny




"David Hwang" <davidhwang (AT) usa (DOT) com> wrote

I agree with Denny - why are you creating and merging partitions so often? You'll have to give us a more detailed explanation of your system if we're going to offer any more advice. We'll also need to know about the queries you're running and any calculated members on the cube

One point about aggregations: yes, they will slow down processing and merging, but you should find that one or two well-chosen (and often small) aggregations can make a big impact on query performance while only increasing process and merge times a little

Chri


----- David Hwang wrote: ----

Thanks for the response. That Performance Guide was quite helpful fo
understanding many aspects of Analysis Server. Unfortunately,
wasn't able to use it to improve the performance of our system. W
are constantly creating new partitions (with new data) and mergin
them in. Essentially doing the incremental cube updates ourselves.
So in our case, we need to worry about three times: process time
merge time, and query time. It seems like anything I do to speed u
one of those will slow down another one of those times. Usin
aggregations is great for query times, but processing and merging o
the new partitions became prohibitively slow. Any suggestions fo
changes that might increase performance across the board

OnlyForPostingToNewsgroups (AT) hotmail (DOT) com (Chris Webb) wrote in message news:<48d9285e.0403180153.5c0f512e (AT) posting (DOT) google.com>..

We are simulating a realtime system and are constantly creating new
temporary partitions (for incoming data) and merging them into the
main cube. This happens probably about twice a minute. After a few
hours, the number of partitions would become quite large, and since
the partitions are created based only on the time of day when the data
comes in, Analysis Server would have to touch all partitions to
satisfy a query.

Also, the dimension structure is pretty much defined by business
requirements and we've reduced the complexity as much as possible.
Not sure what else we can do here.

"Denny Lee" <dennyglee (AT) hotmail (DOT) com> wrote

Why aren't you using incremental update of the cube, rather than
creating and merging temporary partions? With incremental updatate, the
server creates and merges incremental partitions automatically, and this
runs fast.

With regard to dimension design, ensure that dimension joins are
optimized away in the cube schema where possible. This can result in
much faster cube processing.

The other option, if even incremental update can't keep up. is to use a
Real-Time ROLAP partition (if relational data is in SQL Server 2000), in
which case the partition is automatically updated when new fact records
are added.


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

When using DSO, isn't an incremental update just creating temporary
partitions and merging them yourself? There's not an incremental
option under DSO.processTypes. The way I understand it, when Analysis
Manager offers you the incremental process option, it's actually just
using DSO to create a temporary partition and merge it in.

For dimensions, we followed the suggestions given in the performance
guide (link in the first post of this thread) and managed to speed up
processing quite a bit. I don't think there's much else we can do
except try to convince people that they don't really need some of
those dimensions.

We tried using ROLAP, but the queries were extremely slow.

Deepak Puri <deepak_puri (AT) progressive (DOT) com> wrote

I didn't realize that you're using DSO - in which case, there's no
direct incremental update option.

Have you examined the MDX queries generated for the pivot table views,
and tried to optimize them - do they involve cross-joining of multiple
dimensions?

Also, I'm wondering what the partition processing frequency (twice a
minute) is determined by - is it volume of data, real-time display
requirement - since that is a factor.


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

The partition processing frequency is determined by the real-time
display requirement. We're basically processing and adding the data
as fast as we can as it comes in.

How do you optimize MDX queries that are generated by pivot tables in
Excel? Is there a way to give Excel hints on how to construct those
queries?

Deepak Puri <deepak_puri (AT) progressive (DOT) com> wrote

With OWC Pivot Tables, you can customize the MDX query (with some
limitations), but I'm unaware of any options with Excel. Of course,
there are many Excel add-ins that would also allow MDX customization (if
that's an option).


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!