Set Functions: The DrillUpLevel() Function - Page 3

Practice

Preparation: Access SQL Server Management Studio

To reinforce our understanding of the basics we have covered so far, we will use the DrillUpLevel() function in a couple of ways that illustrate its operation. We will do so in simple scenarios that place DrillUpLevel() within the context of meeting business requirements similar to those we might encounter in our respective daily environments. The intent, as in all the practice sessions of this series, is to demonstrate the operation of the DrillUpLevel() function in a straightforward, memorable manner.

We will turn to the SQL Server Management Studio as a platform from which to construct and execute the MDX we examine, and to view the results datasets we obtain.

The Connect to Server dialog appears.

The SQL Server Management Studio opens.

The Databases folder opens, exposing the detected Analysis Services database(s), as depicted in Illustration 3.

NOTE: The Analysis Services databases that appear will depend upon the activities that have taken place in your own environment, and will likely differ from those shown in Illustration 3 above. For purposes of this practice session, the Adventure Works DW database must be present. If this is not the case, consult the Books Online for the installation / connection procedures, and complete these procedures before continuing.

The Database expands, exposing the folders for the various objects housed within an Analysis Services database, as shown in Illustration 4.

The Cubes folder opens. We see two cubes, the first of which, Adventure Works, is the sample cube with which we will be conducting our practice exercises. The cubes appear as depicted in Illustration 5.

The Metadata pane for the Adventure Works cube appears, along with the Query pane to its right, as depicted in Illustration 7.

We will be using the Query pane in the practice session that follows, to construct and execute our MDX queries.

As we discover in articles throughout my Introduction to MSSQL Server Analysis Services series, among others, the SQL Server Management Studio serves us in providing a point of interface with all server types in the SQL Server family, including Analysis Services, Reporting Services and Integration Services servers, as well as supporting many additional functions. Among those functions, I find the capabilities to easily browse data, and to issue queries, highly convenient. We can accomplish querying in several other ways within the integrated Microsoft BI solution, but this is certainly one of the most direct. For more information on our use of the query editor within SQL Server Management Studio for issuing MDX queries within the practice exercises of the MDX Essentials series, see Set Functions: The DRILLDOWNMEMBER() Function. (Articles within my other series explore other capabilities and features of the SQL Server Management Studio, as well as the SQL Server Business Intelligence Studio).

Procedure: Satisfy Business Requirements with MDX

Let's assume, for purposes of our practice example, that we have received a request, once again, from representatives of our client, the Adventure Works organization. As we have noted in other articles of the series, the Reporting department, a group of client-facing authors and developers, often requests assistance such as this. As a part of our relationship with Adventure Works, as well as with other clients, we provide on-site augmentation for business requirements gathering and training, performing workshops, in many cases, that illustrate approaches to meeting specific needs. These combined development workshops / "train the trainer" events have worked well in the past for all concerned.

As usual, the authors and developers in the group are aware that the particular need that they are currently expressing will manifest itself in recurring situations as they work to meet the daily requirements of the Adventure Works information consumers. This particular request for assistance involves a drillup scenario.

We have previously assisted the Reporting department representatives in the performance of drilldowns (see Set Functions: The DRILLDOWNMEMBER() Function and Set Functions: The DRILLDOWNMEMBERTOP() and DRILLDOWNMEMBERBOTTOM() Functions), as well as with drillups (MDX Set Functions: The DRILLUPMEMBER() Function) within the context of members. Moreover, we have supported them specifically with using MDX to support level drilldown capabilities (MDX Set Functions: DrillDownLevel() and Set Functions: The DrillDownLevelTop() and DrillDownLevelBottom() Functions). Just as was the case in these visits, when the client Reporting group wished to base the drilldowns upon levels, instead of members, within their cube structure, they wish to extend their recent excursions into drillup capabilities to dimensional levels in our current session.

The Reporting department representatives explain that they want to perform level drillups, using MDX functions as the mechanism, for the same reason that they wanted to perform level drilldowns with MDX: they wish to leverage the Analysis Services and Reporting Services components of the Microsoft integrated Business Intelligence solution, whereby they can create a targeted "drill up" report that will be triggered from a primary, lower level report, and then "connect" the two in a manner similar to that to which we have alluded in other articles. They might also parameterize the dataset(s) within an OLAP report to "swap" DrillDownLevel() and DrillUpLevel() functions to achieve drilled up or drilled down level perspectives within the report(s) resting upon those datasets, or perhaps take another approach entirely to deliver drilling capability within their reports. As we have stated in previous articles surrounding the MDX "drilling functions," whichever approach the developers take to support a given reporting or analysis need, they will likely work with combinations of drilldown and drillup functions to achieve versatility in the end presentations.

We convince the authors that they will initially want to perform drillup within the context of drilldown capabilities such as those that we established in MDX Set Functions: DrillDownLevel(). A good way to do this will be to construct an example of a scenario where we use DrillDownLevel() to perform drilldown, and then introduce the DrillUpLevel() function within the scenario to reverse the effects of DrillDownMember(), and then to perform drillup upon the same data. (We also note that this approach will leave the developers with a complementary "pair" of examples that can be used to illustrate the use of the DrillDownLevel() and DrillUpLevel() functions in tandem).

In the case at hand, the authors ask that we begin with a query that returns Calendar Year 2003 Reseller Sales information for all Product Lines, presenting the summarized total Product sales for the organization, together with the sales values for each of the individually broken out Product Lines that make up the 2003 total, via use of the DrillDownLevel() function. Next, the authors wish to see a drilled up view of the same data, based upon the application of the DrillUpLevel() function to the dataset presented in the drilled down view.

We work with the Reporting department representatives to prepare a quick draft of the requirements, to corroborate the business need. The result of our joint efforts is a drilled down and a drilled up view of the sample data, laid out in a small spreadsheet, shown in Illustration 8.

This represents a simple need that we can readily answer using the DrillDownLevel() and DrillUpLevel() functions in conjunction. We will apply the former to the specified data to bring about the drilldown of the desired Product Model Line, and then apply the DrillUpLevel() function to return the same data to its rolled up state, as we see in the right half of the image above. We are confident that, by thus creating a drilled down scenario as an initial step, we can more effectively demonstrate the simplest workings of the DrillUpLevel() function within a meaningful context.