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

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
Database Tools
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
News Via RSS Feed

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

Featured Database Articles


Posted Apr 3, 2006

Set Functions: The DrillUpLevel() Function - Page 5

By William Pearson

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

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

15.  Leave the query open for the next step.

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

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

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

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

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

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

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

20.  Type the following on the new row:


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

[Customer].[Customer Geography].[City], 

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

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

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

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

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

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

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

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

24.  Leave the query open for the next step.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Summary ...

In this article, we continued our extended examination of the MDX surrounding drilling up and down within our Analysis Services cubes, focusing this time upon the DrillUpLevel() function. We noted that, like many MDX functions, DrillUpLevel() can be leveraged within and among the various "layers" of the Microsoft integrated Business Intelligence solution to support sophisticated presentations and features. We introduced the function, touching upon examples of effects that we can employ it to deliver to empower information consumers to maneuver between detailed and summarized levels of data.

In introducing DrillUpLevel(), we commented upon its operation and discussed the "collapsed" datasets we can deliver using a specified Set Expression, together with an optional Level Expression, within the function. Next, we examined the syntax involved with DrillUpLevel(), and then undertook a couple of illustrative practice examples within which we met hypothetical business requirements with the function. We generated a query that capitalized on its primary features within the context of a drilled down scenario, in order to witness the function in action.

We then undertook an example where we combined DrillUpLevel() with the Descendants() function, to demonstrate how the "drilled down" effect of Descendants() might serve as a "starting point" for a query to which we might apply "drill up" logic to meet subsequently determined needs for summarization at different levels. Moreover, we discussed points within our query where we might consider the insertion of parameterization in a reporting application (such as Reporting Services), or in other consumer applications, to leverage the function to support drillup in an ad hoc manner – and especially in conjunction with drilldown operations. Throughout our practice session, we briefly discussed the results datasets we obtained from each of the queries we constructed.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

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