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
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
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.
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.
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.
Start the MDX
initially greeted by the Connect dialog, shown in Illustration 1.
The Connect Dialog for the MDX Sample Application
illustration above depicts the name of my server, MOTHER1, and properly
indicates that we will be connecting via the MSOLAP provider (the
Sample Application window appears.
Clear the top
area (the Query pane) of any remnants of queries that might appear.
Ensure that FoodMart
2000 is selected as the database name in the DB box of the toolbar.
Select the Warehouse
cube in the Cube drop-down list box.
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).
Illustration 2: The MDX Sample Application Window