Re: Why AS2k ignores my aggregations???
- From: Chris Webb <ChrisWebb@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Sun, 6 Aug 2006 13:53:01 -0700
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:
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 ...
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:
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.
- Follow-Ups:
- Re: Why AS2k ignores my aggregations???
- From: Jiri Cerny
- Re: Why AS2k ignores my aggregations???
- References:
- Why AS2k ignores my aggregations???
- From: Jiri Cerny
- Re: Why AS2k ignores my aggregations???
- From: Jiri Cerny
- Why AS2k ignores my aggregations???
- Prev by Date: Re: Why AS2k ignores my aggregations???
- Next by Date: Re: quartile report or value banding
- Previous by thread: Re: Why AS2k ignores my aggregations???
- Next by thread: Re: Why AS2k ignores my aggregations???
- Index(es):
Relevant Pages
|
