Introduction to MSSQL Server Analysis Services: Named Sets Revisited

About the Series …

This
article is a member of the series Introduction to MSSQL Server Analysis
Services. The series is designed to provide hands-on application of
the fundamentals of MS SQL Server Analysis Services, with each installment
progressively presenting features and techniques designed to meet specific real
– world needs. For more information on the series, please see my initial
article, Creating Our First
Cube.

Note: This article examines exciting new features of MSSQL
Server 2005. To
follow along with the steps we undertake, the following components, samples and
tools are recommended, and should be installed according to the respective
documentation that accompanies MSSQL Server 2005:

• Microsoft SQL
  Server 2005 Database Engine

• Microsoft SQL
  Server 2005 Analysis Services

• Business
  Intelligence Development Studio

• Microsoft SQL
  Server 2005 sample databases

• The Analysis Services
  Tutorial sample project and other samples, that are available with the
  installation of the above.

  To
  successfully replicate the steps of the article, you also need to have:

• Membership
  within one of the following:
  • the Administrators
    local group on the Analysis Services computer

  • the Server
    role in the instance of Analysis Services.

• Read permissions within any SQL
  Server 2005 sample databases we access within our practice session, if
  appropriate.

  Note: Current Service Pack updates are assumed for the operating system, MSSQL
  Server 2005 ("MSSQL Server"), MSSQL Server 2005 Analysis
  Services ("Analysis Services"), MSSQL Server 2005 Reporting
  Services ("Reporting Services") and the related Books
  Online and Samples. Images are from a Windows 2003
  Server environment, but the steps performed in the articles, together with
  the views that result, will be quite similar within any environment that
  supports MSSQL Server 2005 and its component applications.

Introduction

In this lesson, we revisit Named Sets, a subject that
we undertook in my article
MDX in
Analysis Services: Named Sets in MDX: An Introduction, in March of 2004. There, we introduced Named Sets
from the perspective of the MDX query language, having obtained brief
exposure to the concept of Named Sets earlier in the MDX in Analysis Services series (Using Sets in MDX Queries). We
examined Named Sets as they existed within Analysis Services 2000,
touching upon them from the perspective of Analysis Manager, the Cube
Editor, and related interfaces in Analysis Services.

Named
Sets have changed
somewhat in Analysis Services 2005, but their definition has remained
the same: Named Sets are (MDX)
expressions that return sets, unsurprisingly, which are saved as part of
the definition of a cube in Analysis Services. As we have mentioned before,
Named Sets are created with reuse in mind. Named Sets consist of
one or more tuples. They can be made up of more than one dimension, because a
tuple can consist of one or more dimension members; only one member from each
dimension can be represented per tuple, however, as we have discussed in sessions over the last couple
of years.

In
this article, we will be focusing upon Named Sets that we create within the
MSSQL Server Business Intelligence Development Studio. First, we will
introduce the concepts behind Named Sets, and then we will undertake
illustrative practice examples, based upon a hypothetical business need, to
illustrate the value that Named Sets can offer us. Within the context of
our practice exercise, we will discuss the results we obtain, to reinforce the
subject matter in a way that activates the concepts involved, as well as to
perhaps suggest expanded uses in our own business environments.

Named Sets in Analysis Services

Named
Sets enable the
enterprise to simplify queries, and to provide more useful aliases for complex,
commonly used set expressions and definitions. They are certainly among the
best friends of report developers / authors – indeed, enterprise OLAP reporting
tools, such as Crystal Analysis and others rely heavily upon Named
Sets in their translation of the actions of report authors, within their
graphical interfaces, to the MDX ultimately created to retrieve the desired
data from Analysis Services cubes.

An
illustrative Named Set might be called Top 25 Customers, which
might contain the twenty-five customers, from, say, the Adventure Works cube,
with the highest values for the Sales Amount measure. Once the Named
Set is constructed, we can perform analysis and generate reports upon the Top
25 Customers by simply referencing the Named Set, eliminating the
requirement to provide the qualified names of all twenty-five customers in each
underlying query we assemble to return data about this customer group.

In
this article, we will:

• Discuss
  the concept of Named Sets, and how we can leverage them in Analysis
  Services for analysis and reporting purposes;

• Create a clone
  Analysis Services Database in SQL Server Management Studio, within which to conduct
  our practice exercises;

• Access the Analysis
  Services Database in Business Intelligence Development Studio;

• Create a Named
  Set within an Analysis Services 2005 cube;

• Verify
  operation of our new Named Set by performing a browse of the cube.