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 - Page 2

By William Pearson

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.

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