Skip to main content

BICS Lineage Tracking

This guide covers the three BICS lineage functions that ERPL exposes and shows how to use them for impact analysis, governance, and documentation in SAP BW landscapes.

For BI Administrators and Data Governance Teams

The lineage functions read directly from RSTRAN, RSTRANFIELD, and related BW system tables. They expose the same data Eclipse-based tools see, but as DuckDB tables you can join, filter, and aggregate freely.

The three lineage functions​

All three are zero-positional. Filter and shape results with normal SQL.

FunctionReturnsBest for
sap_bics_lineage_edges()Flat edge list (one row per source→target hop)Catalog-wide lineage, joins, group-bys
sap_bics_lineage_trace()Walk forward from one source, with hop count and path"What is downstream of this table/field?"
sap_bics_lineage_graph_json()Full lineage as a single JSON documentVisualization, export to graph tools

Flat edge list​

sap_bics_lineage_edges() returns one row per edge in the BW data flow graph.

Columns (all VARCHAR):

ColumnDescription
edge_typeEdge category (e.g. transformation step, query element)
src_kindSource object kind (DATASOURCE, INFOPROVIDER, QUERY, …)
src_nameSource object name
src_fieldSource field (empty for object-level edges)
tgt_kindTarget object kind
tgt_nameTarget object name
tgt_fieldTarget field

Named parameters: scope (limits the underlying RFC reads to objects matching the name; useful on very large landscapes), secret (DuckDB secret name).

Get everything​

SELECT * FROM sap_bics_lineage_edges();
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;

Count distinct source tables feeding each InfoProvider​

SELECT
  tgt_name AS infoprovider,
  COUNT(DISTINCT src_name) AS source_table_count
FROM sap_bics_lineage_edges()
WHERE tgt_kind = 'INFOPROVIDER' AND src_kind = 'DATASOURCE'
GROUP BY tgt_name
ORDER BY source_table_count DESC;

Field-level edges only​

SELECT src_name, src_field, tgt_name, tgt_field
FROM sap_bics_lineage_edges()
WHERE src_field <> '' AND tgt_field <> '';

Scope to a specific object​

For large landscapes, pass scope so the underlying RFC reads filter server-side:

SELECT * FROM sap_bics_lineage_edges(scope => '0D_FC_NW_C01_Q0008');

Forward trace from a source​

sap_bics_lineage_trace() answers "what is downstream of this object?". It walks the edge graph forward and returns one row per hop, including the full chain in the path column.

Columns:

ColumnTypeDescription
hopINTEGERDistance from the source (1 = direct downstream)
source_objectVARCHARSource object at this hop
source_fieldVARCHARSource field (empty for object-level edges)
target_objectVARCHARTarget object at this hop
target_fieldVARCHARTarget field
edge_typeVARCHAREdge category
pathVARCHARThe chain of objects walked from the original source to this row

Named parameters: source_object (required for a meaningful trace), source_field (optional, for field-level tracing), secret.

Impact analysis: what depends on VBAK?​

SELECT hop, target_object, edge_type, path
FROM sap_bics_lineage_trace(source_object => 'VBAK')
ORDER BY hop, target_object;

Field-level trace​

If NETWR in VBAK is renamed, what BW objects need updating?

SELECT hop, target_object, target_field, path
FROM sap_bics_lineage_trace(
  source_object => 'VBAK',
  source_field => 'NETWR'
)
ORDER BY hop;

Count downstream queries​

SELECT COUNT(DISTINCT target_object) AS downstream_queries
FROM sap_bics_lineage_trace(source_object => 'VBAK')
WHERE edge_type LIKE '%QUERY%';

JSON graph​

sap_bics_lineage_graph_json() returns the entire lineage as one JSON document. Useful for visualization libraries and external graph tools.

-- One row with the full JSON graph
SELECT * FROM sap_bics_lineage_graph_json();

-- Export to a file
COPY (SELECT * FROM sap_bics_lineage_graph_json())
TO 'lineage.json' (FORMAT 'json');

The JSON shape is suitable for direct ingestion by D3.js, Cytoscape.js, or the JSON exporter of most graph databases.

Common patterns​

Find unused DataSources​

DataSources with no downstream consumers are candidates for cleanup:

WITH all_sources AS (
  SELECT DISTINCT src_name AS name
  FROM sap_bics_lineage_edges()
  WHERE src_kind = 'DATASOURCE'
),
used_sources AS (
  SELECT DISTINCT src_name AS name
  FROM sap_bics_lineage_edges()
  WHERE src_kind = 'DATASOURCE' AND tgt_kind = 'INFOPROVIDER'
)
SELECT name FROM all_sources
EXCEPT
SELECT name FROM used_sources;

Pre-change impact assessment​

Before changing a source table, dump the affected query list:

COPY (
  SELECT DISTINCT target_object AS affected_query
  FROM sap_bics_lineage_trace(source_object => 'VBAK')
  WHERE edge_type LIKE '%QUERY%'
)
TO 'vbak_affected_queries.csv' (HEADER, DELIMITER ',');

Persist a lineage snapshot​

Capture lineage as it was on a specific date — useful for audit/compliance:

CREATE TABLE lineage_snapshot_2026_05_15 AS
SELECT * FROM sap_bics_lineage_edges();

Performance notes​

  • All three functions read from BW system tables via RFC (RSTRAN, RSTRANFIELD, etc.). On large landscapes this can be slow on the first call.
  • Use the scope parameter on sap_bics_lineage_edges() to limit the RFC scan when you only care about one object.
  • For repeated analysis, persist a snapshot to DuckDB (CREATE TABLE ... AS SELECT * FROM ...) and query that.

Combining with metadata functions​

The sap_bics_meta_* family exposes detailed metadata on individual object types (providers, queries, transformations, fields). Join them against sap_bics_lineage_edges() to enrich edges with descriptions:

SELECT
  e.edge_type,
  e.src_kind, e.src_name,
  q.description AS query_description,
  e.tgt_kind, e.tgt_name
FROM sap_bics_lineage_edges() e
LEFT JOIN sap_bics_meta_queries() q ON q.query_name = e.tgt_name
WHERE e.tgt_kind = 'QUERY';

(Exact metadata column names vary by function — call each sap_bics_meta_*() once to inspect its schema.)

Next Steps​