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:

example of pivoted multidimensional 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.

Show: