In the returned dataset, we see that, for the selected
Southeast States, total Internet Sales appears drilled down to,
and summarized for, the Province-States, the Cities, the Postal
Codes, and the individual Customers whose purchases made up the Internet
Sales totals for each of the "rolled up" levels. At this stage,
we have a completely "exploded" view, to which we can apply the DrillUpLevel()
function.
14.
Select File
--> Save MDXQuery2.mdx As ..., name the file MDX042-003,
and place it in the same location as we used to store the previous queries.
15.
Leave the
query open for the next step.
Our
developer colleagues agree that the drilled-down scenario we have established
for further examining the DrillUpLevel() function should be effective in
working with a couple of practice examples. We will undertake using DrillUpLevel()
once again, in the steps that follow. First, we will put the function to work
without specifying the optional Level Expression, and then we will work
through another example where we add the Level Expression, so as to
contrast the difference in the results datasets we obtain.
16.
Replace the
comment line in query MDX042-003 with the following:
-- MDX042-004 DRILLUPLEVEL() in Action - Before Optional Level Expression
17.
Select File
--> Save MDX042-003 As..., name the
file MDX042-004, and save it with the queries created earlier.
18.
Click to the
immediate right of NON EMPTY( - on the fifth row from the top in the
existing query - to place the cursor there.
19.
Press the Enter
key on the PC twice, to create a space between the row and the row
underneath it.
The
cursor appears on the new row (which will become the sixth syntax row from the
top of the Query pane).
20.
Type the
following on the new row:
DRILLUPLEVEL(
21.
On what is now
the twelfth row, to the immediate right of the following:
[Customer].[Customer Geography].[City],
SELF_BEFORE_AFTER)})
Add a closed / "right"
parenthesis, ")"
This
encloses the ROWS specification of our query within the DrillUpLevel()
function. The Query
pane appears, with our input, as shown in Illustration 19.
Illustration 19: Our Modified Query in the Query Pane
(Alterations Circled) ...
22.
Execute the
query by clicking the Execute button in the toolbar, as before.
Analysis
Services populates
the Results pane, as before, and the dataset depicted in Illustration
20 appears.
Illustration 20: Results Dataset –DrillUpLevel() Function
without Optional Level Expression
We can see that the effect of the DrillUpLevel() function
has been to remove the members in the set that exist below the default
level. The default level is one level in the dimensional hierarchy above
the level of the lowest-level members. This means, of course, that the
individual Customers, which exist at the lowest level per the Descendants()
function, are removed, and the next level up, Postal Code, becomes the
lowest level in the returned dataset. The effect, then, is drilling up from
the Customer level to the Postal Code level.
23.
Select File
--> Save MDX042-004 to save the query in its specified
location.
24.
Leave the
query open for the next step.
We will perform a final exercise, this time leveraging the
optional Level Expression, to confirm our understanding of how it works.
25.
Replace the
comment line in query MDX042-004 with the following:
--- MDX042-005 DRILLUPLEVEL() in Action - With Optional Level Expression
26.
Select File
--> Save MDX042-004 As..., name the
file MDX042-005, and save it with the queries created earlier.
27.
Type a comma (",")
to the immediate right of the right brace ( "}" ) that
encloses the ROWS axis of our query (the twelfth row from the top of the
query).
The
comma should be added at the point shown in Illustration 21.
Illustration 21: Adding a Comma after the Right Brace ( "}"
)
28.
After adding
the comma, press the Enter key on the PC two times, to create ample space
between the row to which we added the comma and the two rightmost, right
parentheses.
29.
Type the
following to the immediate left of the two right parentheses:
[Customer].[Customer Geography].[State-Province]
This insertion
represents a Level Expression, the State-Province level of the Customer
dimension, Customer Geography hierarchy. Placing it here, within the
surrounding DrillUpLevel() function, we are telling the function to "remove
all set members that reside in levels lower than the State-Province
level within the Customer Geography hierarchy" – in effect to drill
up to the State-Province level. The Query pane appears, with our input, as depicted
in Illustration 22.
Illustration 22: The Modified Query in the Query Pane
(Alterations Circled) ...
30.
Execute the
query by clicking the Execute button in the toolbar, as before.
The
Results pane is once again populated, as the dataset shown in Illustration
23 appears.
Illustration 23: Results Dataset –DrillUpLevel() Function
with Optional Level Expression
We now see that the effect of the DrillUpLevel() function
has been to drill up to the State-Province level, as directed by our
insertion of the appropriate syntax into the optional Level Expression
of the DrillUpLevel() function. We note that all members below the State-Province
level disappear, leaving us with the summarized States and Country levels
that remain within our dataset. It thus becomes apparent, once again, that we
can achieve many desirable presentation effects through the use of an MDX
drilling function, in combination with other MDX functions. As we noted to be
the case with the Set Expression in our first exercise, and within other
MDX functions that we have treated in our recent "drilling functions"
subset of articles, 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 DrillUpLevel() function under
consideration.
31.
Select File
--> Save MDX042-005 to save the query in the specified
location.
32.
Select File
--> Exit to leave the SQL Server Management Studio, when ready.
The
client representatives inform us that their immediate goals have been met. We
agree to return at a later time to demonstrate approaches to implement the DrillUpLevel()
function, in conjunction with the DrillDownLevel() function, within Reporting
Services, where, as we have noted, the functions lend themselves to easy
and flexible parameterization. The group states that it is anxious to leverage
MDX in this new way to support interactive drillup and drilldown
by organizational information consumers.
Summary ...
In
this article, we continued our extended examination of the MDX surrounding drilling
up and down within our Analysis Services cubes, focusing this time
upon the DrillUpLevel() function. We noted that, like many MDX functions, DrillUpLevel() 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, touching upon examples of effects that we can employ
it to deliver to empower information consumers to maneuver between detailed and
summarized levels of data.
In
introducing DrillUpLevel(), we commented upon its operation and
discussed the "collapsed" datasets we can deliver using a specified Set
Expression, together with an optional Level Expression, within the
function. Next, we examined the syntax involved with DrillUpLevel(), and
then undertook a couple of illustrative practice examples within which we met
hypothetical business requirements with the function. We generated a query
that capitalized on its primary features within the context of a drilled
down scenario, in order to witness the function in action.
We
then undertook an example where we combined DrillUpLevel() with the Descendants()
function, to demonstrate how the "drilled down" effect of Descendants()
might serve as a "starting point" for a query to which we might
apply "drill up" logic to meet subsequently determined needs for
summarization at different levels. Moreover, we discussed points within our
query where we might consider the insertion of parameterization in a reporting
application (such as Reporting Services), or in other consumer
applications, to leverage the function to support drillup in an ad hoc
manner – and especially in conjunction with drilldown operations.
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.
» 
Comment and Contribute
