CREATE SET Statement (MDX)

Creates a named set with session scope for the current cube.

Syntax

CREATE [SESSION] [HIDDEN] SET 
   CURRENTCUBE | Cube_Name
            .Set_Name AS 'Set_Expression'

Arguments

• Cube_Name
  A valid string expression that provides the name of the cube.

• Set_Name
  A valid string expression that provides the name for the named set being created.

• Set_Expression
  A valid Multidimensional Expressions (MDX) expression that returns a set.

Remarks

A named set is a set of dimension members (or an expression that defines a set) that you create to use again. For example, a named set makes it possible to define a set of dimension members that consists of the set of the top ten stores by sales. This set can be defined statically, or by means of a function like TopCount. This named set can then be used wherever the set of the top 10 stores is needed.

The CREATE SET statement creates a named set that remains available throughout the session, and therefore, can be used in multiple queries in a session. For more information, see Creating Session-Scoped Calculated Members (MDX).

You can also define a named set for use by a single query. To define such a set, you use the WITH clause in the SELECT statement. For more information about the WITH clause, see Creating Query-Scoped Named Sets (MDX).

The Set_Expression clause can contain any function that supports MDX syntax. Sets created with the CREATE SET statement that do not specify the SESSION clause have session scope. Use the WITH clause to create a set with query scope.

Specifying a cube other than the cube that is currently connected causes an error. Therefore, you should use CURRENTCUBE in place of a cube name to denote the current cube.

The HIDDEN keyword marks calculated members as not visible. Such calculated members are not visible to users querying the cube.

Scope

A user-defined set can occur within one of the scopes listed in the following table.

• 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 scope. For more information, see Creating Query-Scoped Named Sets (MDX).

• 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 creates a set with session scope. Use the WITH clause to create a set with query scope.

Example

The following example creates a set called Core Products. The SELECT query then demonstrates calling the newly created set. The CREATE SET statement must be executed before the SELECT query can be executed - they cannot be executed in the same batch.

CREATE SET [Adventure Works].[Core Products] AS '{[Product].[Category].[Bikes]}'
    
SELECT [Core Products] ON 0
  FROM [Adventure Works]

See Also

Other Resources

MDX Data Definition Statements (MDX)

Help and Information

Getting SQL Server 2005 Assistance

Change History