Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jan 3, 2006

MDX Set Functions: The DRILLUPMEMBER() Function - Page 2

By William Pearson

The DrillUpMember() Function


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.


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().


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:


(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].[Citrus Heights], 
{[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.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM