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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Dec 5, 2005

MDX Essentials: Set Functions: The DRILLDOWNMEMBERTOP() and DRILLDOWNMEMBERBOTTOM() Functions - Page 5

By William Pearson

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.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




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