DeepSee MDX Reference
Executes a query and returns the results. This section
describes the basic syntax.
SELECT set_expression ON 0, set_expression ON 1 FROM cube_name
The ON clause is an optional axis specification. It
has the following form:
set_expression ON axis_name_or_number
You can specify zero, one, or two ON clauses. If you specify
two ON clauses, you can specify them in either order.
In other implementations of MDX, if you specify ROWS
, you must also specify COLUMNS
. In DeepSee MDX,
if you specify ROWS
but do not specify COLUMNS
, DeepSee automatically generates an ON clause for COLUMNS
. This clause uses the count measure.
is the name of the
cube to use. The set expressions must make sense within that cube.
If you omit the ON clause, MDX returns the count of records
in the cube. If this is a compound cube, this is the sum of the counts
of all cubes combined in that compound cube.
For any given cell of the results, to determine the value to
use, DeepSee finds the intersection
of the member
used for the column and the member (if any used) for the row:
If one member is a measure and the other is not a
measure, DeepSee finds the value of that measure for that member.
For example, if one member is the Ave Age
and the other member is the 34577
ZIP code, then
the corresponding data cell contains the average age of patients whose
home ZIP code is 34577.
If neither member is a measure, DeepSee uses the default
measure, which is usually %COUNT
. For example,
if one member is the gender F
, and the other member
is the 34577
ZIP code, then the corresponding data
cell contains the count of all female patients whose home ZIP code
The following simple example shows patient counts by ZIP code.
SELECT MEASURES.[%COUNT] ON 0, homed.zip.MEMBERS ON 1 FROM patients
1 32006 2,272
2 32007 1,111
3 34577 3,399
4 36711 1,069
5 38928 2,149
In the following example, the patients2
does not include the Home Zip
level. Instead, this
cube has a relationship called Home City
to another cube, called cities
. The query uses
SELECT MEASURES.[%COUNT] ON 0, city.cityd.city.members ON 1 FROM patients2
1 Cedar Falls 1,097
2 Centerville 1,136
3 Cypress 1,124
4 Elm Heights 1,089
5 Juniper 1,133
6 Magnolia 1,063
7 Pine 1,124
8 Redwood 1,083
9 Spruce 1,151