We have seen that options exist in the form of the Execution Location and Large Level Threshold properties to influence the physical location in which a query is processed. These two options within the query processing location intervention type could allow us to take advantage of performance gains based upon our being able to assign resources appropriately. While the Analysis Server provides for tuning in its provision for the establishment of a Large Level Threshold, as we saw in our previous session, optimal syntax arrangement in our queries is another important consideration. Before we attempt to optimize performance in this way, we need to gain a solid understanding of how the PivotTable Service works.
Optimizing Expression Arrangement
To paraphrase some of what we have learned so far in our Optimizing MDX articles, 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:
12. Create the following new query:
— SSP_007-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 article).
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.
13. Execute the query using the Run Query button.
The results dataset appears as shown in Figure 5.
Figure 5: Results Dataset, CrossJoin() added to Query
Our query executes in a matter of moments, even though it is completing myriad more steps than we may have considered. But 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.
14. Save the query as SSP_007-3.
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.