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

More on Location

In our last lesson, we focused on two of the main ways to control processing location, the Large Level Threshold property and the Execution Location parameter, as a part of our discussion of the first intervention type, control of location of query execution. While these two options provide perhaps the most straightforward ways to control where a query is evaluated and executed, there are additional, less direct ways to force processing in a desired location. We will consider some of these approaches to conclude our discussion of the location control intervention type.

Other Means of Influencing Execution Location

At the individual query level, no means is readily available for a client application to direct execution location. We can, however, mandate that large-level operations execute at the client through the use of indirect means. The specification of a named set for use within the query will force processing at the client level. We can, therefore, create a named set (using either of the CREATE SET or WITH SET clauses), containing members of a large level, at the client, and then use the same named set within a query to force client-based execution.

Other options for indirect control of the processing location include calculated members and calculated cells. The manner of creation of a calculated member is important in determining its location-fixing effects. Using the CREATE MEMBER or WITH MEMBER clauses, within a query to define a calculated member at the server, will produce a calculated member that can be processed at the server or client equally successfully. By contrast, using CREATE MEMBER to produce a calculated member within a session will result in forced client-based execution of the query that houses it.

Calculated cells may also force client-based processing. Again, the manner in which the calculated cells are defined is important in determining their location-fixing effects. A calculated cell that is created with the CREATE CELL CALCULATION clause, at either the client or the server, can be processed at the server. By contrast, the use of the WITH CELL CALCULATION clause at the client will result in a query whose processing will be client-based.

The existence of two conditions can force a query to process on the server: a reference to a filter operation within the query, and (consistent with our discussion regarding large levels in our last lesson) a large dimension level. Let's take a look at the mechanics behind this in a little more detail.

We will first fire up the MDX Sample Application, having seen in past lessons how it provides an excellent platform from which to learn about MDX and, as in this case, about the data and the metadata in our cube. Many of the MDX operations that might be performed from a client application can be simulated here or elsewhere, as we demonstrate in an article in our DatabaseJournal Analysis Services series, Drilling Through to Details: From Two Perspectives. The Sample Application affords us another point of view of the interplay of the OLAP data source and MDX.

NOTE: It is important to remember that client applications will differ in many ways. Individual settings, design characteristics, capabilities, and other considerations will likely mean differences in operation and performance using the techniques we describe in our lessons, as in other references.

We are initially greeted by the Connect dialog, shown in Illustration 1.

The illustration above depicts the name of my server, MOTHER1, and properly indicates that we will be connecting via the MSOLAP provider (the default).

The MDX Sample Application window appears.

The MDX Sample Application window should resemble that depicted in Illustration 2, complete with the information from the Warehouse cube displaying in the Metadata tree (left section of the Metadata pane).