Quick help in creating MDX statement

[Former Member]

Introduction

MDX (multi-dimensional expressions) is a language developed by Microsoft for queries using multi-dimensional data. In this weblog i am going to present a help guide for creating MDX statements for extracting data from BW infocubes or queries.

BW provides MDXTEST transaction which can be used to quickly build MDX statement against infocubes or queries. If you click on "Generate Default MDX Statement" then the transaction automatically generates the MDX template.

If you are generating a MDX statement against query then make sure you check the attribute "Release for OLE DB for OLAP" in query designer.

Let's talk about where clause of MDX. In MDX the where clause is used to filter data and this where you specify the slicer condition to slice the data.
If you have more than one slicer condition then you can need use "()" or "{}" for container and "," to separate the conditions.

Example1: In the following example i am retricting the data to US and fiscal period 1 of 2003.

SELECT [Measures].MEMBERS ON AXIS(0) , NON EMPTY [0PRODUCT].MEMBERS * [0BPARTNER].MEMBERS * [0COUNTRY].MEMBERS * [0REGION].MEMBERS ON AXIS(1)FROM [US_PH_SEM/US_BIOPHARMA_SCORECARD_QUERY1]where {[0COUNTRY].[US],[0FISCPER].[K42003001]}

In MDX statement there is no wild search (*, %..) but you can come close to doing a wild search by specify a range in the slicer condition.

Example2: In the following example i am retricting the data to US and fiscal period 1 of 2003 to fiscal period 12 of 2003.

SELECT [Measures].MEMBERS ON AXIS(0) , NON EMPTY [0PRODUCT].MEMBERS * [0BPARTNER].MEMBERS * [0COUNTRY].MEMBERS * [0REGION].MEMBERS ON AXIS(1)FROM [US_PH_SEM/US_BIOPHARMA_SCORECARD_QUERY1]where {[0COUNTRY].[All],[0FISCPER].[K42003001]:[0FISCPER].[K42003012]}

Now let us talk about SAP variables in the MDX statement. Variables are used to supply variable values for all the variables of a query.

Example3: The following example shows you how to pass a individual value and a range of values.

SELECT [Measures].MEMBERS ON AXIS(0) , NON EMPTY [0FUND].MEMBERS ON AXIS(1)FROM [Zxxxtest/ZXxxPR] SAP VARIABLES  [ZBUS_ARE] INCLUDING [0FUNDS_CTR__0BUS_AREA].[62] [0S_FYEAR] INCLUDING [0FISCYEAR].[K42004] [ZFMOPPLN] INCLUDING [0BUD_VERSN].[010]:[0BUD_VERSN][020]
Note: The same query can also be written the following way.
SELECT [Measures].MEMBERS ON AXIS(0) , NON EMPTY [0FUND].MEMBERS ON AXIS(1)FROM [Zxxxtest/ZXxxPR] SAP VARIABLES  [ZBUS_ARE] INCLUDING "62" [0S_FYEAR] INCLUDING "4/2004" [ZFMOPPLN] INCLUDING "010":"020"

You can also use the variables to exclude the data.

Example4: The following example shows you how to pass a individual value and a range of values to exclude data.

SELECT [Measures].MEMBERS ON AXIS(0) , NON EMPTY [0FUND].MEMBERS ON AXIS(1)FROM [Zxxxtest/ZXxxPR] SAP VARIABLES  [ZBUS_ARE] INCLUDING [0FUNDS_CTR__0BUS_AREA].[62] [0S_FYEAR] EXCLUDING [0FISCYEAR].[K42004] [ZFMOPPLN] EXCLUDING [0BUD_VERSN].[010]:[0BUD_VERSN][020]
Note: The same query can also be written the following way.
SELECT [Measures].MEMBERS ON AXIS(0) , NON EMPTY [0FUND].MEMBERS ON AXIS(1)FROM [Zxxxtest/ZXxxPR] SAP VARIABLES  [ZBUS_ARE] INCLUDING "62" [0S_FYEAR] INCLUDING "4/2004" [ZFMOPPLN] EXCLUDING "010":"020"