Learn MDX with Me – Part - 3

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.

Modifying Our Simplest Query

Our simplest MDX query is

SELECT

FROM   [CUBESales]

But the output is undefined here as no one knows what measures information it is showing.

Here we are not specifying any axis. Now we are going to define it.

Recalling the syntax and try this

SELECT [Measures].[Qty Sold] on Columns

FROM   [CUBESales]

Output:

Qty Sold

100

Now try this one

SELECT [Measures].[Qty Sold] on Rows

FROM   [CUBESales]

Output:

Executing the query ...

Query (1, 8) Axis numbers specified in a query must be sequentially specified, and cannot contain gaps.

Execution complete

It gives us error. 

What we want to mean is that, Axis must be sequential. That means Columns then Rows (0 Axis, 1 Axis, 2 Axis …. N Axis).

Now taking two measures together

SELECT [Measures].[Qty Sold],[Measures].[Sales Rate]  on Columns

FROM   [CUBESales]

Output:

Executing the query ...

Parser: The statement dialect could not be resolved due to ambiguity.

Execution complete

So, why the Error is showing? We are using two measures from same measure group so it is a SETS and we must use curly braces ({}) on it.

SELECT {[Measures].[Qty Sold],

        [Measures].[Sales Rate]}  on Columns

FROM   [CUBESales]

Output:

Qty Sold               Sales Rate

100                         1200

Now introduce another measure.

SELECT {[Measures].[Qty Sold],

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

       [Measures].[Calculated Sales Amout] on Rows

FROM   [CUBESales]

Output:

Executing the query ...

The Measures hierarchy already appears in the Axis0 axis.

Execution complete

Again showing error as we find that Same measures group or hierarchy must be in one Axis not multi Axis.

Here measure group or hierarchy named [Measures] exists in both Columns Axis and Rows Axis and causes the Error. To fix it we must provide them in same Axis and that is Columns Axis.

SELECT {[Measures].[Qty Sold],

        [Measures].[Sales Rate],

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

FROM   [CUBESales]

Output:

Qty Sold               Sales Rate           Calculated Sales Amout

100                         1200                       38000

Now try to introduce another Hierarchy on Rows or another Axis.

SELECT {[Measures].[Qty Sold],

        [Measures].[Sales Rate],

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

        [Dim Customer].[Customer Name] on Rows

FROM   [CUBESales]

Output:

                Qty Sold               Sales Rate           Calculated Sales Amout

All          100                         1200                       38000

Here it is showing ALL members details. To see individual customer wise Query.

SELECT {[Measures].[Qty Sold],

        [Measures].[Sales Rate],

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

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

FROM   [CUBESales]

Output:

                                          Qty Sold               Sales Rate           Calculated Sales Amout

Deblina Bhattacharya    20                           300                       3000

Priyo Bannerjee              80                           900                       35000

Now we try this and try to find the Error again

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:

Executing the query ...

Query (4, 9) Members, tuples or sets must use the same hierarchies in the  function.

Execution complete

Here it is showing Valid Error

{[Measures].[Qty Sold],

 [Measures].[Sales Rate],

 [Measures].[Calculated Sales Amout]}

It is a Perfect SETS as all the members came from Same Hierarchy named [Measures].

{[Dim Customer].[Customer Name].[Customer Name],

 [Dim Product].[Product Name].[Product Name]}

But it is NOT a SETS. As members named [Customer Name] came from hierarchy named [Customer name] but member named [Product Name] came from hierarchy named [Product Name]. So it is not a SETS and not used { …., ….}.

We must use (….., ……) as it is TUPLES. So we need to modify the 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]

Don’t Confuse

Please don’t confuse that the all the dimension is on one Axis and all the measures must be in another Axis. We can mixture them but we have to careful about SETS and TUPLES

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:

Analyzing Query:

We are trying to little analyze this Query.

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]

Members [Qty Sold] and members [Sales Rate] came from same hierarchy named [Measures] so they can form a SETS.

But members [Quarter name] came from hierarchy named [Quarter Name] so both the Hierarchy creates TUPLES here.

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

Posted by: MR. JOYDEEP DAS