SQL Server Advent Calendar 8 – Analysis Services Named Sets

Day 8 of my virtual advent calendar, about stuff I like in SQL Server 2008..

Analysis Services allows you to create a set of things you are interested in and persist this in the cube.  MDX is used to define the set e.g.

CREATE SET CURRENTCUBE.[Top 10 Resellers]
AS TopCount([Reseller].[Reseller].[Reseller].Members, 10, [Measures].[Sales Amount]);

creates a set Top 10 resellers of the resellers having the 10 highest sales amounts

If you do this in SQL Server 2005 then that statement would be evaluated when the set was defined and members in the set would be fixed for the life of the named set regardless of any changes to the data in the cube.  This can be a good thing but as I business user I would expect this to be dynamic e.g. if in our example a new reseller meets the rules at the expense of one of the others then I expect to see that reflected in the set in the same way as if I used a SQL view on a table with a top  X count clause.

SQL Server 2008 has the option to create dynamic sets that behave like this and this is simply achieved by altering the create statement to

CREATE DYNAMIC SET CURRENTCUBE.[Dynamic Top 10 Resellers]
AS TopCount([Reseller].[Reseller].[Reseller].Members, 10, [Measures].[Sales Amount]); 

I like the upgrade aspect of this as the set you originally created in will behave as before in Analysis Services 2005 and you simply need to flip the dynamic switch in Analysis Services 2008 to make it behave in what I believe is the more logical fashion.

Technorati Tags: Analysis Services,SQL Server 2008,dynamic sets