MDX Set Functions: DrillDownLevel()

Monday Feb 6th 2006 by William Pearson
Share:

Bill Pearson continues to expand his examination of "drilling mechanics" in MDX. In This article, we explore the DrillDownLevel() function.

About the Series ...

This article is a member of the series, MDX Essentials. The series is designed to provide hands-on application of the fundamentals of the Multidimensional Expressions (MDX) language, with each tutorial progressively adding features designed to meet specific real-world needs. For more information about the series in general, please see my first article, MDX at First Glance: Introduction to MDX Essentials.

Note: To follow along with the steps we undertake within this and prospective articles of the series, the following components, samples and tools are recommended, and should be installed according to the respective documentation that accompanies MSSQL Server 2005:

  • Microsoft SQL Server 2005 Database Engine (for the accompanying SQL Server Management Studio);

  • Microsoft SQL Server 2005 Analysis Services;

  • Business Intelligence Development Studio;

  • The Analysis Services AdventureWorks DW sample database (fully deployed to Analysis Services from the AdventureWorks DW Analysis Services project that accompanies Analysis Services 2005).

The MSSQL Server 2005 and Analysis Services 2005 samples to which I refer above are available within a Microsoft Windows Installer package that accompanies the respective applications on the installation CDs, or which can be downloaded from Microsoft and perhaps elsewhere. The samples are not installed by default. If you did not install the samples and the AdventureWorks sample database during SQL Server 2005 Setup, you can install them after Setup finishes. For more information, see the topic "Running Setup to Install AdventureWorks Sample Databases and Samples" in the SQL Server Books Online. Make sure that you read and follow the installation instructions in the Readme files that accompany the samples that you want to install. For a list of samples, see the topic "Samples" in the SQL Server Books Online.

To successfully replicate the steps of the article, you also need to be a member of the appropriate group / role in Analysis Services to access the sample cube for our exercises. Read permissions are assumed for the samples upon which we will base our practice exercises, where we will often be using the AdventureWorks cube, which arrives with Analysis Services 2005 within the AdventureWorks DW Analysis Services project.

Current Service Pack updates are assumed for the operating system, MSSQL Server 2005 ("MSSQL Server"), MSSQL Server 2005 Analysis Services ("Analysis Services"), MSSQL Server 2005 Reporting Services ("Reporting Services") and the related Books Online and Samples. Images are from a Windows 2003 Server environment, but the steps performed in the articles, together with the views that result, will be quite similar within any environment that supports MSSQL Server 2005 and its component applications.

Within MSSQL Server Analysis Services 2000, we had somewhat limited options (namely, the MDX Sample Application) for easily querying our cube data sources with MDX (at least, that is, options that were available "out of the box" to anyone who had simply installed Analysis Services and its samples). MSSQL Server 2005 offers us numerous means for querying our OLAP data sources. In the interest of working within the current environment, in this and the prospective articles of the series, we will construct and execute our MDX queries, in the most part, from the SQL Server Management Studio, but we will occasionally do so from the SQL Server Business Intelligence Studio, and perhaps from other vantage points, to further enrich the learning experience within the subject matter involved. For more in-depth information on any of these various "points of approach" themselves, see the relevant sections of the Books Online.

Virtually all of the MDX we constructed in earlier articles can now be used in the SQL Server Management Studio, SQL Server Business Intelligence Studio, and in various other areas within the Microsoft integrated Business Intelligence solution. In addition, much of what we construct going forward can be executed in the MDX Sample Application (assuming connection to an appropriate Analysis Services 2000 OLAP data source). MDX as a language continues to evolve and expand: we will focus on many new features in articles to come, while still continuing to consider business uses of MDX in general. The use of MDX to meet the real-world needs of our business environments will continue to be my primary focus within the MDX Essentials series.

Overview

In this article, we will continue our examination, begun in Set Functions: The DRILLDOWNMEMBER() Function, of the MDX surrounding drilling up and down within our Analysis Services cubes. As we have noted, drilling up or down occurs along the lines of drilling paths that are defined within the physical structures of our cubes, and comprises an analytical technique by which an information consumer can maneuver between summarized ("drilling up") and detailed ("drilling down") levels of data. While these drilling paths are typically defined by the cube's dimensional hierarchies, they can also be based upon alternative relationships that exist within or between dimensions.

