Working with Named Sets in Analysis Services
Overview and Discussion
While the basic concepts
remain the same, the functional value of Named Sets has been extended in
Analysis Services 2005. Not only can Named Sets be used in MDX
queries, including those generated via reporting (such as Reporting
Services) and other applications, but Named Sets can be leveraged to
define sets within subcubes. Subcubes can be used as a means of
economizing cube space, specifically within the context of subsequent
statements, and we will examine them separately in more depth in prospective
articles. Suffice it to say, for now, that, in this area, as well as many
others within Analysis Services 2005, we are afforded more flexibility
and control over various aspects of cube operations. We can leverage Named
Sets definitions to include cube data in combination with functions,
operators and numerical values; their potential is significant for the
knowledgeable implementer.
Named Sets are useful and reusable. They
allow us the luxury of simple aliases that we can call upon to represent sets
of dimension members. The underlying set expressions can be complex, and the
ability to enclose these expressions within a user-friendly alias means easier
query construction in general, most particularly when the composite expressions
are used on a recurring basis. As we might imagine, well-constructed and
intuitively titled Named Sets can mean less likelihood of error,
especially in the hands of users with limited MDX exposure. They are often
leveraged in enterprise reporting applications, most commonly when "editors"
are used to generate MDX queries.
In addition to acting as
surrogates for sets with which we work, or upon which we otherwise focus,
routinely, Named Sets also provide other advantages. They provide a
means of simplifying queries by allowing us to not only represent complex sets,
as we have noted, with simpler aliases, but also by providing a level of
abstraction in the representation of the logic behind involved set operations,
again benefiting the ultimate consumer through ease of use and less exposure to
error. Finally, Named Sets can mean more efficient query execution –
whether defined within the cube structure via Business Intelligence Development
Studio, or through other means, or whether defined within a query that is
executed against a given cube, the Named Set is evaluated once, and the
values it contains are subsequently reused, a circumstance that can be
leveraged to increase overall operating efficiency.
Considerations and Comments
For purposes of the practice
exercises within this series, we will be working with samples that are provided
with MSSQL Server 2005 Analysis Services. These samples include,
predominantly, the Adventure Works DW Analysis Services database (with
member objects). The Adventure Works DW Analysis Services database and companion
samples are not installed by default in MSSQL Server 2005. The samples can
be installed during Setup, or at any time after MSSQL Server has
been installed. The topics "Running Setup to Install AdventureWorks
Sample Databases and Samples" in SQL Server Setup Help or
"Installing
AdventureWorks Sample Databases and Samples" in the Books Online (both of which are included on
the installation CD(s), and are available from www.Microsoft.com and other sources, as well),
provide guidance on samples installation.
Important information
regarding the rights / privileges required to accomplish samples installation,
as well as to access the samples once installed, is included in the references
I have noted.
» 
Comment and Contribute
