Using Measures on the COLUMNS Axis
In the special case of a dataset consisting of only the Measures dimension along the x-axis, the resulting flattened view is typical of the way in which atomic multidimensional data is commonly stored in a relational database. This table of granular data is called a fact table. The combination of a fact table and a set of related dimension tables make up a star schema configuration.
For example, consider the following MDX statement. (Unqualified names are assumed to be unique.)
SELECT
{Sales, Cost} ON COLUMNS,
CROSSJOIN({USA, Asia}, {Qtr1, Qtr2, Qtr3, Qtr4}) ON ROWS
FROM SalesCube
WHERE ([1991], Computers)
This produces the following dataset:
In turn, this dataset yields the following flattened rowset:
[Continents].[MEMBER_CAPTION] |
[Countries].[MEMBER_CAPTION] |
[Quarters].[MEMBER_CAPTION] |
[Measures].[Cost] |
[Measures].[Sales] |
---|---|---|---|---|
North America |
USA |
Qtr1 |
123 |
456 |
North America |
USA |
Qtr2 |
789 |
1011 |
North America |
USA |
Qtr3 |
1213 |
1415 |
North America |
USA |
Qtr4 |
1617 |
1819 |
Asia |
NULL |
Qtr1 |
2021 |
2223 |
Asia |
NULL |
Qtr2 |
2425 |
2627 |
Asia |
NULL |
Qtr3 |
2829 |
3031 |
Asia |
NULL |
Qtr4 |
3233 |
3435 |
This representation is intuitive and corresponds to the way in which such data is usually displayed in a tabular view.
This example also illustrates step 2 of the algorithm in the section Flattening Algorithm: If the DIMENSION PROPERTIES clause is not specified, it is equivalent to specifying DIMENSION PROPERTIES MEMBER_CAPTION.