Preparation: Access SQL Server Management Studio
To reinforce our understanding of the basics we have covered
so far, we will use the DrillUpLevel() function in a couple of ways that
illustrate its operation. We will do so in simple scenarios that place DrillUpLevel()
within the context of meeting business requirements similar to those we might
encounter in our respective daily environments. The intent, as in all the
practice sessions of this series, is to demonstrate the operation of the DrillUpLevel()
function in a straightforward, memorable manner.
We will turn to the SQL Server Management Studio as a
platform from which to construct and execute the MDX we examine, and to view
the results datasets we obtain.
Click the Start
Select Microsoft SQL Server
2005 within the Program group of the menu.
Click SQL Server Management Studio,
as shown in Illustration 2.
Illustration 2: Opening SQL Server Management Studio
The Connect to Server dialog
Services in the Server type selector.
Type / select the server name
(server name / instance, if appropriate) in the Server name selector.
information, as required in your own environment.
Click the Connect button
to connect with the specified Analysis Services server.
The SQL Server Management Studio opens.
In the Object Explorer
pane (it appears by default on the left side of the Studio), expand the Databases
folder (click the "+" sign to its immediate left), appearing
underneath the Analysis Server with which we are working.
The Databases folder opens,
exposing the detected Analysis Services database(s), as depicted in Illustration
Illustration 3: Exposing the Analysis Services Databases
in the Object Browser ...
NOTE: The Analysis Services databases
that appear will depend upon the activities that have taken place in your own
environment, and will likely differ from those shown in Illustration 3
above. For purposes of this practice session, the Adventure Works DW
database must be present. If this is not the case, consult the Books
Online for the installation / connection procedures, and complete these
procedures before continuing.
Expand the Adventure Works DW database.
The Database expands, exposing the folders
for the various objects housed within an Analysis Services database,
as shown in Illustration 4.
Illustration 4: Exposing the Object Folders in the
Expand the Cubes
folder within the Adventure Works DW database.
The Cubes folder opens. We see two
cubes, the first of which, Adventure
Works, is the sample cube with
which we will be conducting our practice exercises. The cubes appear as depicted in Illustration 5.
Illustration 5: The Cubes Appear ...
Click the Adventure Works cube to select it.
Click the New Query
button just under the main menu, in the upper left corner of the Management
Studio, as shown in Illustration 6.
Illustration 6: Click the New Query Button with the
Adventure Works Cube Selected
The Metadata pane for the Adventure Works cube
appears, along with the Query pane to its right, as depicted in Illustration
Illustration 7: Adventure Works Cube Metadata Appears ...
We will be using the Query pane in the practice
session that follows, to construct and execute our MDX queries.
As we discover in articles throughout my Introduction to MSSQL
Server Analysis Services series,
among others, the SQL Server Management Studio serves us in providing a point
of interface with all server types in the SQL Server family, including Analysis
Services, Reporting Services and Integration Services servers,
as well as supporting many additional functions. Among those functions, I find
the capabilities to easily browse data, and to issue queries, highly
convenient. We can accomplish querying in several other ways within the
integrated Microsoft BI solution, but this is certainly one of the most
direct. For more information on our use of the query editor within SQL
Server Management Studio for issuing MDX queries within the practice
exercises of the MDX Essentials series, see Set
Functions: The DRILLDOWNMEMBER() Function. (Articles within my other series explore other
capabilities and features of the SQL Server Management Studio, as well
as the SQL Server Business Intelligence Studio).
Procedure: Satisfy Business Requirements with MDX
Let's assume, for purposes of our practice example, that we
have received a request, once again, from representatives of our client, the Adventure
Works organization. As we have noted in other articles of the series, the
Reporting department, a group of client-facing authors and developers, often
requests assistance such as this. As a part
of our relationship with Adventure Works, as well as with other clients,
we provide on-site augmentation for business requirements gathering and
training, performing workshops, in many cases, that illustrate approaches to
meeting specific needs. These combined development workshops / "train the
trainer" events have worked well in the past for all concerned.
As usual, the authors and developers in the group are aware
that the particular need that they are currently expressing will manifest
itself in recurring situations as they work to meet the daily requirements of
the Adventure Works information consumers. This particular request for
assistance involves a drillup scenario.
We have previously assisted the Reporting department
representatives in the performance of drilldowns (see Set
Functions: The DRILLDOWNMEMBER() Function and Set
Functions: The DRILLDOWNMEMBERTOP() and DRILLDOWNMEMBERBOTTOM() Functions), as well as with drillups (MDX
Set Functions: The DRILLUPMEMBER() Function) within the
context of members. Moreover, we have supported them specifically with
using MDX to support level
Set Functions: DrillDownLevel() and Set
Functions: The DrillDownLevelTop() and DrillDownLevelBottom() Functions). Just as was the case in these visits, when the
client Reporting group wished to base the drilldowns upon levels,
instead of members, within their cube structure, they wish to extend
their recent excursions into drillup capabilities to dimensional levels
in our current session.
The Reporting department representatives explain that they
want to perform level drillups, using MDX functions as the mechanism,
for the same reason that they wanted to perform level 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 to which we have alluded in other
articles. They might also parameterize the dataset(s) within an OLAP report to
"swap" DrillDownLevel() and DrillUpLevel() functions
to achieve drilled up or drilled down level perspectives within
the report(s) resting upon those datasets, or perhaps take another approach
entirely to deliver drilling capability within their reports. As we have
stated in previous articles surrounding the MDX "drilling functions,"
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 will initially want to
perform drillup within the context of drilldown capabilities such
as those that we established in MDX
Set Functions: DrillDownLevel(). A good way to do
this will be to construct an example of a scenario where we use DrillDownLevel()
to perform drilldown, and then introduce the DrillUpLevel()
function within the scenario to reverse the effects of DrillDownMember(),
and then to perform drillup upon the same data. (We also note that this
approach will leave the developers with a complementary "pair" of
examples that can be used to illustrate the use of the DrillDownLevel()
and DrillUpLevel() functions in tandem).
In the case at hand, the authors ask that we begin with a
query that returns Calendar Year 2003 Reseller Sales information for all
Product Lines, presenting the summarized total Product sales for
the organization, together with the sales values for each of the individually
broken out Product Lines that make up the 2003 total, via use of
the DrillDownLevel() function. Next, the authors wish to see a drilled
up view of the same data, based upon the application of the DrillUpLevel()
function to the dataset presented in the drilled down view.
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: "Confirmation Draft" of the
Proposed Dataset Objectives
This represents a simple need that we can readily answer
using the DrillDownLevel() and DrillUpLevel() functions in
conjunction. We will apply the former to the specified data to bring about the
drilldown of the desired Product Model Line, and then
apply the DrillUpLevel() 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 simplest workings of the DrillUpLevel()
function within a meaningful context.