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

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Mar 6, 2006

Set Functions: The DrillDownLevelTop() and DrillDownLevelBottom() Functions - Page 6

By William Pearson

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 

   {[Measures].[Internet Sales Amount]}
         ON AXIS(0),
  [Customer].[Customer Geography].[Country].[Australia]
 {[Ship Date].[Calendar].[Month].&[2003]&[4]})  
  ON AXIS(1)
[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:

   [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.

MS SQL Archives

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