…brew your own Business Intelligence

MDX: Scope and Precedence for Calculated Members and Sets

With calculated members and calculated sets, there are 3 primary levels of scope:

  1. Global: calculated members and sets defined at this level (in the MDXScript of the SSAS cube) are available to all queries
  2. Session: calculated members and sets defined at this level (as a standalone statement executed after connecting the cube and establishing a session) is available to all queries executed within the same session.
  3. Query: calculated members and sets defined at this level (as a clause at the top of the MDX query (after the “WITH” keyword)) are only accessible within the query (e.g. listed on an axis, used as part of another calculated member/set, etc). Once the query executes, the member/set no longer exists.

Creating calculated members and sets with Global scope is usually preferable (due to performance related issues involving caching), but that’s not always possible for one reason or another. So what you will typically see is a combination of at least 2 (query scope and global scope) and sometimes session scope (but I’ve only seen this in complex reporting scenarios involving a custom UI).

With separation of development – cube developers designing the cubes and creating globally scoped members and sets vs. report developers writing MDX queries/scripts making use of query scoped and session scoped members and sets – what happens when naming conventions collide?

The short answer is that having members and sets with the same names at different scopes does not produce an error…but there is a precedence of which to be aware – and that is what we will explore in the rest of this post.

Global Scope

Below is a snippet from the MDXScript of my demo cube showing a calculated member and a calculated set:

Create Member CurrentCube.[Measures].[Gross Profit] AS
    [Measures].[Sales Amount] - [Measures].[Product Cost]
,Format_String = "Currency"
,Associated_Measure_Group = 'Internet Sales'
;

Create Set CurrentCube.[Top 10 Customers (Static)] AS
    TopCount(
         [Customer].[Customer Id].Children
        ,10
        ,[Measures].[Sales Amount]
    )
,Display_Folder = 'Sets'
;

As explained above, these objects can be accessed directly from any session/query:

image

image

Session Scope

Below are the commands to create (and drop) a member and set at the session scope with the same names as the ones defined at the global scope:

image

Note the difference in the actual calculation formulas for these members. When we run the same queries from the global scope section (above), we now have different results for the calculated member, but the calculated set is still showing 10 customers (global scope) instead of the 3 customers (session scope):

image

image

 

Based on the results from our query involving the calculated member, we would expect the session-scoped calculated set to take precedence over the one at the global scope…but that is not the case. Instead, we see that the calculated set defined at the global scope takes precedence over one with the same name defined at the session scope.

Note: Don’t forget to drop the session-scoped objects before the next section.

Query Scope

The following MDX queries show what the query-scoped version of the calculated member and set look like…again, notice the difference in actual calculation definitions:

WITH
    MEMBER [Measures].[Gross Profit] AS 100, FORMAT_STRING = "Currency"
SELECT
    {
         [Measures].[Sales Amount]
        ,[Measures].[Product Cost]
        ,[Measures].[Gross Profit]

    } ON 0,
    {
        [Order Date].[Calendar Year].[Calendar Year]
    } ON 1
FROM
    [Adventure Works DW2012]
;

image

 

WITH
    SET [Top 10 Customers (Static)] AS 
        TopCount(
             [Customer].[Customer Id].Children
            ,5
            ,[Measures].[Sales Amount]
        )
SELECT
    {
         [Measures].[Sales Amount]
    } ON 0,
    {
        [Top 10 Customers (Static)] 
    } ON 1
FROM
    [Adventure Works DW2012]
;

image

In this case we see that both the calculated member and set at the query scope take precedence over the ones at the global scope.

And for the sake of completeness, below are the results when the calculated member and set are defined at both the session and query scope – after removing the global scope definitions and deploying the MDXScript. The results show that query scope takes precedence over session scope for both the calculated member and calculated set.

image

image

Wrapping Up

In this post we explored the scenario where members and sets with the same name have been defined at different scopes. Below is a concise summary of our findings:

Calculated Member

  • Query Scope > Session Scope
  • Query Scope > Global Scope
  • Session Scope > Global Scope

Calculated Set

  • Query Scope > Session Scope
  • Query Scope > Global Scope
  • Session Scope < Global Scope Session Scope > Global Scope

 

Stay tuned for future posts where we’ll explore the next set of questions…

  • What’s up with the Session Scope vs Global Scope for the calculated set?
  • What about when a calculated member and calculated set have the same name and same scope?

Update 20140314: In case you plan on skipping the comments ;-)

The reason the calculated set at the session scope didn’t take precedence over the one defined at the global scope is – as Gerhard (b | t) pointed out in the top comment – because of an error with the session scope definition. When defined correctly, the session defined set behaves as expected ;-)

SessionCalculatedSet

2 thoughts on “MDX: Scope and Precedence for Calculated Members and Sets

  1. gbrueckl says:

    I am a bit confused – why did you use CREATE SESSION MEMBER instead of CREATE SESSION SET for your TopCount-Set??
    Actually I would have expected your syntax to throw an error as TopCount returns a set and not a member
    anyway, would be interesting if it behaves the same way if you use CREATE SESSION SET

    -gerhard

    1. Bill says:

      Great eye Gerhard! that is exactly the reason why the session-scope “set” didn’t behave as expected up in the post. I, too, was surprised that an error wasn’t returned when executing the “CREATE SESSION MEMBER” statement which as defined in the post, returned a set.

Leave a Reply