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
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.
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:
DRILLUPLEVEL
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"
SELECT
CROSSJOIN({[Date].[Calendar Year].Members},{[Measures].
[Internet Sales Amount]}) ON COLUMNS,
NON EMPTY(
{[Customer].[Customer Geography].[Country].[United States],
DESCENDANTS(
{[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
FROM
[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()
» 
Comment and Contribute
