Introduction to MDX with Example

Connect to the SSAS server through the Management Studio. In Object Explorer, right click database folder and select New Query and MDX.

Select the Cube and you can see the structure of the cube. The structure of the cube will be shown below; this will contains all the measure groups, KPI and Dimensions. In here I have selected “Adventure Works” under cube and “Internet Sales” Measure Group under measure group, because this will filter the items that related to the Internet Sales.

In MDX at least minimum there should be at one axis, (COLUMNS / ROWS), instead of manually typing the name just drag and drop from the metadata pane.

Some basic queries are following:

To get the total [Internet Sales Amount], this is without dimension.

To get the [Internet Sales Amount] based on Product Category, that means Product Category in Row axis (Output will display the Internet Sales Amount for the entire category).

MEMBERS property will display the all the members of the Dimension or Hierarchy.

All it doesn’t matter row and column order, also can be shift the fields;

The terms Columns and Rows are simply aliases for the true names of the axes:

Axis(0) and Axis(1) respectively. Technically, an MDX query can have up to 128 axes, with aliases for the first five:

1. Columns
2. Rows
3. Pages
4. Sections
5. Chapters

Although our query can have up to 128 axes, but SQL Server Management Studio can only render bi-dimensional results, the following image illustrate that, even our query is correct, error message is appearing.

NONEMPTY fiction returns the tuples that are non-empty (Unknown).

MEMBERS function can be used with measures dimension: return all the measures:

But the above query not display the calculated measures, to display the calculated measures add keyword ADDCALCULATED MEMBERS

We can use the ALLMEMBERS also.