Hi!

We have a solution using AS2000 for some rather unorthodox cubes. There's
calculated members on the "atomic" level which in turn are summarized on
higher levels, so the only real gain we get from AS is pivoting. The
performance is more or less useless.
I'm sliding more and more towards a rewrite using only SQL, but this
wouldn't give the kind of functionality/performance we're after either.

Is there any way to make either 2000 or 2005 pre-calculate the results of
calculated members? (Read: should I start learning 2005 yesterday?)

Here's a sample of one of my calculated members:

IIF([Roles].CurrentMember.Name = "All",
IIF(NOT IsLeaf([Persons].CurrentMember),
Avg(CROSSJOIN(Descendants([Persons].CurrentMember, 999, LEAVES),
[Roles].CurrentMember.Children), [Measures].Match),
Avg([Roles].CurrentMember.Children, [Measures].[UncriticalMatch])
),
[Measures].[UncriticalMatch]
)

UncriticalMatch is a calculated member doing measure1 / measure2.
Persons is a parent-child dimension with leaves on any level. There's also a
dimension for departments.

This cube is impossible to use on higher levels, as it always has to run
down to the bottom and summarize for each level at runtime.

Hope someone has a miracle solution... :P

Lars-Erik

Hello Lars,

As I know, both sql 2000/2005 does not support calculated member
pre-calculate. You may want to go sql 2005 newsgroup for more information
about this:

http://communities.microsoft.com/new...lserver2005&sl
cid=us

In SQL Server 2000 Analysis Services a property is available for all
calculated members which allows the evaluation to be skipped. This property
is called Non Empty Behavior and allows you to select a base measure which
this calculated member relies on. If this base measure is Empty then the
evaluation of this calculated member is skipped. Thus, if you have a
calculated member "Sales + 10%" with the formula "UnitSales * 1.1" you know
that it will be empty if the base measure of UnitSales is empty. Setting
the Non Empty Behavior value to UnitSales will prevent the evaluation and
calculation of this calculated member.

Hope this is helpful.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

================================================== ===



This posting is provided "AS IS" with no warranties, and confers no rights.


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

OK, thanks. That might help a bit.

L-E

"Peter Yang [MSFT]" <petery (AT) online (DOT) microsoft.com> wrote

If all you are doing is to evaluate an average you might be able to take
advantage of the following trick.

1. Create a second real measure on your cube which uses a COUNT
aggregation. Call it something like [Measures].[UncriticalMatchCount]

2. Change your average measure to be
[Measures].[UncriticalMatch] / [Measures].[UncriticalMatchCount]

An average is just a sum of the members divided by the count. In this
way AS2k can pre-aggregate the sum and the count measures and you should
get pretty good performance.

--
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

In article <#GT#hIduFHA.3224 (AT) TK2MSFTNGP10 (DOT) phx.gbl>,
larserik (AT) newsgroup (DOT) nospam says...