Skipping axes

There are situations where we want to display just a list of members with no data associated with them. Naturally, we expect to get that list in rows, so that we can scroll through them vertically instead of horizontally. However, the rules of MDX say that we can't skip the axes. If we want something on rows (which is AXIS(1) by the way), we must use all previous axes as well (columns in this case, which is also known as AXIS(0)).

The reason why we want the list to appear on axis 1 and not axis 0 is because a horizontal list is not as easy to read as a vertical one.

Is there a way to display those members on rows and have nothing on columns? Sure! This recipe shows how.

Getting ready

The notation for an empty set is this: { }. So for the axis 0, we would simply do this:

{ } ON 0 

How to do it...

Follow these steps to open the Query Editor in SQL Server Management Studio (SSMS):

1. Start SQL Server Management Studio (SSMS) and connect to your SQL Server Analysis Services (SSAS) 2012 instance.
2. Click on the target database, Adventure Works DW 2016, and then right-click on the New Query button.

Follow these steps to get a one-dimensional query result with members on rows:

1. Put an empty set on columns (AXIS(0)). The notation for the empty set is this: {}.
2. Put some hierarchy on rows (AXIS(1)). In this case, we used the largest hierarchy available in this cube-customer hierarchy of the same dimension.
3. Run the following query:

         SELECT 
           { } ON 0, 
           { [Customer].[Customer].[Customer].MEMBERS } ON 1 
         FROM 
           [Adventure Works] 
   

How it works...

Although we can't skip axes, we are allowed to provide an empty set on them. This trick allows us to get what we need—nothing on columns and a set of members on rows.

There's more...

Skipping the AXIS(0) is a common technique to create a list for report parameters. If we want to create a list of customers whose name contains John, we can modify the preceding base query to use two functions to get only those customers whose name contains the phrase John. These two functions are Filter() and InStr():

SELECT 
   { } ON 0, 
   { Filter( 
           [Customer].[Customer].[Customer].MEMBERS, 
           InStr( 
                 [Customer].[Customer].CurrentMember.Name, 
                'John' 
               ) > 0 
         ) 
   } ON 1 
FROM 
   [Adventure Works] 

In the final result, you will notice the John phrase in various positions in member names:

The idea behind it

Instead of skipping the AXIS(0), if you put a cube measure or a calculated measure with a non-constant expression on axis 0, you will slow down the query. The slower query time can be noticeable if there are a large number of members from the specified hierarchy. For example, if you put the Sales Amount measure on axis 0, the Sales Amount will have to be evaluated for each member in the rows. Do we need the Sales Amount? No, we don't. The only thing we need is a list of members; hence we have used an empty set {} on AXIS(0). That way, the SSAS engine does not have to go into cube space to evaluate the sales amount for every customer. The SSAS engine will only reside in dimension space, which is much smaller, and the query is therefore more efficient.

Possible workarounds - dummy column

Some client applications might have issues with the MDX statement skipping axes because they expect something on columns, and will not work with an empty set on axis 0. In this case, we can define a constant measure (a measure returning null, 0, 1, or any other constant) and place it on columns. In MDX's terms, this constant measure is a calculated measure. It will act as a dummy column. It might not be as efficient as an empty set, but it is a much better solution than the one with a regular (non-constant) cube measure like the Sales Amount measure.

This query creates a dummy value on columns:

WITH 
MEMBER [Measures].[Dummy] AS NULL 
 
SELECT 
   { [Measures].[Dummy] } ON 0, 
   { [Customer].[Customer].[Customer].MEMBERS } ON 1 
FROM 
   [Adventure Works]