MDX

Definition
MDX (multi-dimensional expressions) is a language developed by Microsoft for queries using multi-dimensional data. An MDX expression returns a multi-dimensional result set (dataset) that consists of axis data and cell data.

The syntax of multi-dimensional expressions is defined in the Microsoft specification OLE DB for OLAP.

Structure

The following table gives an overview of the most important components of MDX statements:

MDX Component	Significance
FROM	Cube selection
SELECT ON COLUMNS ON ROWS	Axes definition Definition of column axes Definition of rows axes
WHERE	Filter conditions definition

Example 1:

The following example of a MDX request contains the above elements:

SELECT
{ [Measures].[CKF_SI_PROFIT],
[Measures].[0D_DOCUMENT],
[Measures].[0D_OORVALSC]
} ON COLUMNS,
NON EMPTY
[0D_PLANT].MEMBERS
ON ROWS
FROM [0D_SD_C03/SAP_DEMO_OLEDB]
WHERE
( [0CALMONTH].[200101],
[0D_COUNTRY].[US] )

The following graphic shows this example and the result:















Examples for basic modules

Entity	Example
Member (characteristic value)	[City].[Las Vegas], [Time].[Year].[2003]
Tuple (several characteristic values, various characteristics)	([City].[Las Vegas],[Time].[Year].[2003],[Event].[SAP TechEd 2003])
Set (several tuples or member)	{([City].[Los Angeles],[Time].[Year].[2002]),([City].[Las Vegas],[Time].[Year].[2003]),}
Value (number, characters)	5, “red”

Examples for expressions that generate the entities named above

Expression	Example
Member as result	.PARENT
Set as result	TOPCOUNT(,,)
Number as result	MAX([, ])

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

Example 2:

 

Result set from a default hierarchy query.

Now, switch to MDX mode and modify the query so that “All” members are included. Instead of selecting just the members from the leaf level, modify the MDX statement to select all members from the entire hierarchy.

SELECT

NON EMPTY {[Measures].[1MEHY2C418T0QYBP2KX9KQBG6]} ON COLUMNS,

NON EMPTY {[0D_COUNTRY PM_COUNTRY].ALLMEMBERS}

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

FROM [0D_PU_C01/ZD_PU_C01_VAR1] CELL PROPERTIES VALUE

The following example of an MDX statement shows how to drill down twice from level 1, to obtain all the members from level 1 down to level 3.

SELECT

NON EMPTY {[Measures].[57FQA4HFVVTQPYLPVV4RP673N] } ON COLUMNS,

NON EMPTY {DRILLDOWNLEVEL(DRILLDOWNLEVEL([0D_DBSIC1

BRANCHE SIC_HIERARCHIE].[LEVEL01].ALLMEMBERS))}

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

FROM [0D_DX_C02/Z_DX_TEST] CELL PROPERTIES VALUE

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

I got an error in this environment :
-SAP BW 7.01 (with SAP GUI client)
-SAP BusinessObjects XI R3.1
-SAP IK 3.1
-Windows server 2008 64bit (App server)
-SAP JCo 3.05

In Designer I have created a Universe based on BW Infocube and when I open my infoview and execute a document with that universe, I have this error:
[...] MDX with error boot error syntactic analyzer: timeout during allocate / cpic-call: ThSAPCRMCV'. (WIS 10901)

This appear only when I use more than one dimension.
I tried to change the librfc32.dll in SysWow64 with last release but I have an error with rfcsvr32 command to register the dll.
In my environment variables have:
PATH:C:\Windows\SysWOW64 (for librfc32.dll)
CLASSPATH:\sapjco3.jar