MDX Set Functions: The DRILLUPMEMBER() Function

Tuesday Jan 3rd 2006 by William Pearson
Share:

Architect Bill Pearson explores the MDX DRILLUPMEMBER() function, continuing his extended examination of the mechanics of drilling up and down within MDX.

About the Series ...

This article is a member of the series, MDX Essentials. The series is designed to provide hands-on application of the fundamentals of the Multidimensional Expressions (MDX) language, with each tutorial progressively adding features designed to meet specific real-world needs. For more information about the series in general, please see my first article, MDX at First Glance: Introduction to MDX Essentials.

Note: To follow along with the steps we undertake within this and prospective articles of the series, the following components, samples and tools are recommended, and should be installed according to the respective documentation that accompanies MSSQL Server 2005:

  •   Microsoft SQL Server 2005 Database Engine (for the accompanying SQL Server Management Studio);

  •   Microsoft SQL Server 2005 Analysis Services;

  •   Business Intelligence Development Studio;

  •   The Analysis Services AdventureWorks DW sample database (fully deployed to Analysis Services from the AdventureWorks DW Analysis Services project that accompanies Analysis Services 2005).

The MSSQL Server 2005 and Analysis Services 2005 samples to which I refer above are available within a Microsoft Windows Installer package that accompanies the respective applications on the installation CDs, or which can be downloaded from Microsoft and perhaps elsewhere. The samples are not installed by default. If you did not install the samples and the AdventureWorks sample database during SQL Server 2005 Setup, you can install them after Setup finishes. For more information, see the topic "Running Setup to Install AdventureWorks Sample Databases and Samples" in the SQL Server Books Online. Make sure that you read and follow the installation instructions in the Readme files that accompany the samples that you want to install. For a list of samples, see the topic "Samples" in the SQL Server Books Online.

To successfully replicate the steps of the article, you also need to be a member of the appropriate group / role in Analysis Services to access the sample cube for our exercises. Read permissions are assumed for the samples upon which we will base our practice exercises, where we will often be using the AdventureWorks cube, which arrives with Analysis Services 2005 within the AdventureWorks DW Analysis Services project.

Current Service Pack updates are assumed for the operating system, MSSQL Server 2005 ("MSSQL Server"), MSSQL Server 2005 Analysis Services ("Analysis Services"), MSSQL Server 2005 Reporting Services ("Reporting Services") and the related Books Online and Samples. Images are from a Windows 2003 Server environment, but the steps performed in the articles, together with the views that result, will be quite similar within any environment that supports MSSQL Server 2005 and its component applications.

Within MSSQL Server Analysis Services 2000, we had somewhat limited options (namely, the MDX Sample Application) for easily querying our cube data sources with MDX (at least, that is, options that were available "out of the box" to anyone who had simply installed Analysis Services and its samples). MSSQL Server 2005 offers us numerous means for querying our OLAP data sources. In the interest of working within the current environment, in this and the prospective articles of the series, we will construct and execute our MDX queries, for the most part, from the SQL Server Management Studio, but we will occasionally do so from the SQL Server Business Intelligence Studio, and perhaps from other vantage points, to further enrich the learning experience within the subject matter involved. For more in-depth information on any of these various "points of approach" themselves, see the relevant sections of the Books Online, or specific articles within my various series'.

Virtually all of the MDX we constructed in earlier articles can now be used in the SQL Server Management Studio, SQL Server Business Intelligence Studio, and in various other areas within the Microsoft integrated Business Intelligence solution, and much of what we construct going forward can be executed in the MDX Sample Application (assuming connection to an appropriate Analysis Services 2000 OLAP data source). MDX as a language continues to evolve and expand: we will focus on many new features in articles to come, while still continuing to focus upon business uses of MDX in general. The use of MDX to meet the real-world needs of our business environments will continue to be my primary focus within the MDX Essentials series.

Overview

In this article, we will continue our examination, begun in Set Functions: The DRILLDOWNMEMBER() Function, of the MDX surrounding drilling up and down within our Analysis Services cubes. As we have noted, drilling up or down occurs along the lines of drilling paths that are defined within the physical structures of our cubes. "Drilling" comprises an analytical technique by which an information consumer can maneuver between summarized ("drilling up") and detailed ("drilling down") levels of data. While these drilling paths are typically specified by the cube's dimensional hierarchies, they can also be based upon alternative relationships that exist within or between dimensions.

An example might be described as follows, based upon an engagement within which I have recently been involved: An executive within a state Department of Education wishes to examine annual Adequate Yearly Progress (AYP) data within a cube constructed primarily to allow comparison and analysis of those results at various levels. The executive can examine national averages, numbers of test takers, and various other measures, and then perform a drilldown operation within a Geography dimension that might then present the data by states. He could then perform subsequent drilldowns to display state regions (or perhaps even congressional districts), counties / school systems, and more. Depending upon the design of the cube, the drilldown process could ultimately take the consumer to the level of the individual schools – perhaps even to the individual students themselves.

Drilling up is a capability that might be taken for granted in a drilldown scenario such as the foregoing. From any of the drilled down levels at which the information consumer finds himself or herself, a corresponding capability to navigate upward, from more detailed levels of data to more summarized levels of data, would be considered a complementary feature. We noted in Set Functions: The DRILLDOWNMEMBER() Function that the DrillDownMember() function supports the capability of Analysis Services to meet a common need for basic drilldown. Drilling up is supported by a function, DrillUpMember(), that is almost identical with respect to its operation, and which differs primarily in the "direction" (upward versus downward in the drilling path) of its action. (Several other "drill" related functions exist in MDX, which we explore in respective articles.)

Like DrillDownMember(), DrillUpMember() acts upon members within a specified set based upon members within a second specified set – the difference is that the "direction" of the drill is upward in the path versus downward, as is obvious from the associated keywords. In fact, as we shall see in our practice exercise, and as we might find as a common approach to providing drilldown and drillup support within an third party application (including reporting applications like Reporting Services), DrillUpMember() can be used to deliver an uncluttered, neat drillup of a set that is produced by the DrillDownMember() function. I often use the two in conjunction in this manner to provide smooth, logical movement along drill paths.

In a manner similar to that of other functions that we have examined in the MDX Essentials series, DrillUpMember() can be useful in a host of different reporting and analysis applications. DrillUpMember(), along with other "navigational" functions like it (virtually all of which we examine in other articles of this series) allows us to exercise a great deal of presentation sleight of hand, in working with MDX in Analysis Services, as well as within Reporting Services and various other reporting applications that can access an Analysis Services cube.

The DrillUpMember() function can be leveraged within and among the various "layers" of the Microsoft integrated Business Intelligence solution to support sophisticated presentations and features. We will introduce the function, commenting upon its operation, and touching upon examples of effects that we can employ it to deliver. As a part of our discussion, we will:

  •   Examine the syntax surrounding the function;
  •   Undertake illustrative examples of the uses of the function in practice exercises;
  •   Briefly discuss the results datasets we obtain in the practice examples.

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.

Practice

Preparation: Access SQL Server Management Studio

To reinforce our understanding of the basics we have covered so far, we will use the DrillUpMember() function in a way that illustrates its operation. We will do so in a simple scenario that places DrillUpMember() within the context of meeting business requirements similar to those we might encounter in our respective daily environments. The intent is to demonstrate the operation of the DrillUpMember() function in a straightforward, memorable manner.

We will turn to the SQL Server Management Studio as a platform from which to construct and execute the MDX we examine, and to view the results datasets we obtain.

1.  Click the Start button.

2.  Select Microsoft SQL Server 2005 within the Program group of the menu.

3.  Click SQL Server Management Studio, as shown in Illustration 2.

Click for larger image

Illustration 2: Opening SQL Server Management Studio

The Connect to Server dialog appears.

4.  Select Analysis Services in the Server type selector.

5.  Type / select the server name (server name / instance, if appropriate) in the Server name selector.

6.  Supply authentication information, as required in your own environment.

7.  Click the Connect button to connect with the specified Analysis Services server.

The SQL Server Management Studio opens.

8.  In the Object Explorer pane (it appears by default on the left side of the Studio), expand the Databases folder (click the "+" sign to its immediate left), appearing underneath the Analysis Server with which we are working.

The Databases folder opens, exposing the Analysis Services database(s) with which we have established connection(s), as depicted in Illustration 3.


Illustration 3: Exposing the Analysis Services Databases in the Object Browser ...

NOTE: The Analysis Services databases that appear will depend upon the activities that have taken place in your own environment, and will likely differ from those shown in Illustration 3 above. For purposes of this practice session, the Adventure Works DW database must be present. If this is not the case, consult the Books Online for the installation / connection procedures, and complete these procedures before continuing.

9.  Expand the Adventure Works DW database.

The Database expands, exposing the folders for the various objects housed within an Analysis Services database, as shown in Illustration 4.


Illustration 4: Exposing the Object Folders in the Database ...

10.  Expand the Cubes folder within the Adventure Works DW database.

The Cubes folder opens. We see two cubes, the first of which, Adventure Works, is the sample cube with which we will be conducting our practice exercises. The cubes appear as depicted in Illustration 5.


Illustration 5: The Cubes Appear ...

11.  Click the Adventure Works cube to select it.

12.  Click the New Query button just under the main menu, in the upper left corner of the Management Studio, as shown in Illustration 6.


Illustration 6: Click the New Query Button with the Adventure Works Cube Selected

The metadata pane for the Adventure Works cube appears, along with the query pane to its right, as depicted in Illustration 7.


Illustration 7: Adventure Works Cube Metadata Appears ...

We will be using the query pane in our practice session that follows, to construct and execute our MDX queries. For more information on the limited use of SQL Server Management Studio within this series, see Set Functions: The DRILLDOWNMEMBER() Function. (Database Journal articles within my other series explore other capabilities and features of the SQL Server Management Studio, as well as the SQL Server Business Intelligence Studio).

Satisfy Business Requirements with MDX

Let's assume, for purposes of our practice example, that we have received a request from a client to provide support in meeting a specific report presentation need. The Reporting department of the Adventure Works organization often requests assistance such as this, which, in the present case, involves a drillup scenario; the authors and developers in the group are aware that the need will manifest itself in recurring similar situations as they work to meet the needs of the Adventure Works information consumers.

We have previously assisted the group in performing drillthrough (see Mastering OLAP Reporting: Drilling Through Using MDX, a recent article in my MSSQL Server Reporting Services series, as well as Drilling Through with MDX: The DRILLTHROUGH Statement, here in the MDX Essentials series). Moreover, we have supported the group recently in performing drilldown (see Set Functions: The DRILLDOWNMEMBER() Function and Set Functions: The DRILLDOWNMEMBERTOP() and DRILLDOWNMEMBERBOTTOM() Functions), which, we believe, will be of benefit as we return to assist them with the drillup capabilities they need.

The Reporting department representatives explain that they want to perform drillups, using MDX functions as the mechanism, for the same reason that they wanted to perform drilldowns with MDX: they wish to leverage the Analysis Services and Reporting Services components of the Microsoft integrated Business Intelligence solution, whereby they can create a targeted "drill up" report that will be triggered from a primary, lower level report, and then "connect" the two in a manner similar to that which we have followed in other articles. They might also parameterize the dataset(s) within an OLAP report to "swap" DrillDownMember() and DrillUpMember() functions to achieve drilled up or drilled down perspectives within the report(s) resting upon those datasets, or perhaps take another approach entirely to deliver drilling capability within their reports. Suffice it to say that, whichever approach the developers take to support a given reporting or analysis need, they will likely work with combinations of drilldown and drillup functions to achieve versatility in the end presentations.

We convince the authors that they want to perform drillup within the context of drilldown capabilities such as those that we established in The DrillDownMember() Function. A good way to do this will be to construct an example of a scenario where we use DrillDownMember() to perform drilldown, and then introduce the DrillUpMember() function within the scenario to reverse the effects of DrillDownMember(), and perform drillup upon the same data.

In the case at hand, the authors tell us that they will again need to provide a query that enables drilldown for the Calendar Year 2003 Reseller Sales of a specific group of the bicycle models that Adventure Works builds. Out of three Product Model Lines within the Product dimension of the Adventure Works cube, the immediate need is to return a dataset that presents two of the lines, Mountain and Road models, in summary, while drilling down the Touring model to the level of its (non-empty) children (the various types of bicycle components for the Touring model) and presenting the Reseller Sales Amounts at that level. They then wish to introduce the DrillUpMember() function to return the same data presentation to the summary rollup for each of the three Product Model Lines.

We work with the Reporting department representatives to prepare a quick draft of the requirements, to corroborate the business need. The result of our joint efforts is a drilled down and a drilled up view of the sample data, laid out in a small spreadsheet, shown in Illustration 8.


Illustration 8: "Confirmation Draft" of the Proposed Dataset Objectives

This represents a simple need that we can readily answer using the DrillDownMember() and DrillUpmember() functions in conjunction. We will apply the former to the specified data to bring about the drilldown of the desired Model Line, and then apply the DrillUpMember() function to return the same data to its rolled up state, as we see in the right half of the image above. We are confident that, by thus creating a drilled down scenario as an initial step, we can more effectively demonstrate the workings of the DrillUpMember() function within a meaningful context.

Preparation: Create a Drilled Down Position from Which to Drill Up

Let's construct a simple query, therefore, to return the requested Calendar Year 2003 Reseller Sales information for the three selected Product Model Lines, presenting the summarized sales for each of the Mountain and Road models, together with the sales values for each of the individually broken out component types that make up the Touring model.

1.  Type (or cut and paste) the following query into the Query pane:


-- MDX039-001 Initial DrillDown With Selected Members
SELECT
   {[Measures].[Reseller Sales Amount]} ON COLUMNS,

NON EMPTY(DRILLDOWNMEMBER( {
   [Product].[Product Model Lines].[Product Line].[Mountain], 
        [Product].[Product Model Lines].[Product Line].[Road], 
            [Product].[Product Model Lines].[Product Line].[Touring]}, 
        {[Product].[Product Model Lines].[Product Line].[Touring]}))ON ROWS
 
FROM 
    [Adventure Works]

WHERE
    ([Date].[Calendar Year].[CY 2003])

The Query pane appears, with our input, as depicted in Illustration 9.


Illustration 9: Our Initial Query in the Query Pane ...

The above query sets the stage for a drillup action, while also (as we have noted in sister articles where we discuss drilldown functions) presenting us with opportunities for extending the drilldown capability that it imparts even further within reporting, and other, consumer-facing applications. One example of such an opportunity lies within the parameterization of various components of the axis definitions, together with the slicer. An obvious example might be to reference a parameter in the last set, {[Product].[Product Model Lines].[Product Line].[Touring]}, of the row axis, through Reporting Services. The parameter might then be passed from a "launch" point in a summary report to a target report containing a query similar to the above within its dataset, generating a drilldown report upon, say, the click of a summary item within the former by an information consumer. (This is but one approach; see my MSSQL Server Reporting Services series for articles that expose practical approaches to this and other methods for accomplishing similar effects.)

2.  Execute the query by clicking the Execute button in the toolbar, as shown in Illustration 10.


Illustration 10: Click Execute to Run the Query...

The Results pane is populated by Analysis Services, and the dataset shown in Illustration 11 appears.


Illustration 11: Results Dataset – Initial Use of DrillDownMember() Function

In the returned dataset, we see that Mountain and Road (the top two rows of the dataset) are presented in "rolled up" state, as is Touring, the third row. However, the rows underneath Touring are composed of its children, the various components of the Touring Model Line. (Moreover, we can verify easily that the values of the child rows in the dataset add to the rolled up Touring total of $ 6,464,481.11.)

3.  Select File --> Save MDXQuery1.mdx As ..., name the file MDX039-001, and place it in a meaningful location.

4.  Leave the query open for the next step.

Our developer colleagues have expressed satisfaction with the example we have presented, as a contextual backdrop for introducing the DrillUpMember() function. We will undertake using the function in our next steps, in the procedural section that follows.

Procedure: Drill Up Using the DrillUpMember() Function

