Microsoft KB Archive/938495

= Slow performance when you run an MDX query that accesses the key attribute hierarchy in a parent-child dimension in SQL Server 2005 Analysis Services =

Article ID: 938495

Article Last Modified on 10/31/2007

-

APPLIES TO


 * Microsoft SQL Server 2005 Analysis Services

-



SYMPTOMS
In SQL Server 2005 Analysis Services, you may experience slow Multidimensional Expressions (MDX) query performance. This problem occurs if the query uses the key attribute hierarchy to access the key attribute members in a parent-child dimension. Additionally, this problem becomes more apparent when the query contains lots of calculated members.

In this case, if you use Windows Performance Monitor to monitor the Analysis Services process, you may notice high memory utilization by the Analysis Services process. Additionally, you find that many subcube requests are generated when you run the query.



CAUSE
This issue occurs because extra subcubes are requested when you run the MDX query. When the MDX query retrieves key attribute members of a parent-child dimension through the key attribute hierarchy, the subcube that is generated has an arbitrary shape. Arbitrary shape subcubes are not reused. Arbitrary shape subcubes are reevaluated every time that a cell value is determined. This behavior causes extra subcubes to be requested. The extra subcube requests slow down the query and consume more memory. The additional calculations increase the cube space that SQL Server 2005 Analysis Services must evaluate when SQL Server 2005 Analysis Services performs the calculations. The expanded cube space increases the number of potential subcube request combinations. Therefore, more subcube requests may be generated. Together, the arbitrary shape subcube requests and the presence of additional calculated members make the performance problem more obvious.

Note An arbitrary shape set is a set that cannot easily be expressed as a crossjoin set of two sets of attribute members. For example {(2004, USA), (2001, Canada)} is an arbitrary shape set.



WORKAROUND
To work around this issue, avoid retrieving members by using the key attribute hierarchy in the parent-child dimension. You must make the key attribute hierarchy of the parent-child dimension invisible. To do this, set the AttributeHierarchyVisible option to False. Users cannot see this hierarchy. Therefore, users cannot browse the hierarchy.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the &quot;Applies to&quot; section.



MORE INFORMATION
The following example query demonstrates the problem and the workaround: WITH

MEMBER DebugMeasure0 as 1 MEMBER DebugMeasure1 as 1 MEMBER DebugMeasure2 as 1 MEMBER DebugMeasure3 as 1 MEMBER DebugMeasure4 as 1 MEMBER DebugMeasure5 as 1 MEMBER DebugMeasure6 as 1 MEMBER DebugMeasure7 as 1 MEMBER DebugMeasure8 as 1 SET DebugSet AS   { [Measures].[DebugMeasure0]

,[Measures].[DebugMeasure1] ,[Measures].[DebugMeasure2] ,[Measures].[DebugMeasure3] ,[Measures].[DebugMeasure4] ,[Measures].[DebugMeasure5] ,[Measures].[DebugMeasure6] ,[Measures].[DebugMeasure7] ,[Measures].[DebugMeasure8] } SELECT CROSSJOIN (       {            [Product].[Model Name].&[Sport-100],             [Product].[Model Name].[all products]        },        DebugSet    ) ON 0, FILTER (   -- The workaround is to avoid using [Employee].[Employee].[Employee].MEMBERS -- [Employee].[Employee].[Employee].MEMBERS,        Descendants(Employees,, LEAVES),         [Measures].[Reseller Tax Amount] >= 100000    ) ON 1 FROM [Adventure Works]

Keywords: kbtshoot kbsql2005as kbexpertiseadvanced kbprb KB938495

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.