Skip to main content

Understand and Execute a BW Query

This guide demonstrates how to work with SAP BW (Business Warehouse) queries using ERPL's BICS (Business Intelligence Consumer Services) interface. You'll learn how to discover query metadata, build OLAP cross-tabs, and execute queries to retrieve analytical data.

For the full function reference and protocol details, see the BICS Protocol Deep Dive.

What is SAP BW?​

SAP BW is SAP's data warehouse solution that provides:

  • Multidimensional Data Model: Cubes, dimensions, and key figures
  • OLAP Functionality: Online Analytical Processing capabilities
  • Data Integration: ETL processes from various SAP and non-SAP sources
  • Reporting: Pre-built reports and ad-hoc analysis

Prerequisites​

Before working with BW queries, ensure you have:

  • ERPL BICS extension installed (subscription required)
  • Access to SAP BW or BW/4HANA system
  • BW user account with query execution permissions
  • A DuckDB sap_rfc secret for the system (see Connecting)
  • Basic understanding of BW concepts (InfoProviders, queries, variables)
BICS runs over RFC

BICS uses the same sap_rfc secret as the RFC extension — there is no separate BW endpoint. Create a secret once per session and every sap_bics_* function picks it up.

Understanding BW Query Structure​

Key Components​

  1. InfoProvider: The data source (InfoCube, DSO, CompositeProvider)
  2. Characteristics: Dimensions for analysis (e.g., Customer, Product, Time)
  3. Key Figures: Measures to analyze (e.g., Sales Amount, Quantity)
  4. Variables: Dynamic parameters for queries
  5. Filters: Static restrictions on data

Query Metadata​

Every BW query contains metadata that describes its structure:

-- Get query metadata (characteristics, key figures, variables)
SELECT * FROM sap_bics_describe('0D_NW_C01', '0D_FC_NW_C01_Q0011');

This returns the technical_name, text, the characteristics and keyfigures structs, and the query's variables.

Discovering Available Queries​

List All Queries​

-- List all available queries
SELECT * FROM sap_bics_show(obj_type => 'QUERY');

-- Or use the dedicated helper with search
SELECT * FROM sap_bics_show_queries(search => 'Q0011');

Get Query Details​

-- Get detailed information about a specific query
SELECT * FROM sap_bics_describe('0D_NW_C01', '0D_FC_NW_C01_Q0011');

The query execution model​

BICS queries are stateful. You open a query state and give it an id, mutate that state with separate calls (placing characteristics on the rows/columns axes and filtering members), then read the result set by the same id. The functions are chained by passing the id string — they are not nested inside one another.

-- 1. Open a state on the cube
SELECT state_id FROM sap_bics_begin('0D_NW_C01', id => 'q1');

-- 2. Place characteristics on the rows axis
SELECT state_id FROM sap_bics_rows('q1', '0D_NW_PROD', op => 'SET');

-- 3. Restrict members with a filter (Division = 7)
SELECT state_id FROM sap_bics_filter('q1', '0D_NW_DIV', '7', op => 'SET');

-- 4. Read the result set
SELECT * FROM sap_bics_result('q1');

The result columns are the cube's own characteristic and key-figure technical names — for 0D_NW_C01 that includes "0D_NW_PROD", "0D_NW_NETV" (net value), and "0D_NW_QUANT" (quantity). Quote them with double quotes because they start with a digit.

Working with Query Variables​

Variables in BW queries (e.g. "Enter fiscal year", "Select date range") are prompts a user fills before execution in SAP Analysis for Office.

Variable metadata is read-only in ERPL

ERPL can read a query's variable definitions (via sap_bics_describe), but filling variable values is not yet supported. Queries that have variables execute with their default/empty values. To restrict the result, place the characteristic on an axis and apply a sap_bics_filter instead.

-- Inspect the variables a query defines
SELECT technical_name, variables
FROM sap_bics_describe('0D_NW_C01', '0D_FC_NW_C01_Q0011');

-- Restrict the result with a filter rather than a variable
SELECT state_id FROM sap_bics_begin('0D_NW_C01', id => 'v1');
SELECT state_id FROM sap_bics_rows('v1', '0D_NW_PROD', op => 'SET');
SELECT state_id FROM sap_bics_filter('v1', '0CALMONTH', '202401', op => 'SET');
SELECT * FROM sap_bics_result('v1');

