Introduction to Cubes
A cube is an n-dimensional structure that contains one axis (or edge) for each dimension. The cells of this cube are tuples. An MDX query retrieves specific tuples from the cube.
It is useful to visualize this cube, at least in simple cases. The DemoMDX cube has 10 dimensions (including the Measures dimension). For the sake of simplicity, the following figure shows three of those dimensions (HomeD, GenD, and Measures). Note that only three measures are actually shown.
Each axis of the cube is divided into segments, with one segment for each of the lowest-level members of the corresponding dimension. For the HomeD axis, these segments are the members of the City level.
Each cell in the cube is a fully qualified tuple. Each tuple has a value, as shown in the figure.
An MDX query is a request for a set of tuples, each of which has a value. Consider the following query:
SELECT CROSSJOIN(MEASURES.[%COUNT],gend.gender.MEMBERS) ON 0, homed.city.MEMBERS ON 1 FROM demomdx
Female Male
1 Cedar Falls 569 571
2 Centerville 625 560
3 Cypress 575 543
4 Elm Heights 545 560
5 Juniper 570 546
6 Magnolia 566 503
7 Pine 562 563
8 Redwood 536 540
9 Spruce 550 516
For this query, the system finds the relevant tuples in the cube and obtains their values. For example, the first tuple is (homed.[cedar falls],gend.female,measures.[%COUNT]). The value of this tuple is 569.
Each measure that is aggregated by addition (such as Age) is contained directly in the cube. For other measures, MDX uses values from the cube and aggregates them as specified in the measure definition.
For example, the Avg Age measure is not contained directly in the cube, but the Age measure is; the Age measure contains the cumulative age of all the patients represented in a tuple. To calculate the Avg Age measure, MDX divides Age by %COUNT. Consider the following query:
SELECT CROSSJOIN(MEASURES.[avg age],gend.gender.MEMBERS) ON 0, homed.city.members ON 1 FROM demomdx
Female Male
1 Cedar Falls 36.90 34.56
2 Centerville 35.98 34.68
3 Cypress 37.02 33.55
4 Elm Heights 36.87 34.05
5 Juniper 38.09 34.26
6 Magnolia 35.64 35.03
7 Pine 36.64 33.38
8 Redwood 36.70 36.52
9 Spruce 37.90 32.93
In this example, the second tuple is (homed.[cedar falls],gend.male,measures.[avg age]). To obtain this value, MDX divides the value of (homed.[cedar falls],gend.male,measures.[age]) by the value of (homed.[cedar falls],gend.male,measures.[%COUNT]) — 19734 divided by 571 is 34.56, as shown in the preceding results.
