The DrillUpMember() Function
Introduction
According to the Books Online,
the DrillUpMember() function "returns a set of members based on the
members included in the set specified in Set1 that are descendants of members
in Set2." The set of child members returned by DrillUpMember() is ordered according to the arrangement within the primary set.
DrillUpMember() assembles the
set by including only those members specified in the primary set that
are immediate descendants of members specified in the secondary set.
DrillUpMember() has
many applications, and 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 Descendants() function and others. We
will examine in detail the syntax for the DrillUpMember() 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 DrillUpMember() can offer knowledgeable developers and / or
report authors. Hands-on practice with DrillUpMember(), wherein we will
create a query that leverages 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 DrillUpMember()
function builds a set of the members based upon a specified primary set
which are descendants of members of a specified secondary set. The
primary set can have any dimensionality, but the secondary set
must be of a single dimension, as we shall see.
If the secondary set does not contain an immediate
ancestor for a given member within the primary set, then that primary set
member is not "drilled up"; the member is simply returned as it
appears within the primary set. In plain terms, if an immediate ancestor
of a given primary set member exists in the specified secondary
set, then that ancestor is returned ("drill up" to
the ancestor occurs within the set returned by the function).
Alternatively, any member specified in the primary set without an
immediate ancestor in the secondary set is simply returned as its "not
drilled up" self. The descendants in the primary set
with immediate ancestors in the secondary set are themselves returned,
as well, with the ultimate result being the member(s) and the "rolled up"
ancestor(s) appearing in the same returned set.
The primary set can consist of tuples instead of
members. In cases like this, a set of tuples is returned instead of a set of
members. As we noted earlier, we can specify mixed dimensionality in the primary
set, but the secondary set can be specified only as a single
dimension.
When we couple it with other functions, we can leverage DrillUpMember()
to deliver a wide range of analysis and reporting utility. As is often the
case 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 DrillUpMember().
Syntax
Syntactically, anytime we
employ the DrillUpMember() function to drill up by a member, the effect
of the function, as we have seen, is to drill up those members of the specified
primary set for which immediate ancestors appear within the specified secondary
set. We specify the two sets within the parentheses to the right of the DrillUpMember
keyword. The general syntax is shown in the following string:
DrillUpMember(Set1,Set2)
(Set1 in the string represents the
"Primary Set" to which I refer throughout our discussion. Set2
represents the "Secondary Set.")
Let's take a look at an example.
The following snippet employs the DrillUpMember() function:
DRILLUPMEMBER( {[Geography].[Geography].[State-Province].[California],
[Geography].[Geography].[City].[Canoga Park],
[Geography].[Geography].[City].[Carson],
[Geography].[Geography].[City].[Citrus Heights],
[Geography].[Geography].[City].[Fullerton],
[Geography].[Geography].[State-Province].[Washington],
[Geography].[Geography].[City].[Bellevue],
[Geography].[Geography].[City].[Bellingham],
[Geography].[Geography].[City].[Lacey],
[Geography].[Geography].[City].[Seattle]},
{[Geography].[Geography].[State-Province].[Washington]}) 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 on the column axis, for calendar year 2003 might
produce a results dataset similar to that depicted in Illustration 1.
Illustration 1: Results Dataset – Washington Cities
Drilled Up to State Level
In the example dataset, we see that California and Washington
(the top and bottom rows of the returned dataset) are presented in "rolled
up" state. Underneath California's summary line, we see the values
for the constituent child cities. We do not see the cities that
contribute to the Washington summary line appear, however. This is
because we have specified Washington as the secondary set within the DrillUpMembers()
function employed in the snippet above. Because the State of Washington
is the immediate ancestor of the four Washington cities we specify in the
snippet, the function drills up to the State level for those cities, and
presents the summary for Washington alone.
We will practice a use of the DrillUpMember() function
in the section that follows.
» 
Comment and Contribute
