Learn MDX with Me – Part - 4

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.

Removing Null Columns or Rows

First we try to execute this query

SELECT {[Measures].[Qty Sold],

        [Measures].[Sales Rate],

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

        ([Dim Customer].[Customer Name].[Customer Name],

         [Dim Product].[Product Name].[Product Name]) on Rows

FROM   [CUBESales]

Output:

If we look at the output we can see that for [Deblina Bhattacharya] and [Saving Loation] all the records is NULL value and we need to remove this records

SELECT {[Measures].[Qty Sold],

        [Measures].[Sales Rate],

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

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

                  [Dim Product].[Product Name].[Product Name]) on Rows

FROM   [CUBESales]

By using NON EMPTY function we can Remove NULL columns or Rows.

To understand it properly

		Qty Sold	Sale Rate	Amount
Deblina	Saving Loation	NULL	NULL	NULL
Deblina	Tooth Paste	1	100	100

If we find all columns value of specified records is NULL and we want to remove it, we must use NON  EMPTY function in ROWS.

For Columns, we used NON  EMPTY function if all the columns value is NULL to remove those columns.

SELECT ({[Dim Time].[Quarter Name].[Quarter Name]},

        {[Measures].[Qty Sold],

         [Measures].[Sales Rate]}) on Columns,

       [Dim Customer].[Customer Name].[Customer Name] on Rows

FROM   [CUBESales]

Output:

SELECT NON EMPTY({[Dim Time].[Quarter Name].[Quarter Name]},

                 {[Measures].[Qty Sold],

                  [Measures].[Sales Rate]}) on Columns,

       [Dim Customer].[Customer Name].[Customer Name] on Rows

FROM   [CUBESales]

Output:

Now Want Some Aggregate

Try this

SELECT {[Measures].[Qty Sold],

        [Measures].[Sales Rate],

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

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

                  [Dim Product].[Product Name].[Product Name]) on Rows

FROM   [CUBESales]

Now Try this.
We are using ALL level Query for [Customer Name] hierarchy.

SELECT {[Measures].[Qty Sold],

        [Measures].[Sales Rate],

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

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

                  [Dim Product].[Product Name].[Product Name]) on Rows

FROM   [CUBESales]

Our purpose is NOT solved. We also see all the Customer, Product and Aggregation

SELECT {[Measures].[Qty Sold],

        [Measures].[Sales Rate],

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

       NON EMPTY ({[Dim Customer].[Customer Name],

                  [Dim Customer].[Customer Name].[Customer Name]},

                  [Dim Product].[Product Name].[Product Name]) on Rows

FROM   [CUBESales]

Output:

Different Type of Orientation for Aggregation:

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],

                  [Dim Customer].[Customer Name].[Customer Name]}) on Rows

FROM   [CUBESales]

Also, Try this:

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].[Customer Name],

                  [Dim Customer].[Customer Name]}) on Rows

FROM   [CUBESales]

So, we got our desired output.

Can anyone tell what changes we made?
Please answer your thoughts in comments box.

Single Attribute Dimension in MDX

SELECT {[Measures].[Qty Sold],

        [Measures].[Sales Rate],

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

        [Dim Customer] on Rows

FROM   [CUBESales]

Output:

Executing the query ...

Query (4, 9) The 'Dim Customer' dimension contains more than one hierarchy, therefore the hierarchy must be explicitly specified.

Execution complete

As we are using only Dimension name in Rows.

If we look at the Dimension named [Dim Customer]

This Dimension has Two Attributes named [Customer ID] and [Customer Name]. So it is not a single attributes dimension.

We are making another customer Dimension with single attribute and try the Query Again

Now try to make a MDX query which has Dimension Name in Rows

SELECT {[Measures].[Qty Sold],

        [Measures].[Sales Rate],

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

        [Dim CustomerSingleAttribute] on Rows

FROM   [CUBESales]

Output:

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

Posted by: MR. JOYDEEP DAS