Adding Calculations to the Cube - Expert Cube Development with SSAS Multidimensional Models - page 173

Since this set is evaluated once, before any queries are run, it will contain the
top-selling products for all time, for all customers, and so on. In other words, when
the set is evaluated it knows nothing about any queries we might want to use it in.
However, this set isn't much use for reporting: it's highly likely that there will be a
different top-ten set of products for each month and each country, and if we wanted
to build a report to show the top-ten products on rows with month and country on
the slicer, we'd not be able to use a static named set. For example, the two following
queries show the same list of products on rows - and in neither case do they show
the top products for the years selected:
SELECT Measures.[Sales Amount] ON COLUMNS,
Best10Products ON ROWS
FROM
Sales
WHERE
([Date Order].[Calendar].[Calendar Year].&[2001])
SELECT Measures.[Sales Amount] ON COLUMNS,
Best10Products ON ROWS
FROM
Sales
WHERE
([Date Order].[Calendar].[Calendar Year].&[2004])
To handle this problem, Analysis Services 2008 introduced dynamic sets. Dynamic
sets are evaluated once per query, in the context of the WHERE clause of that query.
In order to make a static set dynamic, all we have to do is add the DYNAMIC keyword
just before the SET definition, as in:
CREATE DYNAMIC SET Best10ProductsDynamic AS
TopCount (
Product.Product.Product.Members,
10,
Measures.[Sales Amount]
);