Understand and Execute a BW Query from within DuckDB

ERPL makes it easy to retrieve data from SAP Business Warehouse (BW). Our extension couples your analytics in DuckDB with the multidimensional data in BW. We try to guide you how to transition from Analysis for Office to DuckDB.

Introduction

Work in progress

The Business Warehouse (BW) connector is still work in progress. We are working hard to provide you with the best possible experience. If you have any questions, please do not hesitate to contact us.

In today’s digital landscape, critical data assets, including sales history, stock movement, and purchases, often reside within SAP Business Warehouse (BW). Despite its value, the multidimensional data model of BW poses challenges for analytics due to its complexity. The ERPL BW Extension simplifies the process of extracting this data for analysis in DuckDB, aiding the transition from Analysis for Office (AO) to DuckDB or other familiar tools like BEX and Query Designer. Additionally, ERPL serves as a gateway to a comprehensive analytics ecosystem, supporting tools such as R, Python, and Julia. For detailed integration instructions, visit our integration guide.

Get yourself prepared

Required access to queries or cubes

To load data from a BW InfoObject, you need to have access to the query or the underlying cube. The easiest way to check this is to use an SAP reporting tool available in your enterprise, e.g. BEX, Query Designer, Analysis for Office (AO) or the SAP Analytics Cloud (SAC). If you can execute the query there, you should be able to execute it with the ERPL BW Extension, as we use the same underlying protocol.

First and foremost you need to install DuckDB and the ERPL extension. DuckDB as various different installation options, please follow the instructions on the DuckDB website to get yourself prepared. For our scenario down below we recommend to install the Python or R bindings as well. You can check if this worked well with the following commands:

import duckdb
cursor = duckdb.connect()
print(cursor.execute('SELECT 42').fetchall())
library("DBI")
con = dbConnect(duckdb::duckdb(), ":memory:")
dbWriteTable(con, "iris", iris)
dbGetQuery(con, 'SELECT "Species", MIN("Sepal.Width") FROM iris GROUP BY "Species"')

For the ERPL extension please follow the instructions in the installation section of our documentation to get yourself prepared.

Necessary SAP BW terminology

To start we have to introduce a little bit of SAP terminology. In the paralance of SAP, a query or a cube is an InfoProvider. They are pretty similar in the sense that they both have a multidimensional data model. The main difference is that a cube is a physical data model, which is stored in the database. A query is a virtual data model, which is defined on top of a cube.

Overview of available functions

The ERPL BW Extension provides a set of functions to interact with BW InfoProviders. We are working currently on extending the functionality. You can always check the available functions by using the following command:

SELECT * FROM duckdb_functions() WHERE function_name LIKE '%sap_bics%';

The output of the command is a table with the following columns:

┌───────────────┬─────────────┬──────────────────────┬───────────────┬───┬──────────┬──────────────┬─────────┐
│ database_name │ schema_name │    function_name     │ function_type │ … │ internal │ function_oid │ example │
│    varchar    │   varchar   │       varchar        │    varchar    │   │ boolean  │    int64     │ varchar │
├───────────────┼─────────────┼──────────────────────┼───────────────┼───┼──────────┼──────────────┼─────────┤
│ system        │ main        │ sap_bics_show_quer…  │ table         │ … │ true     │         1434 │ NULL    │
│ system        │ main        │ sap_bics_show        │ table         │ … │ true     │         1430 │ NULL    │
│ system        │ main        │ sap_bics_show_cubes  │ table         │ … │ true     │         1432 │ NULL    │
│ system        │ main        │ sap_bics_describe    │ table         │ … │ true     │         1436 │ NULL    │
│ system        │ main        │ sap_bics_describe    │ table         │ … │ true     │         1436 │ NULL    │
│ system        │ main        │ sap_bics_begin       │ table         │ … │ true     │         1438 │ NULL    │
│ system        │ main        │ sap_bics_columns     │ table         │ … │ true     │         1440 │ NULL    │
│ system        │ main        │ sap_bics_rows        │ table         │ … │ true     │         1442 │ NULL    │
│ system        │ main        │ sap_bics_filter      │ table         │ … │ true     │         1444 │ NULL    │
│ system        │ main        │ sap_bics_result      │ table         │ … │ true     │         1446 │ NULL    │
├───────────────┴─────────────┴──────────────────────┴───────────────┴───┴──────────┴──────────────┴─────────┤
│ 10 rows                                                                               14 columns (7 shown) │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

List available queries or cubes

You learned in the section above, that cubes and queries are InfoProviders. This is why you To list all available queries or cubes, you can use the following command:

SELECT * FROM sap_bics_show();

You can supply an argument to the function to filter the result. For example, if you want to list all queries, which contain the word “SALES” in the name, you can use the following command:

SELECT * FROM sap_bics_show(SEARCH='*NW Demo*', OBJ_TYPE='CUBE');

The asterisk * is a wildcard, which matches any character. This filter by default works on queries and cubes. If you want to restict your output on either queries or cubes, you have to provide either the QUERY or CUBE named parameter to the sap_bics_show function. This means you can use the following commands:

The output of the function is a table with the following columns:

  • technical_name: The name of the InfoProvider
  • text: The human readable name of the InfoProvider (short text)
  • type: The type of the InfoProvider, e.g. CUBE or QUERY

Explore a query or cube

After successfully finding the relevant query or cube, of course one wants to understand the structure of the infoprovider. The function sap_bics_describe helps you to explore a query or a cube. Unfortunately we get here in contact with the fact, that the multi-dimensional data model of BW can not be matched one to on the realtional model of DuckDB. The following query shows the describe function:

SELECT * FROM sap_bics_describe('0D_NW_C01');

The output of the function is a table with the following columns:

  • technical_name: The name of the InfoProvider
  • text: The human readable name of the InfoProvider (short text)
  • characteristics: This is a list of all characteristics. We use a DuckDB struct to represent each element. See below for a description.
  • keyfigures: This is a list of the key figures of the InfoProvider.
  • state_rows: Contains a list of characteristics or key figures currently selected as a row dimension.
  • state_columns: Contains a list of characteristics or key figures currently selected as a column dimension.
  • state_free: Contains a list of characteristics or key figures currently not selected.

The output of this function can be further processed with out of the box DuckDB functions, as e.g. UNNEST to explode the struct into multiple rows. The following query shows a list of all characteristics of the InfoProvider:

SELECT UNNEST(characteristics, recursive:=true) as ch_name FROM sap_bics_describe('0D_NW_C01');

The output of the function is a table with the following columns:

