Axes

If we have only one axis it must be the columns axis and we must define a set.The WHERE clause is optional and is used to complete the tuple.

If we have two axes, we separate them with a comma.

If more than two axes  in a query we can refer to them by number or we can refer to one axis by name and the other by number.

MDX is quite flexible that way and there are even more axes possible to use in a query.We can go up to 129 axes in fact, but that’s very rare because most client applications can only display results from a maximum of two axes. But if we built a custom application, we could work with multiple axes.

 

  • Zero Axis

When we fail to provide any information about columns or rows or the WHERE clause, then the default member of every single attribute hierarchy is going to be included.

 

select from sales;

 

So it’s going to be the default of the Products, user-defined hierarchy, the default of Subcategory, the default of Product Key, the default of Product, the default of Category, and so on through each one of these various attributes and the default measure.

 

Now typically, the default for any of attributes is typically the root, All Dates here, All Products over here, but the default Measure, unless it’s defined explicitly in the cube, is simply the first measure that appears in the list when you open up the SQL Server Data Tools Cube Designer and actually look at your set of measures.

ZeroAxes

  • Single Axis:

If we want the “sales amount” from sales and the value with a label letting us know what that value relates to. Then we have a set of a single measure here, it doesn’t have to be a measure, again if we don’t specify the measure explicitly in an axis, then the default is going to be used unless I put it into the WHERE clause.

So we have our Sales Amount on columns, from sales, and again it’s going to be the All Products, All Dates default from anything that we have not named explicitly in the query.

select

[Measures].[Sales Amount] on columns

from sales;

OneAxes

 

  • Double Axes

 

Now we can get more of a pivot table-like experience if we have a double axis query where we have one set on columns and one set on rows.

This again is a set of one so it doesn’t need to be put into braces, but it could be, that would also be valid syntax.

But we have a set of one on columns representing Sales Amount, and then we’re using a set function here to bring in the Members from the Product dimension, the Product hierarchy, the Product level, and then everything that is listed here is included in our query results.

 

So we can put things on rows, put things on columns, have multiple columns here, and then the intersections become tuple coordinates that Analysis Services uses to retrieve values.

select

[Measures].[Sales Amount] on columns,

[Product].[Product].[Product].Members on rows

from sales;

 

TwoAxes

 

  • Axes Order doesn’t matter

 

The order of putting columns first in our query doesn’t really change the results.

 

So our order does not matter, we still get the same structure here. So columns first, rows second, whichever way you prefer to write this.

 

 

select

[Product].[Product].[Product].Members on rows,

[Measures].[Sales Amount] on columns

from sales;

 

ThreeAxes

 

  • Do not skip an axis

 

We don’t  allow to ignore columns and go straight to rows that will generate an error. It tells us that the axis number specified in a query must be sequentially specified and cannot contain gaps.

 

select

[Measures].[Sales Amount] on rows

from sales;

DonotSkipAxes

  • Axis numbers are substitutes for axis names

select

[Measures].[Sales Amount] on 0,

[Product].[Product].[Product].Members on 1

from sales;

AxesNumbers

  • Mix axis name with number

 

We have access numbers, for our columns is axis 0, our rows is axis 1, and so if we’re going to include axis number 1, which would be the rows, we must have axis 0, so we have to have both of them in there.

 

select

[Measures].[Sales Amount] on 0,

[Product].[Product].[Product].Members on rows

from sales;

MixedAxis

  • Multiple axes do not work in SSMS

 

Even though it is possible to have multiple axes and if we try to write a query that includes a third axis we get an error that says results cannot be displayed for cellsets with more than two axes.

 

So that’s a problem with the client application here in Management Studio, it is not setup to support working with more than two axes in the result set. If you’re writing a custom application you might be able to design some way of dealing with that, but we cannot do that here in Management Studio.

 

 

select

[Measures].[Sales Amount] on 0,

[Product].[Product].[Product].Members on 1,

[Date].[Year].[Year].Members on 2

from sales;

MultipleAxes

 

Thank you

 

Leave a Reply

Your email address will not be published. Required fields are marked *