ADO.NET Provider for Microsoft SQL Server Analysis Services

Build 26.0.9655

MDX Query Mapping

When QueryMode is set to MDX, the provider translates SQL queries into MDX (Multidimensional Expressions) for execution against OLAP cube models. This page is a comprehensive guide to how SQL constructs map to MDX queries.

SELECT Queries

The SELECT statement maps to different MDX structures depending on whether the query targets dimensions (hierarchies) or measures. Dimension-only queries use a placeholder measure, while measure queries place values on the columns axis (axis 0).

SQL QueryMDX Query

SELECT * FROM [Adventure Works].Measures

SELECT
    {
        [Measures].[Average Sales Amount],
        [Measures].[Customer Count],
        ...
    } ON 0
FROM [Adventure Works]
CELL PROPERTIES VALUE

SELECT [City], [Country]
FROM [Adventure Works].Customer

WITH MEMBER [Measures].[Microsoft.Mashup.Engine.One] AS 1

SELECT
    { [Measures].[Microsoft.Mashup.Engine.One] } ON 0,
    SUBSET(
        NONEMPTY(
            CROSSJOIN(
                [Customer].[Customer Geography].[City].ALLMEMBERS,
                [Customer].[Customer Geography].[Country].ALLMEMBERS
            ),
            { [Measures].[Microsoft.Mashup.Engine.One] }
        )
    ,@__CDATA_OFFSET)
    PROPERTIES MEMBER_CAPTION ON 1
FROM [Adventure Works]
CELL PROPERTIES VALUE

SELECT [Education]
FROM [Adventure Works].Customer

WITH MEMBER [Measures].[Microsoft.Mashup.Engine.One] AS 1

SELECT
    { [Measures].[Microsoft.Mashup.Engine.One] } ON 0,
    SUBSET(
        NONEMPTY(
            [Customer].[Education].[Education].ALLMEMBERS,
            { [Measures].[Microsoft.Mashup.Engine.One] }
        )
    ,@__CDATA_OFFSET)
    PROPERTIES MEMBER_CAPTION ON 1
FROM [Adventure Works]
CELL PROPERTIES VALUE

Note: When querying only dimension columns, the provider creates a placeholder measure ([Microsoft.Mashup.Engine.One]) to enable row generation. The ALLMEMBERS function returns all members at the specified hierarchy level.

WHERE Filters

WHERE conditions are translated to MDX member references or slicer axis (WHERE clause) filters. The provider uses member key notation (.&[value]) for precise member matching.

SQL QueryMDX Query

SELECT [City]
FROM [Adventure Works].Customer
WHERE [Country] = 'United States'

WITH MEMBER [Measures].[Microsoft.Mashup.Engine.One] AS 1

SELECT
    { [Measures].[Microsoft.Mashup.Engine.One] } ON 0,
    SUBSET(
        NONEMPTY(
            ADDCALCULATEDMEMBERS(
                DESCENDANTS( 
                    { [Customer].[Customer Geography].[Country].&[United States] },
                    [Customer].[Customer Geography].[City],
                    LEAVES
                )
            ),
            { [Measures].[Microsoft.Mashup.Engine.One] }
        )
    ,@__CDATA_OFFSET)
    PROPERTIES MEMBER_CAPTION ON 1
FROM [Adventure Works]
CELL PROPERTIES VALUE

SELECT [Education]
FROM [Adventure Works].Customer
WHERE [Country] IN ('Australia', 'United States')

WITH MEMBER [Measures].[Microsoft.Mashup.Engine.One] AS 1

SELECT
    { [Measures].[Microsoft.Mashup.Engine.One] } ON 0,
    SUBSET(
        NONEMPTY(
            [Customer].[Education].[Education].ALLMEMBERS,
            { [Measures].[Microsoft.Mashup.Engine.One] }
        )
    ,@__CDATA_OFFSET)
    PROPERTIES MEMBER_CAPTION ON 1
