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.

William Pearson
William Pearson
Bill has been working with computers since before becoming a "big eight" CPA, after which he carried his growing information systems knowledge into management accounting, internal auditing, and various capacities of controllership. Bill entered the world of databases and financial systems when he became a consultant for CODA-Financials, a U.K. - based software company that hired only CPA's as application consultants to implement and maintain its integrated financial database - one of the most conceptually powerful, even in his current assessment, to have emerged. At CODA Bill deployed financial databases and business intelligence systems for many global clients. Working with SQL Server, Oracle, Sybase and Informix, and focusing on MSSQL Server, Bill created Island Technologies Inc. in 1997, and has developed a large and diverse customer base over the years since. Bill's background as a CPA, Internal Auditor and Management Accountant enable him to provide value to clients as a liaison between Accounting / Finance and Information Services. Moreover, as a Certified Information Technology Professional (CITP) - a Certified Public Accountant recognized for his or her unique ability to provide business insight by leveraging knowledge of information relationships and supporting technologies - Bill offers his clients the CPA's perspective and ability to understand the complicated business implications and risks associated with technology. From this perspective, he helps them to effectively manage information while ensuring the data's reliability, security, accessibility and relevance. Bill has implemented enterprise business intelligence systems over the years for many Fortune 500 companies, focusing his practice (since the advent of MSSQL Server 2000) upon the integrated Microsoft business intelligence solution. He leverages his years of experience with other enterprise OLAP and reporting applications (Cognos, Business Objects, Crystal, and others) in regular conversions of these once-dominant applications to the Microsoft BI stack. Bill believes it is easier to teach technical skills to people with non-technical training than vice-versa, and he constantly seeks ways to graft new technology into the Accounting and Finance arenas. Bill was awarded Microsoft SQL Server MVP in 2009. Hobbies include advanced literature studies and occasional lectures, with recent concentration upon the works of William Faulkner, Henry James, Marcel Proust, James Joyce, Honoré de Balzac, and Charles Dickens. Other long-time interests have included the exploration of generative music sourced from database architecture.

Latest Articles