┌──────────────────────┬──────────────────────┬───┬──────────────────────┬──────────────────────┬───────────┐
│         text         │    technical_name    │ … │      attributes      │      structures      │ elements  │
│       varchar        │       varchar        │   │ struct("text" varc…  │ struct("text" varc…  │ varchar[] │
├──────────────────────┼──────────────────────┼───┼──────────────────────┼──────────────────────┼───────────┤
│ Unit of Measure      │ 0UNIT                │ … │ []                   │ []                   │ []        │
│ Value Type for Rep   │ 0D_NW_VTYPE          │ … │ []                   │ []                   │ []        │
│ Version              │ 0D_NW_VERS           │ … │ []                   │ []                   │ []        │
│ Country              │ 0D_NW_SORG__0D_NW_…  │ … │ []                   │ []                   │ []        │
│ Sales Organization   │ 0D_NW_SORG           │ … │ [{'text': Country,…  │ []                   │ []        │
│ Sold-to Party        │ 0D_NW_SOLD           │ … │ [{'text': Country,…  │ []                   │ []        │
│ Ship-to Party        │ 0D_NW_SHIP           │ … │ [{'text': Country,…  │ []                   │ []        │
│ Sales Group          │ 0D_NW_SGRP           │ … │ []                   │ []                   │ []        │
│ Region               │ 0D_NW_REGIO          │ … │ []                   │ []                   │ []        │
│ Product Group        │ 0D_NW_PROD__0D_NW_…  │ … │ []                   │ []                   │ []        │
│ Product Category     │ 0D_NW_PROD__0D_NW_…  │ … │ []                   │ []                   │ []        │
│ Product              │ 0D_NW_PROD           │ … │ [{'text': Product …  │ []                   │ []        │
│ Plant                │ 0D_NW_PLANT          │ … │ []                   │ []                   │ []        │
│ Payer                │ 0D_NW_PAYER          │ … │ [{'text': Country,…  │ []                   │ []        │
│ Division             │ 0D_NW_DIV            │ … │ []                   │ []                   │ []        │
│ Country              │ 0D_NW_CODE__0D_NW_…  │ … │ []                   │ []                   │ []        │
│ Company code         │ 0D_NW_CODE           │ … │ [{'text': Country,…  │ []                   │ []        │
│ Country              │ 0D_NW_CNTRY          │ … │ []                   │ []                   │ []        │
│ Distribution Channel │ 0D_NW_CHANN          │ … │ []                   │ []                   │ []        │
│ Currency             │ 0CURRENCY            │ … │ []                   │ []                   │ []        │
│ Calendar Year        │ 0CALYEAR             │ … │ [{'text': Valid fr…  │ []                   │ []        │
│ Calendar Year/Month  │ 0CALMONTH            │ … │ [{'text': Calendar…  │ []                   │ []        │
├──────────────────────┴──────────────────────┴───┴──────────────────────┴──────────────────────┴───────────┤
│ 22 rows                                                                               7 columns (5 shown) │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────┘

The characteristics and key-figures are described by a struct, which contains the following fields:

  • technical_name: The technical name of the characteristic or key figure
  • text: The human readable name of the characteristic or key figure
  • abap_type: The ABAP type of the characteristic or key figure (e.g. CHAR, NUMC, CURR)
  • abap_type_text: The human readable name of the ABAP type
  • attributes: A list of attributes of the characteristic or key figure
  • structures: A list of structures of the characteristic or key figure
  • elements: A list of elements of the characteristic or key figure

The function sap_bics_describe is a powerful tool to understand the structure of a query or a cube. It is the first step to retrieve data from a query or a cube. Beside the ability to explore the structure of a query or a cube, the function also allows to get information about a materialized query. For details see below.

Retrieve data from a query or cube

Finally and most important to retrieve data from a query or cube. However there is a challenge, as the multi-dimensional data model of BW can not be matched one to on the realtional model of DuckDB. This is why we have to use a set of functions to define the structure of the result set. Lets go step by step through the process.

Step 1: Begin the query

SELECT * FROM sap_bics_begin('0D_NW_C01', ID='q1', RETURN='RESULT');

With this function we start the query. The parameters are the following:

  • technical_name: The technical name of the InfoProvider
  • ID: The ID of the query. This is a unique identifier for the query. If you do not provide an ID, the system will generate one for you. The generated ID is returned by the function, but just if the RETURN parameter is set to META.
  • RETURN: The return type of the function. The following values are possible:
    • META: The function returns the ID of the query
    • RESULT: The function returns the result of the query

The result of the above function is a table with the following columns:

┌─────────────┬─────────────┬──────────────┬─────────────┬─────────────┬─────────────┐
│ 0D_NW_COSTV │ 0D_NW_DOCUM │  0D_NW_NETV  │ 0D_NW_OORQT │ 0D_NW_OORV  │ 0D_NW_QUANT │
│   double    │   double    │    double    │   double    │   double    │   double    │
├─────────────┼─────────────┼──────────────┼─────────────┼─────────────┼─────────────┤
│         0.0 │      7128.0 │ 4674811295.0 │     14256.0 │ 607725822.0 │      7108.0 │
└─────────────┴─────────────┴──────────────┴─────────────┴─────────────┴─────────────┘

Step 2: Drill down the query

Next we will drill down, by adding a characteristic to the row dimension. The following command adds the characteristic 0CALMONTH to the row dimension:

SELECT * FROM sap_bics_rows('q1', '0CALMONTH', OP='ADD', RETURN='RESULT');

In the result, we see that the characteristic 0CALMONTH is added to the row dimension. The result is a table with the following columns:

┌───────────┬─────────────┬─────────────┬──────────────┬─────────────┬─────────────┬─────────────┐
│ 0CALMONTH │ 0D_NW_COSTV │ 0D_NW_DOCUM │  0D_NW_NETV  │ 0D_NW_OORQT │ 0D_NW_OORV  │ 0D_NW_QUANT │
│  varchar  │   double    │   double    │    double    │   double    │   double    │   double    │
├───────────┼─────────────┼─────────────┼──────────────┼─────────────┼─────────────┼─────────────┤
│ 201001    │         0.0 │        54.0 │   27894479.0 │       108.0 │   3626286.0 │        54.0 │
│ 201002    │         0.0 │        54.0 │   38343037.0 │       108.0 │   4984600.0 │        54.0 │
│ 201003    │         0.0 │        54.0 │   38410540.0 │       108.0 │   4993374.0 │        54.0 │
│ 201004    │         0.0 │        54.0 │   40079485.0 │       108.0 │   5210338.0 │        54.0 │
│ 201005    │         0.0 │        54.0 │   32379708.0 │       108.0 │   4209363.0 │        54.0 │
│ 201006    │         0.0 │        54.0 │   34721026.0 │       108.0 │   4513737.0 │        54.0 │
│ 201007    │         0.0 │        54.0 │   34524422.0 │       108.0 │   4488184.0 │        54.0 │
│ 201008    │         0.0 │        54.0 │   34699898.0 │       108.0 │   4510991.0 │        54.0 │
│ 201009    │         0.0 │        54.0 │   33414639.0 │       108.0 │   4343908.0 │        54.0 │
│ 201010    │         0.0 │        54.0 │   39688253.0 │       108.0 │   5159476.0 │        54.0 │
│   ·       │          ·  │          ·  │        ·     │         ·   │       ·     │          ·  │
│   ·       │          ·  │          ·  │        ·     │         ·   │       ·     │          ·  │
│   ·       │          ·  │          ·  │        ·     │         ·   │       ·     │          ·  │
│ 202004    │         0.0 │        54.0 │   36124258.0 │       108.0 │   4696155.0 │        54.0 │
│ 202005    │         0.0 │        54.0 │   36261264.0 │       108.0 │   4713968.0 │        54.0 │
│ 202006    │         0.0 │        54.0 │   26820912.0 │       108.0 │   3486723.0 │        54.0 │
│ 202007    │         0.0 │        54.0 │   35587959.0 │       108.0 │   4626439.0 │        52.0 │
│ 202008    │         0.0 │        54.0 │   35049039.0 │       108.0 │   4556374.0 │        52.0 │
│ 202009    │         0.0 │        54.0 │   37736646.0 │       108.0 │   4905766.0 │        52.0 │
│ 202010    │         0.0 │        54.0 │   40675801.0 │       108.0 │   5287853.0 │        54.0 │
│ 202011    │         0.0 │        54.0 │   38744471.0 │       108.0 │   5036785.0 │        54.0 │
│ 202012    │         0.0 │        54.0 │   34376206.0 │       108.0 │   4468907.0 │        54.0 │
│ SUMME     │         0.0 │      7128.0 │ 4674811295.0 │     14256.0 │ 607725822.0 │      7108.0 │
├───────────┴─────────────┴─────────────┴──────────────┴─────────────┴─────────────┴─────────────┤
│ 133 rows (20 shown)                                                                  7 columns │
└────────────────────────────────────────────────────────────────────────────────────────────────┘

We can add another characteristic to the row dimension. The following command adds the characteristic 0D_NW_REGIO to the row dimension:

SELECT * FROM sap_bics_rows('q1', '0D_NW_REGIO', OP='ADD', RETURN='RESULT');

Step 3: Finally receive the query result

Finally we can receive the result of the query. The following command returns the result of the query:

SELECT * FROM sap_bics_result('q1')

The result of the function is a table with the following columns:

┌───────────┬─────────────┬─────────────┬─────────────┬──────────────┬─────────────┬─────────────┬─────────────┐
│ 0CALMONTH │ 0D_NW_REGIO │ 0D_NW_COSTV │ 0D_NW_DOCUM │  0D_NW_NETV  │ 0D_NW_OORQT │ 0D_NW_OORV  │ 0D_NW_QUANT │
│  varchar  │   varchar   │   double    │   double    │    double    │   double    │   double    │   double    │
├───────────┼─────────────┼─────────────┼─────────────┼──────────────┼─────────────┼─────────────┼─────────────┤
│ 201001    │ DE 11       │         0.0 │        12.0 │    5994415.0 │        24.0 │    779275.0 │        12.0 │
│ 201001    │ FR 75       │         0.0 │        12.0 │    8144894.0 │        24.0 │   1058837.0 │        12.0 │
│ 201001    │ GB LO       │         0.0 │         6.0 │    2585619.0 │        12.0 │    336130.0 │         6.0 │
│ 201001    │ US CA       │         0.0 │        12.0 │    5000781.0 │        24.0 │    650103.0 │        12.0 │
│ 201001    │ US NY       │         0.0 │        12.0 │    6168770.0 │        24.0 │    801941.0 │        12.0 │
│ 201001    │ SUMME       │         0.0 │        54.0 │   27894479.0 │       108.0 │   3626286.0 │        54.0 │
│ 201002    │ DE 11       │         0.0 │        12.0 │   11314350.0 │        24.0 │   1470866.0 │        12.0 │
│ 201002    │ FR 75       │         0.0 │        12.0 │    8524491.0 │        24.0 │   1108186.0 │        12.0 │
│ 201002    │ GB LO       │         0.0 │         6.0 │    4928376.0 │        12.0 │    640689.0 │         6.0 │
│ 201002    │ US CA       │         0.0 │        12.0 │    7853293.0 │        24.0 │   1020931.0 │        12.0 │
│   ·       │   ·         │          ·  │          ·  │        ·     │          ·  │        ·    │          ·  │
│   ·       │   ·         │          ·  │          ·  │        ·     │          ·  │        ·    │          ·  │
│   ·       │   ·         │          ·  │          ·  │        ·     │          ·  │        ·    │          ·  │
│ 202011    │ US CA       │         0.0 │        12.0 │    7230711.0 │        24.0 │    939995.0 │        12.0 │
│ 202011    │ US NY       │         0.0 │        12.0 │    7202718.0 │        24.0 │    936354.0 │        12.0 │
│ 202011    │ SUMME       │         0.0 │        54.0 │   38744471.0 │       108.0 │   5036785.0 │        54.0 │
│ 202012    │ DE 11       │         0.0 │        12.0 │    6603666.0 │        24.0 │    858476.0 │        12.0 │
│ 202012    │ FR 75       │         0.0 │        12.0 │    9794461.0 │        24.0 │   1273281.0 │        12.0 │
│ 202012    │ GB LO       │         0.0 │         6.0 │    2196824.0 │        12.0 │    285588.0 │         6.0 │
│ 202012    │ US CA       │         0.0 │        12.0 │   10348463.0 │        24.0 │   1345300.0 │        12.0 │
│ 202012    │ US NY       │         0.0 │        12.0 │    5432792.0 │        24.0 │    706262.0 │        12.0 │
│ 202012    │ SUMME       │         0.0 │        54.0 │   34376206.0 │       108.0 │   4468907.0 │        54.0 │
│ SUMME     │ SUMME       │         0.0 │      7128.0 │ 4674811295.0 │     14256.0 │ 607725822.0 │      7108.0 │
├───────────┴─────────────┴─────────────┴─────────────┴──────────────┴─────────────┴─────────────┴─────────────┤
│ 793 rows (20 shown)                                                                                8 columns │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

Underneath the hood, the extension creates a table in DuckDB to persist the state of the query between the individual function calls. If you use a file based data base connection, this query defintion of course also survives the restart of the DuckDB connection. This is why you can use the sap_bics_result function to retrieve the result of the query, e.g. also in a daily running job.

Summary

ERPL’s integration with SAP BW via the BW Extension makes transitioning from BEX or Query Designer to DuckDB straightforward. You can efficiently execute, drill down, and retrieve multidimensional data from BW queries and cubes for powerful analysis.