MDX Essentials: Set Functions: The DRILLDOWNMEMBERTOP() and DRILLDOWNMEMBERBOTTOM() Functions

Monday Dec 5th 2005 by William Pearson
Share:

Architect Bill Pearson continues his examination of drilling up and down within MDX, exposing two specialized DRILLDOWNMEMBER() functions, DRILLDOWNMEMBERTOP() and DRILLDOWNMEMBERBOTTOM()

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 discussed the nature of drilldown, in our last article, 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.

We introduced the "primary" MDX drilldown function, DrillDownMember(), stating that it supports the capability of Analysis Services to meet the common need for drilldown. Through our overview, discussion, examination of the syntax, and hands-on practice session with the function, we discovered how DrillDownMember() drills down to the members within a specified primary set that are present within a specified second set. 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 DrillDownMember(). DrilldownMemberTop() and DrilldownMemberBottom() are both similar to the DrilldownMember() function. However, instead of behaving like DrilldownMember(), in its inclusion of all children in a specified primary set that also exist in a specified secondary set, DrilldownMemberTop() returns the topmost (specified) number of child members for each member, while the DrilldownMemberBottom() function returns the bottommost (specified) number of child members for each member.

Like the DrillDownMember() function, both DrilldownMemberTop() and DrilldownMemberBottom() can be used in conjunction with tuples within the sets, as well as members. DrilldownMemberTop() and DrilldownMemberBottom() also make provision for allowing repeated comparison passes within their operation, just as we saw with the DrillDownMember() function, through the provision of an optional RECURSIVE keyword.

In a manner similar to DrillDownMember(), among numerous other functions that we have examined in the MDX Essentials series, DrilldownMemberTop() and DrilldownMemberBottom() 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 DrilldownMemberTop() and DrilldownMemberBottom() 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 DrilldownMemberTop() and DrilldownMemberBottom() Functions

Introduction

According to the Books Online, both the DrillDownMemberTop() and DrillDownMemberBottom() functions "drill down the members in a specified set that are present in a second specified set, limiting the result set to a specified number of members." Moreover, both functions "alternatively ... drill down on a set of tuples." The DrillDownMemberTop() and DrillDownMemberBottom() functions thus behave exactly like the DrilldownMember() function that we examined in Set Functions: The DRILLDOWNMEMBER() Function, with one main difference: both "limit the result set to a specified number of members."

Both DrillDownMemberTop() and DrillDownMemberBottom(), like the DrilldownMember() function, work with a primary and secondary set that we specify. The set of child members returned by the functions are ordered by hierarchy, and include those members specified in the primary set that are also present in the secondary set. Implicit in their design, both DrillDownMemberTop() and DrillDownMemberBottom() sort the children of each member in a specified primary set (DrillDownMemberTop() sorts the members in descending order, and DrillDownMemberBottom() sorts the members in ascending order), based upon an optional numeric expression that we provide in either case. After ranking, each of the functions return, like DrilldownMember(), a set containing the parent members and child members, but the members returned by DrillDownMemberTop() and DrillDownMemberBottom() are limited to a specified count. DrillDownMemberTop() and DrillDownMemberBottom() thus return sets containing parent members – with the specified count of child members - that are contained in both the primary and secondary sets – with the highest (for DrillDownMemberTop() ) and lowest (for DrillDownMemberBottom() ) values involved. In effect, instead of including all children for each member that exists in both the primary and secondary sets, DrillDownMemberTop() and DrillDownMemberBottom() return the topmost and bottommost, respectively, specified number of children for each member.

As was the case with DrillDownMember(), the DrillDownMemberTop() and DrillDownMemberBottom() 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. Following 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 DrillDownMemberTop() and DrillDownMemberBottom() 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 DrillDownMemberTop() and DrillDownMemberBottom() functions drill down the members in a specified primary set that are present in a specified secondary set, just as we found to be the case with the more "general" DrillDownMember() in our earlier article. The same basic rules, with regard to the core operation of the functions, apply:

  • The primary set can have any dimensionality, but the secondary set must contain a one-dimensional set;
  • The result set returned by each function is composed of the children of each primary set member that also exists in the secondary set.
  • If the primary set contains a parent member with one or more children, that parent member will not be subjected to drilldown.
  • When the primary set contains tuples, each of the functions returns a set composed of the results of drilling down each tuple in the primary set for which a corresponding match exists in the secondary set.

