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

# flapi.yaml
connections:
  sap-bw:
    init: |
      INSTALL erpl;
      LOAD erpl;
    properties:
      ashost: bw.company.com     # BW application server
      sysnr: '00'                # System number
      client: '100'              # Client
      user: ${SAP_BW_USER}       # BW user
      passwd: ${SAP_BW_PASSWORD} # 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/

request:
  - field-name: year
    field-in: query
    required: true
    validators:
      - type: string
        pattern: '^\d{4}$'
  - field-name: region
    field-in: query
    required: false

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

-- sqls/sales-analysis.sql
-- Query InfoCube for sales data
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 {{conn.sap-bw.ashost}}./BIC/ASALES01   -- InfoCube technical name
WHERE /BIC/OCALYEAR = '{{params.year}}'
{{#params.region}}
  AND /BIC/OCREGION = '{{params.region}}'
{{/params.region}}

DSO (DataStore Object) Queries

-- sqls/customer-360.sql
-- Query DSO for detailed customer data
SELECT 
  CUSTOMER_ID,
  CUSTOMER_NAME,
  SEGMENT,
  TOTAL_PURCHASES,
  LAST_ORDER_DATE,
  LIFETIME_VALUE,
  CHURN_RISK_SCORE
FROM {{conn.sap-bw.ashost}}./BIC/ACUST360   -- DSO technical name
WHERE ACTIVE_FLAG = 'X'
ORDER BY LIFETIME_VALUE DESC
LIMIT 1000

ADSO (Advanced DSO in BW/4HANA)

-- sqls/realtime-inventory.sql
-- Query ADSO for near real-time data
SELECT 
  PLANT,
  MATERIAL,
  STORAGE_LOCATION,
  STOCK_QUANTITY,
  LAST_UPDATE_TIMESTAMP
FROM {{conn.sap-bw.ashost}}./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/

cache:
  enabled: true
  table: bw_sales_cache
  schedule: 12h          # BW updates overnight
  template_file: sales_dashboard_cache.sql

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

-- sqls/sales_dashboard_cache.sql
-- Materialize complex BW query results
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 {{conn.sap-bw.ashost}}./BIC/FSALES f     -- Fact table
LEFT JOIN {{conn.sap-bw.ashost}}./BIC/PCALENDAR c
  ON f.CALYEAR = c.CALYEAR AND f.CALMONTH = c.CALMONTH
LEFT JOIN {{conn.sap-bw.ashost}}./BIC/PREGION r
  ON f.REGION = r.REGION
LEFT JOIN {{conn.sap-bw.ashost}}./BIC/PPRODUCT p
  ON f.PRODUCT = p.PRODUCT
WHERE c.CALYEAR >= YEAR(CURRENT_DATE) - 2    -- Last 2 years
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: TO_DATE(CALDAY, 'YYYYMMDD')

4. Master Data

Join with master data for descriptions:

-- Join dimension with master data
SELECT 
  f.CUSTOMER,
  m.CUSTOMER_NAME,
  m.CITY,
  m.COUNTRY,
  SUM(f.REVENUE) as revenue
FROM {{conn.sap-bw.ashost}}./BIC/FSALES f
LEFT JOIN {{conn.sap-bw.ashost}}./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
# ═══════════════════════════════════════════════════════════════
connections:
  sap-bw-prod:
    init: |
      INSTALL erpl;
      LOAD erpl;
    properties:
      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/

authentication:
  required: true
  methods: [jwt]
  roles: [reporting-user]

cache:
  enabled: true
  table: bw_revenue_report
  schedule: 24h                # Daily refresh
  on_startup: true             # Load on flAPI start
  template_file: revenue_cache.sql

request:
  - field-name: year
    field-in: query
    description: Fiscal year (YYYY)
    required: false
    default: "2024"
  
  - field-name: region
    field-in: query
    description: Region code
    required: false
    validators:
      - type: string
        enum: [EMEA, AMER, APAC]

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

-- sqls/revenue_cache.sql
-- Daily materialization from BW InfoCube
SELECT 
  TO_DATE(fc.CALMONTH || '01', 'YYYYMMDD') 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 {{conn.sap-bw-prod.ashost}}./BIC/AREVENUE01 fc    -- Revenue InfoCube
LEFT JOIN {{conn.sap-bw-prod.ashost}}./BIC/PREGION reg
  ON fc.REGION = reg.REGION
LEFT JOIN {{conn.sap-bw-prod.ashost}}./BIC/PPRODCAT pc
  ON fc.PRODUCT_CAT = pc.PRODUCT_CAT
LEFT JOIN {{conn.sap-bw-prod.ashost}}./BIC/PCOUNTRY cc
  ON fc.COUNTRY = cc.COUNTRY
WHERE SUBSTRING(fc.CALMONTH, 1, 4) >= '2020'    -- Since 2020
  AND fc.RECORD_MODE = 'A'                      -- Active records only
GROUP BY fc.CALMONTH, reg.REGION_TEXT, pc.PRODUCT_CAT_TEXT, cc.COUNTRY_TEXT

-- sqls/revenue_report.sql
-- Fast API queries against materialized cache
SELECT 
  year,
  month,
  month_date,
  region,
  product_category,
  country,
  net_revenue,
  gross_revenue,
  quantity_sold,
  order_count,
  avg_margin_pct
FROM bw_revenue_report
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 {{conn.sap-bw.ashost}}./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​