Longer Lifetime and Scope: Named Sets and Calculated Members

The scope of the named sets and calculated members specified in the previous section is just the MDX statement in which they appear. Consequently, their lifetime is also limited to the life of the query execution.

It is frequently useful to have named sets and calculated members available for the life of a session, and visible to all queries in that session. When used with the UI-related set functions, named sets provide a powerful mechanism to create queries that not only preserve the state, but also provide an efficient method to process UI operations. For more information on how to do this, see “Preserving State During UI Operations” in this section.

To create a named set or a calculated member with longer scope and lifetime, use the following syntax:

create_formula_statement:= CREATE [scope] formula_specification

The syntax for formula_specification is exactly the same as the syntax used in creating named sets and calculated members. For information on syntax, see “Named Sets and Calculated Members” in this chapter. The create formula statement creates a named set or a named member.

The scope and lifetime of this created entity depends on the value of scope. There are two possible values of scope:

• GLOBAL. The created entity will be visible for all queries across all sessions. Its lifetime is until an explicit drop formula statement is issued.
  
  
• SESSION. The created entity will be visible for all queries within the session. Its lifetime is the same as that of the session within which it is created. When the session dies, the entity will be automatically dropped.

As an example, the MDX statement below creates the same dataset that is specified in “Cube and Dataset Example”:

CREATE SalesCube.ColumnsAxisSet AS CROSSJOIN({Venkatrao, Netz},
   {USA_North.CHILDREN, USA_South, Japan})
CREATE SalesCube.RowsAxisSet AS {Qtr1.CHILDREN, Qtr2, Qtr3, 
   Qtr4.CHILDREN}
SELECT ColumnsAxisSet ON COLUMNS, RowsAxisSet ON ROWS
FROM SalesCube
WHERE (Sales, [1991], Products.All)

The following rules determine the semantics of named sets and members that are created by using create_formula_statement:

• If a scope is not specified, then it defaults to SESSION. In the above example, both ColumnsAxisSet and RowsAxisSet have a scope of SESSION.
  
  
• The name of the set or member being created must be qualified by the name of the cube. The absence of such a qualification is a syntax error. This is because there is no inherent cube context associated with a session. Therefore, the member or the set name has to provide the cube context. Thus, in the above example, the sets ColumnsAxisSet and RowsAxisSet were qualified by the cube name, SalesCube.
  
  
• Two SESSION scope named sets/members with the same name can exist on the same cube, as long as they are in two different sessions. This is because the scope of each is the session that created it . They are not visible outside the session.
  
  
• Named sets and members created with SESSION scope can be explicitly dropped during the session that created them.
  
  
• It is an error to drop a named set/member when it is being used by a query.