Hello,

I have a quite normal OLAP cube in AS2000 (8 dims, 2 measures, partitions by
months, only regular dims, no member properties etc.). (I cannot upgrade to
AS2005.) Because of performance, I decided to design the aggregations myself
(with help of PartAggUtil.exe from SQL Server Accelerator for BI). I ended
with 51 aggregations. It was laborious and exciting.

But the performance gain wasn't what I expected.
When I watch the aggregation usage (Perfmon - Last Query - DSN requested/DSN
used), I can see that the AS server does not use some aggregations. Is
chooses some other aggregation (of course bigger).

Did I miss something?

E.g. when the client request DSN 34211121, server uses 34221231, even if
aggregation 34211121 exists as well and is 11 times smaller. (These numbers
are level numbers in my cube, as reported by Perfmon. The base fact table is
DSN 36222232.)

Thanks for any suggestions.

Hi Jiri,

Do you have parent-child dimensions in your cube? I seem to remember that in
AS2K the DSN Requested counter shows the displayed level number for a p/c
dimension rather than the physical level (and p/c dimensions only have two
physical levels, the All Level and the leaf level containing all the members).

AS could also be deliberately using the lower level aggregation because it
thinks it's going to be better to cache that data to improve the performance
of future queries, but that's just speculation: the internals of AS2K aren't
documented in enough detail to be sure.

Chris

"Jiri Cerny" wrote:

Hi Chris,

thanks for your answer.

No, I have only regular dimensions, based on a single table (star schema).

Yes, it is possible that AS uses the lower level aggregations deliberately
because of
further queries. Maybe it assumes that a 20 or 30 ms response time is good
enough. But
from my angle of view, that's very bad assumption. To solve one of my MDX
queries, the AS
client requests information from the AS server nearly 4500-times (with the
same DSN). And
that brings very poor performance.

Jiri

"Chris Webb" wrote ...

OK, so you're seeing the query counter go sky-high when you run a query? In
which case you might do well to try out some connection string properties -
Cache Policy, Cache Ratio and Cache Ratio2. For more information on them see
http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!134.entry
http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!254.entry

Try Cache Policy=7 on its own or in combination with Cache Ratio=0.01 and
Cache Ratio2 = 0.01. These might reduce the number of subqueries.

Chris

"Jiri Cerny" wrote:

Thanks for all the suggestions. I have read the blogs, seen the webcasts and
tried many thinks.

The "Cache Policy=7" really reduces the number of subqueries dramatically,
but unfortunately the response time remains almost the same. Now I see that
the bottleneck it not the server, but the computations on the client.

The other settings (Cache Ratio, Cache Ratio2, Non Empty Threshold, Large
Level Threshold, Execution Location) don't help; in fact, whatever I tried
made thinks worse (the response time increased).

I guess that the problems causes the aggregation in slice. When I try to
replace it with a crossjoin on the axis, the response time is much shorter.
(Of course, the results then are not aggregated as I need it.)

How can I get the performance of the second query and get the aggregate
results I need? (I remind that I have to use AS2000; I am not allowed to
upgrade to AS2005.)

Thanks in advance for any help.

There are simplified versions of my queries:

-------------------------------------------------------------------------
MDX 1 - lasts 12 seconds (even with Cache Policy=7):

with
set [UserStations] as '{
[Station].[5457076],[Station].[5433295],[Station].[5434362],
[Station].[5453613],[Station].[5473275],[Station].[5434364],
[Station].[5473084],[Station].[5457156],[Station].[5411111],
[Station].[5433072],[Station].[5433055],[Station].[5434984],
[Station].[5435342],[Station].[5477025],[Station].[5476955]}'
member [Station].[AggUserStations] as 'sum(UserStations)'
select
[Day].[20060505]:[Day].[20060520] on 0,
non empty [Seller].[Seller].members on 1
from my_cube
where ([AggUserStations])

-------------------------------------------------------------------------
MDX 2 - the custom slice replaced with crossjoin on the axis - lasts only
1.2 seconds:

with
set [UserStations] as '...' (the same as above)
select
[Day].[20060505]:[Day].[20060520] on 0,
non empty crossjoin([Seller].[Seller].members, [UserStations]) on 1
from my_cube

-------------------------------------------------------------------------

Have you tried something like the following?

with
set [UserStations] as '{
[Station].[5457076],[Station].[5433295],[Station].[5434362],
[Station].[5453613],[Station].[5473275],[Station].[5434364],
[Station].[5473084],[Station].[5457156],[Station].[5411111],
[Station].[5433072],[Station].[5433055],[Station].[5434984],
[Station].[5435342],[Station].[5477025],[Station].[5476955]}'
member [Station].[AggUserStations] as 'sum(UserStations)'
select
[Day].[20060505]:[Day].[20060520] on 0,
nonemptycrossjoin( [Seller].[Seller].members
, [Day].[20060505]:[Day].[20060520], [UserStations],
Measures.WhatEverYourMeasureIs, 1)

on 1
from my_cube
where ([AggUserStations])

Chris

"Jiri Cerny" wrote:

Yes, I have tried it formerly.. usually performed excellent, but sometimes
it started perform yet much worse then my original version, until I exited
and restarted my client process. (I guess this behavior is related to
client-side caching.) So I returned to my original version.

Now I tried it again, this time with Cache Policy=7... it looks very well!!
:-)
I will do some more tests, but I believe this is the right solution for me.
And I have learned again sometimes new :-)
Many thanks, Chris! I very appreciate your postings.

BTW, what exactly means that magic option Cache Policy 7 and what are the
other values for this setting? I have found no documentation for it.

Jiri


"Chris Webb" <ChrisWebb (AT) discussions (DOT) microsoft.com> wrote

It's not well documented at all. There's some information in the release
notes for SP4 of AS2K; there's some more in the MDX Tuning webcast (probably
easier to read the transcript if it's still available) that I linked to in
one of the blog postings I linked to earlier in this thread. The various
cache policy options control how data is brought down into the client cache
when a query is run, but explicitly setting 1-6 aren't supported at all as
far as I know.

Glad I could help...

Chris

"Jiri Cerny" wrote: