Re: AS2000 Query very slow with Excel 2003 PTS

generally, the mdx query created by the pivottable is not so good.
if you starts to filter or order members the query is slow.

to improve the response time, try to disable totals or disable the visual total in excel. most of the time the performance is related to these totals specially with AS2000

Order commands provides bad response time too. the way the query is created (again) create a big overhead and slow down the response time.

"Fred" <Fred@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:28852786-AFBA-4CDD-A8EA-C14B9C87210D@xxxxxxxxxxxxxxxx
Hello Deepak,

I am not using any calculated measures. I suspect the problem is related to
my use of 13 dimensions two of which contain about 25 characters each. Have
you run into performance degradation with dimensions that contain large
values? My fact table only has about 10K records.

Thanks.

"Deepak Puri" wrote:

For any calculated measures, set the NON_EMPTY_BEHAVIOR property if
possible:

http://support.microsoft.com/default.aspx?scid=kb;en-us;304137
>>
INF: How to Increase the Speed of MDX Queries that Contain the NON EMPTY
Keyword

Article ID : 304137
Last Review : July 15, 2004
Revision : 1.1
This article was previously published under Q304137

SUMMARY
In some cases, a query slows down considerably when you use both the NON
EMPTY keyword on an axis of a Multidimensional Expression together with
a calculated member.

This article describes how you can optimize a query, by using the Non
Empty Behavior property for the calculated member.
..
>>


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

.

Relevant Pages

  • Re: Querying AD for Group information (type/scope)
    ... To Query for Distribution groups: ... To query for all users that have some group other than "Domain Users" ... users with primaryGroupID equal to 513 belong to the "Domain Users" group. ... The reason the member attribute of the "Domain users" and some other ...
    (microsoft.public.windows.server.active_directory)
  • RE: Hotel Booking Project Delemma
    ... you can add the DISTINCT clause in the query serving data to the report. ... > I am having a dilemma with a Hotel Booking Project which is ... > In the tblHotelBookings I have fields to capture Number of Nights, ... > If one member is booked, but has a second person ...
    (microsoft.public.access.tablesdbdesign)
  • Re: ADAM Subset
    ... Yep this would be a case for an attribute scoped query and as JoeK indicates would require a base level query, ... Joe Richards Microsoft MVP Windows Server Directory Services ... There is a trick called attribute scope query that ADAM supports that allows you to search within the values of a DN-syntax attribute (like member) to filter the results and return attributes on the objects therein. ...
    (microsoft.public.windows.server.active_directory)
  • Re: 2nd layer of filtering?
    ... With DAO, I can rewrite a query using VBA and a querydef object. ... It basically says in psuedo SQL, Select the Member if the member has a ... >> FROM qMembershipSelect AS Y ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: 2nd layer of filtering?
    ... I can rewrite a query using VBA and a querydef object. ... > Would it be better to do this manual loop from the main database, ... > It basically says in psuedo SQL, Select the Member if the member has a ... >>> FROM qMembershipSelect AS Y ...
    (microsoft.public.dotnet.framework.adonet)