The DrillUpLevel() Function
Introduction
According to the Books Online,
the DrillUpLevel() function "drills up the members of a set that
are below a specified level." The
set of members returned by DrillUpLevel()
is ordered by hierarchy, based
upon the members included in a set that we specify within the function.
The order among the original members
in the Set Expression is preserved.
DrillUpLevel() also provides us the option of specifying a Level,
to serve as the threshold upon which the set returned by the function is
based; that is, when we specify a Level, DrillUpLevel() retrieves
only those members within or above the specified level to construct the
set it returns. In cases where we do not specify the optional Level, DrillUpLevel()
retrieves only those members that are a single level higher that the lowest
level of the first dimension referenced within the Set we specify within
the function.
DrillUpLevel() has many
applications, and, as we have seen to be the case with other "drilling"
functions, pairing it with other MDX functions can help us to leverage its
power even further. I frequently use it in combination with various functions,
including the CrossJoin() and Descendants() functions (as we do
within the practice session of this article), among others. We will examine in
detail the syntax for the DrillUpLevel() function after our customary
overview in the Discussion section that follows. Following that,
we will conduct practice examples within a couple of scenarios, constructed to
support hypothetical business needs that illustrate uses for the function.
This will afford us an opportunity to explore some of the delivery options that
DrillUpLevel() can offer the knowledgeable user. Hands-on practice with
DrillUpLevel(), where we will create queries that leverage the function,
will help us to activate what we learn in the Discussion and Syntax
sections.
Discussion
To restate our initial explanation of its operation, the DrillUpLevel()
function drills up the members in a specified Set that lie below a
specified Level. In effect, DrillUpLevel() removes the members of
the specified Set that lie below the specified Level. The order
of the specified Set, again, is maintained, and the set of members
returned is ordered by hierarchy.
As we mentioned earlier, a Level Expression can
optionally be specified. If no Level is specified within the function, DrillUpLevel()
assumes the specified Level to be a single level above the members of
the lowest hierarchical level within the Set. The members residing
at the lowest level of the set are drilled up to the next highest level –
meaning that the lowest level members, upon which the drillup is based,
disappear.
When we specify the Level, DrillUpLevel(), in
effect, drills up all members within the Set Expression that exist
below the specified Level, to the next highest level (assuming, of
course, that a "next highest level" exists within the structure). If we specify a Level in the function,
and it turns out that no members within the Set Expression exist within
the specified Level, then an identical set to that specified within the
Set Expression is returned by DrillUpLevel(), in a manner
identical to that we saw in the DrillDownLevel() function in MDX
Set Functions: DrillDownLevel().
DrillUpLevel() works quite well with sets that have
been produced by the DrillDownLevel() function – for that matter, even
with the DrillDownMember() function. We will be using it with DrillDownLevel(),
in the first of our practice examples, to illustrate the "cooperation"
between the two, perhaps giving insight into the way that they can be readily "paired,"
in switch-like fashion, to support navigation within our reporting and analysis
applications.
As we have noted earlier, when we couple it with other
functions, we can leverage DrillUpLevel() to deliver a wide range of
analysis and reporting utility. As in so many cases with the Microsoft
integrated Business Intelligence solution, consisting of MSSQL Server, Analysis
Services and Reporting Services, this function, residing within the Analysis
Services layer, can be extended to support capabilities and attributes in
the Reporting Services layer. Knowing "where to put the
intelligence" among the various layers is critical to optimization, in
many cases. For more of my observations on this subject, see Multi-Layered
Business Intelligence Solutions ... Require Multi-Layered Architects.
Let's look at some syntax illustrations to further clarify
the operation of DrillUpLevel().
Syntax
Syntactically, anytime we
employ the DrillUpLevel() function to drill up, the effect of the
function, as we have seen, is to create a set by retrieving only the members
that exist above the specified Level. We specify the Set_Expression
(which can be multi-dimensional) within the parentheses to the right of the DrillUpLevel
keyword. The general syntax is shown in the following string:
DrillUpLevel(Set_Expression [ , Level_Expression] )
When no Level Expression
is specified, DrillUpLevel() retrieves only the members of the Set
Expression that are one level above the lowest level in the first
dimension referenced in the Set Expression. DrillUpLevel()
returns the set specified in the Set Expression itself, when there are
no set members within the specified Level – in other words, when there is
nothing from which to drill up – as might be expected.
Let's take a look at an
example. The following snippet employs the DrillUpLevel() function:
NON EMPTY( DRILLUPLEVEL(
{[Geography].[Geography].[Country].[United States],
[Geography].[Geography].[State-Province].[Georgia],
[Geography].[Geography].[City].[Atlanta],
[Geography].[Geography].[City].[McDonough]},
[Geography].[Geography].[State-Province]
)) ON AXIS(1)
This row specification, within a
proper query executed against the Adventure Works sample cube that, say,
stipulated the Reseller Sales Amount measure within the column axis, and
Calendar Year 2004 as a slicer, might produce a results dataset similar
to that depicted in Illustration 1.
Illustration 1: Results Dataset – Georgia Cities Drilled
Up to Higher Levels
In the example dataset, we see that State-Province Georgia,
along Country United States, two members specified within our Set
Expression, appears alongside its summary Reseller Sales Amount.
The two Georgia child Cities (Atlanta and McDonough)
and their respective values do not appear. While the two cities are members of
the set we specify, DrillUpLevel() has, in this case, been supplied the
optional Level Expression. That expression, [Geography].[Geography].[State-Province],
directs the function to drill up the members of the set that appear below the
specified level, in effect removing any members from the result dataset that
lie below the State-Province level. The cities therefore are swept out
of the retuned dataset.
We will practice some uses of the DrillUpLevel()
function in the section that follows.
» 
Comment and Contribute
