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:
The Query pane appears, with our input, as depicted in Illustration 12.
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.
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.