Tuesday, 4 April 2017

Learn MDX with Me - Part - 1

MDX means multi dimensional Expression. So the basic Question is how we write MDX Query for my SSAS Cube. Here we assured you that we are making this MDX learning easier and understandable for everyone.

Opening the SSMS
When we open the SSMS the server type should be Analysis Services.

In the Object Explorer we can find the SSAS Database, Cube, Measures Group and Dimension.

To Simply Browse the Cube
Just Right Click the Cube and Select Browse.

Right Click Cube à Browse

It opens the Browser. Now we can Drag and Drop Dimension attributes and Measures from measures group.

To Open the MDX Editor
Now we are going to click the NEW QUERY Button to open our MDX Editor.

Little Conceptual Thing
When we open the MDX Editor we can find a cube has Measures Group and Dimension.

Dimension contains attributes. When we open a Dimension we can find two types of attributes. One is simple attributes and another is Attribute Hierarchy.
Now open the Hierarchy and Drag the Year into MDX Editor

[Dim Time].[Hierarchy].[Year]

[Dim Time]      – Dimension Name
[Hierarchy]     - Hierarchy Name
[Year]          - Level Name

If we take another example of NON Hierarchy Attributes like Dimension Product.
[Dim Product].[Product Name].[Product Name]

[Dim Product]   -    Dimension Name
[Product Name]  -    Hierarchy Name
[Product Name]  -    Level Name

Let’s take another example from measures group named Measures
[Measures].[Sales Rate]

[Measures]      -    Measures Name
[Sales Rate]    -    Level Name

So there is no Hierarchy Exists.
In the same way Each attributes of the Dimension have default Two levels. One is All and another is member on it. So each attributes has a hierarchy with default two levels in it.
  • All Level
  • Member Level

Except the Hierarchy attributes which have multi level.

Now see, how a attribute is represent

[Dim Time].[Year Name].[Year Name]

[Dim Time]      - Dimension Name
[Year Name]     - Hierarchy Name
[Year Name]     - Level Name

This learning session will be continued. Please make your interest by commenting it.

Posted by: MR. JOYDEEP DAS


  1. Excellent one...it is very much informative tutorial to learn in details...Hope for next blog..

    1. Thanks @Sukamal... I promise that if someone want to learn MDX... it is the Right Article to learn Easily without going so much theoretical thing.