SAP BW / BW/4HANA Connection

Extension Credit

This guide uses the erpl extension by the DuckDB community. Thanks to the contributors who made SAP BW/4HANA integration possible through RFC connections!

Connect flAPI to SAP BW (Business Warehouse) and BW/4HANA systems using the ERPL extension. Access InfoCubes, DSOs, and BEx queries for analytics APIs.

Prerequisites

SAP NetWeaver RFC SDK installed
SAP BW user with query authorizations
Network access to BW system

Quick Start

The ERPL extension is installed from its custom repository and authenticates with a DuckDB sap_rfc secret:

# flapi.yaml
connections:
  sap-bw:
    init: |
      INSTALL 'erpl' FROM 'http://get.erpl.io';
      LOAD 'erpl';

      CREATE OR REPLACE PERSISTENT SECRET sap_bw (
        TYPE sap_rfc,
        ASHOST '${SAP_BW_HOST}',
        SYSNR  '${SAP_BW_SYSNR}',
        CLIENT '${SAP_BW_CLIENT}',
        USER   '${SAP_BW_USER}',
        PASSWD '${SAP_BW_PASSWORD}',
        LANG   'EN'
      );

Connection Parameters

Same as SAP ERP:

Parameter	Description	Required	Example
`ASHOST`	SAP BW server hostname	Yes	`bw.company.com`
`SYSNR`	System number	Yes	`'00'`
`CLIENT`	Client number	Yes	`'100'`
`USER`	SAP username	Yes	`${SAP_BW_USER}`
`PASSWD`	SAP password	Yes	`${SAP_BW_PASSWORD}`
`LANG`	Language	No	`EN`

Reading BW Data

InfoCube Queries

# sqls/sales-analysis.yaml
url-path: /sales/analysis/
method: GET

request:
  - field-name: year
    field-in: query
    required: true
    validators:
      - type: string
        regex: '^\d{4}$'
  - field-name: region
    field-in: query
    required: false
    validators:
      - type: enum
        allowedValues: [EMEA, AMER, APAC]

template-source: sales-analysis.sql
connection:
  - sap-bw

-- sqls/sales-analysis.sql
-- Query InfoCube for sales data via the ERPL sap_read_table function
SELECT
  "/BIC/OCALYEAR"   AS year,
  "/BIC/OCREGION"   AS region,
  "/BIC/OCPRODUCT"  AS product,
  "/BIC/OCUSTOMER"  AS customer,
  "/BIC/OREVENUE"   AS revenue,
  "/BIC/OQUANTITY"  AS quantity,
  "/BIC/OMARGIN"    AS margin
