Hi!

I need to display all members of a level in a web based client. Once the
membercount reaches around 500 performance becomes unacceptable. I've used a
catalog object to display all members of a level and then programmatically
page through the members collection. This didnt work because it loads all
members when first accessing the members collection (see my earlier
question). So i turned to MDX to try to do the paging there. The SubSet
function seemed to be perfect for this. But it seems this functoin first
resolves teh set its passed and then does the actual "subsetting". So when
using the function like: SUBSET([suppliers].[supplier].members, 0, 10) it
first resolves [suppliers].[supplier].members and THEN returns the members
from position 0 to 10. So im stuck with the same problem as with the catalog
approach. Now i've been looking at the possibility of using FILTER for
paging. What i would need is something like "WITH MEMBER [measures].[pos] AS
'[dimension].currentmember.POSITION(?)' ..
FILTER([dimension].[level].members, [measures].[pos] >= 0 AND
[measures].[pos] >= 100) to return members in positions 0 to 100. However i
cant find POSITION anywhere, not as a property in the properties collectoin
nor as an attribute of the member itself. Anyone know if the position of the
member is accessible anywhere?

Any help appriciated

Hi Peter,

There are a number of ways of doing what you're asking for in MDX - for
example, the ITEM function or the ID built-in member property - but I don't
think they'll solve your basic performance problem. Am I right in thinking
that you have a very large, flat dimension? That's to say that it has an All
Level and then all 25000 children? In that case, what you might want to do is
put in one or two levels in between, for example one of 25 members which each
have 10 children, which in turn have 100 children? You can then hide these
levels by setting their Visible property to false, so the users never see
them, but you can still reference them in your MDX; you would then be able to
page through the level by writing MDX something like
[MyDim].[n1].[n2].CHILDREN
and simply increment the values of n1 and n2 from 1 to 25 and 10 respectively.

The reason I think this will improve performance (apart from making your MDX
much easier) is that at the moment I suspect AS is trying to download the
whole of your 25000 member level from the server to the client (in this case
the web server), which can take a lot of time and resources. It might also be
worth checking out the Large Level Threshold connection string property too,
as this controls how and when AS downloads parts of dimensions from server to
client.

Regards,

Chris


"Peter Koller" wrote:

Thanks for your quick reply!

Your solution would work perfectly if i knew wich dimensions use this
technique, unfortunatly i dont. I'm building a general purpose client that
has no knowledge of such meta-meta data.
Any other ideas?


"Chris Webb" <OnlyForPostingToNewsgroups (AT) hotmail (DOT) com> skrev i melding
news:377CEA09-6F1D-40F3-8DE0-36334FFD8182 (AT) microsoft (DOT) com...

In that case, you're better off with using SUBSET or ITEM - here's an example
of the latter:

WITH SET SHOW AS '{[Customers].[CITY].MEMBERS.ITEM(30) :
[Customers].[CITY].MEMBERS.ITEM(35)}'
SELECT MEASURES.MEMBERS ON 0,
SHOW ON 1
FROM SALES

Large, flat dimensions are going to lead to all kinds of performance
problems even after you've got past the member selection process. There's not
much you're going to be able to do about this, I think, although playing
around with connection string properties might make a small difference.

Chris

"Peter Koller" wrote:

..ITEM(i) : .ITEM(y) works perfectly! It doesnt even error when stepping
outside the bounds of available members, so i dont need to do a count!

"Chris Webb" <OnlyForPostingToNewsgroups (AT) hotmail (DOT) com> skrev i melding
news:7AD575F8-C925-4551-A77F-344DC73B1836 (AT) microsoft (DOT) com...