MDX in Analysis Services: Optimizing MDX: More on Location, and the Importance of Arrangement - Page 4

The Importance of Optimal Arrangement

To paraphrase some of what we have learned so far in the Optimizing MDX mini-series, queries that contain levels whose populations of members exceed the Large Level parameter will be processed by the server; queries with levels whose member populations do not exceed the Large Level Threshold in number are within the processing capabilities of the client-based PivotTable Service operation.

The manner in which the PivotTable Service processes queries can become a consideration within the determination of the location of processing. The service acts in such a way that each set (and every combination existing therein) defined in the query is fully incarnated in memory before proceeding with operations. Unsurprisingly, the demands on resources can be crippling for even the most robust machines.

Let's consider an example:

-- MXAS11-3:  Pre-optimized Set Operation
SELECT
{[Measures].[Units Shipped]} ON COLUMNS,
TopCount (
   CrossJoin(
      [Warehouse].[City].Members,
      [Product].[Product Name].Members
),
7,
[Measures].[Units Shipped]
) ON ROWS 
FROM Warehouse

We have expanded our existing query well beyond its last incarnation, adding another dimension and a CrossJoin() function, as if to enhance it to meet (as an illustration) a specific need of an information consumer. We know that the query will be processed on the client if the population of the [Product].[Product Name] level is less than the Large Level Threshold (1,000 default / 750 the setting from our last lesson).

An important fact to remember is that the threshold refers to the level's members; it does not refer to the tuples that exist within a given set within a query. Say our Large Level Threshold has been set at 2200. We already know that we have 1560 named products (from the count we did above); we also have 23 warehouse cities. While the "memberships" of both levels fall well below the threshold of 2200, and thus qualify for client processing, and while our results dataset is the top seven city-product tuples from the perspective of units shipped, we have far more combinations, in the way of resource requirements, with which to contend from the scope of our query.

Our query will assemble over 35,000 combinations (1560 x 23) - all to arrive at the small results dataset that we obtain in the next step.

The results dataset appears as shown in Illustration 5.

Our query executes in a matter of moments, even though it is completing myriad more steps than we may have considered. However, things might have turned out quite differently, within the scope of the realities of similar - but much larger - scenarios that we might have encountered in the real world.

Not taking the real cost of the actual combinations, and relying upon the large level and other level-member-based safeguards to protect us, may actually place a load upon the client that would challenge even a robust server. At the heart of this resource intensive situation lies our old friend the CrossJoin() function, although there are many other similar potential participants in inefficient query construction and operation. We will examine ways to manage these scenarios in the next section.