Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

SQL Server Advent Calendar 8 – Analysis Services Named Sets

SQL Server Advent Calendar 8 – Analysis Services Named Sets

  • Comments 1
  • Likes

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.

Comments
  • Pour tous ceux qui ont gardé une âme d'enfant ! SQL Server Advent Calendar SQL Server Advent

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment