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

6.1.5 Cross Join

As said above, an MDX query can display up to 128 axes. However, most
OLAP tools are only able to display two axes, that is, two-dimensional tables.
In this case, a cross join can be used to combine several dimensions in a single
axis. Suppose that we want to obtain the sales amount for product categories
by country and by quarter. In order to view this query in a matrix format, we
need to combine the customer and time dimensions in a single axis through
the CROSSJOIN function as shown next:

SELECT Product.Category.MEMBERS ON COLUMNS,
CROSSJOIN(Customer.Country.MEMBERS,
[Order Date].Calendar.Quarter.MEMBERS) ON ROWS
FROM Sales
WHERE Measures.[Sales Amount]

Alternatively, we can use the cross join operator ' * ':

SELECT Product.Category.MEMBERS ON COLUMNS,
Customer.Country.MEMBERS *
[Order Date].Calendar.Quarter.MEMBERS ON ROWS
FROM Sales
WHERE Measures.[Sales Amount]

The result of the query is as follows:

Beverages Condiments Confections ···

Austria Q3 1996 $708.80 $884.00 $625.50 ···

Austria Q4 1996 $12,955.60 $703.60 $36.00 ···

Austria Q1 1997 $3,097.50 $1,505.22 ···

Austria Q2 1997 $1,287.50 $1,390.95 $3,159.00 ···

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

More than two cross joins can be applied, as shown in the following query:

SELECT Product.Category.MEMBERS ON COLUMNS,
Customer.Country.MEMBERS *
[Order Date].Calendar.Quarter.MEMBERS *
Shipper.[Company Name].MEMBERS ON ROWS
FROM Sales
WHERE Measures.[Sales Amount]

This query yields the result displayed below: