Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Apr 3, 2006

Set Functions: The DrillUpLevel() Function - Page 4

By William Pearson

Preparation: Create a Drilled Down Position from Which to Drill Up

Let's construct a simple query to provide the "starting point" for our subsequent work with the DrillUpLevel() function. Our intent here is simply to return the requested Calendar Year 2003 Reseller Sales information for all Product Model Lines, presenting the summarized sales of all Lines, together with the sales values for each of the individually broken out Lines of Products that Adventure Works sells.

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

-- MDX042-001 Initial DrillDown With 
 Selected Level
   {[Measures].[Reseller Sales Amount]} 
   [Product].[Product Model Lines]}))ON ROWS
    [Adventure Works]
    ([Date].[Calendar Year].[CY 2003])

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

Click for larger image

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

The above query sets the stage for a drillup action, while also (as we have noted in sister articles where we discuss drilldown functions) presenting us with opportunities for extending the drilldown capability that it imparts even further within reporting, and other, consumer-facing applications. Examples of such opportunities lie within the parameterization of various components of the axis definitions, as well as with the slicer. An obvious example might be to reference a parameter in place of the keyword DRILLDOWNLEVEL in 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 DrillDownLevel() Function

In the returned dataset, we see that All Products is presented in "rolled up" state. The rows underneath All Products are composed of its children, the various Product Model Lines offered by the Adventure Works organization. (And we can verify easily that the values of the child rows in the dataset add to the All Products total of $ 32,202,669.43.)

3.  Select File --> Save MDXQuery1.mdx As ..., name the file MDX042-001, and place it in a meaningful location.

4.  Leave the query open for the next step.

Our developer colleagues have expressed satisfaction with the contextual backdrop we have established for introducing the DrillUpLevel() function. We will undertake using the function in our next steps, in the procedural section that follows, first with the foregoing example, and then within a "fresh" query we will construct.

Procedure: Drill Up Using the DrillUpLevel() 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 DrillDownLevel(), we will next set out to show the drillup process using the same data structures, to establish a frame of reference. We will simply substitute the DrillUpLevel() function in place of the DrillDownLevel() function within the same query, to meet the desired ends.

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

-- MDX042-002 Drillup With Selected Level

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


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

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

7.  Select File --> Save MDX042-001.mdx As ..., name the file MDX042-002, and place it in the same location as its predecessor, 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 DrillUpLevel() Function

And so we see that DrillUpMember() has the opposite effect within the query, as DrillDownMember(). As we might expect, the only real difference between the two functions lies solely in "direction:" "up" or "down." In the returned dataset, we see that All Products (consisting of the Accessory, Components, Mountain, Road and Touring Product Model Lines) are presented in "rolled up" state. The action we have witnessed has been the drillup of the Product Line members of the All Products dimensional level. The rows that appeared underneath All Products in our initial, drilled down dataset, which represented the child members of the All Products level of the dimension (and were, in this case, at the lowest hierarchical level in the set) have been removed from the dataset. The DrillUpLevel() function has assumed the "next level above the members level" to be the Level Expression, which drives the level to which it drills up. The Product Model Lines values have thus been "absorbed" into a single summary line (All Products), for presentation purposes.

While opportunities to parameterize the Set Expression portion of a query like this one might be obvious, one of the reasons that I chose to introduce the DrillUpLevel() function within the query context of an immediately preceding DrillDownMember() is to illustrate another option for parameterization: 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 (or even the "UP" or "DOWN" portion of 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 DrillUpLevel() and DrillDownMember() functions to their parameterization in this way, or to the further approaches to 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 of my 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 express satisfaction with the results, and confirm their understanding in the operation of the DrillUpLevel() function. They present a similar request at this point for assistance in crafting another drillup scenario involving levels. This example will further activate what we have discussed and seen thus far, employing DrillUpLevel() in a simple scenario, somewhat like our first example, initially, and then further 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 drillup scenario as follows: using the Adventure Works cube as a data source, they wish to begin, as before, with a "drilled down" scenario (albeit through a function that lies outside the specific "drilling" function family): we will start with an existing query that returns information surrounding the composition of internet-generated sales from the perspective of select customer locations. Specifically, the current query generates a dataset that presents comparative Internet Sales for all Calendar Years contained in the cube, for customers in three States in the Southeast United States (for which sales are being analyzed), Alabama, Florida, and Georgia. The current query produces a dataset that summarizes Internet Sales, where they exist, at all hierarchical levels for the three States under examination. The report authors tell us that this specific dataset was requested by the information consumers because they want to monitor the gradually growing Adventure Works sales via the Internet in the Southeast, an area within which increased marketing efforts have been applied.

The drilldown action within the current query results in a dataset that presents the total sales for the extreme Southeast United States, drilled down to the sales totals for the States which make up the total. In addition, the States are drilled down to the Cities whose sales compose each State's total, with the Cities drilling down, in like fashion, to the constituent Postal Codes involved. Finally, the names of the Customers that comprise the sales for each Postal Code summary are also exposed, as the bottom level of the drilldown. The report authors / developers have a new appreciation of the fact that, given the correctly constructed core query, the capability to perform ad hoc drilldowns or drillups on the given levels at runtime will become a matter of parameterizing the key component of the ROWS axis specification (or other components) within the MDX query involved. They understand that one of the results will be 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. Their objective is to be able to ultimately provide a report that the information consumers can use to perform ad hoc queries at any level, once we provide a working example of how to accomplish drillups from the all-inclusive hierarchical presentation that they currently have. Moreover, this will give us a hands-on, "before and after" look at how DrillUpLevel() behaves with and without a specified Level Expression.

To outline the requirement further, our "confirmation of understanding draft" of the initial, fully drilled down dataset appears in MS Excel as depicted in Illustration 14.

Illustration 14: "Confirmation Draft" of the Fully Drilled Down Dataset

The two drilled up views we intend to produce as examples appear as shown in Illustration 15.

Illustration 15: "Confirmation Draft" of the Two Proposed Drilled Up Views Examples

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

10.  Select File --> New from the main menu.

11.  Select Query with Current Connection from the cascading menu that appears next, as depicted in Illustration 16.

Illustration 16: Create a New Query with the Current Connection ...

A new tab, with a connection to the Adventure Works cube (we can see it listed in the selector of the Metadata pane) appears in the Query pane.

--- MDX042-003 Initial Query with Full "Drilldown"
   CROSSJOIN({[Date].[Calendar Year].Members},{[Measures].
       [Internet Sales Amount]}) ON COLUMNS,
       {[Customer].[Customer Geography].[Country].[United States],
              {[Customer].[Customer Geography].[State-Province].[Alabama], 
                  [Customer].[Customer Geography].[State-Province].[Florida],
              [Customer].[Customer Geography].[State-Province].[Georgia]}, 
          [Customer].[Customer Geography].[City], SELF_BEFORE_AFTER)})
     ON ROWS
              [Adventure Works]

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

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

The above query again sets the stage for a drillup action, while also presenting us with opportunities to see the combination of MDX level drilling functions and the Descendants() function. Descendants() can be leveraged to achieve many commonly desirable presentation effects, and, along with other MDX functions, is very useful to us in drilling up and down. In the present case, Descendants() will afford us a results dataset that accomplishes the same effect as a drilldown function.

13.  Execute the query by clicking the Execute button in the toolbar.

The Results pane is, once again, populated by Analysis Services. This time, the dataset depicted in Illustration 18 appears.

Illustration 18: Results Dataset – Initial Drilled Down View – Using DESCENDANTS()

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM