MDX

• MDX
• Multidimensional Expressions
• Proposed by Microsoft in 2000.
• Querying language for multidimensinal cubes developed using SSAS.

• Attribute
• Attribue is nothing but a Column.

• Members
• Every thing that comes under a particular attribute, Hierarcy or Level.
• Can be addressed by name or value.

• SET 
• Collection of Members from same hierarchy.
• {member1,member2,member3...}
• Ex: {[Dim Time].[Calender Year].&[2001],[Dim Time].[Calender Year].&[2002]}

• Tuple
• Collection of memebers from different hierarchies Or Collection of Sets.
• ([Dim Time].[Calender Year].&[2001] , [Dim Time].[English Month Name].&[March])

• Dimension

• Hierarchy
• Levels

• SELECT Syntax

• SELECT 

  SET/TUPLE/MEMBER ON COLUMNS/0/AXIS(0),

  SET/TUPLE/MEMBER ON ROWS/1/AXIS(1),

  .

  .

  .

  FROM <CUBE NAME>

  WHERE (<CONDITION>)

• Ex: SELECT FROM [AdventureWorks]
• Axis names (Max 128)
• Columns
• Rows
• pages
• sections
• Chapter

 Examples

----------------------------------------------------------------------------
-- Set Example
---------------------------------------------------------------------------- 

SELECT
    {
        [Time].[Time].[Year].&[2012]
        ,[Time].[Time].[Year].&[2013]
    } ON  COLUMNS   
    , [Measures].[Order Units] ON ROWS
FROM     [Sample]

----------------------------------------------------------------------------
-- Tuple Examples
---------------------------------------------------------------------------- 

 SELECT
    (
        {
            [Time].[Time].[Year].&[2012]
            ,[Time].[Time].[Year].&[2013]
        }
        ,[Product].[Business]
    ) ON  COLUMNS   
    , [Measures].[Order Units] ON ROWS
FROM     [Sample]

SELECT
    (
        [Time].[Time].[Year]
        ,[Measures].[Order Units]
    ) ON  COLUMNS   
    ,[Product].[Business]  ON ROWS
FROM     [Sample]

---------------------------------------------------------------------------- 

 -- Hiding Null Data
-- For Hiding Nulls we can use NON EMPTY() Function

 ---------------------------------------------------------------------------- 

SELECT
    NON EMPTY
    (
        (
            [Time].[Time].[Year]
            ,[Measures].[Order Units]
        )
    ) ON  COLUMNS   
    , [Product].[Global Product Line Code] ON ROWS
FROM     [Sample]

--------------------------------------------------------------------------- 

-- This will Hide columns with all null values
---------------------------------------------------------------------------

SELECT    
    NON EMPTY([Time].[Time].[Year]) ON  COLUMNS  
    ,[Measures].[Order Units] ON ROWS
FROM     [Sample]
 

---------------------------------------------------------------------------- 

-- This will hide all rows and column where data is null
----------------------------------------------------------------------------

SELECT   
    NON EMPTY
     (
        [Time].[Time].[Year]
     )ON  COLUMNS  
    ,NON EMPTY
     (
         {
            [Measures].[Order Units]
            ,[Measures].[Order Value]
         }
     ) ON ROWS
FROM     [Sample]

---------------------------------------------------------------------------- 
-- Memebers Function
-- Returns data in flatterened hierarchy, When used with hierarchy

----------------------------------------------------------------------------  

SELECT    
    NON EMPTY
     (
        [Product].[Product].MEMBERS
        ,[Time].[Time].[Year]
     ) ON  COLUMNS   
    ,NON EMPTY
     (
         {
            [Measures].[Order Units]
            ,[Measures].[Order Value]
         }
     ) ON ROWS
FROM     [Sample]

---------------------------------------------------------------------------- 
-- ALL Memebers Function
----------------------------------------------------------------------------  

SELECT    
    NON EMPTY
     (
        [Product].[Product].ALLMEMBERS,[Time].[Time].[Year]
     )ON  COLUMNS   
    ,NON EMPTY
     (
         {
            [Measures].[Order Units]
            ,[Measures].[Order Value]
         }
     ) ON ROWS
FROM     [Sample]


---------------------------------------------------------------------------- 
--  For Showing Two levels of an hierarcy on different axis use Attributes dirrectly
----------------------------------------------------------------------------  

SELECT    
     [Product].[Business].ALLMEMBERS on COLUMNS
    ,NON EMPTY
     (
         (
             [Product].[Product Line].MEMBERS
            ,{
                 [Measures].[Order Units]
                ,[Measures].[Order Value]
             }
         )
     ) ON ROWS
FROM     [Sample] 

---------------------------------------------------------------------------- 
--  Getting Cell Properties
----------------------------------------------------------------------------  

SELECT
    [Measures].[Order Units] ON COLUMNS
    FROM [Sample]
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

 For Viewing cell properties double click on cell.

---------------------------------------------------------------------------- 
--  Cross Join In MDX
----------------------------------------------------------------------------  

SELECT
    CrossJoin
    (
       
            [Time].[Time].[Year]
            ,[Plant].[Legal Area].Members
       
    ) ON COLUMNS
    ,[Measures].[Order Units] ON rows
FROM [Sample]

  SELECT
    CrossJoin
    (           
        [Time].[Time].Members
        ,[Plant].[Legal Area].Members       
    ) ON COLUMNS
    ,[Measures].[Order Units] ON rows
FROM [Sample]


---------------------------------------------------------------------------- 
--  Navigating Through Hierarchies
---------------------------------------------------------------------------- 


---------------------------------------------------------------------------- 
--  Navigating Through Hierarchies - Children Function
-- «Member».CHILDREN
---------------------------------------------------------------------------- 

SELECT             
        [Time].[Time].[Year].&[2012].Children     
     ON COLUMNS
    ,[Measures].[Order Units] ON rows
FROM [Sample]

---------------------------------------------------------------------------- 
--  Navigating Through Hierarchies - Parent Function
-- «Member».PARENT
---------------------------------------------------------------------------- 

SELECT             
        [Time].[Time].[Quarter].&[Q1 2001].Parent
     ON COLUMNS
    ,[Measures].[Order Units] ON rows
FROM [Sample] 

---------------------------------------------------------------------------- 
--  Navigating Through Hierarchies - Descendants Function
-- Can be used to get Childerns and Grand Childs.
----------------------------------------------------------------------------

SELECT 
      Descendants
      (           
        [Time].[Time].[Quarter].&[Q3 2001]
        ,[Time].[Time].[Month]
      )
     ON COLUMNS
    ,[Measures].[Order Units] ON rows
FROM [Sample] 

SELECT 
      Descendants
      (           
        [Time].[Time].[Year].&[2001]
        ,[Time].[Time].[Month]
      )
     ON COLUMNS
    ,[Measures].[Order Units] ON rows
FROM [sample]

---------------------------------------------------------------------------- 
--  Navigating Through Hierarchies - Ancestors Function
-- Can be used to get Parent and Grand Parent.
-- ANCESTOR( «Member», «Level» )
-- ANCESTORS( «Member», «Distance» )
---------------------------------------------------------------------------- 

SELECT 
      ANCESTORS
      (           
        [Time].[Time].[Month].&[196]
        , 1
      )
     ON COLUMNS
    ,[Measures].[Order Units] ON rows
FROM [sample]
     

SELECT 
      ANCESTORS
      (           
        [Time].[Time].[Month].&[196]
        , 2
      )
     ON COLUMNS
    ,[Measures].[Order Units] ON rows
FROM [sample]
    

---------------------------------------------------------------------------- 
--  Navigating Through Hierarchies - NextMember/PrevMember Functions
--  «Member».NEXTMEMBER
-- «Member».PREVMEMBER
---------------------------------------------------------------------------- 

SELECT             
       [Time].[Time].[Year].&[2004].NextMember
     ON COLUMNS
    ,[Measures].[Order Units] ON rows
FROM [sample] 

SELECT             
       [Time].[Time].[Year].&[2004].PrevMember
     ON COLUMNS
    ,[Measures].[Order Units] ON rows
FROM [sop]

 

---------------------------------------------------------------------------- 
--  Sorting Data in MDX Query
--  Order() Function
----------------------------------------------------------------------------