New features in Named Sets in Analysis Service 2008

One of the important feature in SQL Server 2008 is Dynamic Named Sets. Till SQL Server 2005 named sets were static. In SQL Server 2005 once named sets are executed they were remain static during whole session or query or cube. This was giving wrong results during Topcount or Bottomcount like functions.


Lets look at the example to get more details
In Adventureworks cube
First we create a set of top 5 products for of Internet Sales
create set [Adventure Works].[top5products] as
'topcount([Product].[Product Categories].[Product].members,5, [Measures].[Internet Sales Amount])';
First we will run query fro all time period
select [Measures].[Internet Sales Amount] on 0,
[top5products] on 1 from
[Adventure Works]

Result

Internet Sales Amount
Road-150 Red, 48 $1,205,876.99
Road-150 Red, 62 $1,202,298.72
Road-150 Red, 52 $1,080,637.54
Road-150 Red, 56 $1,055,589.65
Road-150 Red, 44 $1,005,493.87
Now we run the query for year 2003
select [Measures].[Internet Sales Amount] on 0,
[top5products] on 1 from
[Adventure Works]
where [Date].[Calendar].[Calendar Year].&[2003]

Result

Internet Sales Amount
Road-150 Red, 48 (null)
Road-150 Red, 62 (null)
Road-150 Red, 52 (null)
Road-150 Red, 56 (null)
Road-150 Red, 44 (null)
Look at here this products have no sales in 2003, because in 2003 top5products are different then top5products for all time period. But even though it appears in the result because the set top5product is evaluated when it was created and it will be static for all sessions and does not reflect the change in where condition
drop set [Adventure Works].[top5products]
Now we will create dynamic set which will be effected from where condition in the query
create dynamic set [Adventure Works].[top5products] as
'topcount([Product].[Product Categories].[Product].members,5, [Measures].[Internet Sales Amount])';
First we will run query fro all time period
select [Measures].[Internet Sales Amount] on 0,
[top5products] on 1 from
[Adventure Works]

Result

Internet Sales Amount
Road-150 Red, 48 $1,205,876.99
Road-150 Red, 62 $1,202,298.72
Road-150 Red, 52 $1,080,637.54
Road-150 Red, 56 $1,055,589.65
Road-150 Red, 44 $1,005,493.87
Now we will run the query from 2003
select [Measures].[Internet Sales Amount] on 0,
[top5products] on 1 from
[Adventure Works]
where [Date].[Calendar].[Calendar Year].&[2003]
Result
Internet Sales Amount
Mountain-200 Black, 46 $431,458.12
Mountain-200 Black, 42 $403,918.24
Mountain-200 Silver, 38 $389,758.32
Mountain-200 Silver, 42 $373,518.39
Mountain-200 Black, 38 $367,198.40
Here result is different because top5products is different in 2003 than that of 2001 and we are getting right result because of dynamic sets.
drop set [Adventure Works].[top5products]
So because of dyamic set we get right results and better performance which is not possible in static named sets