The Basic MDX Query

A basic Multidimensional Expressions (MDX) query is structured in a fashion similar to the following example:

SELECT [<axis_specification>

       [, <axis_specification>...]]

  FROM [<cube_specification>]

[WHERE [<slicer_specification>]]

·         The breakdown of the <axis_specification> syntax is:

·         <axis_specification> ::= <set> ON <axis_name>

·         <axis_name> ::= COLUMNS | ROWS | PAGES | SECTIONS | CHAPTERS | AXIS(<index>)

·         Each axis dimension is associated with a number:

0 for the x-axis,

1 for the y-axis,

2 for the z-axis, and so on.

The <index> value is the axis number. For the first 5 axes, the aliases

COLUMNS------------------- AXIS(0)

ROWS ----------------------- AXIS(1)

PAGES----------------------- AXIS(2)

SECTIONS------------------- AXIS(3)

CHAPTERS------------------ AXIS(4)

An MDX query cannot skip axes.

Example1:A query cannot have a ROWS axis without a COLUMNS axis, or have COLUMNS axes without a ROWS axis.

·         However, you can specify a SELECT clause with no axes (that is, an empty SELECT clause). In this case, all dimensions are slicer dimensions, and the MDX query selects one cell.

 Basic MDX Syntax - SELECT Statement(SELECT and WHERE clause is mandatory):

In MDX, the SELECT statement is used to specify a dataset containing a subset of multidimensional data.

 To specify a dataset, an MDX query must contain information about:

• The number of axes. You can specify up to 128 axes in an MDX query.

but very few MDX queries will use more than 5 axes.

• The members from each dimension to include on each axis of the MDX query.
• The name of the cube that sets the context of the MDX query.
• The members from a slicer dimension on which data is sliced for members from the axis dimensions.

This information can be complex. As you will see in this topic, MDX syntax can provide such information in a simple and straightforward manner, using the MDX SELECT statement.

Basic MDX Query Example: MDX SELECT statement syntax:

SELECT

   { [Measures].[Unit Sales], [Measures].[Store Sales] } ON COLUMNS,

   { [Time].[1997], [Time].[1998] } ON ROWS

FROM Sales

WHERE ( [Store].[USA].[CA] )

The basic MDX SELECT statement contains a SELECT clause and a FROM clause(SELECT and FROM clause are mandatory)  WHERE clause is optional.

We can also write above query in this way

·        SELECT

·           { [Measures].[Unit Sales], [Measures].[Store Sales] } ON AXIS(0),

·           { [Time].[1997], [Time].[1998] } ON AXIS(1)

·        FROM Sales

·        WHERE ( [Store].[USA].[CA] )

The SELECT clause determines the axis dimensions of an MDX SELECT statement.

The FROM clause determines which multidimensional data source is to be used

The WHERE clause optionally determines which dimension or member to use as a slicer dimension; this restricts the extracting of data to a specific dimension or member.

The MDX query example uses a WHERE clause to restrict the data extract for the axis dimensions to a specific member of the Store dimension.

The syntax format of the MDX SELECT statement is similar to that of SQL syntax; however, you will note several obvious differences:

• MDX syntax distinguishes sets by surrounding tuples or members with braces (the { and } characters.)
• In the MDX query example, the COLUMNS and ROWS axis aliases are used. The MDX query could also have been written in the following fashion, using the ordinal position of each axis:

·        SELECT

·           { [Measures].[Unit Sales], [Measures].[Store Sales] } ON AXIS(0),

·           { [Time].[1997], [Time].[1998] } ON AXIS(1)

·        FROM Sales

·        WHERE ( [Store].[USA].[CA] )

• As with an SQL query, the FROM clause names the source of the data for the MDX query. However, unlike an SQL query, the FROM clause in an MDX query is restricted to a single cube.