When trying to loop through members of a level by an adomd.catalog object
performance takes an incredible hit.
My level has around 25.000 members and it takes about 3 minutes to complete
a loop through them in VB:

For Each mem In Members (adomd.level.members object)
debug.print mem.UniqueName, mem.Caption
Next

It seems like the for each handler loads the entire member collection when
first called. Is there any way to make it behave otherwise? Trying to access
the .count property has the same result, so i cant
for i = 0 to members.count... Anyone have any suggestions on how to stop
adomd from loading the entire members list? What i really want to do is
make a search function that returns the members in a level that match a
passed string. This is very hard to do if this is the performance i have to
live with,


Any help appriciated.

Hi Peter,

It might be better to abandon the ADOMD approach (which I think is always
going to perform badly), and instead write an MDX query that does what you
want using VBA functions - it's likely to be much faster. For example, here's
a query for Foodmart 2000 returning all Cities with names beginning with 'B':

SELECT MEASURES.MEMBERS ON 0,
FILTER(
[Customers].[City].MEMBERS
, LEFT([Customers].CURRENTMEMBER.NAME, 1)="B"
)
ON 1
FROM SALES

hth,

Chris

"Peter Koller" wrote: