SSAS Named Sets – Static Vs Dynamic

Named sets were there in MS SQL Server since beginning. They were only static in SQL Server 2005, but with SQL Server 2008, we have dynamic sets as well.

Also, this is one of the popular questions which are usually asked in BI interviews. The intent in asking this question is not to ascertain that candidate knows the difference between static and dynamic named sets, but to find out whether the candidate knows the intricacies involved in using named sets. What business problems it does solves for us. What are the guidelines to create named sets?

So before we delve into named sets, let’s see how the different parts in an MDX query are resolved:

Here if you notice that sets are evaluated after “FROM” and “WHERE” clauses are evaluated. Thus the concept of STATIC and DYNAMIC named sets shows up.

So to summarize:

STATIC Sets: The MDX query execution context is not passed from “FROM” and “WHERE” clauses to “WITH SET”. They are evaluated during CREATE SET statement execution or at query run time if defined with WITH SET clause. If static named sets are defined in the cube, they are evaluated at the time of cube processing.

DYNAMIC Sets: The MDX query execution context is passed from “FROM” and “WHERE” clauses to “WITH SET”. They are evaluated every time the query is run. It really doesn’t matter where they are defined.

Let’s see above in working, before we delve into remaining intricacies:

STATIC SET

DYNAMIC SET

CREATE
SET [Adventure Works].[TOP 5 PRODUCTS BY SALES] AS
TOPCOUNT(
[Product].[Product Model Categories].[Model Name].MEMBERS

,5

,[Measures].[Internet Sales Amount])

;

SELECT [Measures].[Internet Sales Amount] ON
COLUMNS,

[TOP 5 PRODUCTS BY SALES] ON
ROWS

FROM [Adventure Works];


SELECT

[Measures].[Internet Sales Amount] ON
COLUMNS,

[TOP 5 PRODUCTS BY SALES] ON
ROWS

FROM [Adventure Works]

WHERE

([Date].[Calendar].[Calendar Semester].&[2003]&[2]);


SELECT [Measures].[Internet Sales Amount] ON
COLUMNS,

[TOP 5 PRODUCTS BY SALES] ON
ROWS

FROM

(select [Date].[Calendar].[Calendar Semester].&[2003]&[2] on 0

FROM [Adventure Works]);


STATIC sets did not consider sub cubes

CREATE DYNAMIC
SET [Adventure Works].[TOP 5 PRODUCTS BY SALES] AS
TOPCOUNT(
[Product].[Product Model Categories].[Model Name].MEMBERS

,5

,[Measures].[Internet Sales Amount])

;

SELECT [Measures].[Internet Sales Amount] ON
COLUMNS,

[TOP 5 PRODUCTS BY SALES] ON
ROWS

FROM [Adventure Works];


SELECT

[Measures].[Internet Sales Amount] ON
COLUMNS,

[TOP 5 PRODUCTS BY SALES] ON
ROWS

FROM [Adventure Works]

WHERE

([Date].[Calendar].[Calendar Semester].&[2003]&[2]);


SELECT [Measures].[Internet Sales Amount] ON
COLUMNS,

[TOP 5 PRODUCTS BY SALES] ON
ROWS

FROM

(select [Date].[Calendar].[Calendar Semester].&[2003]&[2] on 0

FROM [Adventure Works]);


 Dynamic sets, did consider the sub cubes.

 So we saw that context set by WHERE clause is considered in evaluating DYNAMIC Sets. Also, when we specified, sub cubes, the context is appropriately set in dynamic set. Find out more about sub cubes versus where clause: here

So far so good. But the story doesn’t end here. Read on…

Before we get into more details let me summarize a simple term MULTI-SELECT. It means, more than one member is specified either in Sub Cube or in WHERE clause. We already saw that sub cube context is not passed on to the static sets. To pass the context, we need to use Dynamic sets.

So, where is the problem? The problem lies in the MDX that is sent from client apps to AS Server. Let us see couple of examples:

The cube browser or OWC component implicitly converts sub cube MDX to WHERE clause. And to pass the context set in WHERE clause to the calculated members, we need to use EXISTING clause. Refer to blog SSAS Sub Select vs Where Clause here.

Excel, and Report Builder uses the sub cubes, so to get correct numbers, we can use dynamic sets. But it may not return the correct results. This is very much relevant in implementing drill through actions.

SSRS generates the MDX (if using the designer) which will have both sub cubes and where clauses. So again we need to be careful in using dynamic sets.

So, I would suggest, named sets must be used with caution.

But all is not bad with named sets. Since AS caches the tuples while evaluating the named set expression, they provide better performance.

Will be back with more blogs, when I get time….

Please provide your feedback. I can be reached at jagdish_malani@hotmail.com

Advertisements

2 Comments on “SSAS Named Sets – Static Vs Dynamic”

  1. MartinIsti says:

    Hi Jagdish,
    Your post is quite useful though one thing is confusing for me. For STATIC sets you wrote:
    “They are evaluated at query run time. If static named sets are defined in the cube, they are evaluated at the time of cube processing.”
    I think this is a typo and static sets are evaluated during processing and/but not at QUERY RUN TIME. Otherwise it would be dynamic, right?
    Cheers,
    MartinIsti


What do you think?.. Let me know...

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s