SAP Datasphere: Building the Calculation Views and Semantic Layer That Make Joule Accurate

Part 3 of 7 — Joule on SAP Business Data Cloud: An End-to-End Project at AutoFrance SA

---

The raw tables are landing. Now we model.

Everything in this part follows a strict three-layer convention:

Raw (RT_) — source tables as they arrive. Never exposed to business users or Joule. Read-only.

Harmonised (CV_) — calculation views. Clean, join, standardise. This is where supplier reconciliation happens, where FI document types get filtered to AP and AR categories, where cost centres map to plant codes.

Semantic (AN_) — analytical datasets. The only objects Joule queries. Every measure has a display label, a description, and a semantic type. Without those annotations Joule cannot understand what a column means.

We built the harmonised layer before the semantic layer, and we built the supplier mapping before any financial view. Here is why.

---

Why Supplier Reconciliation Came First

The temptation was to start with the FCF calculation view — it was the headline deliverable. We did not.

At first join, 119 of 340 active suppliers — €2.1 billion in annual spend — were unmatched between Ariba and S/4HANA. Ariba used V-XXXXX codes. S/4HANA used LIF-XXXXX. Nobody had ever connected them at the data level.

If we had built the FCF view first, Joule would have confidently answered working capital questions covering only 66 percent of supplier spend — and nobody looking at the output would have known the other 34 percent was missing.

Fixing the supplier mapping first was not a detour. It was the only path to a system that is actually correct.

---

Step 1: The SUPPLIER_MAPPING Table

A local table inside AUTOFRANCE_FINANCE — not a remote table. It lives in Datasphere and is the canonical supplier registry everything else references.

sql

CREATE TABLE SUPPLIER_MAPPING (
    CANONICAL_SUPPLIER_ID  NVARCHAR(20)  NOT NULL,   -- SM-00001 format
    LIFNR                  NVARCHAR(10),              -- S/4HANA LIF-XXXXX
    ARIBA_SUPPLIER_ID      NVARCHAR(10),              -- Ariba V-XXXXX
    SUPPLIER_NAME          NVARCHAR(100) NOT NULL,
    VAT_NUMBER             NVARCHAR(20),
    COUNTRY_CODE           NVARCHAR(3),
    ACTIVE                 BOOLEAN       DEFAULT TRUE,
    MAPPED_BY              NVARCHAR(50),
    MAPPED_DATE            DATE,
    DATA_SOURCE            NVARCHAR(20)  DEFAULT 'MANUAL',
    PRIMARY KEY (CANONICAL_SUPPLIER_ID)
);

We populated it in three passes.

Pass 1 — VAT number auto-match (186 suppliers):

sql

SELECT
    a.ARIBA_SUPPLIER_ID,
    a.SUPPLIER_NAME        AS ARIBA_NAME,
    a.VAT_NUMBER           AS ARIBA_VAT,
    s.LIFNR,
    s.NAME1                AS S4_NAME,
    s.STCD3                AS S4_VAT,
    'VAT_AUTO'             AS DATA_SOURCE
FROM RT_ARIBA_CONTRACT  a
JOIN RT_S4_LFA1          s
  ON UPPER(TRIM(a.VAT_NUMBER)) = UPPER(TRIM(s.STCD3))
WHERE a.VAT_NUMBER IS NOT NULL
  AND s.STCD3    IS NOT NULL;

Pass 2 — Normalised name match (29 more suppliers):

sql

SELECT
    a.ARIBA_SUPPLIER_ID,
    a.SUPPLIER_NAME,
    s.LIFNR,
    s.NAME1,
    'FUZZY_NAME'           AS DATA_SOURCE
