Introduction to MSSQL Server Analysis Services: Named Sets Revisited
December 12, 2005
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:
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: