Advanced Topics in MDX - Professional SQL Server Analysis Services 2005 with MDX - page 279

CREATE SET [Adventure Works].[EUROPE]
AS '
{[Customer].[Country-Region].[Country-Region].&[France],
[Customer].[Country-Region].[Country-Region].&[Germany],[Customer].[Country-
Region].[Country-Region].&[United Kingdom]}';
Instead of the WITH clause that you used in the MDX query for set creation,
the CREATE statement allows you to create a set within the scope of a ses-
sion or the entire cube. When you define sets using the CREATE command,
you need to specify the cube name as a prefix as shown in the CREATE
statement as seen above with the [Adventure Works] cube. You do not speci-
fy the name of the dimension when you specify the set. This is because a set
can contain tuples that are formed by multiple hierarchies that are from one or
more dimensions. Hence irrespective of whether all the tuples in a set are
from a single hierarchy or multiple hierarchies, it is not considered to be part
of any single dimension.
Once the set has been created using the CREATE command, it can be ac-
cessed in any query. If the named set was created within a session, it would
be valid only within that specific session and could not be used by users in
other sessions. If the named sets are to be used by several users, we recom-
mend you create them in the cube scope by defining them in the MDX script.
Named sets can be in one of the three scopes when an MDX query is being
executed:
▪ They can be within the query scope where they are defined with the
WITH clause in MDX.
▪ They can be within the session scope where they can be created
within a specific session using the CREATE statement you learned
now.
▪ They can be scoped as global and defined within an MDX script us-
ing the CREATE statement.
Analysis Services checks for resolving members or sets in an MDX query
within query, session, and global scopes, respectively. Once you have cre-
ated the named sets (either in session or global scope) executing the follow-