Longer Lifetime and Scope: Named Sets and Calculated Members

OLE DB Programmer's Reference

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 user-interface–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 user-interface operations. For more information about how to do this, see "Preserving State During User-Interface 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. 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 lasts until an explicit <drop_formula_statement> is issued.
• SESSION. The created entity will be visible for all queries within the session. Its lifetime lasts as long as that of the session within which it is created. When the session dies, the created entity will be automatically dropped.

Note   See also "Visibility in Schema Rowsets" in this chapter.

As an example, the following MDX statement creates the same dataset that is specified in the "SalesData example" in Chapter 21: OLE DB for OLAP Overview:

CREATE SET SalesCube.ColumnsAxisSet AS CROSSJOIN({Venkatrao, Netz},    {USA_North.CHILDREN, USA_South, Japan}) CREATE SET 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, 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. 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.

1998-2001 Microsoft Corporation. All rights reserved.