Dynamic vs Static set in SSAS

We deal with named set a lot when it comes to access multi dimensional data from the cube. There are two types of named set – Static and Dynamic. Till SQL Server 2005 we had only Static named set but now in SQL Server 2008 both are supported (static and dynamic named set).

You create named set either using CREATE SET (when defined in the cube) or WITH SET MDX (when defined in the MDX/Report) syntax. In case of static member, it get evaluated when the CREATE SET statement is executed or MDX is executed in case when it is defined in MDX using WITH SET. In case of static named set once it is executed it persists static during whole session or query or cube. In effect what will happen since named set is static and cached it will give same set of members even when you query with different condition and expect different result.

Assume a case where you need to get top 10 product by sales for whole period and then for a month. So after you run the query for whole period, you will end up getting same set of products for a month also when you execute next. So in such glitches we now have option to create dynamic set which works well.

I will explain what exactly I mean by all this.

Create a static set in the cube as under. In my example I am retrieving top 5 products based on sale.

CREATE SET CURRENTCUBE.[TOP PRODUCT SALES]
 AS TOPCOUNT(
        [INVESTMENT PRODUCT].[PRODUCT NAME].[PRODUCT NAME].MEMBERS,
        5,
        [MEASURES].[INV PROD SALES ACTUAL]) ;

Next, run following MDX to get top 5 product sales. Note that I am not using any time slicer here so what I am getting in here is across the time.

SELECT 
{
    [MEASURES].[INV PROD SALES ACTUAL]
} ON COLUMNS,
{
    NONEMPTY([TOP PRODUCT SALES], 
    [MEASURES].[INV PROD SALES ACTUAL])
} ON ROWS
FROM [YOUR_CUBE];

Here is output of above MDX.
  

Next let’s apply February time filter in the MDX and I expect at least 1-2 different product than one we got above. 

SELECT 
{
    [MEASURES].[INV PROD SALES ACTUAL]
} ON COLUMNS,
{
    NONEMPTY([TOP PRODUCT SALES], 
    [MEASURES].[INV PROD SALES ACTUAL])
} ON ROWS
FROM [YOUR CUBE]
WHERE [TIME].[YQMD].[Month].&[2011-02-01T00:00:00]

Here is the output of above MDX - 
  
Note that though we now got different sales actual, it is the same set of products we earlier got when we ran without using any time filter. So what is happening here is when we created the set using CREATE SET statement in the cube and processed it, it evaluated the top 5 products and same set is being returned.

Now let’s change the set type to dynamic and retry the MDX and this time we expect it to be dynamic. Meaning, we should get different set of products based on top 5 sales when passed different time filter.

CREATE DYNAMIC SET CURRENTCUBE.[TOP PRODUCT SALES]
 AS TOPCOUNT(
        [INVESTMENT PRODUCT].[PRODUCT NAME].[PRODUCT NAME].MEMBERS,
        5,
        [MEASURES].[INV PROD SALES ACTUAL]) ; 

Now run above 2 MDX queries and analyze the result.They should be different now.
SELECT 
{
    [MEASURES].[INV PROD SALES ACTUAL]
} ON COLUMNS,
{
    NONEMPTY([TOP PRODUCT SALES], 
    [MEASURES].[INV PROD SALES ACTUAL])
} ON ROWS
FROM [YOUR CUBE]

SELECT 
{
    [MEASURES].[INV PROD SALES ACTUAL]
} ON COLUMNS,
{
    NONEMPTY([TOP PRODUCT SALES], 
    [MEASURES].[INV PROD SALES ACTUAL])
} ON ROWS
FROM [YOUR CUBE]
WHERE [TIME].[YQMD].[Month].&[2011-02-01T00:00:00]

 
Now you would have noticed that we are getting different set of top 5 products and it is correct now and reflecting per sales. Please leave your feedback/comment.