The DrillDownMember() Function
Introduction
According to the Books Online,
the DrillDownMember() function "drills down the members in a
specified set that are present in a second specified set. Alternatively, the function drills down on a set of
tuples." The set of child members returned by DrillDownMember() is ordered by hierarchy, and includes those members
specified in the primary set that are also present in the secondary
set. The order among the original
members in the primary set is maintained, with all child members
returned by the function included immediately under their parent member.
DrillDownMember() 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 Descendants() function and others. We will examine in
detail the syntax for the DrillDownMember() 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
DrillDownMember() can offer the knowledgeable user. Hands-on practice
with DrillDownMember(), 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 DrillDownMember()
function drills
down the members in a specified primary set that are present in a
specified secondary set. The primary
set can have any dimensionality, but the secondary set must contain
a one-dimensional set, as we shall see. The result set returned by DrillDownMember()
is composed of the children of each primary set member that also
exists in the secondary set. If the primary set contains a
parent member with one or more children, that parent member will not be
subjected to drilldown. When the primary set contains tuples, DrillDownMember()
returns a set composed of the results of drilling down each tuple in the primary
set for which a corresponding match exists in the secondary set.
We noted earlier that the
optional RECURSIVE keyword is available to enable repeated comparison
passes between the primary and secondary sets. When we do not
opt to add RECURSIVE, a single pass through the primary set is
performed, matching each member / tuple with a corresponding member in the secondary
set. When we add the keyword, DrillDownMember() performs continual,
recursive comparisons between the result set and the secondary set,
returning, for each member in the result set that is also present in the
secondary set, the children - until no further members from the result
set can be found within the secondary set. Because only complete
hierarchies are accepted, RECURSIVE forces drill down to the leaf level
of a specified dimensional hierarchy.
When we couple it with other functions, we can leverage DrillDownMember()
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 DrillDownMember().
Syntax
Syntactically, anytime we
employ the DrillDownMember() function to drill down by a member, the
effect of the function, as we have seen, is to drill down those members of the
specified primary set that are also present in the specified secondary
set. We specify the two sets within the parentheses to the right of the DrillDownMember
keyword. We can optionally follow the specified sets, within the same
parentheses, with the RECURSIVE keyword. The general syntax is shown in
the following string:
DrilldownMember(Set1, Set2 [ , RECURSIVE ] )
(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 DrillDownMember() function:
DrillDownMember( {[Geography].[Geography].[State-Province].[California],
[Geography].[Geography].[State-Province].[Oregon],
[Geography].[Geography].[State-Province].[Washington] },
{[Geography].[Geography].[State-Province].[Oregon]} ) )ON ROWS
This row specification, within
a proper query executed against the Adventure Works sample cube that,
say, stipulated year 2003 on the column axis, might produce a results
dataset similar to that depicted in Illustration 1.
Illustration 1: Results Dataset - Oregon Drilled to Child
Cities
In the example dataset, we see that California and Washington
(the top and bottom rows of the dataset) are presented in "rolled up"
state. Between these two summary values, we see Oregon's summary line,
followed by the drilled down values for the constituent child cities.
(We note, too, that the total of the child cities in the dataset add to
the rolled up Oregon total of 616, 268.06.)
We will practice some uses of the DrillDownMember() function
in the section that follows.
» 
Comment and Contribute
