MDX Set Functions: The DRILLUPMEMBER() Function - Page 5

January 3, 2006

Procedure: Drill Up Using the DrillUpMember() Function

Having arrived at a good conceptual starting point, we are positioned to leverage the capability to drill up. After obtaining consensus on the dataset, and corroborating the fact that the group with which we are working has gained an understanding of what we have accomplished with DrillDownMember(), we next set out to show the drillup process using the same data structures. We will simply substitute the DrillUpMember() function in place of the DrillDownMember() function within the same query, to meet the desired ends.

5.  Replace the comment line in query MDX039-001 with the following:

-- MDX039-002 Drillup With Selected Members

6.  Leaving all other syntax "as is," replace the DrillDownMember keyword in our initial query with the following in the Query pane:

DrillUpMember

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

Click for larger image

Illustration 12: "Adjusted" Query in the Query Pane (Modifications Circled) ...

7.  Select File --> Save MDX039-001.mdx As ..., name the file MDX039-002, and place it in a meaningful location, to protect the former query.

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

The Results pane is populated by Analysis Services, and the dataset shown in Illustration 13 appears.


Illustration 13: Results Dataset – Basic Use of DrillUpMember() Function

And so we see that DrillUpMember() has the opposite effect within the query, as DrillDownMember(). With minor exceptions, such as the non-provision of a RECURSIVE keyword, the differences between the two functions lie solely in "direction:" "up" or "down."

In the returned dataset, we see that all Model Lines (Mountain, Road and Touring) are presented in "rolled up" state. The action we have witnessed has been the drillup of the members of the Touring Model Line. The rows that appeared underneath Touring, which represented the Model Line's children, (the components of the Touring Model Line) have now been absorbed into a single summary line, for presentation purposes.

One of the reasons that I chose to introduce the DrillUpMember() function within the query context of an immediately preceding DrillDownMember() is to illustrate just what we have seen: the only thing that decides the "direction" of drilling is the choice of keywords we insert into the query. This makes it easy to see that, within OLAP reporting tools, such as Reporting Services, as well as other applications, we can make the keyword conditional upon an action of the user at runtime. We can therefore parameterize the drill up or drill down action in this manner relatively easily. While we will not further extend our examination of the MDX functions, or of parameterization in general, within this article, I provide hands-on guidance in these subjects within my MSSQL Server Reporting Services series, as well as in other series' at Database Journal.

9.  Select File --> Save MDX039-002.mdx to ensure that we that the file is saved.

The client developers and report authors again express satisfaction with the results, and confirm their understanding in the operation of the DrillUpMember() function. Once they inform us that their immediate goals have been met, we agree to return at a later time to demonstrate approaches to implement the DrillUpMember() function, in conjunction with the DrillDownMember() function, within Reporting Services. The group states that it is anxious to leverage MDX to support interactive drillup and drilldown by organizational information consumers.

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

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 DrillUpMember() function. We noted that, like many MDX functions, DrillUpMember() 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, commenting upon its operation and 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 DrillUpMember(), we commented upon its operation and discussed the "collapsed" datasets we can deliver using a specified primary and secondary set within the function. Next, we examined the syntax involved with DrillUpMember(), and then undertook an illustrative practice example 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. Further, 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