Hi,

I'm using AS2005 and the integrated browser.
I test my cube by adding some filters and 1 measure "# of sales"
then I add the customer name in rows (only 600 customers are returned)
the response time is less then 1 second.

then I try to sort the # of sales measure (descending sort)
and the server starts to use 100% of the CPU
after 5 minutes I stop the process and there is no response from it.

any idea?

thanks.

Jerome.

Can you reproduce this behavior in Adventure Works cube (using an
appropriately sized hierarchy like Products)?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

I can't reproduce this using the adv. work cube

in my case, the customer dimension has no hierarchy, I put the customer name
directly in rows.
my dimension contain 40 000 customers, but when I filter a year, a product
line, and a customer gender, only 600 customers are in the filtered list.
and the sort command is on this list.
also, I sort on a measure, not a calculated one.


"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote

A couple of ideas to narrow down the problem:

- Use SQL Server Profiler to capture the MDX query used by the browser
for sorting (assuming this is possible). Maybe the query is not
optimized?

- Add discretization grouping to the Customer attribute, just to see if
the problem is alleviated by levels:

http://msdn2.microsoft.com/en-us/library/ms175644.aspx

The grouping of members of an attribute into buckets is called
discretization. When end users browse a level of a hierarchy that is
based on an attribute, they see the names and values of the buckets,
instead of the members themselves. To end users, the buckets look like
regular members.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

I can't use the discretization option because this member is the leaf member
and all my attributes are linked to this attribute.

well... after some tests
I have renamed my attribute from Customer to Full Name and set the option to
discourage grouping (like in adv. works sample)
and now I have some results.
query before:
CREATE SESSION
SET [Sales].[{608281EE-D6A3-4411-9E2D-7E746AC3BF1C}Pivot33Axis1Set0] AS
'
{
{ [Customer].[Customer].[All] },
ORDER(
{
AddCalculatedMembers([Customer].[Customer].[Customer].MEMBERS)
},
(
[Measures].[#Sales],
[Store].[Store].[Store].&[13],
[Calendar].[Calendar by Fiscal Year].[Fiscal Year].&[2005]
),
BDESC
)
}
'

query after:
CREATE SESSION
SET [Sales].[{608281EE-D6A3-4411-9E2D-7E746AC3BF1C}Pivot33Axis1Set0] AS
'
{
{ [Customer].[Full Name].[All] },
ORDER(
{
AddCalculatedMembers([Customer].[Full Name].[Full Name].MEMBERS)
},
(
[Measures].[#Sales],
[Store].[Store].[Store].&[13],
[Calendar].[Calendar by Fiscal Year].[Fiscal Year].&[2005]
),
BDESC
)
}
'


"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote