visual rank calculated member on server is very very slow

23 views
Skip to first unread message

alanr

unread,
Oct 13, 2005, 12:06:25 PM10/13/05
to
Greetings!

I am struggling with the performance of a visual ranking calculation
defined on the server. I based this on posts from Tom Chester and
others. I am using the September CTP (which as a side note seems to
have eliminated the need to do order the set before ranking?). Excel
2003 pivot table is client. Note that the member won't work with OWC
because the axis doesn't have the measure on it.

For very small dimensions it is just fine but for those with even a few
hundred members it takes more than a minute. With a thousand members
takes 10+ minutes. Ugh:( This is probably because it is performing
the rank calculation for each cell and nothing is cached between cells.

I have seen some very interesting posts from Deepak that suggest how to
cache ordered sets using "WITH" and Generate but I don't think there is
a way to do that within a server calculated member? Also, I don't want
to cache the ordered set, I want to cache the RANK with it's elegant
handling of ties.

Here is the definition of the member

CREATE MEMBER CURRENTCUBE.[MEASURES].[Rank Desc]
AS
-- IF GRAND TOTAL THEN NULL
IIF(StrToSet("Axis(1)").Item(0).Item(0).Dimension.CurrentMember.Level
IS StrToSet("Axis(1)").Item(0).Item(0).Dimension.DefaultMember.Level,
NULL,
-- MEASURE IS LAST ITEM ON COLUMN AXIS - IF EMPTY THEN NULL
IIF(IsEmpty(StrToSet("Axis(0)").Item(0).Item(StrToSet("Axis(0)").Item(0).Count
-1)), NULL,
-- DIMENSION IS LAST ITEM ON ROW AXIS
Rank(StrToSet("Axis(1)").Item(0).Item(StrToSet("Axis(1)").Item(0).Count
-1).Dimension.CurrentMember,
StrToSet("Axis(1)").Item(0).Item(StrToSet("Axis(1)").Item(0).Count
-1).Dimension.CurrentMember.Siblings,
-- RANK DESC
-1 * StrToSet("Axis(0)").Item(0).Item(StrToSet("Axis(0)").Item(0).Count
-1)))),
VISIBLE = 1 ;

Here is text version of pivot table from adventure works dw

Product Sales Amount Rank Desc
Mountain-200 Black, 38 1649944.275 1
Mountain-200 Black, 42 1418634.069 2
Mountain-200 Silver, 38 1385764.363 3
Mountain-200 Black, 46 1358994.853 4
Mountain-200 Silver, 46 1286225.656 5
Mountain-200 Silver, 42 1266342.646 6
Road-350-W Yellow, 48 1227312.253 7
Mountain-200 Black, 38 1214208.695 8
Mountain-200 Black, 42 1128870.11 9
Road-350-W Yellow, 40 1119190.695 10
.
.
.
LL Headset 1518.48 339
LL Touring Frame - Blue, 62 1400.364 340
ML Mountain Frame-W - Silver, 38 1310.724 341
LL Mountain Frame - Black, 40 1049.118 342
ML Touring Seat/Saddle 962.844 343
LL Road Frame - Red, 52 919.691 344
LL Touring Seat/Saddle 894.96 345
LL Touring Handlebars 746.658 346
Mountain Bike Socks, L 410.4 347
LL Road Frame - Black, 60 178.5808 348
LL Road Seat/Saddle 32.544 349

If I define a server member that does not use the axis and is therefore
not flexible I get much better performance (although with large
dimensions it is still no cause for celebration)

CREATE MEMBER CURRENTCUBE.[MEASURES].[Rank Desc Prod Sales]
AS
IIF(IsEmpty([Measures].[Sales Amount]), NULL,
Rank([Product].[Product].CurrentMember,
[Product].[Product].CurrentMember.Siblings,
-1 * [Measures].[Sales Amount])),
VISIBLE = 1;

Any help that anyone can provide would be greatly appreciated.

Thanks,
Alan

Deepak Puri

unread,
Oct 14, 2005, 1:39:44 AM10/14/05
to
Hi Alan,

I don't have a good solution for this scenario, but here are a couple of
initial thoughts:

It might be possible, though tricky, to cache sets within a calculated
measure using aliases - see Chris Webb's blog:

http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!
172.entry

Though it sounds like you need the 3-parameter version of Rank() to
handle value ties, it might be instructive to see how much faster the
2-parameter version works (of course, an ordered set of row dimension
members would then need to be cached in a Named Set, to be used in
Rank())


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Deepak Puri

unread,
Oct 20, 2005, 2:17:42 AM10/20/05
to
Hi again Alan,

After experimenting with the second suggestion (that of caching an
ordered set and using the 2-parameter version of Rank), here are the
findings/timings on my AS 2005 system (a vanilla 2-processor server),
using the Sept CTP:

- Your original version of [Rank Desc] took 40 seconds to rank the 350
Adventure Works [Product].[Product].[Product].Members. I used [Order
Quantity] as the measure, so as to get numerous tied values (query #1
below).

- Ordering and caching the [Product].[Product].[Product].Members in a
Named Set, then using the 2-parameter version of Rank took only 3
seconds, but obviously did not handle tied values (query #2 below).

- I then added a query-scope cell calculation to handle tied values via
recursion, but keeping the 2-parameter version of Rank. This took 4
seconds (query #3 below). If this meets your needs, it's much faster
than query #1.


Query #1>>
-- This version (ordered) took 40 seconds:
With Member [MEASURES].[Rank Desc]


AS
-- IF GRAND TOTAL THEN NULL
IIF(StrToSet("Axis(1)").Item(0).Item(0).Dimension.CurrentMember.Level
IS StrToSet("Axis(1)").Item(0).Item(0).Dimension.DefaultMember.Level,
NULL,
-- MEASURE IS LAST ITEM ON COLUMN AXIS - IF EMPTY THEN NULL
IIF(IsEmpty(StrToSet("Axis(0)").Item(0).Item(StrToSet("Axis(0)").Item(0)
.Count
-1)), NULL,
-- DIMENSION IS LAST ITEM ON ROW AXIS
Rank(StrToSet("Axis(1)").Item(0).Item(StrToSet("Axis(1)").Item(0).Count
-1).Dimension.CurrentMember,
StrToSet("Axis(1)").Item(0).Item(StrToSet("Axis(1)").Item(0).Count
-1).Dimension.CurrentMember.Siblings,
-- RANK DESC
-1 * StrToSet("Axis(0)").Item(0).Item(StrToSet("Axis(0)").Item(0).Count
-1)))),
VISIBLE = 1

select {[Measures].[Order Quantity], [MEASURES].[Rank Desc]} on columns,
Non Empty Order([Product].[Product].[Product].Members,
[Measures].[Order Quantity], BDESC) on rows
from [Adventure Works]
>>


Query #2>>
-- This version (2-parameter Rank + OrderedRows) took 3 seconds:

With
Set [OrderedRows] AS
Order(Extract(StrToSet("Axis(1)"),


StrToSet("Axis(1)").Item(0).Item(StrToSet("Axis(1)").Item(0).Count

-1).Dimension),


StrToSet("Axis(0)").Item(0).Item(StrToSet("Axis(0)").Item(0).Count

-1), BDESC)
Member [MEASURES].[Rank Desc]


AS
-- IF GRAND TOTAL THEN NULL
IIF(StrToSet("Axis(1)").Item(0).Item(0).Dimension.CurrentMember.Level
IS StrToSet("Axis(1)").Item(0).Item(0).Dimension.DefaultMember.Level,
NULL,
-- MEASURE IS LAST ITEM ON COLUMN AXIS - IF EMPTY THEN NULL
IIF(IsEmpty(StrToSet("Axis(0)").Item(0).Item(StrToSet("Axis(0)").Item(0)
.Count
-1)), NULL,
-- DIMENSION IS LAST ITEM ON ROW AXIS
Rank(StrToSet("Axis(1)").Item(0).Item(StrToSet("Axis(1)").Item(0).Count
-1).Dimension.CurrentMember,

[OrderedRows]))),
VISIBLE = 1
select {[Measures].[Order Quantity], [MEASURES].[Rank Desc]} on columns,
Non Empty Order([Product].[Product].[Product].Members,
[Measures].[Order Quantity], BDESC) on rows
from [Adventure Works]
>>


Query #3>>
-- Calc cell version with value tie logic took 4 seconds:

With
Set [OrderedRows] AS
Order(Extract(StrToSet("Axis(1)"),


StrToSet("Axis(1)").Item(0).Item(StrToSet("Axis(1)").Item(0).Count

-1).Dimension),


StrToSet("Axis(0)").Item(0).Item(StrToSet("Axis(0)").Item(0).Count

-1), BDESC)
Member [MEASURES].[Rank Desc]
AS 0
Cell Calculation [ValueTie]
for '({[MEASURES].[Rank Desc]})'
as


-- IF GRAND TOTAL THEN NULL
IIF(StrToSet("Axis(1)").Item(0).Item(0).Dimension.CurrentMember.Level
IS StrToSet("Axis(1)").Item(0).Item(0).Dimension.DefaultMember.Level,
NULL,
-- MEASURE IS LAST ITEM ON COLUMN AXIS - IF EMPTY THEN NULL
IIF(IsEmpty(StrToSet("Axis(0)").Item(0).Item(StrToSet("Axis(0)").Item(0)
.Count
-1)), NULL,

-- Member is first in [OrderedRows]
IIF(Rank({StrToSet("Axis(1)").Item(0).Item(StrToSet("Axis(1)").Item(0).C
ount
-1).Dimension.CurrentMember as OrderDimCur}.Item(0),
[OrderedRows]) < 2, 1,
-- DIMENSION IS LAST ITEM ON ROW AXIS, value tie
IIF(StrToSet("Axis(0)").Item(0).Item(StrToSet("Axis(0)").Item(0).Count
-1)
= (StrToSet("Axis(0)").Item(0).Item(StrToSet("Axis(0)").Item(0).Count
-1), [OrderedRows].Item(Rank(OrderDimCur.Item(0).Item(0),
[OrderedRows])-2).Item(0)), ([MEASURES].[Rank Desc],
[OrderedRows].Item(Rank(OrderDimCur.Item(0).Item(0),
[OrderedRows])-2)),
-- Default case of ranking
Rank(OrderDimCur.Item(0).Item(0), [OrderedRows])))))
select {[Measures].[Order Quantity], [MEASURES].[Rank Desc]} on columns,
Non Empty Order([Product].[Product].[Product].Members,
[Measures].[Order Quantity], BDESC) on rows
from [Adventure Works]

Deepak Puri

unread,
Oct 22, 2005, 1:02:42 AM10/22/05
to
PS: the value tie logic of the calc cell in query #3 can be incorporated
instead in the calculated measure: [Rank Desc], with no cell calculation
(query time 3 seconds).

Peter via SQLMonster.com

unread,
Oct 22, 2005, 5:51:49 AM10/22/05
to
Dear Deepak :)

Beginner in MDX have a problem

Maybe it is not possible at all, but when I try to use a sample for the
calculated member

WITH MEMBER [Store].[Name] AS '
[Measures].CurrentMember.Name
'
MEMBER [Store].[UniqueName] AS '
[Measures].CurrentMember.UniqueName
'
SELECT [Measures].AllMembers ON COLUMNS,
{[Store].[Name], [Store].[UniqueName]} ON ROWS
FROM [HR]

it gives me the following err

Unable to update the calculated member.
Formula error - syntax error - token is not valid:"WITH ^ MEMBER ^ [Store], ..
.

Please, can You help me with this matter
Peter


--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-olap/200510/1

Deepak Puri

unread,
Oct 23, 2005, 8:30:40 PM10/23/05
to
Hi Peter,

It's not clear what problem you're trying to tackle, but increasing the
SOLVE_ORDER of the 2 calculated members works for me, in this sample
query:

>>
WITH MEMBER [Store].[Name] AS

'[Measures].CurrentMember.Name',
SOLVE_ORDER = 10


MEMBER [Store].[UniqueName] AS

'[Measures].CurrentMember.UniqueName',
SOLVE_ORDER = 10


SELECT [Measures].AllMembers ON COLUMNS,
{[Store].[Name], [Store].[UniqueName]} ON ROWS
FROM [HR]
>>


BTW, it's good to start a new thread when changing topics!

alanjro...@gmail.com

unread,
Oct 31, 2005, 2:45:07 PM10/31/05
to
Thanks again Deepak!

Clearly there are major benefits if I can figure out how to cache the
ordered results but I am stumped as to how to do this within a server
caculated member (CREATE MEMBER CURRENTCUBE.[MEASURES].[Rank Desc] AS
...)

I read the post in Chris Webb's blog that you suggested earlier in this
thread and tried to experiment but came up empty? Any other pointers
you can provide would be enormously appreciated:)

Thanks,
Alan

Deepak Puri

unread,
Nov 2, 2005, 1:39:38 AM11/2/05
to
Hi Alan,

In order to create a server-side [MEASURES].[Rank Desc], I replaced the
Named Set: [OrderedRows] by a calculated measure:
[Measures].[OrderedRows], which is a string version of the set (the
Named Set won't work because it refers to members of the query Axes).
This is like the approach adopted in the entry cited from Chris Webb's
blog. Unfortunately, the Adventure Works query timing doesn't look
promising - over 80 seconds. Anyway, below are the server-side
definitions, in case they work better in your scenario (which might be
the case, if your set is large).

Can't conceive a better solution at the moment; but I'll post a query at
Chris's blog, in case he has any ideas.

>>
CREATE MEMBER CURRENTCUBE.[MEASURES].[OrderedRows] AS
iif(StrToSet("Axis(1)").Item(0).Item(0).Dimension.CurrentMember is
StrToSet("Axis(1)").Item(0).Item(0),
SetToStr(Order(Extract(StrToSet("Axis(1)"),


StrToSet("Axis(1)").Item(0).Item(StrToSet("Axis(1)").Item(0).Count
-1).Dimension),
StrToSet("Axis(0)").Item(0).Item(StrToSet("Axis(0)").Item(0).Count

-1), BDESC)),
(StrToSet("Axis(1)").Item(0).Item(0), [MEASURES].[OrderedRows]));

Create Member CurrentCube.[MEASURES].[Rank Desc] AS

-- IF GRAND TOTAL THEN NULL
IIF(StrToSet("Axis(1)").Item(0).Item(0).Dimension.CurrentMember.Level
IS StrToSet("Axis(1)").Item(0).Item(0).Dimension.DefaultMember.Level,
NULL,
-- MEASURE IS LAST ITEM ON COLUMN AXIS - IF EMPTY THEN NULL
IIF(IsEmpty(StrToSet("Axis(0)").Item(0).Item(StrToSet("Axis(0)").Item(0)
.Count
-1)), NULL,
-- Member is first in [OrderedRows]
IIF(Rank({StrToSet("Axis(1)").Item(0).Item(StrToSet("Axis(1)").Item(0).C
ount
-1).Dimension.CurrentMember as OrderDimCur}.Item(0),

StrToSet(CStr([MEASURES].[OrderedRows]))) < 2, 1,


-- DIMENSION IS LAST ITEM ON ROW AXIS, value tie
IIF(StrToSet("Axis(0)").Item(0).Item(StrToSet("Axis(0)").Item(0).Count
-1)
= (StrToSet("Axis(0)").Item(0).Item(StrToSet("Axis(0)").Item(0).Count
-1),

StrToSet(CStr([MEASURES].[OrderedRows])).Item(Rank(OrderDimCur.Item(0).I
tem(0),
StrToSet(CStr([MEASURES].[OrderedRows])))-2).Item(0)), ([MEASURES].[Rank
Desc],
StrToSet(CStr([MEASURES].[OrderedRows])).Item(Rank(OrderDimCur.Item(0).I
tem(0),
StrToSet(CStr([MEASURES].[OrderedRows])))-2)),


-- Default case of ranking
Rank(OrderDimCur.Item(0).Item(0),

StrToSet(CStr([MEASURES].[OrderedRows])))))));

Chris Webb

unread,
Nov 2, 2005, 11:05:13 AM11/2/05
to
Very interesting problem. I spent some time today looking at how this calc
could be optimised using inline named sets, and only managed to turn up what
look like bugs - I certainly couln't improve on performance.

I'm not sure there is a good solution to this problem really. Quite a lot of
people have asked for calculations which are aware of the query context like
this one, but it's something that MDX isn't very good.

I'll keep my thinking cap on...

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

Deepak Puri

unread,
Nov 2, 2005, 11:29:53 AM11/2/05
to
Chris,

Thanks for checking this out - Alan's performance problem looks like it
could be relevant in other scenarios too.

Now if only (maybe) this could catch Mosha's attention ...

Peter via SQLMonster.com

unread,
Nov 3, 2005, 8:44:07 AM11/3/05
to
Thank`s Deepak :)
the strange thing is - it allways works with the second try
Peter

--
Message posted via SQLMonster.com

http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-olap/200511/1

Richard Tkachuk [MSFT]

unread,
Nov 15, 2005, 6:34:10 PM11/15/05
to
Hi Chris, Deepak,

Frankly, I didn't follow all the logic of how you want this to work but
after killing an hour looking at this, I think I have something that might
work. I think the performance problems stem from resolving the set umpteen
times, so I tried something a little different.

In this aproach, I want the rank of each tuple on the row axis (assuming
ordered). To build that tuple, I created a measure that has the string
equivalent of the tuple:

create member [Adventure Works].measures.CurrentTuple as

"(" + generate(

iif ( Axis(1).item(0).count > 1,

strtoset(

"union(" + rept("{Dimensions(0).members(0)},", Axis(1).item(0).count) +
"All)"

)

,Dimensions(0).members(0))

as z,


iif(z.CurrentOrdinal >1 ,",","") +

Axis(1).item(0).item(z.currentordinal-1).Dimension.Uniquename +
".currentmember"

+ ")"

This is a trick I've used once or twice before. MDX doesn't have a mechanism
to iterate over an expression n times. So I build a set with n members and
iterate over it. It's a hack, but it works (if you have excel on your
machine - the rept function is not native mdx but from the excel vba
library)

Then I created a calculated member that I'll put in a calculation:
create member [Adventure Works].measures.MyRank as
rank(strtotuple(measures.CurrentTuple),Axis(1))

And then the query:

select {[Measures].[Order Quantity], [MEASURES].[myrank]} on columns,

Non Empty Order([Product].[Product].[Product].Members,

[Measures].[Order Quantity], BDESC) on rows

from [Adventure Works]

Gives the ranking of each member. Pretty fast two. About 2 seconds on my
laptop :)

Let me know if this solves the problem.

Cheers,

Richard
--
_______________
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.


"Deepak Puri" <deepa...@progressive.com> wrote in message
news:OplMoq83...@TK2MSFTNGP15.phx.gbl...

Deepak Puri

unread,
Nov 15, 2005, 10:58:36 PM11/15/05
to
Hi Richard,

There's a couple of loose ends to tie up still, I think, with the
approach you suggested:

- Where 2 rows have the same measure value, their ranks should be equal
(as in Rank with a 3rd argument). So for example, both "Road-650 Black,
62" and "Road-650 Black, 48" should have a rank of 291. Since I was able
to meet this requirement by making the ranking measure recursive, that
should not be a big deal.

- The ranking should be done by measure value, regardless of the order
of rows on query axis(1). To meet this requirement, I defined a
calculated measure: [OrderedRows], which saves a string version of the
ordered set for use in subsequent ranking. I was hoping that this string
would be computed once and cached for the rest of the query; however
that didn't appear to happen. Is there any way to cache / save the
ordered set in a calculated member, so that Order() is only computed
once over a query? My measure was:

>>
CREATE MEMBER CURRENTCUBE.[MEASURES].[OrderedRows] AS
iif(StrToSet("Axis(1)").Item(0).Item(0).Dimension.CurrentMember is
StrToSet("Axis(1)").Item(0).Item(0),
SetToStr(Order(Extract(StrToSet("Axis(1)"),
StrToSet("Axis(1)").Item(0).Item(StrToSet("Axis(1)").Item(0).Count
-1).Dimension),
StrToSet("Axis(0)").Item(0).Item(StrToSet("Axis(0)").Item(0).Count
-1), BDESC)),
(StrToSet("Axis(1)").Item(0).Item(0), [MEASURES].[OrderedRows]));
>>


Maybe Chris has some more insight to add as well.


Thanks,

Richard Tkachuk [MSFT]

unread,
Nov 18, 2005, 8:07:44 PM11/18/05
to
I updated some calcualtions and it works the way you suggest. But it's
pretty hacky and something I wouldn't put in a cube any customer of mine
would rely on. (So why do I post? Because the calculation is kind of neat.)
Session ranking is really the way to go.

But anyways I added the calculations below to AW, and it seems to work with
the following issues:
- relies on Excel or OWC (because the measure is always on Axis 1). If you
want this to work with arbitrary clients, you'd have to add logic as to what
axis the measure was on.
- ranks every tuple on the axis. This is ugly - so it ranks totals as with
their children. I can think of a way to fix this so it would only rank
tuples along the axis with other tuples where every member in the tuple had
the identical level as the corresponding member in the other tuple. But that
means sticking a filter or generate inside rank. This is something you
should never do, as it would just destroy performance

Anyway, for what it's worth, some calculations below.

(BTW, this gave me an idea to add a measure RowNumber and ColumnNumber to a
cube that would give the relative row and column number in an arbitrary cell
set. I'll have to write that up sometime...)


create member CurrentCube.measures.CurrentTuple as

"(" + generate(

iif ( Axis(0).item(0).count > 1,

strtoset(

"union(" + rept("{Dimensions(0).members(0)},", Axis(0).item(0).count) +
"All)"

)

,Dimensions(0).members(0))

as z,


iif(z.CurrentOrdinal >1 ,",","") +

Axis(0).item(0).item(z.currentordinal-1).Dimension.Uniquename +
".currentmember"

)

+ ")";

create member [Adventure Works].measures.OrderRank as 1;

create cell calculation [Adventure Works].ccOrderRank for
'({measures.OrderRank})' as

'rank(strtotuple(measures.CurrentTuple),Axis(0),-[Measures].[Order
Quantity] )';

/*-- Set default member for the Destination Currency cube dimension ----*/

Alter Cube CurrentCube

Update Dimension

[Destination Currency].[Destination Currency],

Default_Member =

[Destination Currency].[Destination Currency].[US Dollar];

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

"Deepak Puri" <deepa...@progressive.com> wrote in message

news:eBIJGIm6...@TK2MSFTNGP10.phx.gbl...

Deepak Puri

unread,
Nov 19, 2005, 6:20:38 PM11/19/05
to
Hi Richard,


Thanks for putting some more research into this issue. But you lost me
with this section on currency - and how it relates to the ranking
calculation:

/*-- Set default member for the Destination Currency cube dimension
----*/

..


My other question relates to the performance of the 2-parameter versus
the 3-parameter version of Rank() - at least as implemented in AS 2005
RTM. From Sept CTP timings I recorded (listed earlier in this thread),
the 2-parameter version seemed much faster. So would the 3-parameter
Rank() in the ccOrderRank calculation slow it down?

Chris Webb

unread,
Nov 21, 2005, 10:47:05 AM11/21/05
to
Richard - I think you've hit the nail on the head when you say that it's
pretty hacky - this is an old problem that comes up on this ng from time to
time, but MDX only offers hacky solutions to it. I think it's a valid thing
to want to do, though, so any more functionality to facilitate this kind of
calculation in MDX (such as the ability you mention to find the current
ordinal in a cellset) would be very welcome indeed.

Deepak - one of my colleagues has been messing around with AS2005 stored
procedures and came up with one that significantly improved on the
performance of any of the 'pure' MDX solutions mentioned on this thread. I'll
try to get him to write it up asap and post it on my blog.

Deepak Puri

unread,
Nov 21, 2005, 2:42:36 PM11/21/05
to
Chris and Richard,

I certainly look forward to Chris's solution with a stored procedure.
But there's still a specific lingering question, from my testing, that
intrigues me; so I wonder if either of you can shed light on this:

When I created a Named Set: [OrderedRows] within query scope, and used
it for ranking, performance was good. Because the named set expression
uses Axis(), it can't be created at the cube level. So, I created a
Calculated Measure: [OrderedRows], that is simply a string version of
the Named Set. This caused query time to go from 4 to 80 seconds.

If this increase is caused by the set being repeatedly computed in the
Calculated Measure, isn't there any way to exploit Calculated Measure
"caching" to avoid repeated computation (as Chris presumably did in this
blog entry below)? Or is the increase related to other overhead, such as
converting a set to a string and back?

http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!
170.entry
>>
Avoiding an expensive TOPCOUNT
..
What I did was create a calculated measure which returned the top n set
in string form; using STRTOSET you could then reference it from both the
rows axis and the OTHERS calculated member. The reason this second query
is faster than the first - and the guy who came to me with the problem
says that the second version runs in almost a quarter of the time - is
that the values returned by calculated members are cached so the second
time that Measures.MyTopStr is called it doesn't need to do the topcount
again.
..

Vladimir Chtepa

unread,
Jan 5, 2006, 9:30:15 AM1/5/06
to
Hi

You wrote

"if you have excel on your
machine - the rept function is not native mdx but from the excel vba
library)"

that means, that you solution works on the 32-bit platform. Doesn't it?

Thanks,
Vladimir Chtepa


"Richard Tkachuk [MSFT]" <ric...@microsoft.com> schrieb im Newsbeitrag
news:437a700b$1...@news.microsoft.com...

Reply all
Reply to author
Forward
0 new messages