Practical Examples​

Example 1: Net value by product and division​

SELECT state_id FROM sap_bics_begin('0D_NW_C01', id => 'sales');
SELECT state_id FROM sap_bics_rows('sales', '0D_NW_PROD', op => 'SET');
SELECT state_id FROM sap_bics_filter('sales', '0D_NW_DIV', '7', op => 'SET');

SELECT
    "0D_NW_PROD"  AS product,
    "0D_NW_NETV"  AS net_value,
    "0D_NW_QUANT" AS quantity
FROM sap_bics_result('sales')
ORDER BY net_value DESC;

Example 2: Country breakdown​

SELECT state_id FROM sap_bics_begin('0D_NW_C01', id => 'geo');
SELECT state_id FROM sap_bics_rows('geo', '0D_NW_CNTRY', op => 'SET');

SELECT
    "0D_NW_CNTRY" AS country,
    "0D_NW_NETV"  AS net_value
FROM sap_bics_result('geo')
ORDER BY net_value DESC;

Example 3: Product × month cross-tab​

-- Products on rows, calendar month on columns
SELECT state_id FROM sap_bics_begin('0D_NW_C01', id => 'xtab');
SELECT state_id FROM sap_bics_rows('xtab', '0D_NW_PROD', op => 'SET');
SELECT state_id FROM sap_bics_columns('xtab', '0CALMONTH', op => 'SET');

SELECT * FROM sap_bics_result('xtab');

Advanced Query Operations​

Drill-Down Analysis​

Add a characteristic to the rows axis to drill deeper. op => 'ADD' extends the current axis instead of replacing it:

-- High-level: net value by country
SELECT state_id FROM sap_bics_begin('0D_NW_C01', id => 'drill');
SELECT state_id FROM sap_bics_rows('drill', '0D_NW_CNTRY', op => 'SET');
SELECT "0D_NW_CNTRY" AS country, "0D_NW_NETV" AS net_value
FROM sap_bics_result('drill')
ORDER BY net_value DESC;

-- Drill down: add product under country
SELECT state_id FROM sap_bics_rows('drill', '0D_NW_PROD', op => 'ADD');
SELECT "0D_NW_CNTRY" AS country, "0D_NW_PROD" AS product, "0D_NW_NETV" AS net_value
FROM sap_bics_result('drill')
ORDER BY country, net_value DESC;

Time Series Analysis​

-- Monthly net value
SELECT state_id FROM sap_bics_begin('0D_NW_C01', id => 'time');
SELECT state_id FROM sap_bics_rows('time', '0CALMONTH', op => 'SET');

SELECT "0CALMONTH" AS calmonth, "0D_NW_NETV" AS net_value
FROM sap_bics_result('time')
ORDER BY calmonth;

Comparative Analysis​

Build two states — one per slice — and join their result sets in SQL:

-- State A: division 7
SELECT state_id FROM sap_bics_begin('0D_NW_C01', id => 'div7');
SELECT state_id FROM sap_bics_rows('div7', '0D_NW_PROD', op => 'SET');
SELECT state_id FROM sap_bics_filter('div7', '0D_NW_DIV', '7', op => 'SET');

-- State B: division 15
SELECT state_id FROM sap_bics_begin('0D_NW_C01', id => 'div15');
SELECT state_id FROM sap_bics_rows('div15', '0D_NW_PROD', op => 'SET');
SELECT state_id FROM sap_bics_filter('div15', '0D_NW_DIV', '15', op => 'SET');

WITH a AS (SELECT "0D_NW_PROD" AS product, "0D_NW_NETV" AS netv_7  FROM sap_bics_result('div7')),
     b AS (SELECT "0D_NW_PROD" AS product, "0D_NW_NETV" AS netv_15 FROM sap_bics_result('div15'))
SELECT a.product, a.netv_7, b.netv_15, (a.netv_7 - b.netv_15) AS difference
FROM a JOIN b ON a.product = b.product;

Performance Optimization​

Query Optimization Tips​

  1. Filter early: restrict members with sap_bics_filter to shrink the result set
  2. Limit the drilldown: only place the characteristics you need on the rows/columns axes
  3. Reuse the state: keep mutating the same id instead of re-opening for each variation
  4. Post-process in DuckDB: aggregate, sort, and join the result set with ordinary SQL

Example: Filtered execution​