FROM [Adventure Works]
WHERE
    {
        [Customer].[Customer Geography].[Country].&[Australia],
        [Customer].[Customer Geography].[Country].&[United States]
    }
CELL PROPERTIES VALUE

SELECT [Education]
FROM [Adventure Works].Customer
WHERE [Country] = 'Australia'
  AND [Gender] = 'Male'

WITH MEMBER [Measures].[Microsoft.Mashup.Engine.One] AS 1

SELECT
    { [Measures].[Microsoft.Mashup.Engine.One] } ON 0,
    SUBSET(
        NONEMPTY(
            [Customer].[Education].[Education].ALLMEMBERS,
            { [Measures].[Microsoft.Mashup.Engine.One] }
        ),
        @__CDATA_OFFSET)
    PROPERTIES MEMBER_CAPTION ON 1
FROM [Adventure Works]
WHERE
    CROSSJOIN(
        { [Customer].[Customer Geography].[Country].&[Australia] },
        { [Customer].[Gender].&[Male] }
    )
CELL PROPERTIES VALUE

Note: Filters on different dimensions use CROSSJOIN in the MDX WHERE clause to create an AND condition.

Measure Queries

In MDX mode, pre-defined cube measures are exposed in a virtual Measures table. Querying measures with dimensions uses CROSSJOIN on axis 1 (rows) while measures appear on axis 0 (columns).

SQL QueryMDX Query

SELECT m.[Customer Count]
FROM [Adventure Works].Measures AS m

SELECT
    { [Measures].[Customer Count] } ON 0
FROM [Adventure Works]
CELL PROPERTIES VALUE

SELECT c.[City], c.[Country], m.[Customer Count]
FROM [Adventure Works].Customer AS c
INNER JOIN [Adventure Works].Measures AS m

SELECT
    { [Measures].[Customer Count] } ON 0,
    SUBSET(
        NONEMPTY(
                ADDCALCULATEDMEMBERS(
                    DESCENDANTS( 
                        { [Customer].[Customer Geography].[Country].ALLMEMBERS },
                        [Customer].[Customer Geography].[City],
                        LEAVES
                    )
                ),
            { [Measures].[Customer Count] }
        ),
        @__CDATA_OFFSET)
    PROPERTIES MEMBER_CAPTION ON 1
FROM [Adventure Works]
CELL PROPERTIES VALUE

SELECT c.[City], c.[Gender], p.[Class],
       m.[Average Sales Amount], m.[Average Unit Price]
FROM [Adventure Works].Customer AS c
INNER JOIN [Adventure Works].Product AS p
INNER JOIN [Adventure Works].Measures AS m

SELECT
    {
        [Measures].[Average Sales Amount],
        [Measures].[Average Unit Price]
    } ON 0,
    SUBSET(
        NONEMPTY(
            CROSSJOIN(
                CROSSJOIN(
                    ADDCALCULATEDMEMBERS(
                        DESCENDANTS( 
                            { [Customer].[Customer Geography].[Country].ALLMEMBERS },
                            [Customer].[Customer Geography].[City],
                            LEAVES
                        )
                    ),
                    [Customer].[Gender].[Gender].ALLMEMBERS
                ),
                [Product].[Class].[Class].ALLMEMBERS
            ),
            { [Measures].[Average Sales Amount],
              [Measures].[Average Unit Price] }
        ),
        @__CDATA_OFFSET)
    PROPERTIES MEMBER_CAPTION ON 1
FROM [Adventure Works]
CELL PROPERTIES VALUE

Many BI tools require explicit JOIN syntax with an ON clause. The provider adds a virtual MeasuresId column to every table for this purpose. An INNER JOIN on MeasuresId is treated identically to a CROSS JOIN with the Measures table:

SQL QueryMDX Query

SELECT c.[Country], m.[Customer Count]
FROM [Adventure Works].Customer AS c
INNER JOIN [Adventure Works].Measures AS m
  ON c.MeasuresId = m.MeasuresId

