• Article

Building Named Sets in MDX (MDX)

A set expression can be a lengthy and complex declaration, and therefore be difficult to follow or understand. Or, a set expression may be used so frequently that repeatedly defining the set becomes burdensome. To help make working with a lengthy, complex, or commonly used expression easier, Multidimensional Expressions (MDX) lets you define such an expression as a named set.

Basically, a named set is a set expression to which an alias has been assigned. A named set can incorporate any members or functions that can ordinarily be incorporated into a set. Because MDX treats the named set alias as a set expression, you can use that alias anywhere a set expression is accepted.

You can define a named set to have one of the following contexts:

  • Query-scoped   To create a named set that is defined as part of an MDX query, and therefore whose scope is limited to the query, you use the WITH keyword. You can then use the named set within an MDX SELECT statement. Using this approach, the named set created by using the WITH keyword can be changed without disturbing the SELECT statement.

    For more information about how to use the WITH keyword to create named sets, see Creating Query-Scoped Named Sets (MDX).

  • **Session-scoped   **To create a named set whose scope is wider than the context of the query, that is, whose scope is the lifetime of the MDX session, you use the CREATE SET statement. A named set defined by using the CREATE SET statement is available to all MDX queries in that session. The CREATE SET statement makes sense, for example, in a client application that consistently reuses a set in a variety of queries.

    For more information about how to use the CREATE SET statement to create named sets in a session, see Creating Session-Scoped Named Sets (MDX).

  • **Globally-scoped   **To create a named set whose scope is wider than the context of the session of the user, that is, whose scope is the lifetime of the running instance, you use the CREATE SET statement inside the Default MDX script. See The Basic MDX Script (MDX) for more information. A named set defined by using the CREATE SET in the default MDX script is available to all users in all their MDX queries at any session.

The content of named sets can be evaluated at the moment of creation (static) or any time they are used in a query (dynamic). The CREATE SET [STATIC|DYNAMIC] syntax defines when the set is evaluated; see CREATE SET Statement (MDX)CREATE SET Statement (MDX)for more information. By default, sets are created as STATIC if neither keyword is specified in the create statement.

Named sets with dynamic behavior can be defined globally (in the Default MDX script) or in the scope of a session. However, these named sets are only evaluated when a query is being resolved (query scope). An error will be raised on any attempt to evaluate a dynamic set in a session or global scope; this also applies to indirect references of dynamic sets. The following is a list of calculations, either defined globally or at session scope, which can reference dynamic named sets.

  • Calculated Members

  • Dynamic Named Sets

  • KPIs

  • Right hand side (RHS) expressions on an assignment expression

  • Condition expression of cell calculation

  • Value expression of cell calculation

Inside any MDX query, it is possible to reference a dynamic set because the dynamic set will be evaluated at query scope.

Warning

Because dynamic sets are not evaluated during the CREATE SET command, a link to all objects (static or dynamic) the dynamic set depends on is created. Therefore, any or all of the referenced objects cannot be dropped until the dynamic set is dropped. For example, if a dynamic named set is created on the session that references a session calculated member, then that calculated member cannot be deleted while the dynamic named set still exists.

Dynamic Named Sets Resolution

At any moment in time, up to three versions of a dynamic set could exist:

  • One in the global scope used in the cube script

  • One in the session scope used in session calculations

  • One in the query scope used in query calculations

Which version is used in your calculation depends entirely on the context of your expression and how the dynamic named set is referenced; this applies mostly to indirect references.

For example, if you have a session calculated member that uses a session dynamic named set and you use that calculated member in a query, the named set will be evaluated at the session scope and the WHERE clause of the query will have no effect on the named set. However, if you use the same dynamic named set directly in your query, the named set is evaluated at the query scope and the WHERE clause might affect the results of the named set.