Hi,

I have some issues when I'm using the Execution Location=3;Default Isolation
Mode=1 options.
sometimes the results returned are wrong , sometimes AS shutdown & restart.

its a AS 2000 Std edition.

half of my measures are calculated distinct count measures.
these formulas are CPU consuming, so I want to process these on the server
side instead-of the client side.

what are you connection string recommandations?

I have found this:
Cache ratio=0.1; cache ratio2=0.1;Cache Policy=7

what the Cache Policy=7 do?
I have not found any documentation regarding this parameter.

also, I have a second deployment where the users are connected through the
HTTP protocol. In this case, and for the same type of formula, what are the
best parameters?

thanks

Jerome.

Hi Jéjé,

Crashing AS isn't good - you should probably report it to PSS. Have you got
SP4 installed on both the client and the server?

Regarding Cache Policy=7, the only publicly available sources of information
I've seen are:
http://support.microsoft.com/kb/841178
and in the transcript for this webcast:
http://support.microsoft.com/default.aspx?kbid=886991

Basically, if you see the Query Num counter in Perfmon on your server
increasing by more than a few hundred when you run a query, it's possible
that this setting might improve performance.

Regards,

Chris

--
Blog at:
http://spaces.msn.com/members/cwebbbi/


"Jéjé" wrote:

Hi,

the SP4 is not installed on this server.
I plan to do this next week.

the query num is not so high, genrally 20 to 50; higher value: 400 (for
small quick tests)
but most of the queries are executed at the client side, so, from a server
side, I never see any performance problems, the avg response time is
0seconds!
but the client works for a lot of seconds to process the results.
my clients use the OWC pivot table to access the cubes.

my distinct count calculated measure is :
count(crossjoin(mydimension.leaflevel.members, {measures.CountOf}),
excludeempty)

I don't use this formula:
count(nonemptycrossjoin(mydimension.leaflevel.memb ers, {measures.CountOf}))
because the results are wrong in particular cases. but the performance is
better.

in many cases the distinct count is used in other formula like:
measures.sales / measures.dcountcustomers


"Chris Webb" <OnlyForPostingToNewsgroups (AT) hotmail (DOT) com> wrote

OK, so it sounds like Cache Policy isn't going to be that useful to you.

How large is the leaf level of the dimension you're doing the distinct count
on? I would guess that one of the main reasons why you're seeing a lot of
activity is that you're requesting data at the leaf level of a large
dimension, then at the higher levels of all your other dimensions, and as a
result you're missing any aggregations built in your cube. This could be a
case where manual creation of aggregations is useful - see
http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!119.entry

The other trick you could try is the one documented in this thread:
http://groups.google.co.uk/group/mic...0a245f7d589704

HTH,

Chris




--
Blog at:
http://spaces.msn.com/members/cwebbbi/


"Jéjé" wrote:

I have 100 000 members and its the biggest dimension in my cubes.
also this dimension is hidden to the end user (because I don't have any
hiearchy on this dimension and the user cannot see it for security reasons)
..
I have 5 virtual dimensions based on this one (gender, spoken language...)

but most of the time, the users filter on other dimensions.
Like:
How many distinct customers bought the product X in the year Y?
So I dcount by customer, but not at the higher level. and I have 20
dimensions in my cube.

So, its why I think executing on the server side helps me to identify
aggregations really used (or missing) in my cube.

"Chris Webb" <OnlyForPostingToNewsgroups (AT) hotmail (DOT) com> wrote

By 'no hierarchy', do you mean you only have two levels on this dimension -
the All Level and the level with 100000 members? In which case you need to
insert at least two levels in the middle, so that each member has no more
than a hundred siblings. Your current design is really going to cause
problems for the way AS2K does caching on the client side and this is
probably part of the reason for your poor performance.

Overall, the size of this leaf level, the fact that you have virtual
dimensions in your cube (have you changed the AggregationUsage properties for
these dimensions?) and the fact you have 20 dimensions in your cube, all make
me think you're not going to be hitting any aggregations with your queries. I
would try building one aggregation at the leaf level of your large dimension,
the All Levels of your least used dimensions and midway through your other
most popular dimensions, run a query which should hit this aggregation and
see if it makes any difference to performance.

Regards,

Chris


--
Blog at:
http://spaces.msn.com/members/cwebbbi/


"Jéjé" wrote:

Thanks for the feedback.

Yes, I have only 2 levels: 1 to split the 100 000 rows into 2 subset and
bypass the 64000 limit.

Currently its difficult for me to know what dimensions are used or not.
I know, the model is "strange", my biggest dimension is not accessible to
the end user, but provides 6 to 10 virtual dimensions.
other dimensions are small with only 1 or 2 levels (like hours -> minutes;
Year -> quarter -> month...)
the second biggest is the only dimension with 5 levels (but only 5000
members at the leaf level)

Without server side execution, its difficult to know the real usage... but
I'll work on this.

In parrallel, I start working with AS2005 and the integrated DCount measure
appear to provide all my required functionalities (specially around the
DCount!)

"Chris Webb" <OnlyForPostingToNewsgroups (AT) hotmail (DOT) com> wrote

You're right, AS2005 will probably do everything you want.

There's no way to really know what queries your users are using, but if you
follow the link to my blog that I put in my second post in this thread,
you'll see another link to an article explaining how to understand the
contents of the Query Log. If you set your server to log every query for a
short period, then you should get a pretty good idea of what data your users
are requesting.

On the subject of your large dimension, I probably wasn't clear enough in my
last post. You should try to put at least one more level in to reduce the
number of siblings that each member has. Even though your users won't see it,
it should improve query performance because it helps AS be more efficient
when caching data on the client.

In my experience, server-side execution is not usually the answer to poorly
performing queries in AS2K. Even if you do get Execution Location=3; Default
Isolation Mode=1 to work, you lose any results caching. I've found that
paying attention to aggregation and dimension design is much more likely to
help make queries faster.

Chris

--
Blog at:
http://spaces.msn.com/members/cwebbbi/


"Jéjé" wrote:

Having just seen this webcast:
http://support.microsoft.com/service...5/manifest.xml
(which is highly recommended), I've got some more recommendations:
* Are you setting the Non Empty Behavior property of your calculated
measure? This will help if you are using Non Empty and removing lots of empty
rows/columns when you are running your queries.
* Try setting the Cache Ratio and Cache Ratio2 connection string properties
to very low values, such as 0.000001. This will reduce likelihood that too
much data is brought down to the client (although, as I said, including more
levels in your large dimension will also help here).

Regards,

Chris

--
Blog at:
http://spaces.msn.com/members/cwebbbi/


"Chris Webb" wrote: