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.
IE 7
Firefox 2.0
