Core ideas for MDX select queries

MDX and TSQL are very different, the core idea of the MDX Select query statement is to slice the cube, and the aggregated value of the computed measure of the cube after the cut is the result of the query. Each slice cuts the cube to exclude members that do not meet the slicing criteria.

The schema of cube is very simple, with only two dimension and one measure Group.

1, an MDX statement that uses nested queries

SELECTNON EMPTY {[Measures].[Fact Product Inventory Count],[Measures].[Unit Cost]} onCOLUMNS from  (     SELECT( {[Dim Date].[Date Hierarchy].[Date Key].&[20050702]} ) onCOLUMNS from     (         SELECT( {[Dim Date].[Month number of year].&[7]} ) onCOLUMNS from         (             SELECT( {[Dim Product].[Product Hierarchy].[Product Category Key].&[1]} ) onCOLUMNS from [Adventure Works DW2012]        )    )) 

The role of a nested select is to slice the cube from the top to the outermost, in turn, by filtering out the members of product Category Key [1] in Cube, followed by [Dim Date]. [Month number of year]=[7], and then [Dim Date]. [Date Hierarchy]. [Date key]=[20050702], finally, the cube is filtered three times, and the remaining members are evaluated [Measures]. [Fact Product Inventory Count] and [Measures]. The aggregate value of [Unit cost] is the result of the query.

2, since MDX slices with a nested SELECT clause, you can convert to a WHERE clause and use the WHERE clause to slice the cube.

SELECTNON EMPTY {[Measures].[Fact Product Inventory Count],[Measures].[Unit Cost]} onCOLUMNS from [Adventure Works DW2012]WHERE([Dim Product].[Product Hierarchy].[Product Category Key].&[1],         [Dim Date].[Month number of year].&[7],         [Dim Date].[Date Hierarchy].[Date Key].&[20050702])

3, slicing through rows

SELECTNON EMPTY {[Measures].[Fact Product Inventory Count],[Measures].[Unit Cost]} onCOLUMNS, {([Dim Product].[Product Hierarchy].[Product Category Key].&[1],         [Dim Date].[Month number of year].&[7],         [Dim Date].[Date Hierarchy].[Date Key].&[20050702])} onrows from [Adventure Works DW2012]

Core ideas for MDX select queries