## 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