A general example, which I have used within sister articles surrounding the MDX "drilling" functions, might be extended as follows, based upon an engagement within which I have recently been involved. An executive within a state Department of Education wishes to examine annual Graduation Rates within a cube constructed primarily to allow comparison and analysis of those rates at various levels. The executive can examine national rates and numbers of graduates, and then perform a drilldown operation within a Geography dimension that might then present the data by states. He could then perform subsequent drilldowns to display state regions (or perhaps congressional districts), counties / school systems, and more. Depending upon the design of the cube, the drilldown process could ultimately take the information consumer to the level of the individual schools – perhaps even to the individual students themselves (assuming a world without privacy concerns, of course).

In Set Functions: The DRILLDOWNMEMBER() Function, we discussed that, underneath the capability of Analysis Services to meet this common need is the DrillDownMember() function. Another "staple" support function underpinning drill down capabilities is the DrillDownLevel() function, which will form the subject of this article. DrillDownLevel() drills down the members of a set to a lower level, and additionally offers us the flexibility to specify which level below a given member in the set, as we shall see. In its simplest use, based upon a set we specify, DrillDownLevel() returns a set of child members, each of which is included immediately under its respective parent member. DrillDownLevel() also affords us the option to narrow the children we return to only the members of a given level, if that is desirable.

In addition to its default behavior of taking a set specification as an argument, and drilling down to reveal the members of the level underneath (where appropriate), DrillDownLevel() can accept an additional Level argument to allow us to direct drilldown to a specific level, instead of simply drilling to the immediately "next" level, as we shall see. (We can alternatively supply an index to further control the behavior of the DrillDownLevel() function in targeting a specific level).

In a manner similar to other functions that we have examined in the MDX Essentials series, DrillDownLevel() can be useful in a host of different reporting and analysis applications. DrillDownLevel(), along with other "navigational" functions like it (virtually all of which we examine in other articles of this series) allows us to exercise a great deal of presentation sleight of hand, in working with MDX in Analysis Services, as well as within Reporting Services and various other reporting applications that can access an Analysis Services cube.

As we have seen to be the case with other members of the "family" of drill-related functions, as well as with many other MDX functions in general, the DrillDownLevel() function can be leveraged, within and among the various "layers" of the Microsoft integrated Business Intelligence solution, to support sophisticated presentations and features. We will introduce the function, commenting upon its operation and touching upon examples of effects that we can employ it to deliver. As a part of our discussion, we will:

  • Examine the syntax surrounding the function;
  • Undertake illustrative examples of the uses of the function in practice exercises;
  • Briefly discuss the results datasets we obtain in the practice examples.

The DrillDownLevel() Function

Introduction

According to the Books Online, the DrillDownLevel() function "drills down the members of a set to one level below the lowest level represented in the set, or to one level below an optionally specified level of a member represented in the set."

The set of child members returned by DrillDownLevel() is ordered by hierarchy, and includes those members specified in the specified Set Expression. The order among the original members in the Set Expression is maintained, with all child members returned by the function included immediately under their parent member, much as we saw to be the case with the DrillDownMember() function in Set Functions: The DRILLDOWNMEMBER() Function.

DrillDownLevel() has many applications, and pairing it with other MDX functions can help us to leverage its power even further. I frequently use it in combination with various functions, including the CrossJoin() and Descendants() functions, among others. We will examine in detail the syntax for the DrillDownLevel() function after our customary overview in the Discussion section that follows. Following that, we will conduct practice examples within a couple of scenarios, constructed to support hypothetical business needs that illustrate uses for the function. This will afford us an opportunity to explore some of the delivery options that DrillDownLevel() can offer the knowledgeable user. Hands-on practice with DrillDownLevel(), where we will create queries that leverage the function, will help us to activate what we learn in the Discussion and Syntax sections.

Discussion

To restate our initial explanation of its operation, the DrillDownLevel() function drills down the members (or tuples) in a specified Set Expression to the next lower level (assuming, of course, that one exists) in the structure. The order of the specified set is maintained, except that the children returned by the function are presented after their respective parents. The Set Expression can be of mixed dimensionality.

As we mentioned earlier, a Level argument can optionally be specified. If no Level is specified within the function, the members / tuples residing at the lowest level within the first dimension of the set are drilled down to the next lower level.

When we specify the Level, DrillDownLevel(), in effect, uses all members / tuples within the Set Expression that exist within the specified Level, and drills them down to the next lowest level (again, assuming that a next lowest level exists within the structure). If we specify a Level in the function, and it turns out that no members within the Set Expression exist within the specified Level, then an identical set to that specified within the Set Expression is returned by DrillDownLevel().

DrillDownLevel() also allows us to use a use a numerical Index, instead of specifying a Level in the function. Recall what we said earlier: If no Level is specified within the function, the members / tuples residing at the lowest level within the first dimension of the set are drilled down to the next lower level. When we have a set that consists of tuples of mixed dimensionality, the Index, which is zero-based, allows us to specify the precise hierarchy upon which to drill. Specification of Index 0, for example, would mean to drill down the lowest level members of the tuples residing within the first dimension appearing in the Set Expression, while specifying and Index of 1 or 2 would direct drilldown to occur upon the tuples containing the members of the lowest level of the second or third dimension, respectively.

When we couple it with other functions, we can leverage DrillDownLevel() to deliver a wide range of analysis and reporting utility. As in so many cases with the Microsoft integrated Business Intelligence solution, consisting of MSSQL Server, Analysis Services and Reporting Services, this function, residing within the Analysis Services layer, can be extended to support capabilities and attributes in the Reporting Services layer. Knowing "where to put the intelligence" among the various layers is critical to optimization, in many cases. For more of my observations on this subject, see Multi-Layered Business Intelligence Solutions ... Require Multi-Layered Architects.

Let's look at some syntax illustrations to further clarify the operation of DrillDownLevel().

Syntax

Syntactically, anytime we employ the DrillDownLevel() function to drill down by a level, the effect of the function, as we have seen, is to drill down a specified set to the next lower level. We specify the Set_Expression within the parentheses to the right of the DrillDownLevel keyword. The general syntax is shown in the following string:

DrillDownLevel(Set_Expression [ , {Level_Expression | , Index} ] )

We can specify a Level ("Level_Expression"), as we have discussed, to cause the function to drill to the next lowest level the members / tuples within the Set Expression which exist within the specified Level. (The function returns the set specified in the Set Expression when there are no members within the specified Level – in other words, when there is nothing to which to drill down – as might be expected.) Finally, we can substitute the Index number for the level, as another option for specifying the hierarchy within a Set Expression composed of tuples of mixed dimensions, upon which drilldown is performed by DrilldownLevel(). In our use of the Index option, we simply identify the hierarchy by its numeric position within the tuples, instead of focusing the drilldown via level specification.

Let's take a look at an example. The following snippet employs the DrillDownLevel() function:

NON EMPTY DRILLDOWNLEVEL(
{[Geography].[Geography].[State-Province].[Georgia]}) 
ON AXIS(1)

This row specification, within a proper query executed against the Adventure Works sample cube that, say, stipulated the Reseller Sales Amount measure within the column axis, and Calendar Year 2004 as a slicer, might produce a results dataset similar to that depicted in Illustration 1.


Illustration 1: Results Dataset – Georgia Drilled to (Non-empty) Child Cities

In the example dataset, we see that Georgia, the State-Province specified in our Set Expression, appears alongside its summary Reseller Sales Amount, atop its drilled down non-empty child Cities and their respective values. (We note, too, that the total of the child Cities in the dataset add to the "rolled up" Georgia total of 179,522.90.)

We will practice some uses of the DrillDownLevel() function in the section that follows.

Practice

Preparation: Access SQL Server Management Studio

To reinforce our understanding of the basics we have covered so far, we will use the DrillDownLevel() function in a couple of ways that illustrate its operation. We will do so in simple scenarios that place DrillDownLevel() within the context of meeting business requirements similar to those we might encounter in our respective daily environments. The intent, as always, is to demonstrate the operation of the DrillDownLevel() 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.

1.  Click the Start button.

2.  Select Microsoft SQL Server 2005 within the Program group of the menu.

3.  Click SQL Server Management Studio, as shown in Illustration 2.

Click for larger image

Illustration 2: Opening SQL Server Management Studio

The Connect to Server dialog appears.

4.  Select Analysis Services in the Server type selector.

5.  Type / select the server name (server name / instance, if appropriate) in the Server name selector.

6.  Supply authentication information, as required in your own environment.

7.  Click the Connect button to connect with the specified Analysis Services server.

The SQL Server Management Studio opens.

8.  In the Object Explorer pane (it appears by default on the left side of the Studio), expand the Databases folder (click the "+" sign to its immediate left), appearing underneath the Analysis Server with which we are working.

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


Illustration 3: Exposing the Analysis Services Databases in the Object Browser ...

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.

9.  Expand the Adventure Works DW database.

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


Illustration 4: Exposing the Object Folders in the Database ...

10.  Expand the Cubes folder within the Adventure Works DW database.

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.


Illustration 5: The Cubes Appear ...

11.  Click the Adventure Works cube to select it.

12.  Click the New Query button just under the main menu, in the upper left corner of the Management Studio, as shown in Illustration 6.


Illustration 6: Click the New Query Button with the Adventure Works Cube Selected

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


Illustration 7: Adventure Works Cube Metadata Appears ...

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

In addition to its role of providing an environment for the management of all server types in the SQL Server family, including Analysis Services, Reporting Services and DTS servers, many additional functions can now be performed from the SQL Server Management Studio. 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 Microsoft integrated BI solution, but this is certainly one of the most direct.

We will be using the appropriate query editor in this and many prospective articles for the purpose of issuing MDX queries within the practice exercises of the MDX Essentials series. The query editors within the Management Studio afford us the capability to not only execute MDX queries against our cubes, but handle DMX (queries against our data mining models) and XMLA (covering all command types that can be sent to Analysis Services), as well. The MDX and DMX editors each include a Metadata pane for the convenient display of the metadata for the currently selected data source, a dramatic enhancement over the native tools provided in earlier versions. To some extent, the "drag and drop" capabilities afforded us via the Metadata pane might be useful, but we will typically limit our exercises to "straight MDX" queries, as the focus of this series is the MDX functions, properties, techniques and so forth. (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 the Reporting department 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. We typically 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 need will manifest itself in recurring similar situations as they work to meet the needs of the Adventure Works information consumers. This particular request for assistance involves a drilldown scenario. We have supported the group recently in performing drilldown (see Set Functions: The DRILLDOWNMEMBER() Function and Set Functions: The DRILLDOWNMEMBERTOP() and DRILLDOWNMEMBERBOTTOM() Functions), which, we believe, will be of benefit as we return to assist them with the level drilldown capabilities in which they are now interested.

The Reporting department representatives explain that they want to perform drilldowns, but in the present case, they wish to base the drilldowns upon levels, instead of members, within their cube structure. They wish to use an MDX function as the mechanism, for the same reason that they wanted to perform member 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 down" report that will be triggered from a primary, higher level ("Home") report, and then "connect" the two in a manner similar to that which we have followed in other articles. They might also parameterize the dataset(s) within an OLAP report to "swap" DrillDownLevel() and DrillUpLevel() functions (we explore the latter in another article of the series) to achieve drilled down or drilled up perspectives within the report(s) resting upon those datasets, or perhaps to take another approach entirely to deliver drilling capability within their reports. Suffice it to say that, whichever approach the developers take to support a given reporting or analysis need, in a manner similar to that we have examined from the perspective of members, they will likely work with combinations of drilldown and drillup functions with levels to achieve versatility in the end presentations. While the functions differ in syntactical structure and operation, the uses to which we put the functions can be very similar indeed.

What lies at the heart of the request, we note in our early discussions, is the need to perform drilldown on a hierarchical level. In the case at hand, the authors tell us that they need to provide a query that enables drilldown for the Calendar Year 2003 Reseller Sales of the Adventure Works Product Lines. The immediate need is to return a dataset that presents each of the lines with summary Reseller Sales Amounts at that level.

We work with the Reporting team to prepare a quick draft of the requirement, to corroborate the business need. The result of our joint efforts is the sample data, laid out in a small spreadsheet, shown in Illustration 8.


Illustration 8: "Confirmation Draft" of the Proposed Dataset Objective

This represents a simple, yet practical, need that we can readily answer using the DrillDownLevel() function. This is an excellent starting point for an evolving examination of possible uses for DrillDownLevel(). We are confident that, once we construct and demonstrate the workings of the function, we can count on the authors to both grasp the concepts and to extend their requirements with further questions.

Let's construct a simple query, therefore, to return the requested 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.

1.  Type (or cut and paste) the following query into the Query pane:


-- MDX040-001 Initial DrillDown With Selected Level
SELECT
   {[Measures].[Reseller Sales Amount]} ON COLUMNS,
NON EMPTY(DRILLDOWNLEVEL( {
   [Product].[Product Model Lines]}))ON ROWS
 
FROM 
    [Adventure Works]
WHERE
    ([Date].[Calendar Year].[CY 2003])

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


Illustration 9: Our Initial Query in the Query Pane ...

The above query meets the specific requirement quite well, but also presents us with opportunities for extending the drilldown capability that it imparts even further within reporting, and other, consumer-facing applications. One such opportunity lies within the parameterization of various components of the axis definitions, together with the slicer. An obvious example might be to reference a parameter in the specified Set Expression, {[Product].[Product Model Lines]} of the row axis, through Reporting Services. The parameter might then be passed from a "launch" point in a summary report to a target report containing a query similar to the above within its dataset, generating a drilldown report upon, say, the click of a summary item within the former by an information consumer. (This is but one approach; see my MSSQL Server Reporting Services series for articles that expose practical approaches to this and other methods for accomplishing similar effects.)

2.  Execute the query by clicking the Execute button in the toolbar, as shown in Illustration 10.


Illustration 10: Click Execute to Run the Query...

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


Illustration 11: Results Dataset – Initial Use of DrillDownMember() Function

In the returned dataset, we see that All Products (the top row of the dataset) are presented in "rolled up" state. The rows underneath All Products are composed of its children, the various Product Lines that the organization sells (And we can verify easily that the values of the child rows in the dataset add to the rolled up Touring total of 32,202,669.43.)

3.  Select File ---> Save MDXQuery1.mdx As ..., name the file MDX040-001, and place it in a meaningful location. (Leave MDX040-001.mdx open for the next steps).

Our report author colleagues have expressed satisfaction with the example we have presented. They present a similar request at this point for assistance in crafting another drilldown scenario involving levels. This example will further activate what we have discussed and seen thus far, employing DrillDownLevel() in a simple scenario, somewhat like our first example, initially, and then leveraging the optional Level Expression. This will give us a hands-on, "before and after" look at how DrillDownLevel () behaves with and without a specified Level Expression.

The report authors outline the next drilldown scenario as follows: using the Adventure Works cube, once again, as a data source, they wish to create a query that returns information surrounding the composition of select sales from the perspective of customer locations. Specifically, they wish to present comparative Internet Sales for all Calendar Years contained in the cube, for United States customers, in such a way that the states within which sales took place are summarized. The drilldown requirement in this case consists in the need to present the total sales for the United States, drilled down to the sales totals for the states, which make up the U.S. total. The report authors are convinced that, once they achieve this objective, the capability to perform ad hoc drilldowns on a given state (or a group of states) at runtime will become a matter of parameterizing the key component of the row axis specification within the MDX query involved, a mechanism that can be leveraged, as one example, in a summary / target report pairing (as well as through other avenues) within Reporting Services, such as we have discussed earlier.

To outline the requirement further, our "confirmation of understanding draft," again in MS Excel, is depicted in Illustration 12.


Illustration 12: "Confirmation Draft" of the Proposed Dataset

We obtain consensus on the dataset, and set about constructing the query.

4.  Replace the syntax from our initial query in the Query pane by typing (or cutting and pasting) the following in its place:


--- MDX040-002-1 DrillDownLevel() 
 With Level Expression Option - Before
SELECT
 CROSSJOIN({[Date].
 [Calendar Year].Members},{[Measures].
   [Internet Sales Amount]}) ON COLUMNS,
 NON EMPTY(DRILLDOWNLEVEL( {
   [Customer].[Customer Geography].
   [Country].[United States]}
 )) ON ROWS
FROM 
  [Adventure Works]

The Query pane appears, with our input, as shown in Illustration 13.


Illustration 13: Second Query in the Query Pane ...

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

6.  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 14 appears.


Illustration 14: Results Dataset – Another Basic Use of DrillDownLevel() Function

In the returned dataset, we see that Country United States (the top row of the dataset) is presented in "rolled up" state. The states, within which we have conducted Internet Sales transactions, within the Calendar Years contained within the Adventure Works cube, appear underneath the United States summary line. The states represent the drilled down children of Country United States, whose values add up to the Country United States total. The query has returned the "next level down" from the United States level, or all States with activity. This, as we have discussed, is the default, most straightforward use of DrillDownLevel().

7.  Select File --> Save MDX040-002-1.mdx to ensure that the file is saved. (Leave MDX040-002-1.mdx open for the next steps).

The developers / authors again express satisfaction with the results, agreeing that the current point is a good place from which to demonstrate the use of the optional Level Expression option that we have explained to them in introductory discussions. We agree that a useful next step will be a further extension of the query. As an example of the process involved, the developers / authors wish to see how they might easily make it possible to leverage the Level Expression to direct the function to return members from a specific level underneath the Country United States set ( {[Customer].[Customer Geography].[Country].[United States]} ). As an illustrative example, they wish to direct drill down explicitly to the City level from the current point, and to therefore return the child Cities (with values) for each of the States currently presented, and to display their sales contributions, in turn, to the United States Internet Sales total.

We reiterate that the optional Level Expression exists specifically for this purpose, and take the following steps to demonstrate its operation.

8.  Modify the top line in the query (the commented line) to the following:

--- MDX040-002-2 DrillDownLevel() With Level Expression Option - "After"

9.  Select File --> Save MDX040-002-1 As..., name the file MDX040-002-2, placing it with the previous to query files, to protect the former query.

10.  Place a comma (",") after the existing Set Expression within the Rows axis specification (That is, {[Customer].[Customer Geography].[Country].[United States]} ).

11.  Create a space below [Customer].[Customer Geography].[Country].[United States] using the Enter key just after the newly added comma, pushing down the row containing the ON ROWS keywords in the process.

12.  Insert the following into the space below (between the existing Set Expression and the ON ROWS keywords)

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

The Query pane appears, with the areas affected by our modifications circled, as depicted in Illustration 15.


Illustration 15: Our Second Query, with Modifications

13.  Execute the query by clicking the Execute button in the toolbar, as we did earlier.

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


Illustration 16: Results Dataset (Partial View) – Level Expression at Work ...

Country United States is, indeed, drilled down to child Cities containing sales values, whose individual sales totals make up the summary United States total for each Calendar Year. As we noted to be the case with the Set Expression in the ROWS axis specification in earlier examples, 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 DrillDownLevel() function under consideration.

The report authors state that their immediate goals have been met. We agree to return at a later time to demonstrate approaches to implement the DrillDownLevel() function in Reporting Services to leverage MDX to support interactive drilldown by organizational information consumers.

14.  Select File --> Save MDX040-002-2.mdx to save our work.

15.  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 upon the DrillDownLevel() function. We noted that the DrillDownLevel() function 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 summarized and detailed levels of data.

As a part of our examination of the DrillDownLevel() function, we commented upon its operation and touched upon the "extended" datasets we can deliver using the function with a specified Set Expression and optional Level Expression (as well as discussing its default behavior when no Level Expression is specified). We also discussed the Index option, and how it supports further control over drilldown between levels. Next, we examined the syntax involved with DrillDownLevel(), and then undertook a couple of illustrative practice examples within which we met hypothetical business requirements with the function, generating queries that capitalized on its primary features. Further, we discussed points within our query where we might consider the insertion of parameterization in a reporting application (or other consumer application), such as Reporting Services, to leverage the function to support drilldown in an ad hoc manner. 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.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved