PRB: Slow Query Performance Occurs When You Run an MDX Query That References Dimension Member Properties (826307)

SYMPTOMS

When you run a Multidimensional Expression (MDX) query that references the member properties of a dimension, you may notice that the query performance is slow. This problem may occur if the MDX query references member properties of members that are in a dimension with many siblings with many densely populated member properties.

You may notice that the slow performance occurs only when you run the MDX query initially. If you run the MDX query subsequently, the performance of the MDX query may be very fast.

CAUSE

When a member of a dimension is referenced in an MDX query, all the member siblings of the member are loaded into the memory of the Analysis Services client computer. Even if the MDX query references a member with a specific cell data value, all the member siblings of the member are loaded into the memory.

When a property of a dimension member is referenced in the MDX query, all the member properties of all the member siblings of that member are loaded into the memory. Depending on the number of member siblings and the number and the density of the member properties, this may represent several megabytes of data that will be loaded into the client's memory space and cached. The time that it takes to load the data to the PivotTable Service cache on the client computer causes the performance degradation that is mentioned in the "Symptoms" section. Subsequent executions of the query are faster because the data is already loaded in the client cache.

RESOLUTION

To resolve the problem, do one of the following:

Reduce the number of member siblings in the level that is being referenced in the MDX query.
Reduce the number of member properties of the referenced member and its member siblings.
Use Drillthrough filters in the MDX query to limit the number of rows that are returned by the MDX query.
Use the XML/A Provider after warming the client cache of the provider.
Use virtual dimensions.

MORE INFORMATION

The latency (or the response time) of the initial MDX query varies depending on the number of member properties of the dimension and the number of children of the dimension's parent level (siblings of the dimension). Assuming that there are X number of member properties for the dimension, and that there are Y number of children at the parent level, the initial latency of the query that references a member property of the dimension can be calculated as X * Y.

This problem occurs only in large dimensions with approximately over 30,000 members at the leaf level and with densely populated member properties for the members.

For example, consider the following MDX query example that references the Customer dimension in the FoodMart 2000 sample database in Analysis Services:

Select Measures.Allmembers On Columns, 
[Customers].[Name].[Member Card] Dimension Properties
{[Customers].[All Customers].[USA].[WA].[Yakima].[Allison Ross]} On Rows 
From Sales

Running this MDX query may load all the member's siblings, even though the query only requests information for a single customer. When a member property of a member is referenced in the MDX query, all the member properties of all the member siblings for that member are loaded into the memory. Assuming that there are 90 member properties that are character data type with a length of 250 characters, and that there are 25,000 customers in Yakima, WA, running the query example that is mentioned earlier may result in loading approximately 565 megabytes (MB) of data into the client computer's memory.

Modifying the query example as follows without requesting the member properties may result in Analysis Services loading only about 3 MB of data into the client computer's memory.

Select Measures.Allmembers On Columns, 
{[Customers].[All Customers].[USA].[WA].[Yakima].[Allison Ross]} On Rows 
From Sales