Set Functions: The DrillUpLevel() Function - Page 5

April 3, 2006

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:

DRILLUPLEVEL(

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

[Customer].[Customer Geography].[City], 
  SELF_BEFORE_AFTER)})

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.

MDX Essentials Series
The LEVEL_NUMBER Member Property
The LEVEL_UNIQUE_NAME Intrinsic Member Property
Intrinsic Member Properties: The HIERARCHY_UNIQUE_NAME Property
Intrinsic Member Properties: The DIMENSION_UNIQUE_NAME Property
Further Combination of BottomCount() with Other MDX Functions
Combine BottomCount() with Other MDX Functions to Add Sophistication
Basic Set Functions: The BottomCount() Function, Part I
Intrinsic Member Properties: The MEMBER_VALUE Property
Intrinsic Member Properties: The MEMBER_UNIQUE_NAME Property
Intrinsic Member Properties: The MEMBER_NAME Property
Intrinsic Member Properties: The MEMBER_KEY Property
Intrinsic Member Properties: The MEMBER_CAPTION Property
Set Functions: The StripCalculatedMembers() Function
Set Functions: The AddCalculatedMembers() Function
MDX Numeric Functions: The Min() Function
MDX Numeric Functions: The Max() Function
Set Functions: The .AllMembers Function
MDX Essentials: Set Functions: The MeasureGroupMeasures() Function
String Functions: The .Properties Function, Part II
String Functions: The .Properties Function
Logical Functions: IsGeneration(): Conditional Logic within Filter Expressions
MDX Scripting Statements: Introducing the Simple CASE Statement
Logical Functions: IsGeneration(): Conditional Logic within Calculations
Logical Functions: IsAncestor(): Conditional Logic within Filter Expressions
MDX Clauses and Keywords: Use HAVING to Filter an Axis
Logical Functions: IsAncestor(): Conditional Logic within Calculations
Logical Functions: IsSibling(): Conditional Logic within Filter Expressions
Logical Functions: IsSibling(): Conditional Logic within Calculations
MDX Operators: The IsLeaf() Operator: Conditional Logic within Filter Expressions
MDX Operators: The IsLeaf() Operator: Conditional Logic within Calculations
MDX Numeric Functions: The .Ordinal Function
Other MDX Entities: Perspectives
MDX Operators: The IS Operator
MDX Set Functions: The Distinct() Function
MDX Set Functions: The ToggleDrillState() Function
Set Functions: The DrillUpLevel() Function
Set Functions: The DrillDownLevelTop() and DrillDownLevelBottom() Functions
MDX Set Functions: DrillDownLevel()
MDX Set Functions: The DRILLUPMEMBER() Function
MDX Essentials: Set Functions: The DRILLDOWNMEMBERTOP() and DRILLDOWNMEMBERBOTTOM() Functions
MDX Essentials : Set Functions: The DRILLDOWNMEMBER() Function
MDX Essentials: Drilling Through with MDX: The DRILLTHROUGH Statement
MDX Essentials: String Functions: The .UniqueName Function
MDX Essentials: String Functions: The .Name Function
MDX Essentials: String / Numeric Functions: The CoalesceEmpty() Function
MDX Essentials: Basic Set Functions: The TopCount() Function, Part II
MDX Essentials: Basic Set Functions: The TopCount() Function, Part I
MDX Essentials: Enhancing CROSSJOIN() with Calculated Members
MDX Essentials: Set and String Functions: The GENERATE() Function
MDX Essentials: The CROSSJOIN() Function: Breaking Bottlenecks
MDX Essentials: String / Numeric Functions: More on the IIF() Function
MDX Essentials: String / Numeric Functions: Introducing the IIF() Function
MDX Essentials: Logical Functions: The IsEmpty() Function
MDX Essentials: Basic Set Functions: The EXTRACT() Function
MDX Essentials: Numeric Functions: Introduction to the AVG() Function
MDX Essentials: Basic Member Functions: The .Item() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Subset() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Tail() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Head() Function
MDX Essentials: Basic Set Functions: The CrossJoin() Function
MDX Essentials: Basic Numeric Functions: The Count() Function
MDX Essentials: Basic Set Functions: The Filter() Function
MDX Essentials: Basic Set Functions: The EXCEPT() Function
MDX Essentials: Basic Set Functions: The Intersect() Function
MDX Essentials: Basic Set Functions: The Union() Function
MDX Essentials: Basic Set Functions: The Order() Function
MDX Essentials - MDX Time Series Functions, Part III: The LastPeriods() and ParallelPeriod() Functions
MDX Time Series Functions, Part II: The OpeningPeriod () and ClosingPeriod() Functions
MDX Essentials - MDX Time Series Functions, Part I: PeriodsToDate() and Kindred Functions
MDX Essentials: MDX Member Functions: "Relative" Member Functions
MDX Member Functions: The Cousin () Function
MDX Essentials: Member Functions: More "Family" Functions
MDX Member Functions: The "Family" Functions
MDX Essentials: MDX Members: Introducing Members and Member
MDX Essentials : MDX Operators: The Basics
MDX Essentials: Structure of the MDX Data Model
MDX at First Glance: Introduction to SQL Server MDX Essentials








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers