Skip to main content

Celonis Product Documentation

Replacing SAP cluster tables (BSEG)

SAP ECC stores so-called "transparent" and "cluster" tables. The extraction of cluster tables requires significantly more time than the extraction of transparent tables. Therefore, cluster tables should be excluded/replaced if possible.

Two common SAP cluster tables are BSEG and CDPOS. This topics explain why a replacement of BSEG is beneficial precisely, and how it can be achieved.

Replacing BSEG
Why is the extraction of BSEG underperforming?

  • BSEG is a cluster table → accessing data takes time

  • Many records → filtering on KOART (dependent on the process)

  • Many columns → many remain unused

What is a cluster table?

BSEG is a cluster table, which means it does not actually store real values but simply references other tables' values.

For the BSEG example, those are BSIK, BSAK, BSID and BSAD (as illustrated in the below picture).

35553816.png
Solution

Replacing BSEG with its corresponding secondary index tables

  • AP: BSIK + BSAK

  • O2C: BSAD (+ BSID) → for the Activity 'Clear Invoice' BSAD is sufficient. If there are more activities that require BSEG, BSID may need to be added.

  • AR: BSID + BSAD

  • P2P: BSIK + BSAK

Benefits

  • Decrease extraction time (extracting only relevant records/columns)

  • Improve the pipeline (BSEG is often the overall bottleneck)

  • Make the extraction ready for new delta load approach based on change-logs

  • Consume less storage/compute resources

Shortcomings

The replacement tables (BSIK, BSAK, BSID, BSAK) contain less columns than BSEG. Hence, the replacement could lead to missing fields.

Considering the fields in the App Store analyses for each process, the missing fields (and possible replacements in brackets) are:

  • AP: WERKS (→ LFA1.Land1), SPGRP, SPGRM, SPGRT, SPGRG, SPGRV, SPGRQ, SPGRC, SPGRS, NAME1_CPD, KUNNR, NAME1, NAME, KTOSL, BEWAR, MATNR

  • O2C: no missing fields

  • AR: WERKS (→ LFA1.Land1), MATNR, LIFNR (Affected analyses: Payment Behavior, Segregation of Duties, Dunning Report, Dunning Blocks, Process Benchmark, Overdue Invoices, Low value invoicing, Customer Segmentation, Cash Discount, Days Sales Outstanding, First-Time-Right, Process Cockpit, Aging of Receivables, One-Time Customers)

  • P2P: WERKS (Affected Analysis: Supplier Segmentation)

Considering the BSEG views, the missing fields in the standard script are:

  • AP: AUGCP, VALUT, FDTAG, BZDAT, VRSDT, ANFAE, LINFV, TXDAT, PEROP_BEG, PEROP_END, PRODPER

  • O2C: no missing fields

  • AR: tbd

  • P2P: AUGCP, VALUT, FDTAG, BZDAT, VRSDT, ANFAE, LINFV, TXDAT, PEROP_BEG, PEROP_END, PRODPER

Note on recurring elements

Table BKDF stores templates for recurring documents (e.g., rent or insurance). Those documents were part of BSEG but they are not part of the replacement tables anymore. In case they are still required, BSEG needs to be extracted and joined on BKDF additionally.

However, recurring documents themselves are no accounting documents, whereas they don't affect account balances. Therefore, those elements were not considered in the standard AP connector; for example.

Replacement steps

  1. Check if you are using fields in the analysis that will be missing after the replacement:

    1. Are they replaceable through other tables?

    2. Are they really needed?

  2. Determine which replacement tables are needed (see above).

  3. Extract the Tables:

    1. Order: If both BSI* and BSA* are needed, a second extraction needs to be created to ensure that BSI* is extracted before BSA* (no missing entries)

    2. Filters: GJAHR, CPUDT, BSCHL can be reused

    3. Delta filter: CPUDT can be used

    35553817.png

  4. "Re-create" BSEG in the transformations (Union):

    • Delete duplicates (see examples below)

    • Join on BKPF in transformation (instead of extraction)

  5. Adjust Transformations:

    • Remove filters on KOART in the transformations (due to the specific tables those are not needed anymore)

    • Adjust BSEG data model view (see possible missing fields above)

Transformation Examples

The full load needs to be extended by a transformation in the very beginning. It re-creates the BSEG from the replacement tables. In addition, duplicates resulting from a broader primary key in these tables are removed.

The following example scripts represent different approaches how the replacement can be realized.

Important

A common problem with long running transformations and slow data model loads is due to missing statistics in Vertica. This issue can be resolved by adding the Vertica ANALYZE_STATISTICS statement directly in the SQL. For more information, refer to Vertica Transformations Optimization.

AR Process

Union BSEG (AR)

DROP TABLE IF EXISTS "BSEG";

CREATE TABLE "BSEG" AS (
        SELECT BSAD.*
        FROM "AR_BSAD" AS BSAD
        INNER JOIN BKPF ON 1=1
                AND BKPF.MANDT = BSAD.MANDT
                AND BKPF.BUKRS = BSAD.BUKRS
                AND BKPF.GJAHR = BSAD.GJAHR
                AND BKPF.BELNR = BSAD.BELNR
        UNION ALL
        SELECT BSID.*
        FROM "AR_BSID" AS BSID
        INNER JOIN BKPF ON 1=1
                AND BKPF.MANDT = BSID.MANDT
                AND BKPF.BUKRS = BSID.BUKRS
                AND BKPF.GJAHR = BSID.GJAHR
                AND BKPF.BELNR = BSID.BELNR
                WHERE BSID.BELNR || BSID.BUKRS || BSID.GJAHR || BSID.BUZEI NOT IN (select BELNR || BUKRS || GJAHR || BUZEI from AR_BSAD)
);
AP Process

Union BSEG (AR)

CREATE VIEW "BSEG_UNION" AS (
SELECT * FROM BSIK AS BSIK
UNION ALL
SELECT * FROM BSAK AS BSAK
);

DROP TABLE IF EXISTS BSEG;

CREATE TABLE BSEG AS(
SELECT
    ROW_NUMBER() OVER(PARTITION BY BSEG.MANDT, BSEG.BUKRS , BSEG.BELNR, BSEG.GJAHR, BSEG.BUZEI ORDER BY BSEG.MANDT, BSEG.BUKRS , BSEG.BELNR, BSEG.GJAHR, BSEG.BUZEI, _CELONIS_CHANGE_DATE DESC) AS NUM
        ,BSEG.*
FROM "BSEG_UNION" AS BSEG ORDER BY NUM DESC
);

DELETE FROM BSEG WHERE NUM > 1;
DROP VIEW IF EXISTS "BSEG_UNION";
O2C Process

Union BSEG (O2C)

DROP TABLE IF EXISTS BSAD_C;

CREATE TABLE BSAD_C AS(
SELECT
        ROW_NUMBER() OVER(PARTITION BY BSAD.MANDT, BSAD.BUKRS , BSAD.BELNR, BSAD.GJAHR, BSAD.BUZEI ORDER BY       
        BSAD.MANDT, BSAD.BUKRS , BSAD.BELNR, BSAD.GJAHR, BSAD.BUZEI, BSAD._CELONIS_CHANGE_DATE DESC) AS NUM
        ,BSAD.*
FROM "BSAD" AS BSAD ORDER BY NUM DESC
);

DELETE FROM BSAD_C WHERE NUM > 1;
AP Process for object-centric process mining
CREATE OR REPLACE VIEW "BSEG_UNION" AS (
  SELECT * FROM "BSIK"
  UNION ALL
  SELECT * FROM "BSAK"
);

CREATE OR REPLACE VIEW "BSEG" AS(
  SELECT * FROM 
    (SELECT
      ROW_NUMBER() OVER(PARTITION BY "BSEG"."MANDT", "BSEG"."BUKRS", "BSEG"."BELNR", "BSEG"."GJAHR", "BSEG"."BUZEI" 
        ORDER BY "BSEG"."AUGBL", "BSEG"."MANDT", "BSEG"."BUKRS", "BSEG"."BELNR", "BSEG"."GJAHR", "BSEG"."BUZEI" DESC) AS NUM
      ,"BSEG".*
      , 'K' as KOART
      , "BSIM"."MATNR" as MATNR
    FROM "BSEG_UNION" AS BSEG
    LEFT JOIN "BSIM" ON 1=1
        AND "BSEG"."MANDT" = "BSIM"."MANDT"
        AND "BSEG"."BELNR" = "BSIM"."BELNR"
        AND "BSEG"."GJAHR" = "BSIM"."GJAHR"
        AND "BSEG"."BUZEI" = "BSIM"."BUZEI"
    ) BSEG WHERE NUM = 1
);