SAP ERP Sales Analytics — from raw SD/MM integration to multidimensional aggregates and ontology

Workshop goal

By the time you finish this Workshop, you will have walked through one full cycle of the following flow inside D.Hub.

• Load a single scenario into portal so that one collection, 21 datasets (5 raw + 6 intermediate + 2 unified + 8 aggregate), 18 code nodes, 13 pipelines, an ontology with 10 entities and 10 relations, and one agent are all registered together.
• Clean and transform 5 SAP raw tables — VBAK (order header), VBAP (order line), KNA1 (customer master), MARA (material master), MAKT (material description) — across three domains: sales, material, customer.
• Build the unified master table tb_sales_master that joins all three domains into one row.
• Execute 7 aggregate pipelines in parallel across time (year / month / day), region (country / continent), and category (customer / industry / material type).
• Surface a 5-hop who buys what when how much path across the 10-entity, 10-relation SAP_* graph.

This is the most dataset-rich Workshop in the catalog. The dependency graph across 13 pipelines is the central concept. Recommended duration: 90 minutes.

Prerequisites

• An engineer account with access to D.Hub portal (Editor or higher, with pipeline execution rights)
• ~3 MB of download room for one scenario zip (roughly 6× the others)

No terminal, Python, or dhub2-examples clone needed.

1. Load the scenario (10 min)

The zip is roughly 3 MB, so the upload progress bar runs longer than other scenarios. Once the Import dialog reads completed, one collection appears.

Following manifest.json, the import creates the following in order:

1. One collection — sap (alias: SAP ERP Sales Analytics)
2. 21 datasets — Raw 5 (tb_sap_vbak, tb_sap_vbap, tb_sap_kna1, tb_sap_mara, tb_sap_makt), intermediate 6, unified 2, aggregate 8
3. 18 codes (Python) — Each transformation step + build_sap_ontology
4. 13 pipelines — 4 cleansing + 1 unification + 7 aggregate + 1 ontology
5. Ontology — 10 entities (SAP_Customer, SAP_SalesOrder, SAP_SalesOrderLine, SAP_Country, SAP_Continent, SAP_OrderDate, SAP_OrderMonth, SAP_OrderYear, SAP_MaterialType, SAP_MaterialIndustry), 10 relations
6. One agent — sap_assistant

Step 1 is complete once the sap collection appears in the left tree. 21 datasets in one collection is the standard ERP-analytics shape — the domain boundaries are at the module level (SD/MM), making finer collection splits awkward.

2. Browse the raw SD/MM tables (10 min)

In the sap collection, start with the 5 raw tables.

• tb_sap_vbak — Order header. Columns: VBELN (order number), ERDAT (creation date, YYYYMMDD), KUNNR (customer ID), NETWR (net amount), WAERK (currency)
• tb_sap_vbap — Order line. Columns: composite key VBELN + POSNR (line number), MATNR (material number), KWMENG (quantity), NETWR (line amount)
• tb_sap_kna1 — Customer master. Columns: KUNNR, NAME1 (company name), LAND1 (country code), KTOKD (account group)
• tb_sap_mara — Material master. Columns: MATNR, MTART (material type), MATKL (material group / industry), MEINS (base unit)
• tb_sap_makt — Material description. Columns: MATNR, SPRAS (language key), MAKTX (description text)

SAP's native column names are 4–5 letter abbreviations — unfamiliar at first. Hold three keys in your head: VBELN (order), KUNNR (customer), MATNR (material). The §3 cleansing code nodes rename the rest into human-readable column names.

3. Cleansing pipelines — sales · material · customer (15 min)

In the sap collection's Pipelines section, run the three cleansing pipelines in order.

sap_pipeline_join_sales — Two stages.

1. join_vbak_vbap — Join tb_sap_vbak + tb_sap_vbap on VBELN → tb_sales_all (one row per order line)
2. processing_order_date — Parse ERDAT's YYYYMMDD string into tb_sales's order_date (date) with derived year / month / day columns

sap_pipeline_join_material — Three stages.

1. mapping_mara_info — Map tb_sap_mara's abbreviation columns to human-readable names → tb_mara_info
2. filtering_makt_lang — Keep only the current language rows in tb_sap_makt → tb_makt_lang_filter
3. join_mara_makt — Join the two on MATNR → tb_material (one row per material with description)

sap_pipeline_transform_customer — One stage.

• processing_customer_info — Trim company names in tb_sap_kna1, map country code (LAND1) → ISO → tb_customer

Press Run on each in sequence. Open tb_sales, tb_material, tb_customer previews to confirm columns now read as human-readable names.

4. Unified master table (10 min)

Open the sap_pipeline_unification_total pipeline. Two stages combine all three domains into one row.

