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