CREATE SET

The CREATE SET statement allows the creation of user-defined sets.

You can define a set for use by a single query with the WITH clause in the SELECT statement, or for use in multiple queries in a session with the CREATE SET statement. For more information about WITH, see Using WITH to Define Calculated Members, Sets, and Cache.

BNF

<create-set-statement> ::= CREATE <optional-scope> <create-set-subset> [<create-set-subset>...]

<create-set-subset> ::= SET <cube-name>.<set-name> AS '<set-expression>'

<optional-scope> ::= <empty> | SESSION

Remarks

The <set-expression> value can contain functions that support multidimensional expressions (MDX) syntax.

Sets created with CREATE SET without an <optional-scope> value have session scope.

Scope

A user-defined set can occur within one of three scopes:

• Query scope. The visibility and lifetime of the set is limited to the query. The set is defined in an individual query. Query scope overrides session and global scope. For more information, see Using WITH to Define Calculated Members, Sets, and Cache.
• Session scope. The visibility and lifetime of the set is limited to the session in which it is created. (The lifetime is less than the session duration if a DROP SET statement is issued on the set.) The CREATE SET statement is used to create a set with session scope. Session scope overrides global scope.
• Global scope. The visibility and lifetime of the set is not limited to queries or sessions because the set definition is stored (but its data is not). For more information about creating sets with global scope, see Creating Calculated Members.