SSAS 2005 – Named Sets – TopCount – Static/Dynamic and Ordering

Named Sets in SSAS 2005 (SQL Server Analysis Services 2005) are a good thing. You can set up named sets for things like “Last 3 months” or “Yesterday” which is really nice for end users. The nature of dates is that you can make the MDX dynamic but yet the named set is still “static”.

You can also create sets when running MDX queries, and use them later in queries you run. These are dynamic named sets. The problem is, when you are working on your cube, you cannot define dynamic named sets in the “Calculations” tab. The named sets there are static. If you try to define a named set that say, for instance, tries to get the “Top X Salespeople by Revenue” or something, you can create it, but what happens is when you deploy your cube, the named set gets run for one slice of the cube, usually just the default (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=817566&SiteID=1   and http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1304964&SiteID=1).

I did read on a site, that in SSAS 2008, you will be able to create dynamic named sets in the cube editor, so that is good.

One other thing with named sets, if you create a named set that is using TopCount, when you bring it into an Excel pivot table, you will see the Top X values you are looking for (sliced incorrectly, as I stated above, but still there). Thing is, they are sorted by the SET Expression, not the Numeric Expression. I haven’t found a way to get them to sort by the numeric expression. Example from above, you have the Top 3 Salespeople by Revenue. You want to see that sorted by revenue. If you run a MDX query using TopCount

SELECT
{[Measures].[Revenue]} ON COLUMNS,
{TOPCOUNT([Employees].[Salespeople].MEMBERS,3,
    [Measures].[Revenue])} ON ROWS
FROM
    [MyCube]

you will see it ordered in the query results correctly

John Smith           $1000
Amanda Jones      $950
Homer Simpson     $500

etc

Now if you take the TopCount out –

TOPCOUNT([Employees].[Salespeople].MEMBERS,3,
    [Measures].[Revenue])

and create a named set in your MDX Script of the cube editor

CREATE SET CURRENTCUBE.[Top 3 Salespeople by Revenue]
 AS TOPCOUNT([Employees].[Salespeople].MEMBERS,3,[Measures].[Revenue]) ;        

or even like this as the forums above say

CREATE SET CURRENTCUBE.[Top 3 Salespeople by Revenue]
 AS TOPCOUNT(Existing [Employees].[Salespeople].MEMBERS,3,[Measures].[Revenue]) ;  

When you pull that named set into your pivot table in Excel, they wont be ordered by Revenue, instead, by name

Amanda Jones      $950
Homer Simpson     $500
John Smith           $1000

Funky stuff. My recommendation – if you are going to be needed dynamic named sets like the ones created above, just create a SSRS (SQL Server Reporting Services) report instead, or wait until SSAS 2008 comes out.

Note:

Now, the above is just what I have found. There might some crazy way that I am unsure of in MDX or somewhere to get this to work, but I can’t find anything that would let me do what I want.