FROM RT_ARIBA_CONTRACT  a
JOIN RT_S4_LFA1          s
  ON UPPER(REGEXP_REPLACE(
         REGEXP_REPLACE(a.SUPPLIER_NAME,
             '\s+(SA|SAS|SARL|GMBH|BV|SL|LTD|INC)\s*$','','i'),
         '[^A-Z0-9]',''
     ))
   = UPPER(REGEXP_REPLACE(
         REGEXP_REPLACE(s.NAME1,
             '\s+(SA|SAS|SARL|GMBH|BV|SL|LTD|INC)\s*$','','i'),
         '[^A-Z0-9]',''
     ))
WHERE a.ARIBA_SUPPLIER_ID NOT IN (
    SELECT ARIBA_SUPPLIER_ID FROM SUPPLIER_MAPPING
    WHERE ARIBA_SUPPLIER_ID IS NOT NULL
);

Every fuzzy name match was reviewed manually before inserting. The remaining 26 suppliers were validated by the procurement team against physical contracts and inserted with DATA_SOURCE = ‘MANUAL’.

---

Step 2: CV_SUPPLIER_MAP

sql

CREATE VIEW CV_SUPPLIER_MAP AS
SELECT
    sm.CANONICAL_SUPPLIER_ID,
    sm.SUPPLIER_NAME,
    sm.VAT_NUMBER,
    sm.COUNTRY_CODE,
    lfa.LIFNR,
    lfb.ZTERM                                AS DEFAULT_PAYMENT_TERM,
    arc.ARIBA_SUPPLIER_ID,
    arc.PAYMENT_DAYS                         AS ARIBA_CONTRACT_DAYS,
    arc.EFFECTIVE_DATE                       AS CONTRACT_START_DATE,
    CASE
        WHEN lfa.LIFNR IS NOT NULL
         AND arc.ARIBA_SUPPLIER_ID IS NOT NULL THEN 'BOTH'
        WHEN lfa.LIFNR IS NOT NULL             THEN 'S4_ONLY'
        WHEN arc.ARIBA_SUPPLIER_ID IS NOT NULL THEN 'ARIBA_ONLY'
        ELSE 'UNMAPPED'
    END                                      AS SUPPLIER_COVERAGE,
    sm.ACTIVE
FROM SUPPLIER_MAPPING      sm
LEFT JOIN RT_S4_LFA1        lfa ON sm.LIFNR             = lfa.LIFNR
LEFT JOIN RT_S4_LFB1        lfb ON sm.LIFNR             = lfb.LIFNR
                                AND lfb.BUKRS           = 'FR01'
LEFT JOIN RT_ARIBA_CONTRACT arc ON sm.ARIBA_SUPPLIER_ID = arc.ARIBA_SUPPLIER_ID
WHERE sm.ACTIVE = TRUE;

---

Step 3: CV_COST_PLANT

Cost centres in S/4HANA link to plants via a numbering convention AutoFrance SA established at go-live: the first digit of the cost centre code identifies the plant.

sql

CREATE VIEW CV_COST_PLANT AS
SELECT
    cs.KOSTL,
    cs.KTEXT                    AS COST_CENTRE_NAME,
    CASE SUBSTRING(cs.KOSTL, 1, 1)
        WHEN '1' THEN 'NORD'
        WHEN '2' THEN 'SUD'
        WHEN '3' THEN 'EST'
        WHEN '4' THEN 'IDF'
        ELSE 'SHARED'
    END                         AS PLANT_CODE,
    CASE SUBSTRING(cs.KOSTL, 1, 1)
        WHEN '1' THEN 'Valenciennes'
        WHEN '2' THEN 'Bordeaux'
        WHEN '3' THEN 'Strasbourg'
        WHEN '4' THEN 'Poissy'
        ELSE 'Shared / Corporate'
    END                         AS PLANT_NAME,
    cs.DATBI                    AS VALID_TO
FROM RT_S4_CSKS cs
WHERE cs.KOKRS = 'FR01'
  AND cs.DATBI >= CURRENT_DATE;

---

Step 4: CV_AP_AGING

sql

CREATE VIEW CV_AP_AGING AS
WITH ap_base AS (
    SELECT
        bk.BELNR,
        bk.GJAHR,
        bk.BUDAT                            AS POSTING_DATE,
        bs.LIFNR,
        bs.KOART,
        bs.SHKZG,
        bs.DMBTR                            AS AMOUNT_LCY,
        bs.ZBD3T                            AS NET_PAYMENT_DAYS,
        bs.ZFBDT                            AS BASE_DATE_PAYMENT,
        CASE bs.SHKZG
            WHEN 'H' THEN  bs.DMBTR
            WHEN 'S' THEN -bs.DMBTR
        END                                 AS NET_AMOUNT_LCY,
        DATEADD(DAY, bs.ZBD3T, bs.ZFBDT)   AS DUE_DATE,
        DATEDIFF(DAY,
            DATEADD(DAY, bs.ZBD3T, bs.ZFBDT),
            CURRENT_DATE)                   AS DAYS_OVERDUE,
        bs.KOSTL
    FROM RT_S4_BKPF  bk
    JOIN RT_S4_BSEG  bs
      ON bk.BELNR = bs.BELNR
     AND bk.GJAHR = bs.GJAHR
     AND bk.BUKRS = bs.BUKRS
    WHERE bk.BUKRS  = 'FR01'
      AND bs.KOART  = 'K'
      AND bs.AUGBL  IS NULL
      AND bk.BUDAT >= ADD_MONTHS(CURRENT_DATE, -24)
)
SELECT
    ab.BELNR,
    ab.GJAHR,
    ab.POSTING_DATE,
    ab.DUE_DATE,
    ab.DAYS_OVERDUE,
    ab.NET_AMOUNT_LCY,
    ab.NET_PAYMENT_DAYS,
    CASE
        WHEN ab.DAYS_OVERDUE <= 0              THEN 'CURRENT'
        WHEN ab.DAYS_OVERDUE BETWEEN 1 AND 30  THEN '01_30_DAYS'
        WHEN ab.DAYS_OVERDUE BETWEEN 31 AND 60 THEN '31_60_DAYS'
        WHEN ab.DAYS_OVERDUE BETWEEN 61 AND 90 THEN '61_90_DAYS'
        ELSE '90_PLUS_DAYS'
    END                                        AS AGING_BUCKET,
    sm.CANONICAL_SUPPLIER_ID,
    sm.SUPPLIER_NAME,
    sm.COUNTRY_CODE,
    sm.SUPPLIER_COVERAGE,
    cp.PLANT_CODE,
    cp.PLANT_NAME
FROM ap_base              ab
LEFT JOIN CV_SUPPLIER_MAP  sm ON ab.LIFNR  = sm.LIFNR
LEFT JOIN CV_COST_PLANT    cp ON ab.KOSTL  = cp.KOSTL;

The AUGBL IS NULL filter keeps only open, uncleared items. The 24-month window keeps the view performant against a 40-million-row BSEG.

---

Step 5: CV_AR_AGING

sql

