I've got a quandry that I'm hoping some wise sage can help me with.

Background:
I've got a cube containing (amongst other things) ticket sales for a
group of performances. The relevant dimensions are
1. [p] (performance), with a "performance_date" property and one
sub-All level (performance_id),
2. [tixsold] (tickets sold), and
3. [do] (days out, or the sales on a given number of days prior to a
given performance's performance_date - the Key for it's one sub-All
level, days_out, corresponds to this number, i.e., "do.&[20]")

My problem is that I need to generate a result set that lists all open
performances (i.e., haven't occured yet), the current tixsold value
for the given performance and (here's the issue) the average
cumulative tixsold value for an arbitrary number of performances
immediately prior to the current performance at the current
performance's "days out" value.

The only solution I've found that seems to work (the 50 in the
LastPeriods function being the aforementioned arbitrary number of
prior performances) is along the lines of:

WITH
MEMBER Measures.days_out AS '
DateDiff(
"d"
,Now()
,CDate(p.CurrentMember.Properties("performance_dat e"))
)
'
MEMBER Measures.days_out_member_str AS '
IIf(
Val(Measures.days_out) < 0
,"do.&[0]"
,"do.&[" + CStr(Val(Measures.days_out)) + "]"
)
'
SET open_perfs AS '
Filter(
{ p.performance_id.Members }
,Val(Measures.days_out) >= 0
)
'
MEMBER Measures.avg_prior_tixsold AS '
StrToVal(
"Avg(
LastPeriods(
50
,p.CurrentMember.PrevMember
)
,Sum(
PeriodsToDate(
do.[All]
," + CStr(Measures.days_out_member_str) + "
)
,tixsold
)
)"
)
'
SELECT
{Measures.days_out, Measures.tixsold, Measures.avg_prior_tixsold}
on columns
, { open_perfs }
on rows
FROM performance_cube

The StrToVal seems the only way to apply the open_perfs set's current
Measures.days_out value within the context of the Avg function's
LastPeriods set. Unfortunately, using StrToVal seems to slow the
query's performance beyond the timeout limit of our application (4
min.), especially when other measures are applied or the number of
prior performances is large.

Does anyone see a more efficient way to generate the average of a
group of dimension members while referencing a CurrentMember from the
same dimension?

Many Thanks In Advance

Ian Russell

Hi Ian,

You're right, using string manipulation like this can impact performance and
it's a good idea to avoid it if you can. What I would recommend to try
instead is using named sets defined on the fly within the query. You can use
a set to hold the current value of [do] and use that within the AVG funtion;
although, as in this case, to be able to define the set requires a bit of
inelegant code (see below...). Without having the cube in front of me, I
think the resulting calculated member will look something like this:

MEMBER Measures.avg_prior_tixsold AS '
Avg(
LastPeriods(
50
, (p.CurrentMember.PrevMember,
{{STRTOMEMBER(Measures.days_out_member_str)} as CURRENTDO}.ITEM(0)).ITEM(0)
)
,Sum(
PeriodsToDate(
do.[All]
, CURRENTDO.ITEM(0)
)
,tixsold
)
)
'
Let me know if this produces the correct results, or if indeed it's any
faster or slower than before. It might also be worth paying extra special
attention to aggregations and other general cube performance tuning topics,
to ensure that your calculated member can get the raw values it needs as
quickly as possible to do the calculations; for this, take a look at the
following white paper:
http://www.microsoft.com/technet/tre...e/ANSvcsPG.asp

And lastly, are you using SP3 on both client and server? That will probably
help things too.

HTH,



Chris



--

Chris Webb
Consultant, Microsoft Services Switzerland
(to email: remove 'online.' from the address this was posted with)

Microsoft Services Switzerland and our partners can help you with your
Analysis Services project!

Disclaimer: This posting is provided 'AS IS' with no warranties, and confers
no rights.

"Ian Russell" <ian (AT) labstechnology (DOT) com> wrote

Thanks, Chris.
Your approach, ingenious if inelegent, does return the correct data,
although I've found that referencing a MEMBER value in a StrTo...
function, i.e., "STRTOMEMBER(Measures.days_out_member_str)", only
works when the referenced MEMBER is explicitly cast to a string via
CStr().

Unfortunately, performance-wise, it was a tad slower than the StrToVal
approach, probably due to the overhead in resolving the named set for
each row plus the continued need to resolve a string value (just a
guess).

Regarding aggregations, the cube's storage is currently designed for
100% performance gain (via DSO), and the p.performance_id level is
forcibly added to any aggregations it might be missing from. I'll try
forcing the do.days_out level in as well.

Regarding SP3, the server (SP3) and the "client" (OPENQUERY in a SQL
Server 2000 SP3 stored proc to an MSOLAP linked server) run on the
same machine - not ideal, but unavoidable for the moment. Would there
be a significant performance gain from using ADOMD? We've avoided that
due to the frequency of business logic changes and dll registration
issues - will there ever be an ADOMD.NET? Also, does PTS play any part
in this sort of setup?

Finally, is there NO way to use the <set>.Current function with a
query-scoped (as opposed to function-scoped) named set? That alone
would make a world of difference, methinks.

Many thanks again,

Ian

Please disregard my bemoaning the lack of an ADOMD.NET - just found
the beta download

Humbly,
Ian

For this specific MDX calc, can you side-step the issue of maintaining
the [p] dimension CurrentMember by flipping the order - sum over [do]
outside of the [p] loop, then divide?

Here's roughly how this could translate to the MDX query:
MEMBER Measures.days_out AS '
DateDiff(
"d"
,Now()
,CDate(p.CurrentMember.Properties("performance_dat e"))
)
'
SET open_perfs AS '
Filter(
{ p.performance_id.Members }
,Val(Measures.days_out) >= 0
)
'
MEMBER Measures.avg_prior_tixsold AS
'Sum(
LastPeriods(
-Val(Measures.days_out)-1
,do.[All]
)
,Sum(
LastPeriods(
50
,p.CurrentMember.PrevMember
)
,tixsold
)
)/50'

SELECT
{Measures.days_out, Measures.tixsold, Measures.avg_prior_tixsold}
on columns
, { open_perfs }
on rows
FROM performance_cube

- Deepak

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