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.
Illustration 8: "Confirmation Draft" of the
Proposed Dataset Objectives
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.
1.
Type (or cut and paste) the
following query into the Query pane:
-- 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 Query pane
appears, with our input, as depicted in Illustration 9.
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. 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.)
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 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.)
3.
Select File
--> Save MDXQuery1.mdx As ..., name the file MDX039-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 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.