Practice
Preparation: Access SQL Server Management Studio
To reinforce our understanding of the basics we have
covered, we will use the StripCalculatedMembers() function within queries
that illustrate its operation. The intention, of course, is to demonstrate the
use of StripCalculatedMembers() 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. If you do not know how to access the SQL
Server Management Studio in preparation for using it to query an Analysis
Services cube (we will be using the sample Adventure Works cube in
the Adventure Works DW Analysis Services database), please
perform the steps of the following procedure, located in the References
section of my articles index:
This procedure will take us through opening a new Query
pane, upon which we can create our first query within the section that follows.
Procedure: Satisfy Business Requirements with MDX
As a basis for our practice example, we will assume that we
have received a request for assistance 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 with designing queries to support organizational analysis
and reporting efforts. As a part of our relationship with Adventure Works,
as well as with other clients, we provide on-site staff augmentation for
business requirements gathering and training, as well as for combined
development workshops and “train the trainer” events.
In a brief discussion with members of the Reporting
department, we learn that a need has arisen to craft MDX queries for some new
analysis and reporting requirements. First, several requirements have been
identified to generate datasets, from the Adventure Works cube, to
support OLAP reports that management has requested. The client has implemented
the integrated Microsoft BI solution, and, in addition to using Analysis
Services as an OLAP data source, they use Reporting Services as an
enterprise reporting solution. The MDX we explore together, we are told, will
thus be adapted and extended for ultimate use within Reporting Services,
in multiple parameterized reports.
The requests relayed by the client representatives evidence
a need to present multidimensional data in a manner that we think might best be
served with the StripCalculatedMembers() function. Once our colleagues
provide an overview of the business requirements, and we conclude that StripCalculatedMembers()
is likely to be a key component of the option we offer, we provide the
details about the function and its use, much as we have done in the earlier
sections of this article. We convince the authors that they might best become
familiar with the StripCalculatedMembers() function by examining an
introductory example, where we employ the function to generate a straightforward
group of the members, excluding calculated members, that are contained
within the scope of a specified set expression, based upon an example we
provided in an earlier session where we demonstrated a means of generating a
simple set of base and calculated members in results dataset.
» 
Comment and Contribute