SELECT
    { [Measures].[Customer Count] } ON 0,
    SUBSET(
        NONEMPTY(
            [Customer].[Customer Geography].[Country].ALLMEMBERS,
            { [Measures].[Customer Count] }
        ),
        @__CDATA_OFFSET)
    PROPERTIES MEMBER_CAPTION ON 1
FROM [Adventure Works]
CELL PROPERTIES VALUE

Aggregations and GROUP BY

SQL aggregate functions are translated to MDX calculated members using the WITH MEMBER clause. GROUP BY translates to selecting members at a specific hierarchy level on axis 1.

SQL QueryMDX Query

SELECT MAX(m.[Customer Count]) AS mymax
FROM [Adventure Works].Customer AS c
INNER JOIN [Adventure Works].Measures AS m

WITH MEMBER Measures.[mymax] AS ([Measures].[Customer Count])

SELECT
    {
        [Measures].[mymax]
    } ON 0
FROM [Adventure Works]
CELL PROPERTIES VALUE

SELECT SUM(m.[Customer Count]) AS sum
FROM [Adventure Works].Customer AS c
INNER JOIN [Adventure Works].Measures AS m
WHERE c.[Country] IN ('United States', 'Australia')
GROUP BY c.[Country]

WITH MEMBER Measures.[sum] AS ([Measures].[Customer Count])

SELECT
    { [Measures].[sum] } ON 0,
    SUBSET(
        NONEMPTY(
            { [Customer].[Customer Geography].[Country].&[Australia],
              [Customer].[Customer Geography].[Country].&[United States] },
            { [Measures].[sum] }
        ),
        @__CDATA_OFFSET)
    PROPERTIES MEMBER_CAPTION ON 1
FROM [Adventure Works]
CELL PROPERTIES VALUE

SELECT COUNT(c.[Country]) AS count
FROM [Adventure Works].Customer AS c

WITH MEMBER Measures.[count] AS COUNT (
    ADDCALCULATEDMEMBERS(
        DESCENDANTS( 
            { [Customer].[Customer Geography].CurrentMember },
            [Customer].[Customer Geography].[Country],
            SELF
        )
    )
)

SELECT
    { [Measures].[count] } ON 0
FROM [Adventure Works]
CELL PROPERTIES VALUE

SELECT COUNT(c.[City]) AS count
FROM [Adventure Works].Customer AS c
WHERE c.Country = 'Australia'

WITH MEMBER Measures.[count] AS COUNT (
    ADDCALCULATEDMEMBERS(
        DESCENDANTS( 
            { [Customer].[Customer Geography].[Country].&[Australia] },
            [Customer].[Customer Geography].[City],
            SELF
        )
    )
)

SELECT
    { [Measures].[count] } ON 0
FROM [Adventure Works]
CELL PROPERTIES VALUE

ORDER BY

ORDER BY is translated to the MDX ORDER function, which wraps the set being sorted. Dimension columns sort by MEMBER_CAPTION, while measure columns sort by value.

SQL QueryMDX Query

SELECT [Education]
FROM [Adventure Works].Customer
ORDER BY [Education] ASC

WITH MEMBER [Measures].[Microsoft.Mashup.Engine.One] AS 1

SELECT
    { [Measures].[Microsoft.Mashup.Engine.One] } ON 0,
    SUBSET(
        ORDER(
            NONEMPTY(
                [Customer].[Education].[Education].ALLMEMBERS,
                { [Measures].[Microsoft.Mashup.Engine.One] }
            ),
            [Customer].[Education].CurrentMember.MEMBER_CAPTION, BASC
        )
    ,@__CDATA_OFFSET)
    PROPERTIES MEMBER_CAPTION ON 1
FROM [Adventure Works]
CELL PROPERTIES VALUE

SELECT c.[City], c.[Country], m.[Customer Count]
FROM [Adventure Works].Customer AS c
INNER JOIN [Adventure Works].Measures AS m
WHERE m.[Customer Count] > 0
ORDER BY m.[Customer Count]

SELECT
    {
        [Measures].[Customer Count]
    } ON 0,
    SUBSET(
        ORDER(
            NONEMPTY(
                ADDCALCULATEDMEMBERS(
                    DESCENDANTS( 
                        {
                            [Customer].[Customer Geography].[Country].ALLMEMBERS
                        },
                        [Customer].[Customer Geography].[City],
                        LEAVES
                    )
                ),
                {
                    [Measures].[Customer Count]
                }
            ),
            [Measures].[Customer Count], BASC
        )
    ,@__CDATA_OFFSET)
    HAVING [Measures].[Customer Count] > 0        PROPERTIES MEMBER_CAPTION ON 1
FROM [Adventure Works]
CELL PROPERTIES VALUE

The ORDER function uses these sort flags:

BASCBreak-Ascending (preserves hierarchy structure, ascending)
BDESCBreak-Descending (preserves hierarchy structure, descending)

LIMIT and OFFSET

Pagination is handled by the MDX SUBSET function, which extracts a subset of members starting at a given offset.

SQL QueryMDX Query

SELECT [City], [Country]
FROM [Adventure Works].Customer
WHERE [Country] = 'United States'
LIMIT 20 OFFSET 10

WITH MEMBER [Measures].[Microsoft.Mashup.Engine.One] AS 1

SELECT
    { [Measures].[Microsoft.Mashup.Engine.One] } ON 0,
    SUBSET(
        NONEMPTY(
            ADDCALCULATEDMEMBERS(
                DESCENDANTS( 
                    { [Customer].[Customer Geography].[Country].&[United States] },
                    [Customer].[Customer Geography].[City],
                    LEAVES
                )
            ),
            { [Measures].[Microsoft.Mashup.Engine.One] }
        )
    ,@__CDATA_OFFSET,@__CDATA_LIMIT)
    PROPERTIES MEMBER_CAPTION ON 1
FROM [Adventure Works]
CELL PROPERTIES VALUE

The SUBSET function signature is SUBSET(set, start_index, [count]). The @__CDATA_OFFSET and @__CDATA_LIMIT parameters are replaced at execution time.

CASE Expressions

SQL CASE expressions translate directly to MDX CASE syntax within a calculated member definition.

SQL QueryMDX Query

SELECT CASE [Country]
    WHEN 'United States' THEN 'States'
    ELSE 'Not States'
END AS StatesOrNoStates
FROM [Adventure Works].Customer

WITH MEMBER Measures.[StatesOrNoStates] AS 
  CASE [Customer].[Customer Geography].CurrentMember.MEMBER_CAPTION 
  WHEN  'United States' THEN 'States' 
  ELSE  'Not States' 
  END 

SELECT
    { [Measures].[StatesOrNoStates] } ON 0,
    SUBSET(
        NONEMPTY(
            [Customer].[Customer Geography].[Country].ALLMEMBERS,
            { [Measures].[StatesOrNoStates] }
        ),
        @__CDATA_OFFSET)
    PROPERTIES MEMBER_CAPTION ON 1
FROM [Adventure Works]
CELL PROPERTIES VALUE

HAVING Clause

HAVING conditions on aggregated results are translated to MDX FILTER or applied through NONEMPTY with measure thresholds.

SQL QueryMDX Query

SELECT m.[Customer Count]
FROM [Adventure Works].Customer AS c
INNER JOIN [Adventure Works].Measures AS m
WHERE m.[Customer Count] > 100
  AND c.[Country] = 'Australia'
GROUP BY c.[City]

SELECT
    {
        [Measures].[Customer Count]
    } ON 0,
    SUBSET(
        NONEMPTY(
            ADDCALCULATEDMEMBERS(
                DESCENDANTS( 
                    {
                        [Customer].[Customer Geography].[Country].&[Australia]
                    },
                    [Customer].[Customer Geography].[City],
                    LEAVES
                )
            ),
            {
                [Measures].[Customer Count]
            }
        )
    ,@__CDATA_OFFSET)
    HAVING [Measures].[Customer Count] > 100        PROPERTIES MEMBER_CAPTION ON 1
FROM [Adventure Works]
CELL PROPERTIES VALUE

DISTINCT

A DISTINCT projection on dimension columns naturally maps to selecting all members at the requested hierarchy level. MDX returns one row per member at a level, so no explicit deduplication is required.

SQL QueryMDX Query

SELECT DISTINCT [Country]
FROM [Adventure Works].Customer

WITH MEMBER [Measures].[Microsoft.Mashup.Engine.One] AS 1

SELECT
    { [Measures].[Microsoft.Mashup.Engine.One] } ON 0,
    SUBSET(
        NONEMPTY(
            [Customer].[Customer Geography].[Country].ALLMEMBERS,
            { [Measures].[Microsoft.Mashup.Engine.One] }
        )
    ,@__CDATA_OFFSET)
    PROPERTIES MEMBER_CAPTION ON 1
FROM [Adventure Works]
CELL PROPERTIES VALUE

COUNT(DISTINCT)

COUNT(DISTINCT) on a dimension column is mapped to a calculated measure that counts members at that hierarchy level using DESCENDANTS with the SELF flag.

SQL QueryMDX Query

SELECT COUNT(DISTINCT c.[City]) AS citycount,
       COUNT(DISTINCT c.[Country]) AS countrycount
FROM [Adventure Works].Customer AS c

WITH MEMBER Measures.[citycount] AS COUNT (
    ADDCALCULATEDMEMBERS(
        DESCENDANTS( 
            { [Customer].[Customer Geography].CurrentMember },
            [Customer].[Customer Geography].[City],
            SELF
        )
    )
)
MEMBER Measures.[countrycount] AS COUNT (
    ADDCALCULATEDMEMBERS(
        DESCENDANTS( 
            { [Customer].[Customer Geography].CurrentMember },
            [Customer].[Customer Geography].[Country],
            SELF
        )
    )
)

SELECT
    { [Measures].[citycount], [Measures].[countrycount] } ON 0
FROM [Adventure Works]
CELL PROPERTIES VALUE

LIKE Filters

LIKE filters are not natively supported in MDX. The provider retrieves all members at the relevant hierarchy level (using ALLMEMBERS) and applies the LIKE pattern on the client side.

SQL QueryMDX Query

SELECT [Country]
FROM [Adventure Works].Customer
WHERE [Country] LIKE 'United%'

WITH MEMBER [Measures].[Microsoft.Mashup.Engine.One] AS 1

SELECT
    { [Measures].[Microsoft.Mashup.Engine.One] } ON 0,
    SUBSET(
        NONEMPTY(
            [Customer].[Customer Geography].[Country].ALLMEMBERS,
            { [Measures].[Microsoft.Mashup.Engine.One] }
        )
    ,@__CDATA_OFFSET)
    PROPERTIES MEMBER_CAPTION ON 1
FROM [Adventure Works]
CELL PROPERTIES VALUE

MDX Function Reference

The following table summarizes key MDX functions used in query translation:

MDX FunctionPurpose
CROSSJOIN(set1, set2)Cartesian product of two sets
NONEMPTY(set, [measure_set])Removes tuples with empty cells
SUBSET(set, start, [count])Returns subset for pagination (LIMIT/OFFSET)
ORDER(set, expression, flag)Sorts set by expression (ORDER BY)
FILTER(set, condition)Filters set by boolean condition (WHERE)
DESCENDANTS(member, level, flag)Navigates hierarchy to descendant level
ADDCALCULATEDMEMBERS(set)Includes calculated members in result
ALLMEMBERSReturns all members at hierarchy level
COUNT(set)Counts members in set
WITH MEMBERDefines calculated measure (aggregate functions)

Hierarchy Navigation

MDX uses DESCENDANTS to navigate parent-child relationships in hierarchies:

DESCENDANTS FlagBehavior
LEAVESReturns leaf-level members (default for dimension queries)
SELFReturns members at specified level only (for COUNT)

Copyright (c) 2026 CData Software, Inc. - All rights reserved.
Build 26.0.9655