Pages

Monday, May 24, 2010

Static Named Sets v/s Dynamic Named Sets

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

result 1 

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.

Static set result

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.

Total result for all countries

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
    ) ;

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];

The output is shown below

Dynamic Set result

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.

CREATE 
  DYNAMIC 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].&[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

Dynamic set result with Canada on rows 

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

 

5 comments:

  1. very informative article.. Thanks

    ReplyDelete
  2. Do you know if it's possible to refer to a Named Set within a role definition (e.g. AllowedSet MDX)?

    ReplyDelete
  3. Hi Mark,

    Here is a quote from the great book "Expert Cube Development with SSAS 2008" by Alberto Ferrari, Marco Russo and Chris Webb

    "... The basic rule is that dimension security is evaluated before the MDX Script is evaluated, and cell security is evaluated after the MDX Script. This means that it is safe to reference named sets and calculated members in cell security expressions but not in dimension security expressions. This is a simplification of what actually happens, however: in some cases it does appear to be possible to reference MDX Script objects from dimension security expressions but it can lead to some unexplained behavior so we do not recommend doing this."

    Read Chris's blog post here: http://cwebbbi.wordpress.com/2008/01/22/dimension-security-tips-tricks-and-problems/

    Link to Mosha's blog post: http://tinyurl.com/moshascriptinit

    HTH,

    Hrvoje Piasevoli

    ReplyDelete
  4. Mark: Hopefully, you have got the answer
    Hrvoje: Thank you for answering this for me... :)

    ReplyDelete
  5. Thanks for sharing your info. I really appreciate your efforts and I will be waiting for your further write ups thanks once again.
    html5 video player

    ReplyDelete