Database Reference
In-Depth Information
Returning data on the query axes
Each MDX query refers to one or more axes on which data should be returned. You
can refer to an axis by its name (columns, rows, pages, and so on) or by its ordin-
al number, starting at zero. You cannot skip an axis, so each query must include
columns (ordinal 0), but could also include rows (ordinal 1), pages (ordinal 2), and so
on. The majority of frontend tools can only work with two axes, columns and rows, so
don't let this scare you. The
SELECT
clause of the query must include the definition of
all the axes, and the
FROM
clause defines a single cube (or
perspective
) from which
you extract the data. The
WHERE
clause contains a
slicer
limiting the data set specified
in the query's
SELECT
clause.
Getting ready
To follow the examples in this chapter, please connect to the
Analysis Services
2012
instance using the
SQL Server Management Studio
(
SSMS
), right-click on the
Adventure Works 2012
sample database, and go to
New Query
|
MDX
. This will
open a new query window. After typing the query text, you can execute queries by
pressing the
F5
key or by clicking on the execute button that includes an exclamation
mark (
!
).
How to do it...
Let's get started and look into the steps for returning data on the query axis.
1. Execute the following query to retrieve the total amount of
Reseller Sales
Amount
across all the dimensions:
SELECT [Measures].[Reseller Sales Amount]
ON 0
FROM [Adventure Works]
Since the query only refers to the
Measures
dimension, Analysis Services
will return data for the default member of each dimension hierarchy. Unless
you explicitly define the default member for each attribute while designing a
dimension, the default member is
ALL
.
Search WWH ::
Custom Search