Cube-Based MDX Calculations - Microsoft SQL Server 2008 Analysis Services - page 209

the named set, Analysis Services calculates its value just once, when the named set
resolved. The following query executes almost twice as quickly as the previous one:

WITH
SET USADescendants AS Descendants([Customer].[Customers].[Country].&[USA],,
LEAVES) member Measures.x AS 'AGGREGATE(TopCount(USADescendants, 5,
[Measures].[Sales Count] ),[Measures].[Sales Count])'
SELECT Measures.x ON COLUMNS,
[Time].[Time].[Date].members ON ROWS FROM [Warehouse and Sales]

We did not move the TopCount function into the named set because doing so would affect
the results of the query.

Dynamic Named Sets

Analysis Services 2008 supports dynamic named sets, in addition to the static named sets
supported by earlier versions. Analysis Services now enables you to create a named set on
a session or in MDX script of a cube and revaluate it in context of each query that refer-
ences it. In a sense, dynamic named sets created with a CREATE SET statement behave the
same way as named sets created in a query do—it uses sets or tuples specified in a WHERE
clause and a SubSelect of a query. To demonstrate the difference between dynamic and
static sets, let's take on the following example:

1. Create a static named set for finding our best customers, using the following
expression:

CREATE SET [Warehouse and Sales].TopCustomers AS
'TopCount([Customer].[Customers].[Customer].members,5, [Measures].[Sales
Count])'
2. Execute query that should return the best customers in year 1997:

SELECT TopCustomers ON COLUMNS
FROM [Warehouse and Sales] WHERE
([Time].[Time].[Year].[1997],[Measures].[Sales Count])
Figure 12.13 shows the results of this query. Because our named set is static, this query
returns the best customers in a context of the default member—member ALL of time
dimension and doesn't take our WHERE clause into consideration.

Dawn Laner Ida Rodriguez Mary Francis Benigar James Horvat George Todero

104 125 146 127 123

FIGURE 12.13

Static named set doesn't take WHERE clause of a query into consideration.