Querying Data Warehouses - Data Warehouse Systems: Design and Implementation - page 178

The axis specification states the axes of a query as well as the members
selected for each of these axis. There can be up to 128 axes in an MDX query.
Each axis has a number: 0 for the x -axis, 1 for the y -axis, 2 for the z -axis,
and so on. The first axes have predefined names, namely, COLUMNS , ROWS ,
PAGES , CHAPTERS ,and SECTIONS . Otherwise, the axes can be referenced
using the AXIS(number) or the number naming convention, where AXIS(0)
corresponds to COLUMNS , AXIS(1) corresponds to ROWS ,andsoon.Itis
worth noting that query axes cannot be skipped, that is, a query that includes
an axis must not exclude lower-numbered axes. For example, a query cannot
have a ROWS axis without a COLUMNS axis.
The slicer specification on the WHERE clause is optional. If not specified,
the query returns the default measure for the cube. Unless we want to display
the Measures dimension, most queries have a slicer specification.
The simplest form of an axis specification consists in taking the members
of the required dimension, including those of the special Measures dimension,
as follows:

SELECT [Measures].MEMBERS ON COLUMNS,
[Customer].[Country].MEMBERS ON ROWS
FROM Sales

This query displays all the measures for customers summarized at the country
level. In MDX the square brackets are optional, except for a name with
embedded spaces, with numbers, or that is an MDX keyword, where they
are required. In the following, we omit unnecessary square brackets. The
result of this query is given next.

Unit Price Quantity Discount Sales Amount Freight Sales Count

Austria $84.77 4,644 21.71%

9.72%
17.94%
9.09%
11.76%
19.26%

$115,328.31 $6,827.10 114

Belgium $64.65 1,242 $30,505.06 $1,179.53 49

Denmark $70.28 1,156 $32,428.94 $1,377.75 45

Finland $54.41 848 $17,530.05 $827.45 51

France $64.51 3,052 $77,056.01 $3,991.42 172

Germany $79.54 8,670 $219,356.08 $10,459.01 309

Ireland
···

··· ··· ··· ··· ··· ···

Notice that there is no customer from Ireland and therefore the correspond-
ing row has only null values. In order to remove such values, the NONEMPTY
function must be used:

SELECT Measures.MEMBERS ON COLUMNS,
NONEMPTY(Customer.Country.MEMBERS) ON ROWS
FROM Sales