MDX Named Sets in SQL Server 2005/2008 

I-Data Weekly - March 10, 2008
Kevin S. Goff, Microsoft MVP 2008

Welcome to the debut of I-Data Weekly, a technical blog dedicated to Business Intelligence with the Microsoft SQL Server family of products.  I-Data stands for Intelligent Data, reflecting the goal of presenting the type of content you'll find in SetFocus' Master's Program - Business Intelligence Track .  

Each week, we'll present a different technical piece from our Business Intelligence Courseware - covering items such as data patterns with SSIS packages, building hierarchical OLAP cube dimensions, custom MDX code, advanced reporting with SQL Server Reporting Services, dashboard construction with PerformancePoint Server, and SharePoint integration.  As Microsoft is scheduling SQL Server 2008 for release later this year, we'll also include some coverage of the new BI features that SQL 2008 has to offer.

Our first installment of I-Data Weekly focuses on MDX Named Sets, and will cover the following:

• What are MDX Named Sets?
• How can you use MDX Named Sets in SQL Server 2005?
• Are there any "gotchas" with MDX Named Sets?
• Does SQL Server 2008 offer any new features for MDX Named Sets?

1 - What are MDX Named Sets?

An MDX Named Set is a list of members, based on some pre-defined retrieval logic. You may have a named set that lists your Customers in zip code order or products sold for 2005 in sales order amount. Since an MDX Named Set is simply an MDX expression, you could create a more complex Named Set using other MDX functions like DESCENDANTS to build a list of members from a customer geography, starting at a certain level (Region) down to another level (Market). 

Since MDX Named Sets are built with MDX statements, you can only use Named Sets against SQL Server Analysis Services (OLAP) Databases.

2 - How can you use MDX Named Sets

Here is an example for creating a new MDX Named Set, inside the Adventure Works OLAP cube.  I've opened the AdventureWorks SSAS project and I've added a new named set using the design tools in Business Intelligent Development Studio 2005 (BIDS).   The named set will use the MDX function TOPCOUNT to retrieve the top 10 selling products by Sales Amount, from the AdventureWorks Product dimension:

After you process/deploy the cube, you can use the MDX named set in places where you use MDX code.  For example, I'll run the following MDX query inside of SQL Server Management Studio 2005, to see the top 10 selling products.

3 - Are there any "gotchas" with MDX Named Sets?

The above example is very basic - you may be wondering, "What if I want to view the Top 10 Product Sales for France?"  (Or for any other condition)

Well, if you try that, you'll get the following result:

Huh?  That doesn't look correct.  While the sales dollars are certainly lower (since I only ran it for one country, France), the order isn't correct.  As a matter of fact, it's the exact same product order as the first query!  Why is that???

Well, the answer is fairly simple - a permanent MDX named set is only evaluated once, so it won't recognize or "see" any dimension slicing that you do with a WHERE clause.  So the TOPCOUNT function simply returns the names of the top 10 products across the entire OLAP database, regardless of any subsequent dimension slicing. 

So your next question is probably, "Well, how would I get a top 10 list of products for France?"  Well, you won't be able to use the Named Set from the OLAP cube.  You'd have to write your own MDX named set "in-line" that includes the TOPCOUNT function, as follows:

By creating a temporary Named Set and using it "in-line", you're assured of getting accurate results (i.e.  the temporary named set will honor any dimension slicing.)

Of course, your next question might be, "Well, doesn't that mean that permanent MDX named sets are basically static, and  are limited in terms of reusability?"  The answer is 'yes'....if you're using SQL Server 2005. However, SQL Server 2008 addresses this issue with a new feature called Dynamic Named Sets.

4 - Does SQL Server 2008 offer any new features for MDX Named Sets?

SQL Server 2008 addresses the issue of static Named Sets by offering a choice for whether a named set should be static or Dynamic (see the pull-down below, where a new dynamic MDX Named Set called DynamicProductTop10Sales.

If you define a Named Set as dynamic, you can then use it anywhere you'd normally use an MDX named set, and dimension slicing will be honored/respected:

And finally, if you wanted to create a ranked member for each product, you could create a calculated member (with the MDX RANK function) that reads the Dynamic Named Set:

Final thoughts:

MDX Named Sets are helpful to abstract out the logic of a list of members into a self-contained entity.  They are also valuable for reusability if you have several OLAP reports with member lists that contain the same logic. SQL Server 2008 increased their value significantly with the new feature for making Named Sets Dynamic .  Those who perform even a moderate amount of data retrieval and reporting from OLAP cubes will find Named Sets helpful.

The  SetFocus Business Intelligence Masters Program spends a great deal of time showing different examples of how you can leverage MDX Named Sets across different BI tools, such as Reporting Services and PerformancePoint Server.

About the author: 

• Kevin S. Goff is a contract trainer and courseware contributor for SetFocus, LLC.
• He has been a Microsoft C# .NET MVP since 2005, and is a regular columnist for CoDe Magazine, for The Baker's Dozen Productivity Tips Series. 
• He is also the author of  Pro VS 2005 Reporting using SQL Server and Crystal Reports, published by Apress. 
• He is the principal consultant for Common Ground Solutions, and specializes in solutions in .NET, SQL Server, and Crystal Reports. 
• His main site is http://www.commongroundsolutions.net, and his blog is http://www.TheBakersDozen.net.

Email this Author!

Add the above content to:

Google   Y! MyWeb   Furl   Simpy   Spurl   Blink  Del.icio.us   Digg   Technorati