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