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

We will create an MDX query that helps us to "qualify" a second query as containing elements that cause it to fall within the two conditions we have exposed above that force a query to process on the server.

-- MXAS11-1:  Qualification through Count
WITH
   MEMBER[Measures].[Count] AS 
   'COUNT({ [Product].[Product Name].Members})'
SELECT
{[Measures].[Count]}ON COLUMNs
FROM Warehouse

Again, our intent here is to ascertain that an upcoming example expression ([Product].[Product Name].Members) will "qualify" as meeting conditions that would force it to process on the server. Therefore, in our first query, we are simply obtaining a count of the members of the given level.

The results dataset appears as shown in Illustration 3.

We see that [Product].[Product Name].Members refers to a genuine Large Level, because the number of members in the Product Name level (1,560) of the Product dimension exceeds the Large-Level Threshold we set in our last lesson (750). For that matter, it exceeds even the default threshold that existed before our modifications (1000). (The number of members is also verifiable at the RDBMS level in the FoodMart2000.mdb sample that is installed with MSSQL Server 2000 Analysis Services).

Let's use the level whose population we have just quantified in the COUNT query above to illustrate. The inclusion of a filter within our query will also be a driver for server-based processing, as we shall see.

-- MXAS11-2:  Qualification through Count & Filter
SELECT
{[Measures].[Units Shipped]} ON COLUMNS,
TopCount ([Product].[Product Name].Members, 
7, [Measures].[Units Shipped]) ON ROWS
FROM Warehouse

The use of TopCount() above provides an instance where server-based execution is likely to be appropriate, for the reasons we have already exposed. Most of the large level will be pruned away by the filter action before returning the result. As we noted earlier, existence of a filter operation within the query is another driver for server processing.

The results dataset appears as shown in Illustration 4.

While level-size and filter requirements are good criteria to use in most cases for determining the likelihood of forced server-based processing, there are scenarios where even meeting or exceeding the parameters of these two criteria will not force a query to execute on the server. Examples of these situations include the presence of a function or functions within the query (say a user-defined function that is registered solely on the client) that cannot execute on the server. Also, as is somewhat obvious, a query that is executed against a local cube will not process on a remote server.

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 together, optimal syntax arrangement in our queries is perhaps a more important consideration. Before we attempt to optimize performance in this way, we need to gain a solid understanding of how the PivotTable Service works.