Non-Deterministic Functions in Named Sets

By Bryan C. Smith

Named sets are a great way to encapsulate complex or frequently used set definitions. There are three ways to create a named set:

1. The WITH SET clause of an MDX SELECT statement
   
2. The CREATE SESSION SET statement
   
3. The CREATE SET statement within the cube calculations script
   

Which of these you select depends on your needs and determines how and when the set is resolved. This is critical to understand when using non-deterministic functions as demonstrated in the following exercises.

These exercises assume you have a copy of the Adventure Works DW database running on a local instance of SSAS 2005. It is also assumed you have the permissions needed to adjust the Date/Time settings of this system.

 

The WITH SET clause

Change your system's time to the year 2004. Open an MDX Query window in SSMS, connect to the local instance containing Adventure Works DW, and execute the following:

with
set [Current Year] as

    STRTOMEMBER("[Date].[Calendar Year].[CY "+CSTR(YEAR(NOW()))+"]")

select

    [Measures].[Reseller Sales Amount] on 0,

    [Current Year] on 1

from    [Adventure Works]

;

	Reseller Sales Amount
CY 2004	$16,038,062.60

 

Change your system's time to the year 2003 and re-execute the same query:

	Reseller Sales Amount
CY 2003	$32,202,669.43

 

The named set created with the WITH SET clause is query-scoped. It is compiled and resolved with each execution of the query but is not available to other queries unless the set definition is repeated.

 

The CREATE SESSION SET Statement

Set your system's time to the year 2004 and execute this query to create the named set:

create
session
set [Adventure Works].[Current Year] as

    STRTOMEMBER("[Date].[Calendar Year].[CY "+CSTR(YEAR(NOW()))+"]")

;

With execution completed, change your system's time to the year 2003. Now, execute this query:

select

    [Measures].[Reseller Sales Amount] on 0,

    [Current Year] on 1

from    [Adventure Works]

;

	Reseller Sales Amount
CY 2004	$16,038,062.60

 

A couple things are happening here. First, the CREATE SESSION SET statement is compiled separate from the query. Any query utilizing the connection within which the set was compiled can leverage it. Once the connection is terminated, the set is lost.

Next, the set is resolved to its members when the CREATE SESSION SET is submitted. Set membership does not change until the set is dropped and recreated.

 

The CREATE SET Statement in the Calculations Script

Change your system's time back to the year 2004. In BIDS, add the following to the calculations script and redeploy the cube:

create
set [Adventure Works].[Current Year] as

    STRTOMEMBER("[Date].[Calendar Year].[CY "+CSTR(YEAR(NOW()))+"]")

;    

 

Change your system's time to the year 2003, reconnect to the Adventure Works DW database, and execute the following query.

select

    [Measures].[Reseller Sales Amount] on 0,

    [Current Year] on 1

from    [Adventure Works]

;

	Reseller Sales Amount
CY 2003	$32,202,669.43

 

Now, reset your system's time to the year 2004 and re-execute the query from above:

	Reseller Sales Amount
CY 2003	$32,202,669.43

 

Open a new query window and execute the SELECT statement again:

	Reseller Sales Amount
CY 2003	$32,202,669.43

 

With your system's time still set to the year 2004, restart the SSAS 2005 service and reconnect. Execute the query again:

	Reseller Sales Amount
CY 2004	$16,038,062.60

 

Unlike the session-scoped set, the named set is not resolved until the first time it is used. The members of that set are then defined and used across all queries until the SSAS service is restarted. With the first query submitted against the restarted service, the set's membership is defined again.

Before going further, please reset your system to the current time.

 

Additional Thoughts

I originally wrote this blog entry to use the RND function as my non-deterministic function. I would randomly select a Reseller to demonstrate the patterns shown above. I found an unusual pattern when I deployed the named set as part of the calculations script. Restarting the SSAS service resulted in a random member being returned with the first query, but redeployment of the database reset the membership of the set to the same member every time. I guess the take home message here is when using non-deterministic functions in your named sets, be sure to understand the basic patterns and test thoroughly.