I am seeing a performance problem when making the following queries in the
Project Real database:

---------------------------------------------------------------------------

SELECT { [Measures].[Sale Amt] } TYPE ON COLUMNS ,

NON EMPTY { ORDER( { DESCENDANTS( [Item].[Publisher Name].[All],
[Item].[Publisher Name].[Publisher Name] ) }, ( [Measures].[Sale Amt] ),
BDESC ) } ON ROWS

FROM [REAL Warehouse]


WHERE ( [Item].[By Category].[Subject].&[174], [Vendor].[Vendor
Name].&[PENGUIN GROUP (USA) PAPERBACKS] )

---------------------------------------------------------------------------

The query takes about 2:30 to run. I found two different ways to improve
the performance. The first is remove the sorting (i.e., ORDER) from the
query reduces the query down to 3 seconds, but the results are not ordered
correctly. The second solution is to replace the attribute hierarchy
([Vendor].[Vendor Name]) in the where clause with the corresponding strong
hierarchy ([Vendor].[Vendor].[Vendor Name]). This also reduces the response
time to a few seconds.


Why does sorting behave differently between an attribute hierarchy and a
strong (user-defined) hierarchy?



This MDX query takes more then 30 minutes:
---------------------------------------------------------------------------
SELECT NON EMPTY { [Measures].[Sale Amt] } ON COLUMNS ,

NON EMPTY { ORDER( { DESCENDANTS( [Customer].[Customer].[All],
[Customer].[Customer].[Customer Name] ) }, ( [Measures].[Sale Amt] ), BDESC )
} ON ROWS

FROM [REAL Warehouse]

WHERE ( [Time].[Calendar].[Calendar Qtr].&[4]&[2004],
[Item].[Author].&[SOLLORS W], [Item].[By Category].[Subject].&[87],
[Item].[Item].&[5932398], [Item].[Publisher Name].&[New York University
Press], [Store].[Geography].[City].&[Arlington Heights],
[Vendor].[Vendor].[Vendor Name].&[NEW YORK UNIVERSITY PRESS] )

---------------------------------------------------------------------------


The same solutions drastically reduce the response time.

Here are some Adventure works examples:

----------------------

select { [Measures].[Internet Order Count] } on columns,

NON EMPTY { ORDER ({ DESCENDANTS([Product].[Product].[All Products]
,[Product].[Product].[Product] ) }, ( [Measures].[Internet Order Count] ),
BDESC ) } ON ROWS

from [Adventure Works]

where ([Customer].[City].&[Spokane]&[WA])

----------------------


Takes 5 seconds, but replacing the attribute hierarchy with the strong
returns the results in 1 second:



----------------------

select { [Measures].[Internet Order Count] } on columns,

NON EMPTY { ORDER ({ DESCENDANTS([Product].[Product].[All Products]
,[Product].[Product].[Product] ) }, ( [Measures].[Internet Order Count] ),
BDESC ) } ON ROWS

from [Adventure Works]

where ([Customer].[Customer Geography].[City].&[Spokane]&[WA])

-----------------------



--
Brandon Stirling
ProClarity Corp.

other question...
where is the "Project Real" database???
I don'T see any download anywhere...

"Brandons" <Brandons (AT) discussions (DOT) microsoft.com> wrote

Hi Brandon,

When comparing the trace of the 2 versions of Adventure Works query in
SQL Profiler, there is a big difference in query processing event
patterns between the 2 - don't know how to correlate this to attribute
vs. strong heirarchy.

For the attribute hierachy version, the initial "Get Cahce Data"
specified All Products, and just a Customer City attribute of Spokane.
This is followed by a series of "Query Subcube - Get Cache Data", each
of which specifies an individual Product, along with Customer Country of
USA, State of Washington and City of Spokane.

By contrast, the strong hierarchy version uses all 3 attributes:
Customer Country of USA, State of Washington and City of Spokane in the
initial "Get Cache Data". After that, there don't seem to be any "Query
Subcube" and "Get Cache Data" events for individual Products at all -
maybe the initial "Get Cahce Data" retrieves all relevant data?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Project is REAL is a series or white papers starting with
http://msdn.microsoft.com/library/de...05InSrREAL.asp.

I am not sure it can be downloaded outside of Microsoft.

--
Brandon Stirling

"Jéjé" wrote:

ok, thanks.

"Brandons" <Brandons (AT) discussions (DOT) microsoft.com> wrote