1. join_sales_material — Left-join tb_sales + tb_material on MATNR → tb_sales_material (one row per order line carries material info)
2. join_customer_sales_material — Left-join that result + tb_customer on KUNNR → tb_sales_master

One row of tb_sales_master carries:

• Order metadata (VBELN, order_date, year, month, day)
• Line metadata (POSNR, quantity, line_amount)
• Material metadata (MATNR, material_type, material_industry, description)
• Customer metadata (KUNNR, customer_name, country, continent)

The denormalization is deliberate. The 7 aggregate pipelines all read this one table — they can group-by time, customer, material, or region without further joins.

Press Run. When it finishes, open the preview on tb_sales_master and confirm one row carries all four sections.

5. Multidimensional aggregate pipelines × 7 (15 min)

This step is the analytical surface of ERP. Run all 7 in parallel.

Time series × 3 — Aggregates per year / month / order_date on tb_sales_master.

• sap_pipeline_year_summary → tb_year_info (yearly revenue · line count · average amount)
• sap_pipeline_month_summary → tb_month_info (monthly)
• sap_pipeline_date_summary → tb_date_info (daily)

Customer / region × 3 — Aggregates per KUNNR / country / continent.

• sap_pipeline_customer_summary → tb_customer_summary
• sap_pipeline_country_summary → tb_customer_country_summary
• sap_pipeline_continent_summary → tb_customer_continent_summary

Category × 2 — Aggregates per material metadata.

• sap_pipeline_industry_summary → tb_industry_summary (per material industry)
• sap_pipeline_material_type → tb_material_summary (per material type)

Run each pipeline. When all finish, confirm each of the 8 aggregate datasets has at least 1 row.

6. Ontology + graph exploration (15 min)

Finally, the graph view. Run the ontology_materialization pipeline. build_sap_ontology reads the unified master and 8 aggregates in batch mode and lands the 10 entities + 10 relations in upsert mode.

Entities × 10:

• Transactions — SAP_SalesOrder, SAP_SalesOrderLine
• Customer — SAP_Customer, SAP_Country, SAP_Continent
• Time — SAP_OrderDate, SAP_OrderMonth, SAP_OrderYear
• Material categories — SAP_MaterialType, SAP_MaterialIndustry

Relations × 10 (key ones):

• SAP_locatedIn (customer → country), SAP_PartOf (country → continent)
• SAP_CUSTOMER_PLACED_ORDER, SAP_ORDER_ON_DATE, SAP_ORDER_HAS_LINE
• SAP_LINE_MATERIAL_TYPE, SAP_LINE_MATERIAL_INDUSTRY
• SAP_PartOf_YEAR, SAP_PartOf_MONTH

In the Graph explorer, surface the 5-hop who-buys-what-when-how-much path in one query.

MATCH (c:SAP_Customer)-[:SAP_CUSTOMER_PLACED_ORDER]->(o:SAP_SalesOrder)
       -[:SAP_ORDER_HAS_LINE]->(l:SAP_SalesOrderLine)
       -[:SAP_LINE_MATERIAL_INDUSTRY]->(ind:SAP_MaterialIndustry),
      (o)-[:SAP_ORDER_ON_DATE]->(d:SAP_OrderDate)
RETURN c, o, l, ind, d
LIMIT 30

Four dimensions — customer → order → line → material industry with date attached — fan out in one view. Patterns that don't easily surface in cross-tab SQL (which industries a customer concentrates on, and when they do so) become visible at a glance.

7. Next steps and a retrospective (5 min)

By this point you've walked through five flows on a single ERP scenario:

• SAP native abbreviations → human-readable column mapping
• 3-domain cleansing → denormalized unified master
• 7-dimension parallel aggregates
• 10-entity graph → multi-axis navigation
• Operational simplicity of the 13-pipeline dependency graph

Pick the most appealing direction:

• The Engineer Path Pipeline scheduling lesson — register the 13-pipeline dependency graph (cleansing 3 → unification 1 → aggregates 7 + ontology 1) as a daily batch.
• Run a natural-language query against the sap_assistant agent (next-round topic).
• Map your own ERP module (MM inventory, PP production, FI accounting) to the same shape — raw → cleanse → unify → aggregate → ontology.

Verification checklist

• The sap collection holds 21 datasets (raw 5 + intermediate 6 + unified 2 + aggregate 8).
• After sap_pipeline_join_sales, tb_sales's order_date is date type and the derived year / month columns are populated.
• After sap_pipeline_unification_total, tb_sales_master carries all four sections — order + line + material + customer — in one row.
• The 3 time-series aggregate row counts grow year ≤ month ≤ day.
• In the Graph explorer, starting from one SAP_Customer you can expand the 5-hop path (Customer → SalesOrder → SalesOrderLine → MaterialIndustry + OrderDate) in one view.