Sunday 9 April 2017

Learn MDX with Me – Part - 6

Introduction
Now we are continuing our journey of MDX. Now we are trying to drill down more on MDX Query. Hope the session is very interesting.

Understanding Parent function
Parent function represents the Parent of the current member. To understand it, we are creating a new Hierarchy with the name of [CallenderHierarchy






Now try to see a member of Calendar Hierarchy

SELECT {[Measures].[Qty Sold],
        [Measures].[Sales Rate],
        [Measures].[Calculated Sales Amout]} on Columns,
        [Dim Time].[CallenderHierarchy].[Year Name].&[Calendar 2017].
           &[Quarter 1, 2017].&[January 2017] on Rows
FROM   [CUBESales]

Output:

Qty Sold
Sales Rate
Calculated Sales Amout
Jan-17
80
900
35000

SELECT {[Measures].[Qty Sold],
        [Measures].[Sales Rate],
        [Measures].[Calculated Sales Amout]} on Columns,
        [Dim Time].[CallenderHierarchy].[Year Name].&[Calendar 2017].
           &[Quarter 1, 2017].&[January 2017].parent on Rows
FROM   [CUBESales]

Output:


Qty Sold
Sales Rate
Calculated Sales Amout
Quarter 1, 2017
100
1200
38000

SELECT {[Measures].[Qty Sold],
        [Measures].[Sales Rate],
        [Measures].[Calculated Sales Amout]} on Columns,
        [Dim Time].[CallenderHierarchy].[Year Name].&[Calendar 2017].
           &[Quarter 1, 2017].&[January 2017].parent.parent on Rows
FROM   [CUBESales]


Output:


Qty Sold
Sales Rate
Calculated Sales Amout
Calendar 2017
100
1200
38000

Ancestors Function
It is quite difficult to use parent function because if we have lot of Hierarchy Levels. Suppose we have 9 Hierarchy levels and the members of the last Hierarchy Level we want to move the top level… we just do code like that


[Members of Last Level].Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent


So we have another function named Ancestors. It tales the current member and the level number that we need to see.


SELECT {[Measures].[Qty Sold],
        [Measures].[Sales Rate],
        [Measures].[Calculated Sales Amout]} on Columns,
        Ancestors([Dim Time].[CallenderHierarchy].[Year Name].&[Calendar 2017].
           &[Quarter 1, 2017].&[January 2017], 2) on Rows
FROM   [CUBESales]

Output:


Qty Sold
Sales Rate
Calculated Sales Amout
Calendar 2017
100
1200
38000

SELECT {[Measures].[Qty Sold],
        [Measures].[Sales Rate],
        [Measures].[Calculated Sales Amout]} on Columns,
        Ancestors([Dim Time].[CallenderHierarchy].[Year Name].
            &[Calendar 2017].&[Quarter 1, 2017], 1) on Rows
FROM   [CUBESales]

Output:


Qty Sold
Sales Rate
Calculated Sales Amout
Calendar 2017
100
1200
38000

Instead of Level 1,2,3 we can directly use the Level name also.


SELECT {[Measures].[Qty Sold],
        [Measures].[Sales Rate],
        [Measures].[Calculated Sales Amout]} on Columns,
        Ancestors([Dim Time].[CallenderHierarchy].[Year Name].
            &[Calendar 2017].&[Quarter 1, 2017],
            [Dim Time].[CallenderHierarchy].[Year Name]) on Rows
FROM   [CUBESales]

Output:


Qty Sold
Sales Rate
Calculated Sales Amout
Calendar 2017
100
1200
38000



Ascendants Function
The Ascendants function returns all of the ancestors of a member from the member itself up to the top of the member’s hierarchy; more specifically, it performs a post-order traversal of the hierarchy for the specified member, and then returns all ascendant members related to the member, including itself, in a set. This is in contrast to the Ancestor function, which returns a specific ascendant member, or ancestor, at a specific level.


Try this

SELECT {[Measures].[Qty Sold],
        [Measures].[Sales Rate],
        [Measures].[Calculated Sales Amout]} on Columns,
        ascendants([Dim Time].[CallenderHierarchy].[Year Name].&[Calendar 2017].
           &[Quarter 1, 2017].&[January 2017]) on Rows
FROM   [CUBESales]



Qty Sold
Sales Rate
Calculated Sales Amout
Jan-17
80
900
35000
Quarter 1, 2017
100
1200
38000
Calendar 2017
100
1200
38000
All
100
1200
38000

Finding Brothers and Sister of Specified members


We have to find the current members Parent first and then find the children of the parents.
   

     Finding Parent

SELECT {[Measures].[Qty Sold],
        [Measures].[Sales Rate],
        [Measures].[Calculated Sales Amout]} on Columns,
        [Dim Time].[CallenderHierarchy].[Year Name].&[Calendar 2017].
           &[Quarter 1, 2017].&[January 2017].parent on Rows
FROM   [CUBESales]

Output:


Qty Sold
Sales Rate
Calculated Sales Amout
Quarter 1, 2017
100
1200
38000

  

  Finding Children of the Parent

SELECT {[Measures].[Qty Sold],
        [Measures].[Sales Rate],
        [Measures].[Calculated Sales Amout]} on Columns,
        [Dim Time].[CallenderHierarchy].[Year Name].&[Calendar 2017].
           &[Quarter 1, 2017].&[January 2017].parent.children on Rows
FROM   [CUBESales]

Output:


Qty Sold
Sales Rate
Calculated Sales Amout
Feb-17
20
300
3000
Jan-17
80
900
35000
Mar-17
(null)
(null)
(null)

Siblings Function
It is the same output as Parent.Children function

SELECT {[Measures].[Qty Sold],
        [Measures].[Sales Rate],
        [Measures].[Calculated Sales Amout]} on Columns,
        [Dim Time].[CallenderHierarchy].[Year Name].&[Calendar 2017].
           &[Quarter 1, 2017].&[January 2017].siblings on Rows
FROM   [CUBESales]

Output:


Qty Sold
Sales Rate
Calculated Sales Amout
Feb-17
20
300
3000
Jan-17
80
900
35000
Mar-17
(null)
(null)
(null)


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




Posted by: MR. JOYDEEP DAS

No comments:

Post a Comment