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.Defa ultMember.Level,
NULL,
-- MEASURE IS LAST ITEM ON COLUMN AXIS - IF EMPTY THEN NULL
IIF(IsEmpty(StrToSet("Axis(0)").Item(0).Item(StrTo Set("Axis(0)").Item(0).Count
-1)), NULL,
-- DIMENSION IS LAST ITEM ON ROW AXIS
Rank(StrToSet("Axis(1)").Item(0).Item(StrToSet("Ax is(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

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 ***

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.Defa ultMember.Level,
NULL,
-- MEASURE IS LAST ITEM ON COLUMN AXIS - IF EMPTY THEN NULL
IIF(IsEmpty(StrToSet("Axis(0)").Item(0).Item(StrTo Set("Axis(0)").Item(0)
..Count
-1)), NULL,
-- DIMENSION IS LAST ITEM ON ROW AXIS
Rank(StrToSet("Axis(1)").Item(0).Item(StrToSet("Ax is(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.Defa ultMember.Level,
NULL,
-- MEASURE IS LAST ITEM ON COLUMN AXIS - IF EMPTY THEN NULL
IIF(IsEmpty(StrToSet("Axis(0)").Item(0).Item(StrTo Set("Axis(0)").Item(0)
..Count
-1)), NULL,
-- DIMENSION IS LAST ITEM ON ROW AXIS
Rank(StrToSet("Axis(1)").Item(0).Item(StrToSet("Ax is(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.Defa ultMember.Level,
NULL,
-- MEASURE IS LAST ITEM ON COLUMN AXIS - IF EMPTY THEN NULL
IIF(IsEmpty(StrToSet("Axis(0)").Item(0).Item(StrTo Set("Axis(0)").Item(0)
..Count
-1)), NULL,
-- Member is first in [OrderedRows]
IIF(Rank({StrToSet("Axis(1)").Item(0).Item(StrToSe t("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("Axi s(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

Deepak Puri
Microsoft MVP - SQL Server

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

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).


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

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...-olap/200510/1

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:

'[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!


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

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

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.

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.Defa ultMember.Level,
NULL,
-- MEASURE IS LAST ITEM ON COLUMN AXIS - IF EMPTY THEN NULL
IIF(IsEmpty(StrToSet("Axis(0)").Item(0).Item(StrTo Set("Axis(0)").Item(0)
.Count
-1)), NULL,
-- Member is first in [OrderedRows]
IIF(Rank({StrToSet("Axis(1)").Item(0).Item(StrToSe t("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("Axi s(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])))))));

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

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" wrote:

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 ...


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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