Database Reference
In-Depth Information
(Diet Cola, Connecticut)
(Diet Cola, New Hampshire)
(Caffeine Free Cola, New York)
(Caffeine Free Cola, Massachusetts)
(Caffeine Free Cola, Florida)
(Caffeine Free Cola, Connecticut)
(Caffeine Free Cola, New Hampshire)
The Crossjoin makes things much easier, particularly when working with large sets,
where it would not be practical to hard code every member combination.
It is important to understand the order in which the Crossjoin will return member sets:
Crossjoin({[Jan], [Feb], [Mar]}, {[Sales], [COGS], [Margin]})
will return:
([Jan], [Sales]), ([Feb], [Sales]), ([Mar], [Sales]), ([Jan],
[COGS]), ([Feb], [COGS]), ([Mar], [COGS]), ([Jan], [Margin]),
([Feb], [Margin]), ([Mar], [Margin]).
This is important to be aware of particularly when using other functions that are sen-
sitive to order, like head() or tail(). The head() and tail() functions allow you to grab
a particular tuple from a set based on its position from the beginning or end of the set.
Therefore, it is crucial to understand the result set order to return the correct results.
6.7 Query struCture
6.7.1 Axis
now that we understand all the pieces that make up an mDx query, let's start to put
them together to actually create an mDx query. In multidimensional terminology, an
axis represents how a Set is presented in the results of a query. An axis can be thought
of as a column or a row on a spreadsheet. multiple axes are used to present data where
Sets are nested. While you can specify up to 64 axes in a query, only the first five axes
are defined by keywords. The axes are CoLumnS, roWS, PAgES, ChAPtErS, and
SECtIonS. In general, I find it easier to just use the Axis ordinal (i.e., AxIS(0), AxIS(1),
AxIS(2), etc). you cannot skip an axis; you must start with 0 or CoLumnS and proceed
in order. It is possible to place an empty Set { } on an AxIS. I will demonstrate later in
the chapter how this might be useful when talking about metadata queries where you
are querying outline information, but not looking to return actual data.
An example of a simple mDx query is:
SELECT
{[Sales], [COGS], [Margin]} ON AXIS(0),
{[Year].Children} ON AXIS(1)
FROM [Sample.Basic];
Results in:
Axis-1 (Sales) (COGS) (Margin)
+--------+----------+---------+--------
(Qtr1) 96820.00 42877.00 53943.00
(Qtr2) 101679.00 45362.00 56317.00