SELECT state_id FROM sap_bics_begin('0D_NW_C01', id => 'opt');
SELECT state_id FROM sap_bics_rows('opt', '0D_NW_PROD', op => 'SET');
SELECT state_id FROM sap_bics_filter('opt', '0CALMONTH', '202401', op => 'SET');
SELECT state_id FROM sap_bics_filter('opt', '0D_NW_DIV', '7', op => 'SET');

SELECT "0D_NW_PROD" AS product, "0D_NW_NETV" AS net_value
FROM sap_bics_result('opt')
WHERE "0D_NW_NETV" > 1000        -- additional client-side filtering in DuckDB
ORDER BY net_value DESC
LIMIT 100;

Error Handling​

BICS surfaces server-side problems (unknown query, missing authorization, invalid characteristic) as DuckDB errors — the statement fails rather than returning a status column. Wrap risky steps in your pipeline and check connectivity up front:

-- Confirm the system is reachable before running a query
PRAGMA sap_rfc_ping;

-- Verify the query exists (case-sensitive) before executing it
SELECT * FROM sap_bics_show_queries(search => '0D_FC_NW_C01_Q0011');

Common BW Query Errors​

  1. Query/cube not found: verify the technical name (case-sensitive) via sap_bics_show
  2. Authorization issues: ensure the user has BW analysis authorizations (RSEC)
  3. Empty result: check filter member keys (not display texts) with sap_bics_describe_infoobject
  4. Variable queries: remember variable filling is unsupported — restrict via filters

Integration with Data Science​

Export to Python/R​

-- Build the state, then export its result set to CSV
SELECT state_id FROM sap_bics_begin('0D_NW_C01', id => 'exp');
SELECT state_id FROM sap_bics_rows('exp', '0D_NW_PROD', op => 'SET');
SELECT state_id FROM sap_bics_filter('exp', '0CALMONTH', '202401', op => 'SET');

COPY (SELECT * FROM sap_bics_result('exp')) TO 'sales_analysis.csv' WITH (HEADER);

Machine Learning Integration​

-- Prepare a labelled dataset from the result set
SELECT state_id FROM sap_bics_begin('0D_NW_C01', id => 'ml');
SELECT state_id FROM sap_bics_rows('ml', '0D_NW_PROD', op => 'SET');

SELECT
    "0D_NW_PROD"  AS product,
    "0D_NW_NETV"  AS net_value,
    "0D_NW_QUANT" AS quantity,
    CASE WHEN "0D_NW_NETV" > 10000 THEN 'HIGH_VALUE' ELSE 'STANDARD' END AS segment
FROM sap_bics_result('ml');

Best Practices​

1. Query Design​

  • Use the cube's real technical names for characteristics and key figures
  • Inspect structure with sap_bics_describe before building a cross-tab
  • Test queries with different filter combinations

2. Filtering​

  • Filter on member keys, not display texts
  • Use op => 'SET' | 'ADD' | 'REMOVE' to manage selections precisely
  • Apply restrictions on the SAP side with sap_bics_filter; refine further in DuckDB

3. Data Quality​

  • Verify data completeness for the slice you filtered
  • Handle missing or initial values explicitly
  • Cross-check totals against a known report

4. Security​

  • Use a sap_rfc secret (SNC for encrypted connections)
  • Rely on BW analysis authorizations to scope access
  • Audit RFC logons via the SAP security audit log

Troubleshooting​

Common Issues​

  1. Slow performance: filter and limit the drilldown before reading the result
  2. Memory: aggregate in DuckDB rather than pulling every cell
  3. Connection problems: check connectivity with PRAGMA sap_rfc_ping
  4. Authorization errors: verify BW user permissions

Debugging Tips​

-- Enable ERPL tracing to see the underlying RFC calls
SET erpl_trace_enabled = TRUE;
SET erpl_trace_level   = 'DEBUG';   -- TRACE | DEBUG | INFO | WARN | ERROR

-- Test with no filters
SELECT state_id FROM sap_bics_begin('0D_NW_C01', id => 'dbg');
SELECT state_id FROM sap_bics_rows('dbg', '0D_NW_PROD', op => 'SET');
SELECT * FROM sap_bics_result('dbg');

-- Check query metadata
SELECT * FROM sap_bics_describe('0D_NW_C01', '0D_FC_NW_C01_Q0011');