Introduction
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
[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
[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
Excellent one...it is very much informative tutorial to learn in details...Hope for next blog..
ReplyDeleteThanks @Sukamal... I promise that if someone want to learn MDX... it is the Right Article to learn Easily without going so much theoretical thing.
Delete