Some Simple MDX queries

Multidimensional Expressions (MDX) lets you query multidimensional objects, such as cubes, and return multidimensional cellsets that contain the cube's data.


1. Simple MDX Query
select [Measures].[Sales Amount] on columns
,[Dim Product].[Colorkey].[Colorkey] on rows
from [Adventure Works DW]


2. Slicing /WHERE CLAUSE - To Slice Data from cube we can use Where clause , SALES OF HELMET
    select [Measures].[Sales Amount] on columns,
         [Dim Product].[Colorkey].[Colorkey] on rows
    from [Adventure Works DW]
        where [Dim Product].[Product Subcategory Key].&[31]

    OR We can USE "member" FUNCTION - In above query we use "[Dim Product].[Colorkey].[Colorkey]" in rows, where the third attribute in string represent the member of the color key. We can also omit the third attribute from string and we can use "member" instead of it.
    select [Measures].[Sales Amount] on columns,
        [Dim Product].[Colorkey].members on rows
    from [Adventure Works DW]
    where [Dim Product].[Product Subcategory Key].&[31]

3. Example from Snow flake schema:
    select [Measures].[Sales Amount] on columns,
        non empty  [Dim Product].[Hierarchy].[Product Category Key] on rows
    from [Adventure Works DW]

4. Apply Filtering on data using Filter function
    select [Measures].[Sales Amount] on columns
        --,[Dim Product].[Colorkey].[Colorkey] on rows
        ,filter([Dim Product].[Colorkey].[Colorkey].members,
        [Measures].[Sales Amount] >4000000) on rows
    from [Adventure Works DW]
5. Drill down  the cube dimension

    SELECT       {[Measures].[Sales Amount]} ON COLUMNS,
          {[Dim Product].[Product Category Key] } on rows
    FROM       [Adventure Works DW]

          --

    SELECT       {[Measures].[Sales Amount]} ON COLUMNS,
          DRILLDOWNLEVEL({[Dim Product].[Product Category Key] }) ON ROWS
    FROM      [Adventure Works DW]
         
          --
         
    SELECT       {[Measures].[Sales Amount]} ON COLUMNS,
          DRILLDOWNLEVEL({ [Dim Product].[Product Subcategory Key]}) ON ROWS
    FROM       [Adventure Works DW]