Using Measures on the COLUMNS Axis

OLE DB Programmer's Reference

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.

1998-2001 Microsoft Corporation. All rights reserved.