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

Satisfy Business Requirements with MDX

Let's assume, for purposes of our practice example, that we have received a request from a client to provide support in meeting a specific report presentation need. The Reporting department of the Adventure Works organization often requests assistance such as this, which, in the present case, involves a drillup scenario; 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.

We have previously assisted the group in performing drillthrough (see Mastering OLAP Reporting: Drilling Through Using MDX, a recent article in my MSSQL Server Reporting Services series, as well as Drilling Through with MDX: The DRILLTHROUGH Statement, here in the MDX Essentials series). Moreover, 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 drillup capabilities they need.

The Reporting department representatives explain that they want to perform drillups, using MDX functions as the mechanism, for the same reason that they wanted to perform 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 which we have followed in other articles. They might also parameterize the dataset(s) within an OLAP report to "swap" DrillDownMember() and DrillUpMember() functions to achieve drilled up or drilled down perspectives within the report(s) resting upon those datasets, or perhaps 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, they will likely work with combinations of drilldown and drillup functions to achieve versatility in the end presentations.

We convince the authors that they want to perform drillup within the context of drilldown capabilities such as those that we established in The DrillDownMember() Function. A good way to do this will be to construct an example of a scenario where we use DrillDownMember() to perform drilldown, and then introduce the DrillUpMember() function within the scenario to reverse the effects of DrillDownMember(), and perform drillup upon the same data.

In the case at hand, the authors tell us that they will again need to provide a query that enables drilldown for the Calendar Year 2003 Reseller Sales of a specific group of the bicycle models that Adventure Works builds. Out of three Product Model Lines within the Product dimension of the Adventure Works cube, the immediate need is to return a dataset that presents two of the lines, Mountain and Road models, in summary, while drilling down the Touring model to the level of its (non-empty) children (the various types of bicycle components for the Touring model) and presenting the Reseller Sales Amounts at that level. They then wish to introduce the DrillUpMember() function to return the same data presentation to the summary rollup for each of the three Product Model Lines.

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 DrillDownMember() and DrillUpmember() functions in conjunction. We will apply the former to the specified data to bring about the drilldown of the desired Model Line, and then apply the DrillUpMember() 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 workings of the DrillUpMember() function within a meaningful context.

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

Let's construct a simple query, therefore, to return the requested Calendar Year 2003 Reseller Sales information for the three selected Product Model Lines, presenting the summarized sales for each of the Mountain and Road models, together with the sales values for each of the individually broken out component types that make up the Touring model.

-- MDX039-001 Initial DrillDown With Selected Members
SELECT
   {[Measures].[Reseller Sales Amount]} ON COLUMNS,

NON EMPTY(DRILLDOWNMEMBER( {
   [Product].[Product Model Lines].[Product Line].[Mountain], 
        [Product].[Product Model Lines].[Product Line].[Road], 
            [Product].[Product Model Lines].[Product Line].[Touring]}, 
        {[Product].[Product Model Lines].[Product Line].[Touring]}))ON ROWS
 
FROM 
    [Adventure Works]

WHERE
    ([Date].[Calendar Year].[CY 2003])

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. One example of such an 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 last set, {[Product].[Product Model Lines].[Product Line].[Touring]}, 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.)

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

In the returned dataset, we see that Mountain and Road (the top two rows of the dataset) are presented in "rolled up" state, as is Touring, the third row. However, the rows underneath Touring are composed of its children, the various components of the Touring Model Line. (Moreover, we can verify easily that the values of the child rows in the dataset add to the rolled up Touring total of $ 6,464,481.11.)

Our developer colleagues have expressed satisfaction with the example we have presented, as a contextual backdrop for introducing the DrillUpMember() function. We will undertake using the function in our next steps, in the procedural section that follows.