Set Functions: The DrillUpLevel() Function

Monday Apr 3rd 2006 by William Pearson
Share:

Architect Bill Pearson returns with an examination of the DRILLUPLEVEL() function. This article continues our exploration of drilling up and down within MDX, with a focus upon opportunities for parameterization.

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, as well as the software and systems requirements for getting the most out of the member lessons, please see Set Functions: The DRILLDOWNMEMBER() Function, where important information is detailed regarding the applications, samples and other components required to complete our practice exercises.

Overview

In this article, we will continue the examination of "MDX for drilling up and down" that we began in Set Functions: The DrillDownMember() Function. We have discussed the nature of drilling, in general, in previous articles of our sub-series, stating that it comprises an analytical technique through which means an information consumer can maneuver between summarized ("drilling up") and detailed ("drilling down") levels of data. We noted that drilling up or down occurs along the lines of drilling paths that are defined within the structure of our cubes, and which are typically specified by the cube's dimensional hierarchies. We mentioned, too, that these paths can 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 an expected complementary feature.

In an earlier article, Set Functions: The DrillDownLevel() Function, we introduced the "primary," level-directed MDX drilldown function, DrillDownLevel(), stating that it supports the capability of Analysis Services to meet the common need for drilldown from a point within a given Set to members within the next lower level, or to a level which we can specify using an optional Level Expression (or, alternatively, an optional Index). Drilling up from a level-directed perspective is supported by a function, DrillUpLevel(), that is quite similar to DrillDownLevel(), with respect to its operation, differing, of course, in the "direction" (upward versus downward, along the drilling path) of its action.

In this article we will examine a means for drilling in the "upward" direction, through the DrillUpLevel() function, another "staple" support function underpinning drill capabilities within MDX. DrillUpLevel() drills up the members of a set to a higher level. It drills up each member of a specified set that exists below a level that we can optionally specify. (If we do not specify the level, the function identifies the lowest level in the set, and removes the members that reside at that level, to present a drilled up state at the next level).

In a manner similar to other functions that we have examined in the MDX Essentials series, DrillUpLevel() can be useful in a host of different reporting and analysis applications. DrillUpLevel(), 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. Like other MDX functions in general, the DrillUpLevel() 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 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.

Practice

Preparation: Access SQL Server Management Studio

To reinforce our understanding of the basics we have covered so far, we will use the DrillUpLevel() function in a couple of ways that illustrate its operation. We will do so in simple scenarios that place DrillUpLevel() within the context of meeting business requirements similar to those we might encounter in our respective daily environments. The intent, as in all the practice sessions of this series, is to demonstrate the operation of the DrillUpLevel() 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.


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 detected Analysis Services database(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 the practice session that follows, to construct and execute our MDX queries.

As we discover in articles throughout my Introduction to MSSQL Server Analysis Services series, among others, the SQL Server Management Studio serves us in providing a point of interface with all server types in the SQL Server family, including Analysis Services, Reporting Services and Integration Services servers, as well as supporting many additional functions. Among those functions, I find the capabilities to easily browse data, and to issue queries, highly convenient. We can accomplish querying in several other ways within the integrated Microsoft BI solution, but this is certainly one of the most direct. For more information on our use of the query editor within SQL Server Management Studio for issuing MDX queries within the practice exercises of the MDX Essentials series, see Set Functions: The DRILLDOWNMEMBER() Function. (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).

Procedure: Satisfy Business Requirements with MDX

Let's assume, for purposes of our practice example, that we have received a request, once again, from representatives of our client, the Adventure Works organization. As we have noted in other articles of the series, the Reporting department, a group of client-facing authors and developers, often requests assistance such as this. As a part of our relationship with Adventure Works, as well as with other clients, we provide on-site augmentation for business requirements gathering and training, performing workshops, in many cases, that illustrate approaches to meeting specific needs. These combined development workshops / "train the trainer" events have worked well in the past for all concerned.

As usual, the authors and developers in the group are aware that the particular need that they are currently expressing will manifest itself in recurring situations as they work to meet the daily requirements of the Adventure Works information consumers. This particular request for assistance involves a drillup scenario.

We have previously assisted the Reporting department representatives in the performance of drilldowns (see Set Functions: The DRILLDOWNMEMBER() Function and Set Functions: The DRILLDOWNMEMBERTOP() and DRILLDOWNMEMBERBOTTOM() Functions), as well as with drillups (MDX Set Functions: The DRILLUPMEMBER() Function) within the context of members. Moreover, we have supported them specifically with using MDX to support level drilldown capabilities (MDX Set Functions: DrillDownLevel() and Set Functions: The DrillDownLevelTop() and DrillDownLevelBottom() Functions). Just as was the case in these visits, when the client Reporting group wished to base the drilldowns upon levels, instead of members, within their cube structure, they wish to extend their recent excursions into drillup capabilities to dimensional levels in our current session.

The Reporting department representatives explain that they want to perform level drillups, using MDX functions as the mechanism, for the same reason that they wanted to perform level 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 to which we have alluded in other articles. They might also parameterize the dataset(s) within an OLAP report to "swap" DrillDownLevel() and DrillUpLevel() functions to achieve drilled up or drilled down level perspectives within the report(s) resting upon those datasets, or perhaps take another approach entirely to deliver drilling capability within their reports. As we have stated in previous articles surrounding the MDX "drilling functions," 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 will initially want to perform drillup within the context of drilldown capabilities such as those that we established in MDX Set Functions: DrillDownLevel(). A good way to do this will be to construct an example of a scenario where we use DrillDownLevel() to perform drilldown, and then introduce the DrillUpLevel() function within the scenario to reverse the effects of DrillDownMember(), and then to perform drillup upon the same data. (We also note that this approach will leave the developers with a complementary "pair" of examples that can be used to illustrate the use of the DrillDownLevel() and DrillUpLevel() functions in tandem).

In the case at hand, the authors ask that we begin with a query that returns Calendar Year 2003 Reseller Sales information for all Product Lines, presenting the summarized total Product sales for the organization, together with the sales values for each of the individually broken out Product Lines that make up the 2003 total, via use of the DrillDownLevel() function. Next, the authors wish to see a drilled up view of the same data, based upon the application of the DrillUpLevel() function to the dataset presented in the drilled down view.

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 DrillDownLevel() and DrillUpLevel() functions in conjunction. We will apply the former to the specified data to bring about the drilldown of the desired Product Model Line, and then apply the DrillUpLevel() 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 simplest workings of the DrillUpLevel() function within a meaningful context.

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

Let's construct a simple query to provide the "starting point" for our subsequent work with the DrillUpLevel() function. Our intent here is simply to return the requested Calendar Year 2003 Reseller Sales information for all Product Model Lines, presenting the summarized sales of all Lines, together with the sales values for each of the individually broken out Lines of Products that Adventure Works sells.

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

-- MDX042-001 Initial DrillDown With 
 Selected Level
 
SELECT
 
   {[Measures].[Reseller Sales Amount]} 
    ON COLUMNS,
 
NON EMPTY(DRILLDOWNLEVEL( {
 
   [Product].[Product Model Lines]}))ON ROWS
 
FROM 
 
    [Adventure Works]
 
WHERE
    ([Date].[Calendar Year].[CY 2003])

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

Click for larger image

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. Examples of such opportunities lie within the parameterization of various components of the axis definitions, as well as with the slicer. An obvious example might be to reference a parameter in place of the keyword DRILLDOWNLEVEL in 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 DrillDownLevel() Function

In the returned dataset, we see that All Products is presented in "rolled up" state. The rows underneath All Products are composed of its children, the various Product Model Lines offered by the Adventure Works organization. (And we can verify easily that the values of the child rows in the dataset add to the All Products total of $ 32,202,669.43.)

3.  Select File --> Save MDXQuery1.mdx As ..., name the file MDX042-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 contextual backdrop we have established for introducing the DrillUpLevel() function. We will undertake using the function in our next steps, in the procedural section that follows, first with the foregoing example, and then within a "fresh" query we will construct.

Procedure: Drill Up Using the DrillUpLevel() 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 DrillDownLevel(), we will next set out to show the drillup process using the same data structures, to establish a frame of reference. We will simply substitute the DrillUpLevel() function in place of the DrillDownLevel() function within the same query, to meet the desired ends.

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

-- MDX042-002 Drillup With Selected Level

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

DRILLUPLEVEL

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


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

7.  Select File --> Save MDX042-001.mdx As ..., name the file MDX042-002, and place it in the same location as its predecessor, 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 DrillUpLevel() Function

And so we see that DrillUpMember() has the opposite effect within the query, as DrillDownMember(). As we might expect, the only real difference between the two functions lies solely in "direction:" "up" or "down." In the returned dataset, we see that All Products (consisting of the Accessory, Components, Mountain, Road and Touring Product Model Lines) are presented in "rolled up" state. The action we have witnessed has been the drillup of the Product Line members of the All Products dimensional level. The rows that appeared underneath All Products in our initial, drilled down dataset, which represented the child members of the All Products level of the dimension (and were, in this case, at the lowest hierarchical level in the set) have been removed from the dataset. The DrillUpLevel() function has assumed the "next level above the members level" to be the Level Expression, which drives the level to which it drills up. The Product Model Lines values have thus been "absorbed" into a single summary line (All Products), for presentation purposes.

While opportunities to parameterize the Set Expression portion of a query like this one might be obvious, one of the reasons that I chose to introduce the DrillUpLevel() function within the query context of an immediately preceding DrillDownMember() is to illustrate another option for parameterization: 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 (or even the "UP" or "DOWN" portion of 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 DrillUpLevel() and DrillDownMember() functions to their parameterization in this way, or to the further approaches to 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 of my 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 express satisfaction with the results, and confirm their understanding in the operation of the DrillUpLevel() function. They present a similar request at this point for assistance in crafting another drillup scenario involving levels. This example will further activate what we have discussed and seen thus far, employing DrillUpLevel() in a simple scenario, somewhat like our first example, initially, and then further leveraging the optional Level Expression. This will give us a hands-on, "before and after" look at how DrillDownLevel () behaves with and without a specified Level Expression.

The report authors outline the next drillup scenario as follows: using the Adventure Works cube as a data source, they wish to begin, as before, with a "drilled down" scenario (albeit through a function that lies outside the specific "drilling" function family): we will start with an existing query that returns information surrounding the composition of internet-generated sales from the perspective of select customer locations. Specifically, the current query generates a dataset that presents comparative Internet Sales for all Calendar Years contained in the cube, for customers in three States in the Southeast United States (for which sales are being analyzed), Alabama, Florida, and Georgia. The current query produces a dataset that summarizes Internet Sales, where they exist, at all hierarchical levels for the three States under examination. The report authors tell us that this specific dataset was requested by the information consumers because they want to monitor the gradually growing Adventure Works sales via the Internet in the Southeast, an area within which increased marketing efforts have been applied.

The drilldown action within the current query results in a dataset that presents the total sales for the extreme Southeast United States, drilled down to the sales totals for the States which make up the total. In addition, the States are drilled down to the Cities whose sales compose each State's total, with the Cities drilling down, in like fashion, to the constituent Postal Codes involved. Finally, the names of the Customers that comprise the sales for each Postal Code summary are also exposed, as the bottom level of the drilldown. The report authors / developers have a new appreciation of the fact that, given the correctly constructed core query, the capability to perform ad hoc drilldowns or drillups on the given levels at runtime will become a matter of parameterizing the key component of the ROWS axis specification (or other components) within the MDX query involved. They understand that one of the results will be a mechanism that can be leveraged, as one example, in a summary / target report pairing (as well as through other avenues) within Reporting Services, such as we have discussed earlier. Their objective is to be able to ultimately provide a report that the information consumers can use to perform ad hoc queries at any level, once we provide a working example of how to accomplish drillups from the all-inclusive hierarchical presentation that they currently have. Moreover, this will give us a hands-on, "before and after" look at how DrillUpLevel() behaves with and without a specified Level Expression.

To outline the requirement further, our "confirmation of understanding draft" of the initial, fully drilled down dataset appears in MS Excel as depicted in Illustration 14.


Illustration 14: "Confirmation Draft" of the Fully Drilled Down Dataset

The two drilled up views we intend to produce as examples appear as shown in Illustration 15.


Illustration 15: "Confirmation Draft" of the Two Proposed Drilled Up Views Examples

We obtain consensus on the dataset, and set about constructing the query.

10.  Select File --> New from the main menu.

11.  Select Query with Current Connection from the cascading menu that appears next, as depicted in Illustration 16.


Illustration 16: Create a New Query with the Current Connection ...

A new tab, with a connection to the Adventure Works cube (we can see it listed in the selector of the Metadata pane) appears in the Query pane.


--- MDX042-003 Initial Query with Full "Drilldown"
 
SELECT
 
   CROSSJOIN({[Date].[Calendar Year].Members},{[Measures].
 
       [Internet Sales Amount]}) ON COLUMNS,
 
   NON EMPTY(
   
       {[Customer].[Customer Geography].[Country].[United States],
   
           DESCENDANTS(
 
              {[Customer].[Customer Geography].[State-Province].[Alabama], 
        
                  [Customer].[Customer Geography].[State-Province].[Florida],
           
              [Customer].[Customer Geography].[State-Province].[Georgia]}, 
               
          [Customer].[Customer Geography].[City], SELF_BEFORE_AFTER)})
 
     ON ROWS
             
FROM 
              [Adventure Works]

The Query pane appears, with our input, as shown in Illustration 17.


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

The above query again sets the stage for a drillup action, while also presenting us with opportunities to see the combination of MDX level drilling functions and the Descendants() function. Descendants() can be leveraged to achieve many commonly desirable presentation effects, and, along with other MDX functions, is very useful to us in drilling up and down. In the present case, Descendants() will afford us a results dataset that accomplishes the same effect as a drilldown function.

13.  Execute the query by clicking the Execute button in the toolbar.

The Results pane is, once again, populated by Analysis Services. This time, the dataset depicted in Illustration 18 appears.


Illustration 18: Results Dataset – Initial Drilled Down View – Using DESCENDANTS()

In the returned dataset, we see that, for the selected Southeast States, total Internet Sales appears drilled down to, and summarized for, the Province-States, the Cities, the Postal Codes, and the individual Customers whose purchases made up the Internet Sales totals for each of the "rolled up" levels. At this stage, we have a completely "exploded" view, to which we can apply the DrillUpLevel() function.

14.  Select File --> Save MDXQuery2.mdx As ..., name the file MDX042-003, and place it in the same location as we used to store the previous queries.

15.  Leave the query open for the next step.

Our developer colleagues agree that the drilled-down scenario we have established for further examining the DrillUpLevel() function should be effective in working with a couple of practice examples. We will undertake using DrillUpLevel() once again, in the steps that follow. First, we will put the function to work without specifying the optional Level Expression, and then we will work through another example where we add the Level Expression, so as to contrast the difference in the results datasets we obtain.

16.  Replace the comment line in query MDX042-003 with the following:

-- MDX042-004 DRILLUPLEVEL() in Action - Before Optional Level Expression

17.  Select File --> Save MDX042-003 As..., name the file MDX042-004, and save it with the queries created earlier.

18.  Click to the immediate right of NON EMPTY( - on the fifth row from the top in the existing query - to place the cursor there.

19.  Press the Enter key on the PC twice, to create a space between the row and the row underneath it.

The cursor appears on the new row (which will become the sixth syntax row from the top of the Query pane).

20.  Type the following on the new row:

DRILLUPLEVEL(

21.  On what is now the twelfth row, to the immediate right of the following:

[Customer].[Customer Geography].[City], 
  SELF_BEFORE_AFTER)})

Add a closed / "right" parenthesis, ")"

This encloses the ROWS specification of our query within the DrillUpLevel() function. The Query pane appears, with our input, as shown in Illustration 19.


Illustration 19: Our Modified Query in the Query Pane (Alterations Circled) ...

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

Analysis Services populates the Results pane, as before, and the dataset depicted in Illustration 20 appears.


Illustration 20: Results Dataset –DrillUpLevel() Function without Optional Level Expression

We can see that the effect of the DrillUpLevel() function has been to remove the members in the set that exist below the default level. The default level is one level in the dimensional hierarchy above the level of the lowest-level members. This means, of course, that the individual Customers, which exist at the lowest level per the Descendants() function, are removed, and the next level up, Postal Code, becomes the lowest level in the returned dataset. The effect, then, is drilling up from the Customer level to the Postal Code level.

23.  Select File --> Save MDX042-004 to save the query in its specified location.

24.  Leave the query open for the next step.

We will perform a final exercise, this time leveraging the optional Level Expression, to confirm our understanding of how it works.

25.  Replace the comment line in query MDX042-004 with the following:

--- MDX042-005 DRILLUPLEVEL() in Action - With Optional Level Expression

26.  Select File --> Save MDX042-004 As..., name the file MDX042-005, and save it with the queries created earlier.

27.  Type a comma (",") to the immediate right of the right brace ( "}" ) that encloses the ROWS axis of our query (the twelfth row from the top of the query).

The comma should be added at the point shown in Illustration 21.


Illustration 21: Adding a Comma after the Right Brace ( "}" )

28.  After adding the comma, press the Enter key on the PC two times, to create ample space between the row to which we added the comma and the two rightmost, right parentheses.

29.  Type the following to the immediate left of the two right parentheses:

 [Customer].[Customer Geography].[State-Province]

This insertion represents a Level Expression, the State-Province level of the Customer dimension, Customer Geography hierarchy. Placing it here, within the surrounding DrillUpLevel() function, we are telling the function to "remove all set members that reside in levels lower than the State-Province level within the Customer Geography hierarchy" – in effect to drill up to the State-Province level. The Query pane appears, with our input, as depicted in Illustration 22.


Illustration 22: The Modified Query in the Query Pane (Alterations Circled) ...

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

The Results pane is once again populated, as the dataset shown in Illustration 23 appears.


Illustration 23: Results Dataset –DrillUpLevel() Function with Optional Level Expression

We now see that the effect of the DrillUpLevel() function has been to drill up to the State-Province level, as directed by our insertion of the appropriate syntax into the optional Level Expression of the DrillUpLevel() function. We note that all members below the State-Province level disappear, leaving us with the summarized States and Country levels that remain within our dataset. It thus becomes apparent, once again, that we can achieve many desirable presentation effects through the use of an MDX drilling function, in combination with other MDX functions. As we noted to be the case with the Set Expression in our first exercise, and within other MDX functions that we have treated in our recent "drilling functions" subset of articles, the added Level Expression can also be subjected to parameterization within a reporting (or other) application, for far-reaching capabilities with regard to manipulation of the supporting dataset of a target report containing the DrillUpLevel() function under consideration.

31.  Select File --> Save MDX042-005 to save the query in the specified location.

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

The client representatives inform us that their immediate goals have been met. We agree to return at a later time to demonstrate approaches to implement the DrillUpLevel() function, in conjunction with the DrillDownLevel() function, within Reporting Services, where, as we have noted, the functions lend themselves to easy and flexible parameterization. The group states that it is anxious to leverage MDX in this new way to support interactive drillup and drilldown by organizational information consumers.

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 DrillUpLevel() function. We noted that, like many MDX functions, DrillUpLevel() 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, 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 DrillUpLevel(), we commented upon its operation and discussed the "collapsed" datasets we can deliver using a specified Set Expression, together with an optional Level Expression, within the function. Next, we examined the syntax involved with DrillUpLevel(), and then undertook a couple of illustrative practice examples 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.

We then undertook an example where we combined DrillUpLevel() with the Descendants() function, to demonstrate how the "drilled down" effect of Descendants() might serve as a "starting point" for a query to which we might apply "drill up" logic to meet subsequently determined needs for summarization at different levels. Moreover, 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