MDX Part-1

MDX emerged circa 1998, when it first began to appear in commercial applications. MDX was created to query OLAP databases


You can use Multidimensional Expressions (MDX) to query multidimensional data or to create MDX expressions for use within a cube.

Cube is organized based on measures, dimensions, and dimension attributes.


The output of an MDX query has the form of a cube itself.The query captures both the output specification and the dimensionality of the resulting cube.

Example1: We are asked information consumer to provide the total sales and total cost amounts for the years 1997 and 1998 individually for all USA-based stores (including all products). We are asked, moreover, to provide the information in a two-dimensional grid, with the sales and cost amounts (called measures in our data warehouse) in the rows and the years (1997 and 1998) in the columns.

1. Type the following query into the Query pane:
   

   --MDX01-1:  Basic Query
   SELECT
   {[Time].[1997],[Time].[1998]}ON COLUMNS,
   {[Measures].[Warehouse Sales],[Measures].[Warehouse Cost]}  ON ROWS
   FROM Warehouse
   WHERE  ([Store].[All Stores].[USA])
   

The diagram below labels the various parts of the query:

1. Two dashes (--) represent one of three typical ways to place a comment in MDX syntax, so that it is ignored when the MDX is parsed.


2.The cube that is targeted by the query (the query scope) appears in the FROM clause of the query. The FROM clause in MDX works much as it does in SQL.


3.The cube that is targeted by the query (the query scope) appears in the FROM clause of the query. The FROM clause in MDX works much as it does in SQL.


4. The output of an MDX query, which uses a cube as a data source, is another cube, whereas the output of an SQL query (which uses a columnar table as a source) is typically columnar.


5.It is important to realize that MDX's cube output allows us to place any dimension from the source cube onto any axis of the query's result cube. Many axes can exist, and it is often better to think in terms of "axes" than in "dimensions"  when designing an MDX query.


6.A query has one or more axes. The query above has two. (The first three axes that are found in MDX queries are known as rows, columns and pages.)


7. Keep in mind that columns always come before rows, and rows always precede pages, within the query.


8.Curled brackets "{}" are used in MDX to represent a set of members of a dimension or group of dimensions. The query above has one dimension each on the two query axes. The dimensions that appear are the Measures and Time dimensions.


9.We can display more than one dimension on a result axis. When we do this, an "intersection" occurs, in effect, and each cell appearing in the associated axis relates to thecombination of a member from each of the indicated dimensions. When more than one dimension is mapped onto an axis, the axis is said to consist of "tuples," containing the members of each of the mapped dimensions. 


We see the results below

Example 2:
Consumers have asked for a comparison between the total US warehouse sales for the first and second quarters of 1997.

SELECT

{[Time].[1997].[Q1],[Time].[1997].[Q2]}ON COLUMNS,

{[Warehouse].[All Warehouses].[USA]}  ON ROWS

FROM Warehouse

WHERE  ([Measures].[Warehouse Sales])

Because we have specified the Warehouse Sales measure in the WHERE statement, we have made it the slicer dimension. The slicer shows that we have picked only the Warehouse Sales measure from the measures dimension