BICS Protocol Deep Dive

This comprehensive guide covers the BICS (BI Consumer Services) protocol in ERPL. Learn how to execute SAP BW queries, extract cube data, and track end-to-end lineage from source tables to BEx queries.

What is BICS?

BICS (BI Consumer Services) is SAP's protocol for accessing SAP Business Warehouse (BW) data. It allows you to execute queries, extract cube data, access InfoProviders, and track complete data lineage from source ERP tables through transformations to final BEx queries.

BICS Architecture

Basic Querying

List Available Cubes and Queries

-- Show all InfoProviders (Cubes)
SELECT * FROM sap_bics_show(obj_type => 'CUBE');

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

-- Show all InfoProviders
SELECT * FROM sap_bics_show(obj_type => 'INFOPROVIDER');

Simple Query Execution

The basic BICS query pattern follows this flow:

1. Begin - Start a query session
2. Filter - Apply filters and selections
3. Result - Execute and get data

-- Basic pattern: begin → filter → result
SELECT * FROM sap_bics_result(
  sap_bics_filter(
    sap_bics_begin('0BWTESTCUBE'),
    '0CALMONTH', '202401'
  )
);

-- More complex example with multiple filters — chain one sap_bics_filter() per characteristic.
-- Each filter() call takes (state, characteristic, value [, more_values...]).
SELECT * FROM sap_bics_result(
  sap_bics_filter(
    sap_bics_filter(
      sap_bics_filter(
        sap_bics_begin('0BWTESTCUBE'),
        '0CALMONTH', '202401'
      ),
      '0CALYEAR', '2024'
    ),
    '0MATERIAL', 'MAT001'
  )
);

Column Selection

-- Select specific columns
SELECT * FROM sap_bics_result(
  sap_bics_columns(
    sap_bics_filter(
      sap_bics_begin('0BWTESTCUBE'),
      '0CALMONTH', '202401'
    ),
    '0CALMONTH',
    '0CALYEAR',
    '0MATERIAL',
    '0SALES_AMOUNT'
  )
);

Intermediate Querying

Working with Hierarchies

-- List available hierarchies
SELECT * FROM sap_bics_show_hierarchies();

-- Query hierarchy data (flat list)
SELECT * FROM sap_bics_hierarchy('0MATERIAL_HIER');

-- Pin a hierarchy version and return as a recursive tree
SELECT * FROM sap_bics_hierarchy(
  '0MATERIAL_HIER',
  version => '01',
  as_tree => true
);

InfoObject Details

-- Describe InfoObject
SELECT * FROM sap_bics_describe_infoobject('0MATERIAL');

-- Get InfoObject details
SELECT 
  technical_name,
  description,
  data_type,
  length
FROM sap_bics_describe_infoobject('0MATERIAL');

Query Descriptions

-- Describe cube structure
SELECT * FROM sap_bics_describe('0BWTESTCUBE');

-- Describe query
SELECT * FROM sap_bics_describe('0BWTESTCUBE', 'ZQUERY_SALES');

-- Describe persisted query
SELECT * FROM sap_bics_describe();

Advanced Querying

Complex Filtering

-- Multiple filter conditions — chain one sap_bics_filter() call per characteristic.
SELECT * FROM sap_bics_result(
  sap_bics_filter(
    sap_bics_filter(
      sap_bics_filter(
        sap_bics_filter(
          sap_bics_begin('0BWTESTCUBE'),
          '0CALMONTH', '202401'
        ),
        '0CALYEAR', '2024'
      ),
      '0MATERIAL', 'MAT001'
    ),
    '0SALES_ORG', '1000'
  )
);

-- Multiple values for a single characteristic: pass them as positional varargs
-- to a single filter() call (e.g. multi-select).
SELECT * FROM sap_bics_result(
  sap_bics_filter(
    sap_bics_begin('0BWTESTCUBE'),
    '0CALMONTH', '202401', '202402', '202403'
  )
);

Query State Management

-- Begin query session
SELECT * FROM sap_bics_begin('0BWTESTCUBE');

-- Apply filters
SELECT * FROM sap_bics_filter(
  sap_bics_begin('0BWTESTCUBE'),
  '0CALMONTH', '202401'
);

-- Get results
SELECT * FROM sap_bics_result(
  sap_bics_filter(
    sap_bics_begin('0BWTESTCUBE'),
    '0CALMONTH', '202401'
  )
);

AO-style Characteristic Properties

sap_bics_set_char_prop toggles per-characteristic display properties that match the radio controls in SAP Analysis for Office's Properties panel. The mutation persists in the BICS state and is honoured by the next sap_bics_result call.

-- Build a 1D cross-tab on country
SELECT * FROM sap_bics_begin('0D_NW_C01', id => 'q1');
SELECT * FROM sap_bics_rows('q1', '0D_NW_CNTRY', op => 'SET');

-- Display member texts instead of keys ("Germany" vs "DE")
SELECT * FROM sap_bics_set_char_prop('q1', '0D_NW_CNTRY', 'DISPLAY', 'TEXT');

-- Show both key and text concatenated
SELECT * FROM sap_bics_set_char_prop('q1', '0D_NW_CNTRY', 'DISPLAY', 'BOTH');

-- Sort by member descending
SELECT * FROM sap_bics_set_char_prop('q1', '0D_NW_CNTRY', 'SORT', 'DESC');

-- Fetch with the new presentation
SELECT * FROM sap_bics_result('q1');

prop	Allowed value	Maps to BICS state field
DISPLAY	KEY | TEXT | BOTH	RESULT_SET_PRESENTATION bitflag (KEY=4, TEXT=32)
TOTALS	SHOW | HIDE	RESULT_VISIBILITY ('A' / 'N')
SORT	ASC | DESC | NONE	RESULT_SET_SORTING.DIRECTION ('A' / 'D' / '')

The DESCRIBE payload of state_rows, state_columns, and state_free carries {display, totals, sort} so clients can read the current values without an extra round-trip.

Grand-total row visibility

BICS does not expose a state field for the SUMME / "Overall Result" grand-total row. TOTALS='HIDE' is persisted to per-characteristic RESULT_VISIBILITY but the server still returns the grand-total row in sap_bics_result. Clients that want AO's "Hide Result" behaviour can filter the row whose row-characteristic value matches SUMME / Overall Result / localised variants — that's what AO itself does.

Interactive: bics-tui

bics-tui (in bics/examples/tui/) is a terminal UI that wraps every BICS function above into a Textual app modelled on SAP Analysis for Office: log on, browse cubes / queries, build a cross-tab by moving characteristics between Rows / Columns / Background Filter, filter members, and toggle Display / Sort / Totals for a focused characteristic or Scaling Factor / Decimal Places for a focused key figure — all from a context-sensitive Properties panel on the right. Every server-side action is mirrored into an always-on SQL recorder, so the resulting script replays cleanly in a vanilla DuckDB shell.

Logon

The logon screen pre-fills the standard ABAP Platform Trial credentials — overwrite for your own system. The fields are wired straight into a DuckDB CREATE SECRET of type sap_rfc.

Analysis screen

Three-column layout: cross-tab grid on the left, design panel in the middle (Data Source / Columns / Rows / Background Filter / Key Figures), context-sensitive Properties panel on the right. Hotkeys are shown next to each section header.

Properties — Characteristic on axis

Focus a characteristic in any axis list and the Properties panel switches to three radio groups: Display (Key / Text / Both), Totals (Show / Hide / Conditional), Sort (None / Asc / Desc). Display and Sort round-trip via sap_bics_set_char_prop; Totals Hide is applied client-side (BICS does not expose a state field for the grand-total row).

Properties — Key figure

Focus a key figure and the panel switches to Scaling Factor and Decimal Places. Both are client-side numeric formatting — BICS state carries no field for them. The KF columns in the cross-tab re-format live as you change the controls.

Run it

GEN=ninja make release          # from erpl monorepo root
cd bics/examples/tui
uv sync
LD_LIBRARY_PATH=/path/to/erpl/nwrfcsdk/linux/lib uv run python -m bics_tui

The screenshots above are regenerated headlessly via scripts/capture_screens.py (Textual's export_screenshot → SVG → PNG with a mock session for layout-only capture; no SAP connection needed).

Advanced: Lineage Tracking (For SAP BW Experts)

For BI Administrators

This section covers advanced lineage tracking from ERP tables through DataSources, InfoProviders, and BEx queries.

Complete Lineage as Edges

sap_bics_lineage_edges() returns the BW data flow as a flat edge list spanning ERP source tables → DataSources → transformations → InfoProviders → BEx queries. Each row is one source-to-target edge with these columns:

Column	Description
edge_type	Edge category (e.g. transformation, query-element)
src_kind, src_name, src_field	Source object kind, name, and optional field
tgt_kind, tgt_name, tgt_field	Target object kind, name, and optional field

Use cases:

• Impact analysis — what happens if a source table changes?
• Data governance — track data flow and transformations
• Documentation — automatic lineage documentation
• Compliance — audit data lineage for regulations

-- All edges in the system
SELECT * FROM sap_bics_lineage_edges();

-- Edges related to a specific BEx query — filter in SQL
SELECT *
FROM sap_bics_lineage_edges()
WHERE tgt_name = '0D_FC_NW_C01_Q0008' OR src_name = '0D_FC_NW_C01_Q0008'
ORDER BY src_kind, tgt_kind;

-- Scope the underlying RFC reads to one object (faster on large landscapes)
SELECT * FROM sap_bics_lineage_edges(scope => '0D_FC_NW_C01_Q0008');

Forward Trace from a Source

sap_bics_lineage_trace() walks the graph forward from a specific source object/field — perfect for "if I change this ERP table, what downstream BW objects break?". The result includes a hop column and a path column (the chain of objects walked) so users can see the full downstream blast radius.

-- Everything downstream of the VBAK ERP table
SELECT * FROM sap_bics_lineage_trace(source_object => 'VBAK')
ORDER BY hop;

-- Field-level trace
SELECT * FROM sap_bics_lineage_trace(
  source_object => 'VBAK',
  source_field => 'NETWR'
);

Lineage as a JSON Graph

sap_bics_lineage_graph_json() returns the full lineage as a JSON document, suitable for visualization libraries (D3, Cytoscape, etc.) or export to external graph tools.

SELECT * FROM sap_bics_lineage_graph_json();

Metadata Mining

-- InfoProvider metadata
SELECT * FROM sap_bics_meta_providers();

-- DataSource metadata
SELECT * FROM sap_bics_meta_datasources();

-- Transformation metadata
SELECT * FROM sap_bics_meta_transformations();

-- Query metadata
SELECT * FROM sap_bics_meta_queries();

-- Query usage statistics
SELECT * FROM sap_bics_meta_query_usage();

-- Query elements
SELECT * FROM sap_bics_meta_query_elements();

-- Query statistics
SELECT * FROM sap_bics_meta_query_stats();

Field-Level Metadata

-- DataSource field metadata
SELECT * FROM sap_bics_meta_datasource_fields();

-- Provider field metadata
SELECT * FROM sap_bics_meta_provider_fields();

-- Transformation field metadata
SELECT * FROM sap_bics_meta_transform_fields();

-- HCPR (Hierarchy Change Pointer) metadata
SELECT * FROM sap_bics_meta_hcpr_components();

-- HCPR mapping metadata
SELECT * FROM sap_bics_meta_hcpr_mapping();

-- InfoObject metadata
SELECT * FROM sap_bics_meta_infoobjects();

-- Object cross-reference metadata
SELECT * FROM sap_bics_meta_objxref();

Real-World Examples

Daily Sales Report

-- Extract daily sales data from BW cube
WITH daily_sales AS (
  SELECT 
    "0CALMONTH" AS sales_month,
    "0MATERIAL" AS material,
    "0SALES_AMOUNT" AS amount,
    "0SALES_QTY" AS quantity
  FROM sap_bics_result(
    sap_bics_filter(
      sap_bics_begin('0BWTESTCUBE'),
      '0CALMONTH', '202401',
      '0SALES_ORG', '1000'
    )
  )
)
SELECT 
  sales_month,
  COUNT(*) AS material_count,
  SUM(amount) AS total_amount,
  SUM(quantity) AS total_quantity,
  AVG(amount) AS avg_amount
FROM daily_sales
GROUP BY sales_month
ORDER BY sales_month;

Material Analysis with Hierarchy

-- Analyze materials using hierarchy
WITH material_hierarchy AS (
  SELECT * FROM sap_bics_hierarchy('0MATERIAL_HIER', as_tree => true)
),
sales_data AS (
  SELECT 
    "0MATERIAL" AS material,
    "0SALES_AMOUNT" AS amount
  FROM sap_bics_result(
    sap_bics_filter(
      sap_bics_begin('0BWTESTCUBE'),
      '0CALMONTH', '202401'
    )
  )
)
SELECT 
  h.hierarchy_level,
  h.material_name,
  s.amount,
  s.amount * 100.0 / SUM(s.amount) OVER() AS percentage
FROM material_hierarchy h
JOIN sales_data s ON h.material = s.material
ORDER BY s.amount DESC;

Cross-System Data Integration

-- Combine BW data with ERP data
WITH bw_sales AS (
  SELECT 
    "0MATERIAL" AS material,
    "0SALES_AMOUNT" AS bw_amount
  FROM sap_bics_result(
    sap_bics_filter(
      sap_bics_begin('0BWTESTCUBE'),
      '0CALMONTH', '202401'
    )
  )
),
erp_materials AS (
  SELECT 
    MATNR AS material,
    MTART AS material_type,
    MEINS AS base_unit
  FROM sap_read_table('MARA', MAX_ROWS => 10000)
)
SELECT 
  e.material,
  e.material_type,
  e.base_unit,
  b.bw_amount,
  CASE 
    WHEN b.bw_amount > 10000 THEN 'High Value'
    WHEN b.bw_amount > 1000 THEN 'Medium Value'
    ELSE 'Low Value'
  END AS value_category
FROM erp_materials e
LEFT JOIN bw_sales b ON e.material = b.material
WHERE b.bw_amount IS NOT NULL
ORDER BY b.bw_amount DESC;

Performance Optimization

Query Optimization

-- Use column selection to reduce data transfer
SELECT * FROM sap_bics_result(
  sap_bics_columns(
    sap_bics_filter(
      sap_bics_begin('0BWTESTCUBE'),
      '0CALMONTH', '202401'
    ),
    '0CALMONTH',
    '0SALES_AMOUNT'  -- Only select needed columns
  )
);

-- Apply filters early to reduce data volume
SELECT * FROM sap_bics_result(
  sap_bics_filter(
    sap_bics_begin('0BWTESTCUBE'),
    '0CALMONTH', '202401',  -- Filter by month first
    '0SALES_ORG', '1000'    -- Then by sales org
  )
);

Batch Processing

-- Process multiple queries in parallel
SELECT 'Q0001' AS query_name, COUNT(*) AS record_count
FROM sap_bics_result(
  sap_bics_filter(
    sap_bics_begin('0BWTESTCUBE'),
    '0CALMONTH', '202401'
  )
)

UNION ALL

SELECT 'Q0002' AS query_name, COUNT(*) AS record_count
FROM sap_bics_result(
  sap_bics_filter(
    sap_bics_begin('0BWTESTCUBE'),
    '0CALMONTH', '202402'
  )
);

Troubleshooting

Common Issues

Query Not Found

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

-- Verify query name (case-sensitive)
SELECT * FROM sap_bics_describe('0BWTESTCUBE', 'ZQUERY_SALES');

Permission Denied

-- Check InfoProvider access
SELECT * FROM sap_bics_show(obj_type => 'CUBE');

-- Verify user permissions in SAP BW
SELECT * FROM sap_bics_describe('0BWTESTCUBE');

Data Not Found

-- Check filter values
SELECT * FROM sap_bics_describe_infoobject('0CALMONTH');

-- Verify data exists
SELECT * FROM sap_bics_result(
  sap_bics_begin('0BWTESTCUBE')  -- No filters to see all data
);

Debugging Tips

-- Enable debug mode
SET debug_mode = true;

-- Check query structure
SELECT * FROM sap_bics_describe('0BWTESTCUBE');

-- Test basic query
SELECT * FROM sap_bics_begin('0BWTESTCUBE');

-- Monitor query performance
EXPLAIN SELECT * FROM sap_bics_result(
  sap_bics_filter(
    sap_bics_begin('0BWTESTCUBE'),
    '0CALMONTH', '202401'
  )
);

For SAP BW Experts

Advanced BICS Internals

This section covers BICS protocol internals for SAP BW administrators and developers.

BICS Protocol Details

BICS uses HTTP/HTTPS with XML payloads for communication:

• Port: Usually 8080 or 8443 (HTTPS)
• Protocol: HTTP/HTTPS with XML
• Authentication: Basic Auth or SAML
• Compression: Optional gzip compression
• Unicode: Full Unicode support

Performance Considerations

1. Query Optimization: Use filters and column selection
2. Connection Pooling: Reuse connections for multiple queries
3. Batch Processing: Process multiple queries in parallel
4. Metadata Caching: Cache metadata for repeated access

Security Best Practices

1. Network Security: Use HTTPS for all BICS connections
2. Authentication: Use strong passwords or certificates
3. Authorization: Implement proper BW user roles
4. Audit Logging: Enable BICS audit logs in SAP BW

Lineage Tracking Best Practices

1. Regular Updates: Refresh lineage data periodically
2. Impact Analysis: Use lineage for change impact assessment
3. Documentation: Generate automatic lineage documentation
4. Compliance: Maintain lineage for regulatory requirements

Next Steps

🚀 Ready for More?

• ODP Protocol Guide - Delta replication from SAP
• RFC Protocol Guide - Read SAP tables and call functions
• Function Reference - Complete API docs

🔧 Advanced Topics

• BICS Lineage Tracking - Track data lineage
• Performance Tuning - Optimize BW queries
• Real-World Use Cases - Complete scenarios

💡 Examples

• ERPL Examples - More real-world BICS examples
• Integration with Python - Use BICS data with Pandas
• Real-World Use Cases - Complete scenarios

---

Need help? Check our troubleshooting guide or browse more examples.