JDBC Driver for SAP Business Warehouse

Build 26.0.9655

Query Mapping

When QueryPassthrough is set to false (default), the driver translates SQL queries into MDX (Multidimensional Expressions) for execution against SAP Business Warehouse. 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,
    NON EMPTY
    SUBSET(
        CROSSJOIN(
            [Customer].[Customer Geography].[City].ALLMEMBERS,
            [Customer].[Customer Geography].[Country].ALLMEMBERS
        ),
        0, 1000000
    )
    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,
    NON EMPTY
    SUBSET(
        [Customer].[Education].[Education].ALLMEMBERS,
        0, 1000000
    )
    PROPERTIES MEMBER_CAPTION ON 1
FROM [Adventure Works]
CELL PROPERTIES VALUE

Note: When querying only dimension columns, the driver 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 driver 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,
    NON EMPTY
    SUBSET(
        ADDCALCULATEDMEMBERS(
            DESCENDANTS( 
                { [Customer].[Customer Geography].[Country].&[United States] },
                [Customer].[Customer Geography].[City],
                LEAVES
            )
        ),
        0, 1000000
    )
    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,
    NON EMPTY
    SUBSET(
        [Customer].[Education].[Education].ALLMEMBERS,
        0, 1000000
    )
    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,
    NON EMPTY
    SUBSET(
        [Customer].[Education].[Education].ALLMEMBERS,
        0, 1000000
    )
    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,
    NON EMPTY
    SUBSET(
        ADDCALCULATEDMEMBERS(
            DESCENDANTS( 
                { [Customer].[Customer Geography].[Country].ALLMEMBERS },
                [Customer].[Customer Geography].[City],
                LEAVES
            )
        ),
        0, 1000000
    )
    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,
    NON EMPTY
    SUBSET(
        CROSSJOIN(
            CROSSJOIN(
                ADDCALCULATEDMEMBERS(
                    DESCENDANTS( 
                        { [Customer].[Customer Geography].[Country].ALLMEMBERS },
                        [Customer].[Customer Geography].[City],
                        LEAVES
                    )
                ),
                [Customer].[Gender].[Gender].ALLMEMBERS
            ),
            [Product].[Class].[Class].ALLMEMBERS
        ),
        0, 1000000
    )
    PROPERTIES MEMBER_CAPTION ON 1
FROM [Adventure Works]
CELL PROPERTIES VALUE

Many BI tools require explicit JOIN syntax with an ON clause. The driver 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,
    NON EMPTY
    SUBSET(
        [Customer].[Customer Geography].[Country].ALLMEMBERS,
        0, 1000000
    )
    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,
    NON EMPTY
    SUBSET(
        { [Customer].[Customer Geography].[Country].&[Australia],
          [Customer].[Customer Geography].[Country].&[United States] },
        0, 1000000
    )
    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,
    NON EMPTY
    SUBSET(
        ORDER(
            [Customer].[Education].[Education].ALLMEMBERS,
            [Customer].[Education].CurrentMember.MEMBER_CAPTION, BASC
        ),
        0, 1000000
    )
    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,
    NON EMPTY
    SUBSET(
        ORDER(
            ADDCALCULATEDMEMBERS(
                DESCENDANTS( 
                    {
                        [Customer].[Customer Geography].[Country].ALLMEMBERS
                    },
                    [Customer].[Customer Geography].[City],
                    LEAVES
                )
            ),
            [Measures].[Customer Count], BASC
        ),
        0, 1000000
    )
    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. SAP BW requires both start_index and count to be supplied as literal integers. When a query specifies neither LIMIT nor OFFSET, the driver uses defaults of 0 and 1000000 so that the SUBSET signature remains complete.

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,
    NON EMPTY
    SUBSET(
        ADDCALCULATEDMEMBERS(
            DESCENDANTS( 
                { [Customer].[Customer Geography].[Country].&[United States] },
                [Customer].[Customer Geography].[City],
                LEAVES
            )
        ),
        10, 20
    )
    PROPERTIES MEMBER_CAPTION ON 1
FROM [Adventure Works]
CELL PROPERTIES VALUE

The SUBSET function signature is SUBSET(set, start_index, count). SAP BW does not accept the two-argument form, so the driver always emits both arguments -- substituting the OFFSET and LIMIT values from the SQL query, or falling back to 0, 1000000 when one or both are omitted.

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,
    NON EMPTY
    SUBSET(
        [Customer].[Customer Geography].[Country].ALLMEMBERS,
        0, 1000000
    )
    PROPERTIES MEMBER_CAPTION ON 1
FROM [Adventure Works]
CELL PROPERTIES VALUE

HAVING Clause

HAVING conditions on aggregated results are translated to the MDX HAVING clause applied to the axis. The condition can also be combined with FILTER for more complex predicates.

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,
    NON EMPTY
    SUBSET(
        ADDCALCULATEDMEMBERS(
            DESCENDANTS( 
                {
                    [Customer].[Customer Geography].[Country].&[Australia]
                },
                [Customer].[Customer Geography].[City],
                LEAVES
            )
        ),
        0, 1000000
    )
    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,
    NON EMPTY
    SUBSET(
        [Customer].[Customer Geography].[Country].ALLMEMBERS,
        0, 1000000
    )
    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 driver 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,
    NON EMPTY
    SUBSET(
        [Customer].[Customer Geography].[Country].ALLMEMBERS,
        0, 1000000
    )
    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)

SAP VARIABLES

The SAP VARIABLES clause is a SAP BW-specific extension to MDX. Variables defined at the cube level allow dynamic parameterization of queries. The driver exposes these variables through a virtual SAPVARIABLES table that can be joined into a query, and translates SQL filter predicates on that table into the SAP VARIABLES clause of the generated MDX.

SQL QueryMDX Query

SELECT [Level]
FROM [Dimension]
JOIN SAPVARIABLES s
WHERE s.[Lifecycle Status] = 'closed'

SAP VARIABLES [!V000001] INCLUDING [2CSEPM_ISOLCSTTST].[C]
// Interval selection type 
SELECT [Level]
FROM [Dimension]
JOIN SAPVARIABLES s
WHERE s.[Lifecycle Status] IN ('Closed', 'New')

SAP VARIABLES [!V000001] INCLUDING [2CSEPM_ISOLCSTTST].[C]:[2CSEPM_ISOLCSTTST].[N]

The MDX form depends on the variable's VariableSelectionType. The driver translates SQL captions to the underlying variable names and member keys automatically. See Retrieving SAP BW Data for a full description of the SAPVARIABLES virtual table.

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