Static Named Sets v/s Dynamic Named Sets

Written by Jason Thomas on 25 May 2010. Posted in MDX

Reposted from Jason Thomas blog with the author's permission.

It is no big secret that most of the questions that are asked in interviews today are repeated. One of those questions which interviewers just love to ask again and again is the difference between a static named set and a dynamic named set. The popularity of this question is further aided by the fact that this particular feature of dynamic named sets was newly introduced in SQL Server 2008. So here I continue my interview question series on this topic.

Many times, it can happen that we end up writing very complex MDX expressions revolving sets. To simplify such expressions or to improve the performance of some queries, we can extract the definitions of some sets into a separate named set expression which are called named sets. A named set can be created either using a CREATE SET statement or a WITH SET statement (For syntax, refer Building Named Sets in MDX). In SQL Server 2005, there was only the Static named sets feature but in SQL Server 2008, both Dynamic and Static named sets are present.

Static Named Sets

The value of a static named set is evaluated either when the CREATE SET statement is executed (if it is defined inside the cube) or right after the WHERE clause is resolved (if the set is defined within a WITH clause). Hence, when a query references a static named set, the set is not resolved again in the context of the current coordinates which explains why it is known as a static named set.

To explain this better, let us consider a scenario where we want to count the number of years for a particular country where the order is greater than 4000.

WITH
MEMBER [measures].[country count] AS
    Count
    (
      Filter
      (
        [Date].[Calendar Year].[Calendar Year].ALLMEMBERS
       ,
          (
            [Customer].[Customer Geography].CurrentMember
           ,[Measures].[Internet Order Quantity]
          )
        > 4000
      )
    )
SELECT
{
    [Measures].[Internet Order Quantity]
   ,[measures].[country count]
} ON 0
,{
      [Customer].[Customer Geography].[Country].ALLMEMBERS
    *
      [Date].[Calendar Year].Children
} ON 1
FROM [Adventure Works];

This will give us the following output

Now let us simulate creating a static named set in the Adventure Works cube and see the result by running the following query in SQL Server Management Studio (SSMS).

CREATE
STATIC SET [Adventure Works].[Filtered Year] AS
    Filter
    (
      [Date].[Calendar Year].[Calendar Year].ALLMEMBERS
     ,
        (
          [Customer].[Customer Geography].CurrentMember
         ,[Measures].[Internet Order Quantity]
        )
      > 4000
    ) ;

GO
WITH
MEMBER [measures].[country count] AS
    Count([Filtered Year])
SELECT
{
    [Measures].[Internet Order Quantity]
   ,[measures].[country count]
} ON 0
,{
      [Customer].[Customer Geography].[Country].ALLMEMBERS
    *
      [Date].[Calendar Year].Children
} ON 1
FROM [Adventure Works]
WHERE
[Customer].[Country].&[Canada];

Note that we are using GO statement in between, else you will get an error in SSMS saying that multiple statements are not allowed. As per the explanation above, the set would be evaluated when the CREATE SET statement is executed and the coordinates at that context for Customer Geography is ALL. So now when the set is referenced in the query below, it will the count for the ALL member even if Canada is selected.

To verify this, I queried the cube to just return the count of countries when no country is selected and the country count matches with the above result.

Now what do we do if I just wanted to consider the coordinates in the where condition and return the result based on it (in this eg, consider the filter condition on Canada and return just the periods having order greater than 4000 for Canada which is 1)? That is where Dynamic sets come to our aid.

Dynamic Named Sets

With the help of dynamic named sets, we can create a named set which would be revaluated in context of each query that references it. It is evaluated in the context of WHERE clause and SubSelect of every query but are not evaluated in the context of every cell. The most visible difference would be in the case of dynamic named sets created in the cube. Let us use the previous example and see what happens when we use a dynamic set instead of a static set.

CREATE
DYNAMIC SET [Adventure Works].[Filtered Year] AS
    Filter
    (
      [Date].[Calendar Year].[Calendar Year].ALLMEMBERS
     ,
        (
          [Customer].[Customer Geography].CurrentMember
         ,[Measures].[Internet Order Quantity]
        )
      > 4000
    ) ;

The output is shown below

Finally we get the result we want taking the filter conditions in the WHERE clause (for Canada). We can also test what happens when we remove Canada from the WHERE clause and introduce it on the rows.

GO
WITH
MEMBER [measures].[country count] AS
    Count([Filtered Year])
SELECT
{
    [Measures].[Internet Order Quantity]
   ,[measures].[country count]
} ON 0
,{
      [Customer].[Customer Geography].[Country].&[Canada]    *
      [Date].[Calendar Year].Children
} ON 1
FROM [Adventure Works]

As mentioned, the set will be evaluated using the current coordinates which would be ALL for Country as there is nothing on the WHERE clause. The output is shown below

Now that you have understood that the difference between a static named set and dynamic named set is not only the difference in their syntax, you might want to go to the following blogs by Mosha (who is called the father of MDX) and understand how a named set improves performance

Jason has been working with Microsoft BI tools since he joined the IT industry in 2006. Since then he has worked across SSAS, SSRS and SSIS for a large number of clients. He is currently working for Mariner and is based out of Charlotte, NC. His personal blog can be found at http://road-blogs.blogspot.com

Tags: mdx

Static Named Sets v/s Dynamic Named Sets

Similar Articles