The main difference between the operation of each of the DrillDownMemberTop() and DrillDownMemberBottom() functions and the DrilldownMember() function is that DrillDownMemberTop() and DrillDownMemberBottom() each limits the results set it returns to a specific number of members. The number is specified within the function in a count we supply. As one might expect from the "top" and "bottom" specialization of the functions, DrillDownMemberTop() and DrillDownMemberBottom() perform rankings of the children of the members that they identify as existing in both the primary and secondary sets (based upon an optional numeric expression we can specify), DrillDownMemberTop() in descending order and DrillDownMemberBottom() in ascending order.

This is the point at which the count we provide comes into play: DrillDownMemberTop() and DrillDownMemberBottom() limit the members returned to the number of the count. As an example, if the count we specified was five (5), DrillDownMemberTop() would return the highest five members, and DrillDownMemberBottom() would return the lowest five members (assuming five or more members existed within the qualified set).

We noted earlier that the optional RECURSIVE keyword is available to enable repeated comparison passes between the primary and secondary sets, again just as was the case with the DrillDownMember() function. When we do not opt to add RECURSIVE, a single pass through the primary set is performed, matching each member / tuple with a corresponding member in the secondary set, before applying the limitations of the count we have discussed. When we add the RECURSIVE keyword, both DrillDownMemberTop() and DrillDownMemberBottom() perform a continual, recursive comparison between the result set and the secondary set, returning, for each member in the result set that is also present in the secondary set, the children – until no further members from the result set can be found within the secondary set. The count limitation is then applied to the accumulated members before returning the set of desired size.

A wide range of analysis and reporting opportunities await the practitioner who becomes adept at coupling DrillDownMemberTop() and DrillDownMemberBottom() 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 DrillDownMemberTop() and DrillDownMemberBottom().

Syntax

Syntactically, anytime we employ the DrillDownMemberTop() and DrillDownMemberBottom() functions to drill down by a member, the initial effect of both functions, as we have discussed, is to drill down those members of the specified primary set that are also present in the specified secondary set. We specify the two sets within the parentheses to the right of the DRILLDOWNMEMBERTOP / DRILLDOWNMEMBERBOTTOM keyword. In both cases, too, we can optionally follow the specified sets, within the same parentheses, with the RECURSIVE keyword. The general syntax is shown in the following string:

DrilldownMemberTop / Bottom(Set1, Set2, Count [ , [Numeric Expression] [, RECURSIVE ] ])

(Set1 in the string represents the "Primary Set" to which I refer throughout our discussion. Set2 represents the "Secondary Set.")

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

DRILLDOWNMEMBERTOP( {
   [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]},3, 
  [Measures].[Reseller Sales Amount] )) ON ROWS

This ROWS axis specification, within a proper query executed against the Adventure Works sample cube that, say, stipulated the Reseller Sales Amount in the column specification, and calendar year 2003 in the slicer, might produce a results dataset similar to that depicted in Illustration 1.


Illustration 1: Results Dataset – Product Lines Drilled to Child Products

In the example dataset, we see that the three cycle Product Lines, Mountain, Road and Touring (the top three rows of the dataset) are presented in "rolled up" state. Underneath the Touring summary value (the third row from the top), we see the drilled down values for the top three (ranked upon the Reseller Sales Amounts for each) constituent child Model Names, Touring-1000, Touring-3000 and Touring-2000. (We note, too, that the total of the child Model Names in the dataset add to the rolled up Touring total of 6,464,481.11.) Moreover, we recognize, too, the descending sort of the top three children presented within the Touring Product Line.

DrillDownMemberBottom() works in much the same manner, except that it provides the bottom values, based upon the measure upon which we choose to rank, in an ascending sort, as we shall see. We will get some practice using the DrillDownMemberTop() and DrillDownMemberBottom() 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 the DrillDownMemberTop() and DrillDownMemberBottom() functions in a couple of exercises to illustrate their operation on the same general data. We will do so in simple scenarios that place DrillDownMemberTop() and DrillDownMemberBottom() 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: Click the New Query Button with the Adventure Works Cube Selected

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 a 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. They seek working examples, which they intend to extrapolate to recurring business needs they encounter within the enterprise.

The authors want to perform drilldowns much as they did in our previous session in Set Functions: The DRILLDOWNMEMBER() Function, but wish to extend the basic drilldowns we performed there to include the capability to present "top" and "bottom" results based upon drilldown, using the related MDX functions as the mechanism. Without getting too heavily into the details of the approach for this 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 BI 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 on a group of members. 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 a couple of the 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 DrillDownMemberTop() and DrillDownMemberBottom() functions.

The report authors tell us that they first wish to drill down for Internet Order Quantity and Internet Sales Amount for the Accessories and Clothing Product Categories, returning the top seven (7) seller Subcategories for CY2003, ranking them upon Internet Sales Amount.

We suggest beginning with the generic DrillDownmember() function, and then morphing it into its DrilldownMemberTop() derivative, so as to build upon our work with DrilldownMember() in the previous 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 DRILLDOWNMEMBER() Function.) Based upon this reasoning, therefore, we propose to begin by returning the two 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 DrillDownMemberTop() and DrillDownMemberBottom() functions.

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, Core Dataset Objective

This represents a simple, yet practical, need that we can readily answer using the DrillDownMember() function. We are confident that, once we construct and demonstrate the workings of the function, we can then expand upon those concepts with examples of each of the focuses of this article, the DrillDownMemberTop() and DrillDownMemberBottom() 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 two selected Product Categories, presenting the summarized sales for Accessories and Clothing, together with the corresponding values for each of the individually broken out component Subcategories that make up each of the Accessories and Clothing categories.

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


-- MDX038-001 Standard DrillDown With Selected Members

SELECT
   {[Measures].[Internet Order Quantity], [Measures].[Internet Sales Amount]} 
         ON COLUMNS,
DRILLDOWNMEMBER( {
 [Product].[Product Categories].[Category].[Accessories], 
 
    [Product].[Product Categories].[Category].[Clothing]},
  
          {[Product].[Product Categories].[Category], 
   
    [Product].[Product Categories].[Category].[Clothing].Children }
  
              ) 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 ...

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 Accessories and Clothing are presented in "rolled up" state. However, the rows underneath each of Accessories and Clothing are composed of the respective Category's children, the various Subcategory components. (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 MDX038-001, and place it in a meaningful location. (Leave MDX038-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 DrillDownMemberTop() and DrillDownMemberBottom() functions, beginning with the initial request: to perform drill down for Internet Order Quantity and Internet Sales Amount for the Accessories and Clothing Product Categories, returning the top seven (7) seller Subcategories for CY2003, ranking them upon Internet Sales Amount.

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

--
MDX038-002 DrillDownMemberTop - Simple

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

6.  In the fourth line from the top in the query, replace the DRILLDOWNMEMBER keyword with the following:

DRILLDOWNMEMBERTOP

7.  On the last line of the ROW AXIS specification (appearing one line above the ON ROWS keywords), add a comma (",") after the right curly brace (appearing to the immediate right of the .Children function).

8.  Insert a new line between

Product].[Product Categories].[Category].[Clothing].Children },

and

) ON ROWS

as follows:

7,[Measures].[Internet Sales Amount]

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

Click for larger image

Illustration 12: Our Second Query, with Modifications

9.  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 – DrilldownMemberTop() at Work ...

We note that both Product Categories are drilled down, and display the top seven underlying Subcategories with regard to Internet Sales Amount.

