View Source

This short guide shows the structure of a simple BW query and presents an example.

Structure of a BW query

A BW query is akin to a regular SQL query, with a few peculiarities. Indeed, the columns that need to be queried can be Key Figures (or Measures), SAP Variables, Columns and their Properties. Those columns need to be defined separately in the query. Some conditions can also be applied on SAP variables (and other types of variables?). All those column names must be referred to by their Technical Names, found via the SAP Logon software (see Retrieving columns and variables names for a BW query).

The following generic query CAT_NAME/CUBE_NAME extracts the key values MEASURE_1, MEASURE_2, MEASURE_3 and the columns COLUMN_1 (and its property PROPERTY_1), COLUMN_2 (and its properties PROPERTY_2 and PROPERTY_3) and COLUMN_3. It filters the results on the SAP variables VARIABLE_1 that must be equal to VALUE_1 and VARIABLE_2 that must be between VALUE_2 and VALUE_3 (included).

SELECT
NON EMPTY
{
[Measures].[MEASURE_1], /* Comment, usually name of the measure */
[Measures].[MEASURE_2], /* Another comment */
[Measures].[MEASURE_3] /* Another comment */
}
ON COLUMNS,
NON EMPTY
{
[COLUMN_1].Children * /* Comment, usually name of the column */
[COLUMN_2].Children * /* Another comment */
[COLUMN_3].Children /* Another comment */

}
DIMENSION PROPERTIES
[COLUMN_1].[PROPERTY_1],
[COLUMN_2].[PROPERTY_2],
[COLUMN_2].[PROPERTY_3]

ON ROWS
FROM
[CAT_NAME/CUBE_NAME]
SAP VARIABLES
[VARIABLE_1] INCLUDING VALUE_1 /* VARIABLE_1 is equal to VALUE1 */
[VARIABLE_2] INCLUDING VALUE_2:VALUE_3 /* VARIABLE_2 is between VALUE1 and VALUE2 (included) */

Here is an example of SAP query following the same structure:

SELECT
NON EMPTY
{
[Measures].[AKLI1L2RFKV5TJ0Y4GINVJJ1H], /* Unit Price */
[Measures].[BW_RKF_MVSDSO45_0015], /* Invoiced Qty */
[Measures].[AKLI1L2RFKV5X0JSWTP1L9M1T], /* Invoices Net sales (CP) */
[Measures].[AKLI1L2RFKV5X0JSWTP21D1B5], /* Invoices Net sales (CO) */
[Measures].[AKLI1L2RFKV5X0JSWTOZRO0RL], /* Invoices Gross sales (CP) */
[Measures].[AKLI1L2RFKV5X0JSWTP1TNM5D], /* Invoices Gross sales (CO) */
[Measures].[AKLI1L2RFKV5TD0QH5OJ0XHBP], /* Discount & surcharges (CO) */
[Measures].[AKLI1L2RFKV5TD0QH5OJ7D911], /* Rebates (CO) */
[Measures].[AKLI1L2RFQC0SNVDSCS8QG6WV] /* Commissions (CO) */
}
ON COLUMNS,
NON EMPTY
{
[0LOGSYS].Children * /* Source */
[C_ITM_SD].Children * /* Order line */
[0BILL_DATE].Children *
[C_BILLTYP].Children * /* Billing type */
[CPFCTR1_2].Children * /* gbu */
[0G_CWWE01__CPFCTR2_2].Children * /* group of activities */
[0G_CWWE01__C_MAGNITU].Children * /* bfc market */
[C_COMPCDE].Children * /* Company Code */
[C_DOCTYP2].Children * /* Document type */
[C_BILLITM__C_PMNTRM2].Children * /* Terms of payment */
[C_PROD].Children * /* Material Group */
[C_MATNR2].Children * /* Material */
[C_CUSTSAL__C_FLGDIST].Children * /* Distributor Flag*/
[C_SOLDID__C_FLGINT].Children * /* internal sales flag*/
[C_SOLDID].Children * /* soldto */
[C_SHIPTID__0COUNTRY].Children * /* country */
[C_SHIPTID__C_GZONE].Children * /* zone */
[C_CUSTSAL__C_SALEMP].Children * /* sales rep (soldto) */
[C_SHIPTID].Children * /* shipto */
[C_GBR14__C_GBR4].Children * /* enduse */
[C_GBR14__C_GBR2].Children * /* enduse market*/
[C_DOCNUMB__C_INCOTRM].Children * /* Incoterms */
[C_SHIPNU2].Children * /* Shipment Id */
[0DOC_CURRCY].Children * /* Document Currency */
[C_DOCNUMB__C_CUSTGR2].Children /* Customer Segment*/

}
DIMENSION PROPERTIES
[C_BILLTYP].[2C_BILLTYP],
[C_PROD].[2C_PROD],
[C_MATNR2].[8C_MATNR2], [C_MATNR2].[10CRM_PROD], [C_MATNR2].[10PROD_HIER], [C_MATNR2].[50PROD_HIER],
[C_SHIPTID].[8C_SHIPTID],
[C_SHIPTID].[1C_CORPGR], [C_SHIPTID].[2C_CORPGR],
[C_SOLDID].[8C_SOLDID],
[C_SOLDID].[1C_CORPGR], [C_SOLDID].[2C_CORPGR], [C_SOLDID].[20COUNTRY], [C_SOLDID].[10REGION], [C_SOLDID].[1C_MZONE], [C_SOLDID].[1C_GZONE],
[C_CUSTSAL__C_SALEMP].[20TCTEMAIL],
[C_SHIPNU2].[8C_SHIPNU2],
[0DOC_CURRCY].[20DOC_CURRCY]

ON ROWS
FROM
[MVSDSO45/BW_QRY_MVSDSO45_SD0001]
SAP VARIABLES
[!V000002] INCLUDING '01.2020':'06.2020' /* Calendar year/month */
[!V000003] INCLUDING 'SP' /*BFC GBU (Selection Option, Optional) */
[VARCUR] INCLUDING 'EUR' /* Target Currency */
[!V000027] INCLUDING 'VKG' /* Qty conv. unit (Single value) */

Common mistakes

Since BW queries that fail to run do not always give a clear error message, they can be tricky to debug. Here are some tips:

Be careful to always check for "," or "*" between the measures and columns respectively.
Always make sure that the query you are using is meant for you to be used. Indeed, some queries can see their SAP variables change their technical names, leading to errors in the query.

Structure of a BW query

Common mistakes

Related articles