Set Functions: The StripCalculatedMembers() Function - Page 2
April 7, 2008
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.