As we noted was the case with the secondary set in the ROWS axis specification, as well as with the RECURSIVE keyword, for DrilldownMember()in Set Functions: The DRILLDOWNMEMBER() Function, the Count (here fixed at 7) – and even the numeric function (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 DRILLDOWNMEMBERTOP, DRILLDOWNMEMBERBOTTOM, or the more generic DRILLDOWNMEMBER 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.

10.  Select File -> Save MDX038-002 to save the query.

Let's take a look at another extension upon our original DrilldownMember() query above. This time, let's say, the objective will be to put the DrillDownMemberBottom() function to use in simple form, to provide a working sample for the client representatives with which we are working. The request this time is to perform drill down for the same measures, Internet Order Quantity and Internet Sales Amount for the Accessories and Clothing Product Categories, returning the bottom seven (7) seller Subcategories for CY2003, ranking them upon Internet Sales Amount. The similarities in the query (all is the same except the function keyword) will make it easy to contrast its workings with that of the last query, where we exploited DrillDownMemberTop().

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

-- MDX038-003 DrillDownMemberBottom - Simple

12.  Select File -> Save MDX038-002 As..., name the file MDX038-003, placing it with the previous query files, to protect the former query.

13.  In the fifth line from the top in the query, replace the DRILLDOWNMEMBERTOP keyword with the following:

DRILLDOWNMEMBERBOTTOM

The rest of the query is already in place, as we intend to supply a count of 7, as before, as well as to base ranking upon the Internet Sales Amount measure. (I hope this makes it obvious how simply "switching" the keyword via a parameter in the reporting layer, or elsewhere, means switching the "top" / "bottom" nature of the query. The same can be done with the count, as well as other elements of the query, for easy ad hoc variations by the intended consumers).

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


Illustration 14: The Query – Modified to Use DrilldownMemberBottom()

14.  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 15 appears.


Illustration 15: Results Dataset – DrilldownMemberBottom() in Action ...

We note that, as we might have expected, both Product Categories are drilled down, and display the bottom seven underlying Subcategories with regard to Internet Sales Amount.

15.  Select File -> Save MDX038-003 to save the query.

Finally, let's add a bit more complexity to our straightforward use of the DrillDownMemberBottom() function, and supply the keyword necessary to make our query recursive. The process of applying the RECURSIVE keyword is similar for DrillDownMemberTop() and DrillDownMemberBottom(), so we will let this example suffice for the present exercise. Recall that our specification of either of the functions as "recursive" means that, after performing the initial sort of the primary set, which we have discussed above, the respective function recursively compares the members of the primary set to those of the secondary set, with the results that we shall see in the following steps.

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

-- MDX038-004 DrillDownMemberBottom - with Recursion

17.  Select File -> Save MDX038-003 As..., name the file MDX038-004, placing it with the previous query files, to protect the former query.

18.  Modify the next to last line in the ROWS axis specification (the tenth line in the query from the top) from the following:

7,[Measures].[Internet Sales Amount]

to the following:

7,[Measures].[Internet Sales Amount], RECURSIVE

NOTE: The modification consists solely of adding a comma (",") after the right brace of the numeric expression Internet Sales Amount, and following it with the RECURSIVE keyword.

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


Illustration 16: Adding Recursion to our DrilldownMemberBottom() Function ...

19.  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 partially shown in Illustration 17 appears.


Illustration 17 Partial Results Dataset – DrilldownMemberBottom() with RECURSIVE Keyword

We see the Clothing Subcategories drilled even further to their own respective children. As we noted was the case with the count and numeric expression, as well as with other components of the DrilldownMember(), DrilldownMemberTop() and DrilldownMemberBottom() functions, the RECURSIVE keyword can also be subjected to parameterization within a reporting (or other) application, for extended capabilities with regard to manipulation of a target report containing the function of the DRILLDOWNMEMBER() family under consideration.

20.  Select File -> Save MDX038-004 to save the query.

The report authors state that their immediate goals have been met. We agree to return at a later time to demonstrate approaches to implement the DrilldownMemberTop() and DrilldownMemberBottom() functions in Reporting Services to leverage MDX to support interactive drilldown by organizational information consumers.

21.  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. After reviewing the nature of drilldown, in general, and the DrillDownMember() function specifically, we introduced two specialized set functions that are based upon the principles underlying DrillDownMember(). We noted that DrilldownMemberTop() and DrilldownMemberBottom() operate quite similarly to the DrilldownMember() function, except in the nature of the datasets returned: Instead of including all children in a specified primary set that also exist in a specified secondary set, DrilldownMemberTop() returns the topmost (specified) number of child members for each member, while the DrilldownMemberBottom() function returns the bottommost (specified) number of child members for each member. We discussed other similarities between DrillDownMember() and these two functions, including the provision of the optional RECURSIVE keyword to allow for repeated comparison passes in their operation.

We discussed throughout the article how, in a manner similar to the DrillDownMember() function, DrilldownMemberTop() and DrilldownMemberBottom() can each be useful in a host of different reporting and analysis applications, affording much flexibility through 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 highlighted general approaches to leveraging DrilldownMemberTop() and DrilldownMemberBottom() within and among the various "layers" of the Microsoft integrated Business Intelligence solution to support sophisticated presentations and features.

In introducing DrilldownMemberTop() and DrilldownMemberBottom(), 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 from a core query containing the broader DrillDownMember() function which we then modified to leverage DrilldownMemberTop() and DrilldownMemberBottom() to meet the more specific business needs of a hypothetical group of information consumers. 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