Having arrived at a good conceptual starting point, we are positioned to leverage the capability to drill up. After obtaining consensus on the dataset, and corroborating the fact that the group with which we are working has gained an understanding of what we have accomplished with DrillDownMember(), we next set out to show the drillup process using the same data structures. We will simply substitute the DrillUpMember() function in place of the DrillDownMember() function within the same query, to meet the desired ends.

5.  Replace the comment line in query MDX039-001 with the following:

-- MDX039-002 Drillup With Selected Members

6.  Leaving all other syntax "as is," replace the DrillDownMember keyword in our initial query with the following in the Query pane:

DrillUpMember

The Query pane appears, with our input, as depicted in Illustration 12.

Click for larger image

Illustration 12: "Adjusted" Query in the Query Pane (Modifications Circled) ...

7.  Select File --> Save MDX039-001.mdx As ..., name the file MDX039-002, and place it in a meaningful location, to protect the former query.

8.  Execute the query by clicking the Execute button in the toolbar, as before.

The Results pane is populated by Analysis Services, and the dataset shown in Illustration 13 appears.


Illustration 13: Results Dataset – Basic Use of DrillUpMember() Function

And so we see that DrillUpMember() has the opposite effect within the query, as DrillDownMember(). With minor exceptions, such as the non-provision of a RECURSIVE keyword, the differences between the two functions lie solely in "direction:" "up" or "down."

In the returned dataset, we see that all Model Lines (Mountain, Road and Touring) are presented in "rolled up" state. The action we have witnessed has been the drillup of the members of the Touring Model Line. The rows that appeared underneath Touring, which represented the Model Line's children, (the components of the Touring Model Line) have now been absorbed into a single summary line, for presentation purposes.

One of the reasons that I chose to introduce the DrillUpMember() function within the query context of an immediately preceding DrillDownMember() is to illustrate just what we have seen: the only thing that decides the "direction" of drilling is the choice of keywords we insert into the query. This makes it easy to see that, within OLAP reporting tools, such as Reporting Services, as well as other applications, we can make the keyword conditional upon an action of the user at runtime. We can therefore parameterize the drill up or drill down action in this manner relatively easily. While we will not further extend our examination of the MDX functions, or of parameterization in general, within this article, I provide hands-on guidance in these subjects within my MSSQL Server Reporting Services series, as well as in other series' at Database Journal.

9.  Select File --> Save MDX039-002.mdx to ensure that we that the file is saved.

The client developers and report authors again express satisfaction with the results, and confirm their understanding in the operation of the DrillUpMember() function. Once they inform us that their immediate goals have been met, we agree to return at a later time to demonstrate approaches to implement the DrillUpMember() function, in conjunction with the DrillDownMember() function, within Reporting Services. The group states that it is anxious to leverage MDX to support interactive drillup and drilldown by organizational information consumers.

10.  Select File --> Exit to leave the SQL Server Management Studio, when ready.

Summary ...

In this article, we continued our extended examination of the MDX surrounding drilling up and down within our Analysis Services cubes, focusing this time upon the DrillUpMember() function. We noted that, like many MDX functions, DrillUpMember() can be leveraged within and among the various "layers" of the Microsoft integrated Business Intelligence solution to support sophisticated presentations and features. We introduced the function, commenting upon its operation and touching upon examples of effects that we can employ it to deliver to empower information consumers to maneuver between detailed and summarized levels of data.

In introducing DrillUpMember(), we commented upon its operation and discussed the "collapsed" datasets we can deliver using a specified primary and secondary set within the function. Next, we examined the syntax involved with DrillUpMember(), and then undertook an illustrative practice example within which we met hypothetical business requirements with the function. We generated a query that capitalized on its primary features within the context of a drilled down scenario, in order to witness the function in action. Further, we discussed points within our query where we might consider the insertion of parameterization in a reporting application (such as Reporting Services), or in other consumer applications, to leverage the function to support drillup in an ad hoc manner – and especially in conjunction with drilldown operations. Throughout our practice session, we briefly discussed the results datasets we obtained from each of the queries we constructed.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved