Set Functions: The DrillDownLevelTop() and DrillDownLevelBottom() Functions

Monday Mar 6th 2006 by William Pearson
Share:

Join Architect Bill Pearson as he explores the DRILLDOWNLEVELTOP() and DRILLDOWNLEVELBOTTOM() functions. This article continues our examination 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, 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 often 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.

In our last article, Set Functions: The DrillDownLevel() Function, we introduced the "primary," level-directed MDX drilldown function, DrillDownLevel(). We stated that it supports the capability of Analysis Services to meet the common need for drilldown from 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. Through our overview, discussion, examination of the syntax, and hands-on practice session with the function, we discovered how DrillDownLevel() drills down the members of a set to a lower level, and additionally offers us the flexibility to specify which level below a given member in the set, as well as providing a means whereby we can specify an index to further control the behavior of the DrillDownLevel() function in targeting a specific level. We focused on several ways to leverage the function in our queries and, ultimately, in reporting and other end applications, to meet the business needs of organizational information consumers.

In this article we will examine two specialized set functions that are based upon the principles underlying DrillDownLevel(). DrillDownLevelTop() and DrillDownLevelBottom() are both similar to the DrillDownLevel() function. However, instead of behaving like DrillDownLevel(), in its inclusion of all children for each member within the specified Level Expression, DrillDownLevelTop() returns the topmost (specified) number of child members for each member, while the DrillDownLevelBottom() function returns the bottommost (specified) number of child members for each member.

Like the DrillDownLevel() function, both DrillDownLevelTop() and DrillDownLevelBottom() can be used in conjunction with tuples within the sets, as well as members. In a manner similar to DrillDownMember(), among numerous other functions that we have examined in the MDX Essentials series, DrillDownLevelTop() and DrillDownLevelBottom() can each be useful in a host of different reporting and analysis applications. Like other "navigational" functions of their feather (virtually all of which we examine in other articles of this and other series') each of these two drilldown functions 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 DrillDownLevelTop() and DrillDownLevelBottom() functions 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 functions, commenting upon the operation of each and touching upon examples of effects that we can employ each to deliver. As a part of our discussion, we will:

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

The DrillDownLevelTop() and DrillDownLevelBottom() Functions

Introduction

According to the Books Online, the DrillDownLevelTop() function "drills down the topmost members of a set, at a specified level, to one level below." Books Online states, moreover, that the DrillDownLevelBottom() function "drills down the bottommost members of a set, at a specified level, to one level below."

The DrillDownLevelTop() and DrillDownLevelBottom() functions thus behave exactly like the DrillDownLevel() function that we examined in Set Functions: The DrillDownLevel() Function, with one main difference: both "limit the result set to a specified number of members."

Both DrillDownLevelTop() and DrillDownLevelBottom(), like the DrillDownLevel() function, return children for members within the Level Expression that we specify. However, in addition to returning a set containing the parent members and child members, like DrilldownLevel(), DrillDownLevelTop() and DrillDownLevelBottom() return members limited to a specified Count. DrillDownLevelTop() and DrillDownLevelBottom() thus return sets containing parent members – with the specified count of child members at the level specified in Level Expression (when they exist within the structure to which the functions are applied) – with the highest (for DrillDownLevelTop() ) and lowest (for DrillDownLevelBottom() ) values involved. In other words, instead of including all children for each Set Expression member at the specified level, DrillDownMemberTop() and DrillDownMemberBottom() return the topmost and bottommost, respectively, specified number of children for each member.

As was the case with DrillDownLevel(), the DrillDownLevelTop() and DrillDownLevelBottom() functions have many applications, and pairing them with other MDX functions can help us to leverage their power even further. We will examine in detail the syntax for the functions after our customary overview in the Discussion section that follows. After that, we will conduct practice examples within a couple of scenarios, constructed to support hypothetical business needs that illustrate uses for the tandem functions. This will afford us an opportunity to explore some of the delivery options that DrillDownLevelTop() and DrillDownLevelBottom() can offer the knowledgeable user. Hands-on practice with these functions, where we will create expressions that leverage them to meet reporting needs, will help us to activate what we learn in the Discussion and Syntax sections.

Discussion

To restate our initial explanation of their operation, the DrillDownLevelTop() and DrillDownLevelBottom() functions drill down the members in a specified Set Expression that exist at the level we specify in Level Expression. The functions drill down the members of the Set Expression to one level below the lowest level represented within the Set Expression, or to one level below an (optional) Level Expression that we can specify, for a member existing within the set. When we do not supply a Level Expression, the expressions drill to the lowest level of members in the Set Expression. DrillDownLevelTop() and DrillDownLevelBottom() return the top or bottom (respectively) Count of members / tuples.

Several basic rules apply, some of which are similar to those that we have seen with the DrillDownLevel() function, within the core operations of the DrillDownLevelTop() and DrillDownLevelBottom() functions:

  • The set specified in the Set Expression can have any dimensionality;

  • The result set returned by each function is composed of the children of each member of the Set Expression that exist within the level specified in the Level Expression.

  • When one or more of its children follow a given member within the Set Expression, drilling does not occur upon the member.

  • The default behavior for each function is to drill down to the lowest level of members that exist within the Set Expression, when a Level Expression is not provided.

  • The DrillDownLevelTop() function returns the set containing the parent members (from the Set Expression) and the top Count members / tuples from within the specified or default level, whichever is appropriate, sorting them in descending order, according to the Numeric Expression (or in descending order according to the values of the cells represented by the set of child members).

  • The DrillDownLevelBottom() function returns the set containing the parent members (from the Set Expression) and the bottom Count members / tuples from within the specified or default level, whichever is appropriate, sorting them in ascending order, according to the Numeric Expression (or in ascending order according to the values of the cells represented by the set of child members).

  • The DrillDownLevelTop() and DrillDownLevelBottom() functions base their evaluation and return of the top or bottom Count, respectively, upon the Numeric Expression. The Numeric Expression would represent a measure, typically; input of a constant numeric expression here would result in an error.

A wide range of analysis and reporting opportunities await the practitioner who becomes adept at coupling DrillDownLevelTop() and DrillDownLevelBottom() with other functions. As is often the case with MDX functions within the Microsoft integrated BI solution, (consisting of MSSQL Server, Analysis Services and Reporting Services), these functions, residing within the Analysis Services layer, can be extended to support (directly and indirectly) 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 DrillDownLevelTop() and DrillDownLevelBottom().

Syntax

Syntactically, anytime we employ the DrillDownLevelTop() and DrillDownLevelBottom() functions to drill down by a level, the initial effect of both functions, as we have discussed, is to drill down those members of the specified Set Expression to members within the next lower level, or to a level which we can specify using an optional Level Expression. We specify the Set Expression and Count within the parentheses to the right of the DRILLDOWNLEVELTOP / DRILLDOWNLEVELBOTTOM keyword. We can supply a Level Expression next, within either function. In both cases, too, we can optionally follow the Level Expression with a Numeric Expression, upon which the "top" or "bottom" Count is based. The general syntax is shown in the following string:

DrillDownLevelTop / Bottom (Set_Expression, Count [ , [ Level_Expression ][ ,Numeric_Expression ] ] )

Let's take a look at an example. The following snippet employs the DrillDownLevelTop() function (and, with a simple change of the keyword, it would represent the same syntax as for the DrillDownLevelBottom() function):

DRILLDOWNLEVELTOP(
    ({[Customer].[Customer Geography].[Country].[United States], 
        [Customer].[Customer Geography].[Country].[Australia]}, 5,
              [Customer].[Customer Geography].[Country], 
                    [Measures].[Internet Sales Amount])
ON AXIS(1)

This row specification, within a proper query executed against the Adventure Works sample cube that, say, stipulated the Internet 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 – United States and Australia Drilled Down to Top Five Child State -Provinces

In the example dataset, we see that the function has drilled down on the Customer Geography members United States and Australia. Drill down has occurred to the Country level, with the returned members (from the level below) limited to the top five, based upon Internet Sales.

DrillDownLevelBottom() works in much the same manner, except that it provides the bottom values, based upon the Level and (optional) Numeric Expression we supply in conjunction with the Set Expression, in an ascending sort, as we shall see. We will get some practice using the DrillDownLevelTop() and DrillDownLevelBottom() functions 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 each of the DrillDownLevelTop() and DrillDownLevelBottom() functions in practice exercises, to illustrate its operation upon sample data. We will do so in simple scenarios that place DrillDownLevelTop() and DrillDownLevelBottom() 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 functions 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, as 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 in the left upper corner 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) for our respective server, 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, and we see two cubes. The first of these (assuming only the samples are installed – your environment may be different, of course), 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. (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 our practice example, that we have received another call from members of the Reporting department of a client, the AdventureWorks organization, requesting our assistance in meeting a report presentation need. As has been the case in numerous requests for help in the past, the report authors realize that, while they are seeking assistance with a specific scenario in the immediate term, the need will manifest itself in numerous similar scenarios that they expect to face in the long term. As always, they seek working examples, which they intend to extrapolate to recurring business needs they encounter within the enterprise.

The authors want to perform drilldowns from a level perspective, much as they did in our earlier session in Set Functions: The DrillDownLevel() Function. However, they inform us that they now wish to extend the basic level drilldowns we performed there to include the capability to present "top" and "bottom" results based upon level drilldowns, using the related MDX functions as the mechanism, in a manner similar to the way they performed top" and "bottom" drilldowns on members with the DrillDownMemberTop() and DrillDownMemberBottom() functions. (See Set Functions: The DrillDownMemberTop() and DrillDownMemberBottom() Functions within my Database Journal MDX Essentials series for our activities with these functions).

As we have noted in similar scenarios within recent articles of our series, and without getting too heavily into the details of approaches for this that we might undertake at the reporting application level, it is safe to say that this can be easily accommodated within the realm of the Analysis Services and Reporting Services components of the Microsoft integrated business intelligence solution. We would simply create a targeted "drilldowntop" or "drilldownbottom" report (or perhaps even a single drilldown report with a parameterized "top" or "bottom" modifier) that would be triggered from a primary, summary level report, and then "connect" the primary and secondary report(s) in a manner similar to that which we followed in a drillthrough scenario in a recent article in my MSSQL Server Reporting Services series, Mastering OLAP Reporting: Drilling Through Using MDX.

What lies at the heart of the request, we note in our early discussions with the client representatives, is the need to perform drilldown upon members of a given hierarchical level to members to a lower level (most often the next lower level, but possibly to another level that we wish to be able to specify). In the example case at hand, the authors tell us that they need to provide a query that enables drilldown for the Calendar Year 2003 Internet Order Quantity and Internet Sales Amount for three Adventure Works Product Categories. While, as we have said, the ultimate objective might be to make the "top" or "bottom" number returned within our report datasets variable, we decide to begin with examples of the routine usage of the DrillDownLevelTop() and DrillDownLevelBottom() functions.

The report authors tell us that they first wish to drill down, from the perspective of Internet Order Quantity and Internet Sales Amount, upon the Bikes, Clothing and Accessories Product Categories, returning the top two (2) seller Subcategories for CY2003. They wish to rank the Subcategories, and thus derive the "top" Subcategories, based upon Internet Sales Amount.

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


Illustration 8: "Confirmation Draft" of the Proposed, Ultimate Dataset Objective

We suggest beginning with the generic DrillDownLevel() function, and then morphing it into its DrilldownLevelTop() derivative, so as to build upon our efforts with DrilldownLevel() in an earlier session. (This may also prove beneficial for readers who are joining us with this article, and have not had an opportunity to explore our work with the more general function in Set Functions: The DrillDownLevel() Function.) Based upon this reasoning, therefore, we propose to begin by returning the three Product Categories in summary, displaying beneath each summary the drilled down children of the respective Category (themselves Product Subcategories) together with the Internet Order Quantity and Internet Sales Amount values related to each Subcategory for CY2003. Once this is accomplished, we will have a core query upon which to base our subsequent efforts with the DrillDownLevelTop() and DrillDownLevelBottom() functions.

Let's construct a simple query, therefore, to return the requested Calendar Year 2003 Internet Order Quantity and Internet Sales Amount information for the three selected Product Categories, presenting the summarized quantity and sales values for Bikes, Clothing, and Accessories, together with the corresponding values for each of the individually broken out component Subcategories that make up each of the Bikes, Clothing, and Accessories categories.

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


-- MDX041-001 Initial DrillDown With Selected Level
SELECT
   {[Measures].[Internet Order Quantity], [Measures].[Internet Sales Amount]} 
         ON AXIS(0),
DRILLDOWNLEVEL( 
 {[Product].[Product Categories].[Category].[Bikes], 
 
 [Product].[Product Categories].[Category].[Clothing],
  
 [Product].[Product Categories].[Category].[Accessories]})ON AXIS(1)
  
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 ...

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 Bikes, Clothing, and Accessories are presented in "rolled up" state. Moreover, the rows underneath each of Bikes, Clothing, and Accessories are composed of the respective Category's children, the various Subcategory level members. (And we can verify easily that the values of the child rows in the dataset add to the rolled up totals.)

3.  Select File -> Save [default file name] As ..., name the file MDX041-001, and place it in a meaningful location. (Leave MDX041-001 open for the following steps.)

Now that we have an idea what appears within the "drilled down" Product Categories, let's focus on the use of the DrillDownLevelTop() and DrillDownLevelBottom() functions, beginning with the initial request: to perform drill down for Internet Order Quantity and Internet Sales Amount, upon the Bikes, Clothing, and Accessories Product Categories, returning the top selling two (2) Subcategories for CY2003, ranking them upon Internet Sales Amount.

4.  Modify the top line in the query (the commented line) to the following:

-- MDX041-002-1 DrillDownLevelTop - Initial

5.  Select File -> Save MDX041-001 As..., name the file MDX041-002-1, placing it with the previous query files, to protect the former query.

6.  In the fifth line from the top in the query, replace the DRILLDOWNLEVEL keyword with the following:

DRILLDOWNLEVELTOP

7.  On the last line of the AXIS(1) specification (appearing one line above the FROM keyword), add a comma (",") after the right curly brace symbol ("}") – that is, appearing to the immediate right of following:

[Product].[Product Categories].
[Category].[Accessories])

8.  Insert a space, and then the following, after the comma just added:

2,

9.  Immediately after typing the comma (",") to the right of the 2, press the Enter key four times to push the remainder of the line down and insert a line between it and the line from which it travels.

10.  Insert the following syntax:

[Product].[Product Categories].
[Category],

between the following line into the open line we have created and the ON AXIS(1) keywords, that is, between:

[Product].[Product Categories].
[Category].[Accessories]),

and

)ON AXIS(1)

The new line represents the Level Expression we discussed in the Syntax section above. This is the level to which we are specifying that we wish to drill down via DrillDownLevelTop().

11.  Immediately after the newly added syntax, press the Enter key twice, to insert space underneath it.

12.  Insert the following syntax about midway in the newly created space:

[Measures].[Internet Sales Amount]

This insertion represents the Numeric Expression, upon which the "top" Count of two (2) is based. We therefore are specifying that we wish to return the two Subcategories for each of the Bikes, Clothing, and Accessories Product Categories, in terms of Internet Sales.

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


Illustration 12: Our Core Query, with Modifications

13.  Execute the query by clicking the Execute button in the toolbar, as we did earlier.

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


Illustration 13: Results Dataset – DrillDownLevelTop() at Work ...

We note that the three Product Categories are drilled down, displaying under each the top two underlying Subcategories with regard to Internet Sales Amount.

As we noted was the case with various components of the axis definitions, together with the slicer, in Set Functions: The DrillDownLevel() Function, the Count (here fixed at 2) – and even the Level Expression (here specified as the Category level of the Product hierarchy) and (as we will see in our next practice example) Numeric Expression (here specified as the Internet Sales Amount measure) – might be subjected to parameterization within a reporting (or other) application. For that matter, we might even create a report parameter whose setting determined the placement of the DrillDownLevelTop, DrillDownLevelBottom, or the more generic DrillDownLevel keyword to allow variable actions within the query underlying a given report dataset. As with many other options that accrue to us when developing within the Microsoft integrated BI solution, the clever combination of MDX functions and parameterization can mean far-reaching capabilities with regard to manipulation of a target report via our MDX queries.

14.  Select File -> Save MDX041-002-1 to save the query.

Let's look at a further example of the operation of DrillDownLevelTop(). The report developers and authors with which we are working have informed us that they are pleased with the clarity of the example we have presented, and state that they wish to see the same query in action, this time adjusted to use the Internet Order Quantity as the basis upon which the "top two" Subcategories are selected. We can manage this change with a simple substitution of the Numeric Expression, currently represented by the Internet Sales Amount measure.

15.  Modify the top line in the query (the commented line) to the following:

-- MDX041-002-2 DrillDownLevelTop - New Basis Measure

16.  Select File -> Save MDX041-002-1 As..., and name the file MDX041-002-2, placing it with the previous query files, to protect our former query.

17.  Replace the following Numeric Expression (which we added in our last set of steps above):

[Measures].[Internet Sales Amount])

(just above the line containing the keywords ON AXIS(1)) with the following:

[Measures].[InternetOrder Quantity])

The Query pane appears, with our modifications circled, as depicted in Illustration 14.


Illustration 14: The Modified Query ...

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

The dataset shown in Illustration 15 appears in the Results pane.


Illustration 15: Results Dataset – DrillDownLevelTop() with Another Basis

We note that the three Product Categories are drilled down, as before, displaying under each the top two underlying Subcategories. This time, however, it is apparent that the "top" selections have been made based upon the Internet Order Quantity measure.

19.  Select File -> Save MDX041-002-2 to save the query.

The report developers and authors are again happy with the confirmation of understanding that they have received from our example. They wish to perform a final practice example, whereby our objective will be to put the DrillDownLevelBottom() function to use in simple form. The request this time builds, to some extent, upon concepts surrounding drill down by level, but is slightly more elaborate than the previous examples. This time, multiple concepts come into play in meeting the business requirement.

The reporting group tells us that they wish to construct a query that returns a dataset containing a date element, along with a Customer component. They wish to present the five lowest Internet Sales state / provinces, from a shipping perspective, for a given Country, within a given shipping month. While our example centers on the Australian customers, the report authors and developers state that they grasp the fact that the Country itself, along with the shipping period and other query components, can be parameterized (in their case, within Reporting Services), so that, once they have a working example of a query that returns the desired information, they can leverage it to go well beyond company sales to the Australian customer base.

We will start with a core query, as we often have in the past, to return the basic data elements in a straightforward manner, before we complicate it with the drill down considerations. To create the core query, we must first align the Customer information with the required time element, the calendar shipping month of April 2003. We will do this within a new core query, taking the following steps.

20.  Select File --> New from the main menu in the SQL Server Management Studio.

21.  Select Query with Current Connection from the context menu that appears, as depicted in Illustration 16.

Click for larger image

Illustration 16: Creating a New Query from the Current Connection ...

A new tab appears within the Query pane.

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


-- MDX041-003-1 DrillDownLevelBottom Example - Core Query before 
 DrillDownLevelBottom()

SELECT
   {[Measures].[Internet Sales Amount]}
         ON AXIS(0),
CROSSJOIN(
 {
  [Customer].[Customer Geography].[Country].[Australia]
 },
 
 {[Ship Date].[Calendar].[Month].&[2003]&[4]})  
     
  ON AXIS(1)
FROM 
[Adventure Works]

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


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

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

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


Illustration 18: Results Dataset – Initial Core Query, Juxtaposing Customer Country and Time

In the returned dataset, we see that the Customer Country of interest, Australia, is crossjoined with April 2003. It is from this perspective that we will put level drilldown into place, with the ultimate objective being to return the five Australian Customers with the lowest Internet Sales for products shipped in the Calendar Month of April 2003.

24.  Select File -> Save [default file name] As ..., name the file MDX041-003-1, and place it in a meaningful location. (Leave MDX041-003-1 open for the following steps.)

Now that we have an idea what appears within the "drilled up" Customer Country level, we have a perspective from which to focus on the use of the DrillDownLevelBottom() function. We have assembled the core requirement, Internet Sales for the Customers in Australia for April 2003. Our next steps will be to drill down one level below the Customer Country, within the Customer Geography hierarchy of the Customer dimension, to the State - Province level. We will perform this level drilldown, and retrieve the five Australian Customers with the lowest Internet Sales for products shipped in the Calendar Month of April 2003, by modifying our new core query.

25.  Modify the top line in the query (the commented line) to the following:

-- MDX041-003-2 DrillDownLevelBottom Example – Adding DrillDown

26.  Select File -> Save MDX041-003-1 As..., name the file MDX041-003-2, placing it with the previous query files, to protect the former query as before.

Because the desired effect is actually to drill down within the Customer Geography hierarchy, and not the juxtaposed Customer Country and Shipping Month, we must place the DrillDownLevelMember() function within our CrossJoin() function. (This is one of the reasons that creating the base query first can often help us to plan the steps to getting to the desired end result).

27.  Within the CrossJoin clause, create a space, and add the DRILLDOWNMEMBER keyword with a left parenthesis, as shown (bold) below:


CROSSJOIN(
  {
    DRILLDOWNLEVELBOTTOM (
   [Customer].[Customer Geography].[Country].[Australia]

28.  Insert a comma after [Customer].[Customer Geography].[Country].[Australia], in the line underneath the newly inserted DRILLDOWNMEMBER keyword shown above.

29.  Insert a space, and then the number 5, behind the newly inserted comma.

30.  Insert a comma after the number 5.

31.  Press the ENTER key a couple of times to begin another blank row underneath that in which we have made the most recent changes.

32.  Add the following in the new row:

[Customer].[Customer Geography].[Country], 

NOTE: Be sure to add the comma, as shown, at the end of the expression.

This represents the Level Expression, which the DrillDownLevelBottom() function uses to dictate the level upon which drill down is based. In other words, by inserting this Level Expression, we are directing DrillDownLevelBottom() to drill down to the level immediately below the Country level of the Customer Geography hierarchy of the Customer dimension, to the State–Province level.

33.  Press the ENTER key to begin another blank row.

34.  Add the following in the new row:

[Measures].[Internet Sales Amount])

NOTE: Be sure to add the right parenthesis, as shown, at the end of the expression.

This represents the Numeric Expression, which the DrillDownLevelBottom() function uses to determine the basis upon which we are directing the function to return the specified number of "bottom values." In the immediate example, we are inserting the measure Internet Sales Amount to instruct DrillDownLevelBottom() to return the five State-Province members with the lowest values for Internet Sales for the month of April 2003.

The Query pane appears, with our modifications circled, as shown in Illustration 19.


Illustration 19: Our Core Query, with Modifications

35.  Execute the query by clicking the Execute button in the toolbar, as we did earlier.

The dataset depicted in Illustration 20 appears in short order.


Illustration 20: Results Dataset – DrillDownLevelBottom() in Action ...

We note that the Customer Country Australia appears, with the five lowest State-Province performers in April 2003, from the perspective of Internet Sales. We also notice that the corresponding Internet Sales values for the State-Provinces are in ascending order.

We are reminded, once again, that several of the components of our query represent opportunities for parameterization, including the "Bottom" portion of the primary keyword (we could parameterize the selection of "Top" or "Bottom" here), the Set Expression, the Count, the Level Expression, and the Numeric Expression. Moreover, we have additional opportunities of this sort with the slicer and the Rows axis.

36.  Select File -> Save MDX041-003-2 to save the query.

The report authors state that their immediate goals have been met. They wish to practice with these concepts next, and then to regroup for further drilling-related functions later. We agree to return to demonstrate additional drill-related functions in Reporting Services, whereby we can leverage MDX to support interactive drilling by organizational information consumers, in the near future.

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

Summary ...

In this article, we continued an extended examination of "the MDX behind drilling up and down" within our Analysis Services cubes, which we began in Set Functions: The DRILLDOWNMEMBER() Function. In this article, we focused upon two specialized set functions that are based upon the principles underlying DrillDownLevel(), the DrillDownLevelTop() and DrillDownLevelBottom() functions. We noted from the outset that, instead of behaving like DrillDownLevel(), in its inclusion of all children for each member within the specified Level Expression, DrillDownLevelTop() returns the topmost (specified) number of child members for each member, while the DrillDownLevelBottom() function returns the bottommost (specified) number of child members for each member.

We noted that, again like the DrillDownLevel() function, either of DrillDownLevelTop() or DrillDownLevelBottom() can be used in conjunction with tuples within the sets, as well as members. We also commented throughout the article that, like numerous other functions that we have examined in the MDX Essentials series, DrillDownLevelTop() and DrillDownLevelBottom() can each be useful in a host of different reporting and analysis applications. Each of these two drilldown functions 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. We noted that the DrillDownLevelTop() and DrillDownLevelBottom() functions can be leveraged, within and among the various "layers" of the Microsoft integrated Business Intelligence solution, to support sophisticated presentations and features.

Within our introduction of the DrillDownLevelTop() and DrillDownLevelBottom() functions, we commented upon the operation of each, and touched upon examples of effects that we can employ each to deliver. We examined the syntax involved with each of the functions, and then undertook hands-on exercises, building, initially, from a core query containing the broader DrillDownLevel() function which we then modified to leverage DrillDownLevelTop() to meet the more specific business needs of a hypothetical group of information consumers. We then handled a slightly more elaborate requirement with DrillDownLevelBottom(), designing first a core query in an attempt to provide focused examination upon the addition of the DrillDownLevelBottom() function within an existing query's construction whereby we could isolate the function under consideration from the rest of the query components that surrounded it. Finally, we discussed the results of each of the datasets we obtained within our practice examples.

» 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