Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Dec 12, 2005

Introduction to MSSQL Server Analysis Services: Named Sets Revisited

By William Pearson

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.


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.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM