Creating a Top 1, Top 10, Top n Customer or Product List using Named Sets in SSAS Part 1 of 3

Just like stored procedures in SQL Server, Analysis Services provide  a similar functionality through stored MDX expressions called named sets.  SQL server stored procedures allow you to:
– Return a set of data from your database
– Can be reutilized by being called in scripts
– Are named descriptive of the data they return (ideally)

Similarly, SSAS allows you to create reusable pieces of MDX code that can be called in other MDX scripts by its name or alias and have the same characteristics as described above. Additionally, these reusable pieces of MDX code may or may not use parameters just like parameter-less stored procedures.  In essence a Named Set is a predefined subset of your cube or a sub-cube. For purpose of simplicity I will go over named sets that do not use parameters.

One of the most common queries requested by Sales and Marketing Managers is a list of Top 10 Customers.

This list of customers may be required in ad-hoc queries as well as weekly, monthly, quarterly or yearly sales reports. This is where Named Sets in SSAS are most beneficial. Instead of rewriting the same MDX query over and over and having to change it everywhere you use it each time the criteria for Top 10 customers changes (believe me it will), you simply call this named set and whatever changes you make to the named set trickles down to wherever you call it.

The general MDX syntax to define this Named Set is:

CREATE SET CURRENTCUBE.[Top 10 Customers]
AS
TopCount
(
  (Existing [Customer]. [Customer].[Customer].Members]),
  10,
  [Measures].[Sales Amount]
);

As you can see the MDX function that helps us easily create this list is TopCount(). The keyword *Existing* forces the data set to be reevaluated each time dimension criteria changes, for example if different time period such as Year,  Quarter or Month is selected or if the cube is sliced by a particular product. In this case, the Top 10 Customers list will be generated based on the criteria applied.

Conversely if you need to create a list of the 10 least profitable customers or least profitable products for example, you can use the BottomCount() MDX function in a similar fashion.

Special attention needs to be taken when using BottomCount() MDX function for customers or products that had no correponding sales amount for the given criteria. For example if during last month 15 customers have no sales [Sales Amount]=0 or [Sales Amount] is NULL, then BottomCount() customers list will be made up of customers with $0 or NULL  sales amounts. These $0 and NULL sales amount records can be filtered out using the Filter() MDX function for $0 values and with the NONEMPTY() MDX function.

In Part 2 of this post I will be providing some examples of Named
Sets using the Filter(),  NonEmpty() and another useful function called Item() which allows you to select a particular tuple from a set.

In Part 3, I will be explaining the difference between TopCount() vs. Head() and BottomCount() vs Tail() MDX functions. In some instances a particular function might be simpler and more efficient.