CREATE VIEW CV_AR_AGING AS
WITH ar_base AS (
    SELECT
        bk.BELNR,
        bk.GJAHR,
        bk.BUDAT                            AS POSTING_DATE,
        bs.KUNNR,
        CASE bs.SHKZG
            WHEN 'S' THEN  bs.DMBTR
            WHEN 'H' THEN -bs.DMBTR
        END                                 AS NET_AMOUNT_LCY,
        DATEADD(DAY, bs.ZBD3T, bs.ZFBDT)   AS DUE_DATE,
        DATEDIFF(DAY,
            DATEADD(DAY, bs.ZBD3T, bs.ZFBDT),
            CURRENT_DATE)                   AS DAYS_OVERDUE,
        bs.ZBD3T                            AS NET_PAYMENT_DAYS,
        bs.KOSTL
    FROM RT_S4_BKPF  bk
    JOIN RT_S4_BSEG  bs
      ON bk.BELNR = bs.BELNR
     AND bk.GJAHR = bs.GJAHR
     AND bk.BUKRS = bs.BUKRS
    WHERE bk.BUKRS  = 'FR01'
      AND bs.KOART  = 'D'
      AND bs.AUGBL  IS NULL
      AND bk.BUDAT >= ADD_MONTHS(CURRENT_DATE, -24)
)
SELECT
    ar.BELNR,
    ar.GJAHR,
    ar.POSTING_DATE,
    ar.KUNNR,
    ar.DUE_DATE,
    ar.DAYS_OVERDUE,
    ar.NET_AMOUNT_LCY,
    ar.NET_PAYMENT_DAYS,
    CASE
        WHEN ar.DAYS_OVERDUE <= 0              THEN 'CURRENT'
        WHEN ar.DAYS_OVERDUE BETWEEN 1 AND 30  THEN '01_30_DAYS'
        WHEN ar.DAYS_OVERDUE BETWEEN 31 AND 60 THEN '31_60_DAYS'
        WHEN ar.DAYS_OVERDUE BETWEEN 61 AND 90 THEN '61_90_DAYS'
        ELSE '90_PLUS_DAYS'
    END                                        AS AGING_BUCKET,
    cp.PLANT_CODE,
    cp.PLANT_NAME
FROM ar_base            ar
LEFT JOIN CV_COST_PLANT  cp ON ar.KOSTL = cp.KOSTL;

---

Step 6: CV_FCF_PLANT

The centrepiece of the harmonised layer. FCF per plant per fiscal period.

sql

CREATE VIEW CV_FCF_PLANT AS
WITH
ap_payments AS (
    SELECT
        cp.PLANT_CODE,
        TO_VARCHAR(bk.BUDAT, 'YYYYMM')      AS FISCAL_PERIOD,
        SUM(CASE bs.SHKZG WHEN 'S' THEN bs.DMBTR ELSE 0 END)
                                            AS AP_PAID_EUR,
        SUM(CASE bs.SHKZG WHEN 'H' THEN bs.DMBTR ELSE 0 END)
                                            AS AP_INVOICED_EUR,
        COUNT(DISTINCT bs.LIFNR)            AS SUPPLIER_COUNT
    FROM RT_S4_BKPF  bk
    JOIN RT_S4_BSEG  bs
      ON bk.BELNR = bs.BELNR AND bk.GJAHR = bs.GJAHR AND bk.BUKRS = bs.BUKRS
    LEFT JOIN CV_COST_PLANT cp ON bs.KOSTL = cp.KOSTL
    WHERE bk.BUKRS = 'FR01'
      AND bs.KOART = 'K'
      AND bk.BUDAT >= ADD_MONTHS(CURRENT_DATE, -24)
    GROUP BY cp.PLANT_CODE, TO_VARCHAR(bk.BUDAT, 'YYYYMM')
),
ar_receipts AS (
    SELECT
        cp.PLANT_CODE,
        TO_VARCHAR(bk.BUDAT, 'YYYYMM')      AS FISCAL_PERIOD,
        SUM(CASE bs.SHKZG WHEN 'H' THEN bs.DMBTR ELSE 0 END)
                                            AS AR_COLLECTED_EUR,
        SUM(CASE bs.SHKZG WHEN 'S' THEN bs.DMBTR ELSE 0 END)
                                            AS AR_INVOICED_EUR
    FROM RT_S4_BKPF  bk
    JOIN RT_S4_BSEG  bs
      ON bk.BELNR = bs.BELNR AND bk.GJAHR = bs.GJAHR AND bk.BUKRS = bs.BUKRS
    LEFT JOIN CV_COST_PLANT cp ON bs.KOSTL = cp.KOSTL
    WHERE bk.BUKRS = 'FR01'
      AND bs.KOART = 'D'
      AND bk.BUDAT >= ADD_MONTHS(CURRENT_DATE, -24)
    GROUP BY cp.PLANT_CODE, TO_VARCHAR(bk.BUDAT, 'YYYYMM')
)
SELECT
    COALESCE(ap.PLANT_CODE, ar.PLANT_CODE)  AS PLANT_CODE,
    COALESCE(ap.FISCAL_PERIOD, ar.FISCAL_PERIOD) AS FISCAL_PERIOD,
    COALESCE(ar.AR_COLLECTED_EUR, 0)        AS AR_COLLECTED_EUR,
    COALESCE(ar.AR_INVOICED_EUR, 0)         AS AR_INVOICED_EUR,
    COALESCE(ap.AP_PAID_EUR, 0)             AS AP_PAID_EUR,
    COALESCE(ap.AP_INVOICED_EUR, 0)         AS AP_INVOICED_EUR,
    COALESCE(ap.SUPPLIER_COUNT, 0)          AS ACTIVE_SUPPLIER_COUNT,
    COALESCE(ar.AR_COLLECTED_EUR, 0)
  - COALESCE(ap.AP_PAID_EUR, 0)            AS FCF_ESTIMATE_EUR,
    CASE
        WHEN COALESCE(ap.AP_INVOICED_EUR, 0) = 0 THEN NULL
        ELSE (COALESCE(ap.AP_PAID_EUR, 0) / ap.AP_INVOICED_EUR) * 30
    END                                     AS DPO_DAYS,
    CASE
        WHEN COALESCE(ar.AR_INVOICED_EUR, 0) = 0 THEN NULL
        ELSE (COALESCE(ar.AR_COLLECTED_EUR, 0) / ar.AR_INVOICED_EUR) * 30
    END                                     AS DSO_DAYS
FROM ap_payments  ap
FULL OUTER JOIN ar_receipts ar
  ON ap.PLANT_CODE = ar.PLANT_CODE AND ap.FISCAL_PERIOD = ar.FISCAL_PERIOD;

---

Step 7: CV_TERMS_CHANGE

Detects suppliers who shortened payment terms in the last 6 months — a leading indicator of working capital pressure.

sql

CREATE VIEW CV_TERMS_CHANGE AS
WITH term_history AS (
    SELECT
        ARIBA_SUPPLIER_ID,
        PAYMENT_DAYS,
        EFFECTIVE_DATE,
        LAG(PAYMENT_DAYS) OVER (
            PARTITION BY ARIBA_SUPPLIER_ID ORDER BY EFFECTIVE_DATE
        )                                   AS PREVIOUS_PAYMENT_DAYS,
        LAG(EFFECTIVE_DATE) OVER (
            PARTITION BY ARIBA_SUPPLIER_ID ORDER BY EFFECTIVE_DATE
        )                                   AS PREVIOUS_EFFECTIVE_DATE
    FROM RT_ARIBA_TERMS_HISTORY
)
SELECT
    th.ARIBA_SUPPLIER_ID,
    sm.CANONICAL_SUPPLIER_ID,
    sm.SUPPLIER_NAME,
    th.PREVIOUS_PAYMENT_DAYS,
    th.PAYMENT_DAYS                         AS NEW_PAYMENT_DAYS,
    th.PREVIOUS_PAYMENT_DAYS - th.PAYMENT_DAYS AS DAYS_SHORTENED,
    th.EFFECTIVE_DATE                       AS CHANGE_DATE,
    arc.ANNUAL_SPEND_EUR,
    (arc.ANNUAL_SPEND_EUR / 365.0)
        * (th.PREVIOUS_PAYMENT_DAYS - th.PAYMENT_DAYS)
                                            AS ESTIMATED_CASH_IMPACT_EUR
FROM term_history  th
JOIN CV_SUPPLIER_MAP sm ON th.ARIBA_SUPPLIER_ID = sm.ARIBA_SUPPLIER_ID
LEFT JOIN (
    SELECT ARIBA_SUPPLIER_ID, SUM(AMOUNT_EUR) AS ANNUAL_SPEND_EUR
    FROM RT_ARIBA_PO
    WHERE PO_DATE >= ADD_MONTHS(CURRENT_DATE, -12) AND STATUS != 'CANCELLED'
    GROUP BY ARIBA_SUPPLIER_ID
) arc ON th.ARIBA_SUPPLIER_ID = arc.ARIBA_SUPPLIER_ID
WHERE th.PREVIOUS_PAYMENT_DAYS IS NOT NULL
  AND th.PAYMENT_DAYS < th.PREVIOUS_PAYMENT_DAYS
  AND th.EFFECTIVE_DATE >= ADD_MONTHS(CURRENT_DATE, -6);

---

Step 8: AN_FCF_PLANT — The Semantic Layer Dataset Joule Reads

Set Semantic Usage to “Analytical Dataset” in the Datasphere view properties. Then annotate every column in the Output Columns tab. The annotations live in the metadata catalogue, not in the SQL.

The 14 KPI measures:

Column	Label	Description Joule reads
FCF_ESTIMATE_EUR	Free Cash Flow	AR collected minus AP paid. Management estimate for working capital monitoring. Not IFRS compliant.
AR_COLLECTED_EUR	AR Collections	Cash received from customers. FI document type DZ postings, company code FR01.
AR_INVOICED_EUR	AR Invoiced	New customer receivables raised. FI document type DR postings.
AP_PAID_EUR	AP Payments	Cash paid to suppliers. FI document type ZP postings.
AP_INVOICED_EUR	AP Invoices Received	New vendor invoices posted. FI document types KR and RE.
DPO_DAYS	Days Payable Outstanding	Average days AutoFrance takes to pay suppliers. Higher is better for AutoFrance cash position.
DSO_DAYS	Days Sales Outstanding	Average days customers take to pay. Lower is better.
DPO_DAYS_QTR	DPO Quarterly Average	3-month rolling average of DPO. Use for quarter-over-quarter comparisons, not the monthly figure.
DSO_DAYS_QTR	DSO Quarterly Average	3-month rolling average of DSO.
ACTIVE_SUPPLIER_COUNT	Active Suppliers	Distinct suppliers with at least one AP posting in the period.
PLANT_CODE	Plant	NORD (Valenciennes), SUD (Bordeaux), EST (Strasbourg), IDF (Poissy).
PLANT_NAME	Plant Name	Full name of the AutoFrance SA plant.
FISCAL_PERIOD	Period	Accounting period YYYYMM, calendar year.
SUPPLIER_COVERAGE_RATE	Supplier Coverage	Percentage of AP volume where supplier is matched in both S/4HANA and Ariba. Below 90% = mapping gap.

The annotation quality difference is real. Before annotation, Joule’s response to “what was FCF at NORD in September 2024?” included: “I am not certain whether this represents euros, thousands, or another unit.”

After annotation it said:

“The estimated free cash flow at the Valenciennes plant (NORD) for September 2024 was €14.8 million. AR collected €38.2M minus AP paid €21.4M minus inventory investment €2.0M.”

---

Build Order

Build in this sequence — each object depends on the one above it:

1. SUPPLIER_MAPPING (local table, no dependencies)
2. RT_ remote tables (from Part 2)
3. CV_COST_PLANT
4. CV_SUPPLIER_MAP
5. CV_AP_AGING
6. CV_AR_AGING
7. CV_FCF_PLANT
8. CV_TERMS_CHANGE
9. AN_FCF_PLANT (set Semantic Usage here)

---

Related Reading on DataCloudInsights

The complete Building an AI-Powered Finance Assistant with SAP Joule on BDC series:

• Part 1 – Building an AI-Powered Finance Assistant with SAP Joule on BDC
• Part 2 –  SAP Datasphere: Connecting S/4HANA, Ariba, and Databricks
• Part 3 – SAP Datasphere: Building the Calculation Views and Semantic Layer That Make Joule Accurate

---

Published by the Data Cloud Insights team — SAP data professionals with hands-on BDC and Joule implementation experience across enterprise clients in Europe.