FROM sap_read_table('/BIC/ASALES01')
WHERE "/BIC/OCALYEAR" = '{{{ params.year }}}'
{{#params.region}}
  AND "/BIC/OCREGION" = '{{{ params.region }}}'
{{/params.region}}

DSO (DataStore Object) Queries

-- sqls/customer-360.sql
SELECT
  CUSTOMER_ID,
  CUSTOMER_NAME,
  SEGMENT,
  TOTAL_PURCHASES,
  LAST_ORDER_DATE,
  LIFETIME_VALUE,
  CHURN_RISK_SCORE
FROM sap_read_table('/BIC/ACUST360')
WHERE ACTIVE_FLAG = 'X'
ORDER BY LIFETIME_VALUE DESC
LIMIT 1000

ADSO (Advanced DSO in BW/4HANA)

-- sqls/realtime-inventory.sql
SELECT
  PLANT,
  MATERIAL,
  STORAGE_LOCATION,
  STOCK_QUANTITY,
  LAST_UPDATE_TIMESTAMP
FROM sap_read_table('/BIC/AINVENT01')
WHERE LAST_UPDATE_TIMESTAMP >= CURRENT_TIMESTAMP - INTERVAL 1 HOUR

Common BW Objects

InfoCubes

Pre-aggregated analytical data:

0SD_C03 - Sales analysis
0PUR_C01 - Purchase analysis
0FI_GL_4 - General ledger
/BIC/A* - Custom InfoCubes

DSOs

Detailed transactional data:

0MATERIAL - Material master
0CUSTOMER - Customer master
/BIC/A* - Custom DSOs

BEx Queries

Access published BEx queries via ERPL (requires RFC function modules).

Caching BW Data

Critical for BW: queries can take 30+ seconds. Always cache:

# sqls/sales-dashboard.yaml
url-path: /sales/dashboard/
method: GET

cache:
  enabled: true
  table: bw_sales_cache
  schedule: 12h
  template-file: sales_dashboard_cache.sql

template-source: sales_dashboard.sql
connection:
  - sap-bw

-- sqls/sales_dashboard_cache.sql
-- Materialize complex BW query results into DuckLake
INSERT INTO {{cache.catalog}}.{{cache.schema}}.{{cache.table}}
SELECT
  c.CALYEAR        AS year,
  c.CALMONTH       AS month,
  r.REGION_TEXT    AS region,
  p.PRODUCT_TEXT   AS product_category,
  SUM(f.REVENUE)   AS total_revenue,
  SUM(f.QUANTITY)  AS total_quantity,
  AVG(f.MARGIN_PCT) AS avg_margin_pct,
  COUNT(DISTINCT f.CUSTOMER) AS unique_customers
FROM sap_read_table('/BIC/FSALES') f
LEFT JOIN sap_read_table('/BIC/PCALENDAR') c
  ON f.CALYEAR = c.CALYEAR AND f.CALMONTH = c.CALMONTH
LEFT JOIN sap_read_table('/BIC/PREGION') r
  ON f.REGION = r.REGION
LEFT JOIN sap_read_table('/BIC/PPRODUCT') p
  ON f.PRODUCT = p.PRODUCT
WHERE c.CALYEAR >= YEAR(CURRENT_DATE) - 2
GROUP BY c.CALYEAR, c.CALMONTH, r.REGION_TEXT, p.PRODUCT_TEXT

Performance:

Method	Query Time	API Response
Direct BW	30-120 seconds	Timeout
flAPI cache	N/A	1-3ms

BW-Specific Considerations

1. Technical Names

BW uses cryptic technical names:

InfoCubes: /BIC/ASALES01, 0SD_C03
Characteristics: /BIC/OCUSTOMER, 0CALMONTH
Key figures: /BIC/OREVENUE, 0AMOUNT

Find names in:

Transaction RSA1 (Data Warehousing Workbench)
Transaction LISTCUBE (view InfoCube data)

2. Naming Conventions

/BIC/A*  - Custom InfoCubes/DSOs
/BIC/O*  - InfoObject characteristics
/BIC/P*  - Master data tables
0*       - SAP standard objects

3. Date/Time Fields

BW uses specific formats:

CALMONTH: YYYYMM (e.g., 202401)
CALDAY: YYYYMMDD (e.g., 20240115)
Convert in SQL: strptime(CALDAY, '%Y%m%d')

4. Master Data

Join with master data for descriptions:

SELECT
  f.CUSTOMER,
  m.CUSTOMER_NAME,
  m.CITY,
  m.COUNTRY,
  SUM(f.REVENUE) AS revenue
FROM sap_read_table('/BIC/FSALES') f
LEFT JOIN sap_read_table('/BIC/PCUSTOMER') m
  ON f.CUSTOMER = m.CUSTOMER
GROUP BY f.CUSTOMER, m.CUSTOMER_NAME, m.CITY, m.COUNTRY

Complete Example

# ═══════════════════════════════════════════════════════════════
# flapi.yaml - BW connection
# ═══════════════════════════════════════════════════════════════
project-name: sap-bw-reporting-api

template:
  path: './sqls'
  environment-whitelist:
    - '^SAP_BW_.*'
    - '^REPORTING_.*'

connections:
  sap-bw-prod:
    init: |
      INSTALL 'erpl' FROM 'http://get.erpl.io';
      LOAD 'erpl';

      CREATE OR REPLACE PERSISTENT SECRET sap_bw_prod (
        TYPE sap_rfc,
        ASHOST '${SAP_BW_HOST}',
        SYSNR  '${SAP_BW_SYSNR}',
        CLIENT '${SAP_BW_CLIENT}',
        USER   '${SAP_BW_USER}',
        PASSWD '${SAP_BW_PASSWORD}',
        LANG   'EN'
      );

# ═══════════════════════════════════════════════════════════════
# sqls/revenue-report.yaml
# ═══════════════════════════════════════════════════════════════
url-path: /reports/revenue/
method: GET

auth:
  enabled: true
  type: bearer
  jwt-secret: '${REPORTING_JWT_SECRET}'

cache:
  enabled: true
  table: bw_revenue_report
  schedule: 24h
  template-file: revenue_cache.sql

request:
  - field-name: year
    field-in: query
    description: Fiscal year (YYYY)
    required: false
    default: "2024"
    validators:
      - type: string
        regex: '^\d{4}$'

  - field-name: region
    field-in: query
    description: Region code
    required: false
    validators:
      - type: enum
        allowedValues: [EMEA, AMER, APAC]

template-source: revenue_report.sql
connection:
  - sap-bw-prod

-- sqls/revenue_cache.sql
-- Daily materialization from BW InfoCube
INSERT INTO {{cache.catalog}}.{{cache.schema}}.{{cache.table}}
SELECT
  strptime(fc.CALMONTH || '01', '%Y%m%d') AS month_date,
  SUBSTRING(fc.CALMONTH, 1, 4)            AS year,
  SUBSTRING(fc.CALMONTH, 5, 2)            AS month,
  reg.REGION_TEXT                         AS region,
  pc.PRODUCT_CAT_TEXT                     AS product_category,
  cc.COUNTRY_TEXT                         AS country,
  SUM(fc.NET_REVENUE)                     AS net_revenue,
  SUM(fc.GROSS_REVENUE)                   AS gross_revenue,
  SUM(fc.QUANTITY)                        AS quantity_sold,
  COUNT(DISTINCT fc.ORDER_NUMBER)         AS order_count,
  AVG(fc.MARGIN_PCT)                      AS avg_margin_pct
FROM sap_read_table('/BIC/AREVENUE01') fc
LEFT JOIN sap_read_table('/BIC/PREGION') reg
  ON fc.REGION = reg.REGION
LEFT JOIN sap_read_table('/BIC/PPRODCAT') pc
  ON fc.PRODUCT_CAT = pc.PRODUCT_CAT
LEFT JOIN sap_read_table('/BIC/PCOUNTRY') cc
  ON fc.COUNTRY = cc.COUNTRY
WHERE SUBSTRING(fc.CALMONTH, 1, 4) >= '2020'
  AND fc.RECORD_MODE = 'A'
GROUP BY fc.CALMONTH, reg.REGION_TEXT, pc.PRODUCT_CAT_TEXT, cc.COUNTRY_TEXT

-- sqls/revenue_report.sql
-- Fast API queries against the materialized cache
SELECT
  year,
  month,
  month_date,
  region,
  product_category,
  country,
  net_revenue,
  gross_revenue,
  quantity_sold,
  order_count,
  avg_margin_pct
FROM {{cache.catalog}}.{{cache.schema}}.{{cache.table}}
WHERE 1=1
{{#params.year}}
  AND year = '{{{ params.year }}}'
{{/params.year}}
{{#params.region}}
  AND region = '{{{ params.region }}}'
{{/params.region}}
ORDER BY month_date DESC, net_revenue DESC

Best Practices

DO

Cache aggressively (BW queries are slow)
Refresh overnight when BW updates
Use master data joins for readable output
Filter by date ranges to limit data volume
Test queries in SE16N/LISTCUBE first
Request BW indexes on frequently filtered fields

DON'T

Query BW directly without caching
Use SELECT * on large InfoCubes
Expose raw technical names in APIs
Query historical data without date filters
Run real-time queries against InfoCubes

Troubleshooting

No Data Returned

-- Check if data exists
SELECT COUNT(*) FROM sap_read_table('/BIC/ASALES01')

Common causes:

Wrong technical name
No data for filter criteria
Need to activate DSO/InfoCube
Authorization missing

Slow Queries

BW query taking > 60 seconds:

Solutions:

Enable caching (required)
Add date range filters
Request BW team add indexes
Use aggregated InfoCubes instead of DSOs
Pre-aggregate in cache template

Authorization Issues

Error: RFC_ERROR_AUTHORITY

Required authorizations:

S_RFC - RFC authorization
S_RS_COMP - BW component authorization
Query-specific authorizations

Contact SAP Basis/BW team.

Environment Setup

# .env
export SAP_BW_HOST="bw-prod.company.com"
export SAP_BW_SYSNR="00"
export SAP_BW_CLIENT="100"
export SAP_BW_USER="BW_API_USER"
export SAP_BW_PASSWORD="secure-password"

Next Steps

SAP ERP Connection: Connect to SAP ERP/S4HANA
Caching Setup: Essential for BW
BigQuery Connection: Alternative cloud warehouse

Prerequisites​

Quick Start​

Connection Parameters​

Reading BW Data​

InfoCube Queries​

DSO (DataStore Object) Queries​

ADSO (Advanced DSO in BW/4HANA)​

Common BW Objects​

InfoCubes​

DSOs​

BEx Queries​

Caching BW Data​

BW-Specific Considerations​

1. Technical Names​

2. Naming Conventions​

3. Date/Time Fields​

4. Master Data​

Complete Example​

Best Practices​

DO​

DON'T​

Troubleshooting​

No Data Returned​

Slow Queries​

Authorization Issues​

Environment Setup​

Next Steps​