Performace problems with large dimensions

[brentl]

I'm using Microsoft Analysis Services with CAP 8.5, and have an item dimension with about 100,000 base level members. CAP works fine when I drill down through parent levels, but occassionaly, I need to a TOP x query over all of the base level members.

I've tried two things. First, from CAP's designer, I went to the Row Dimension Panel and used the "Select All Base Members" option. This takes well over an hour.
Next, I edited the MDX to refer to these base members with [Dimension Name].[Level Name].members syntax. The query runs in just a few seconds in the MDX Sample Application, but takes over an hour with CAP.

CAP seems to be re-writing the MDX statement by referring to each member individually.

I keep thinking I'm missing somehting elementary here. We currently have OLAP reports over the web using ADOMD, but would like to have Crystal's ad-hoc capabilities. I haven't gotten anywhere with Crystal's Tech Support people. Any insight will be greatly appreciated.

 

[OliverS]

I don't have a long delay (~10s) when selecting all base members in the MemberSelector, but I do get really long delays when trying to edit the MDX after selecting lots of members. I'm wondering if CAPro does use something similar to the [Dim Name].[Level Name].members internally but for some reason can't use that to write out the MDX.

Do you really need to edit the MDX? And I find that TOP x takes ages in the MDX sample app as well.

Oliver

 

[brentl]

Thanks for your reply. I think your correct about CAP not being able to write out the MDX using .members, I just question what it is doing internally as well.

I just got a response back from Crystal Decisions help by e-mail, and they basically said that they had found a performance problem handling large numbers of fields and were getting the information to their developers. Hopefully, this is somehting they can correct fairly quickly.