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

Learn MDX with Me – Part - 5

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.

Retrieving Specified Member Data

Now we are trying to retrieve some specified member data. We have two customer and we want to see only specified customer information.

 SELECT {[Measures].[Qty Sold],

        [Measures].[Sales Rate],

        [Measures].[Calculated Sales Amout]} on Columns,

        [Dim Customer].[Customer Name].&[Deblina Bhattacharya] on Rows

FROM   [CUBESales]

Output:

Please look at the MDX, how we retrieve a specified customer information.

[Dim Customer].[Customer Name].&[Deblina Bhattacharya]

Here we are using the members by using &

Now try this same with product information.

SELECT {[Measures].[Qty Sold],

        [Measures].[Sales Rate],

        [Measures].[Calculated Sales Amout]} on Columns,

       NON EMPTY ([Dim Product].[Product Name].[Product Name],

      [Dim Customer].[Customer Name].&[Deblina Bhattacharya]) on Rows

FROM   [CUBESales]

Output:

Understanding Hierarchy and Members Attributes

First we want to see the Hierarchy structure

Now try this MDX

SELECT {[Measures].[Qty Sold],

        [Measures].[Sales Rate],

        [Measures].[Calculated Sales Amout]} on Columns,

        [Dim Time].[Hierarchy] on Rows

FROM   [CUBESales]

Output:

It just shows the ALL members data.

Now we need to see all the members that we mentioned in the hierarchy level. So we introduce a member named members with the hierarchy.

SELECT {[Measures].[Qty Sold],

        [Measures].[Sales Rate],

        [Measures].[Calculated Sales Amout]} on Columns,

        non empty([Dim Time].[Hierarchy].members) on Rows

FROM   [CUBESales]

Output:

[Dim Time].[Hierarchy].members

Hope you understand the differences.

Now we are dragging a members from calendar hierarchy and put the .members in it

SELECT {[Measures].[Qty Sold],

        [Measures].[Sales Rate],

        [Measures].[Calculated Sales Amout]} on Columns,

        [Dim Time].[Hierarchy].[Year].&[2017-01-01T00:00:00]

           .&[2017-01-01T00:00:00].&[January 2017].members on Rows

FROM   [CUBESales]

Output:

Executing the query ...

Query (4, 9) The MEMBERS function expects a level expression for the 1 argument. A member expression was used.

Execution complete

So

[ Hierarchy ] à [ Members ] à[ Children ]

SELECT {[Measures].[Qty Sold],

        [Measures].[Sales Rate],

        [Measures].[Calculated Sales Amout]} on Columns,

        [Dim Time].[Hierarchy].[Year].&[2017-01-01T00:00:00]

           .&[2017-01-01T00:00:00].Children on Rows

FROM   [CUBESales]

Output:

Remember that the Children only works with Hierarchy Attribute not any Normal Attributes.

Understanding DESCENDANT function

The Descendant function works only the Hierarchy member attributes.

Syntax is:

DESCENDANT(<Hierarchy members>, <Level>)

Its starts from zero level 0 then 1, 2, n

In our Case

SELECT {[Measures].[Qty Sold],

        [Measures].[Sales Rate],

        [Measures].[Calculated Sales Amout]} on Columns,

        descendants([Dim Time].[Hierarchy].[Year].

        &[2017-01-01T00:00:00],0) on Rows

FROM   [CUBESales]

SELECT {[Measures].[Qty Sold],

        [Measures].[Sales Rate],

        [Measures].[Calculated Sales Amout]} on Columns,

        descendants([Dim Time].[Hierarchy].[Year].

           &[2017-01-01T00:00:00],1) on Rows

FROM   [CUBESales]

SELECT {[Measures].[Qty Sold],

        [Measures].[Sales Rate],

        [Measures].[Calculated Sales Amout]} on Columns,

        descendants([Dim Time].[Hierarchy].[Year].

           &[2017-01-01T00:00:00],2) on Rows

FROM   [CUBESales]

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

Posted by: MR. JOYDEEP DAS