# Scenario: SAP ERP Sales Analytics

An SAP ERP integration scenario that ingests raw SD (Sales & Distribution) and MM (Materials Management) tables, joins and transforms them into analytics-ready views, and produces time-series, customer, material, and geographic summaries.

---

## Entity Relationship Overview

```text
Raw SAP tables
  tb_sap_vbak  tb_sap_vbap  tb_sap_kna1  tb_sap_mara  tb_sap_makt

               ┌─────────────────────────────────────────────┐
               │  sap_pipeline_join_sales                    │
               │  VBAK + VBAP → tb_sales_all → tb_sales      │
               └─────────────────────────────────────────────┘

               ┌─────────────────────────────────────────────┐
               │  sap_pipeline_join_material                 │
               │  MARA → tb_mara_info                        │
               │  MAKT → tb_makt_lang_filter                 │
               │  mara_info + makt_lang → tb_material        │
               └─────────────────────────────────────────────┘

               ┌─────────────────────────────────────────────┐
               │  sap_pipeline_transform_customer            │
               │  KNA1 → tb_customer                         │
               └─────────────────────────────────────────────┘

               ┌─────────────────────────────────────────────┐
               │  sap_pipeline_unification_total             │
               │  tb_material + tb_sales → tb_sales_material │
               │  tb_customer + tb_sales_material            │
               │                     → tb_sales_master       │
               └─────────────────────────────────────────────┘

  tb_sales_master ──► year / month / date / customer /
                      country / continent / industry / material summaries
  summaries + tb_sales + tb_sales_master ──► ontology_materialization
                                      → customer/order/order-line/material graph
```

---

## Datasets

| Name | Description |
| ---- | ----------- |
| `tb_sap_vbak` | SAP SD sales order headers (order no., date, org, customer) |
| `tb_sap_vbap` | SAP SD sales order line items (material, qty, net value) |
| `tb_sap_kna1` | SAP SD customer master (name, country, region) |
| `tb_sap_mara` | SAP MM material master (type, industry, base unit) |
| `tb_sap_makt` | SAP MM material descriptions (language, short/long text) |
| `tb_sales_all` | Raw join of VBAK + VBAP |
| `tb_sales` | Sales with parsed and processed order date fields |
| `tb_customer` | Processed customer dimension from KNA1 |
| `tb_mara_info` | Mapped material attributes from MARA |
| `tb_makt_lang_filter` | Material descriptions filtered to target language |
| `tb_material` | Final material dimension table (MARA + MAKT joined) |
| `tb_sales_material` | Sales joined with material dimension |
| `tb_sales_master` | Unified master table: sales + material + customer |
| `tb_year_info` | Annual sales aggregation |
| `tb_month_info` | Monthly sales aggregation |
| `tb_date_info` | Daily sales aggregation |
| `tb_customer_summary` | Per-customer sales summary |
| `tb_customer_country_summary` | Country-level customer aggregation |
| `tb_customer_continent_summary` | Continent-level customer aggregation |
| `tb_industry_summary` | Industry-level sales aggregation |
| `tb_material_summary` | Material type sales aggregation |
| `tb_order_review` | Order-review result using monthly revenue share, discount, and margin |

---

## Pipelines

### `sap_pipeline_join_sales`

**Purpose:** Produces the foundational sales fact table by combining SAP order headers (VBAK) with line items (VBAP) and enriching the result with structured date dimensions. All downstream pipelines that need order data read from `tb_sales`.

| Step | Script | Input → Output | Role |
| ---- | ------ | -------------- | ---- |
| `join_vbak_vbap` | `join_vbak_vbap` | `tb_sap_vbak` + `tb_sap_vbap` → `tb_sales_all` | Join order headers and line items on order number |
| `processing_order_date` | `processing_order_date` | `tb_sales_all` → `tb_sales` | Parse SAP date format; extract year, month, day columns |

---

### `sap_pipeline_join_material`

**Purpose:** Builds the material dimension table by combining material master attributes (MARA) with filtered human-readable descriptions (MAKT). Provides a clean material reference that can be joined to sales data.

| Step | Script | Input → Output | Role |
| ---- | ------ | -------------- | ---- |
| `mapping_mara_info` | `mapping_mara_info` | `tb_sap_mara` → `tb_mara_info` | Map raw MARA fields to analysis-ready attributes |
| `filtering_makt_lang` | `filtering_makt_lang` | `tb_sap_makt` → `tb_makt_lang_filter` | Filter material descriptions to a single target language |
| `join_mara_makt` | `join_mara_makt` | `tb_mara_info` + `tb_makt_lang_filter` → `tb_material` | Join attributes and descriptions into the material dimension |

---

### `sap_pipeline_transform_customer`

**Purpose:** Transforms the SAP customer master (KNA1) into a clean customer dimension with standardized country and region codes, ready for geographic aggregation.

| Step | Script | Input → Output | Role |
| ---- | ------ | -------------- | ---- |
| `processing_customer_info` | `processing_customer_info` | `tb_sap_kna1` → `tb_customer` | Standardize and enrich customer master fields |

---

### `sap_pipeline_unification_total`

**Purpose:** Creates the single unified master table (`tb_sales_master`) that all summary pipelines read from. Joins the three core dimensions — sales, material, and customer — into one denormalized fact table optimized for aggregation.

| Step | Script | Input → Output | Role |
| ---- | ------ | -------------- | ---- |
| `join_sales_material` | `join_sales_material` | `tb_material` + `tb_sales` → `tb_sales_material` | Enrich sales records with material attributes |
| `join_customer_sales_material` | `join_customer_sales_material` | `tb_customer` + `tb_sales_material` → `tb_sales_master` | Attach customer dimension to produce the unified master |

---

### Summary Pipelines

Each pipeline below reads from `tb_sales_master` (or an intermediate summary) and produces a single aggregated output. They are independent and can run in parallel after `sap_pipeline_unification_total` completes.

| Pipeline | Input → Output | Purpose |
| -------- | -------------- | ------- |
| `sap_pipeline_year_summary` | `tb_sales_master` → `tb_year_info` | Annual sales totals for trend analysis |
| `sap_pipeline_month_summary` | `tb_sales_master` → `tb_month_info` | Monthly sales totals for seasonal analysis |
| `sap_pipeline_date_summary` | `tb_sales_master` → `tb_date_info` | Daily sales totals for operational monitoring |
| `sap_pipeline_customer_summary` | `tb_sales_master` → `tb_customer_summary` | Per-customer revenue, order count, and value |
| `sap_pipeline_country_summary` | `tb_customer_summary` → `tb_customer_country_summary` | Country-level revenue aggregation |
| `sap_pipeline_continent_summary` | `tb_customer_summary` → `tb_customer_continent_summary` | Continent-level revenue aggregation |
| `sap_pipeline_industry_summary` | `tb_sales_master` → `tb_industry_summary` | Revenue breakdown by material industry code |
| `sap_pipeline_material_type` | `tb_sales_master` → `tb_material_summary` | Revenue breakdown by material type |
| `sap_pipeline_order_review` | `tb_sales_master` → `tb_order_review` | Flag concentrated high-value, low-margin orders |

### Training commercial assumptions

The public SAP sample does not include pricing conditions, currency, or standard cost. The sales-material
join therefore adds explicit training assumptions so learners can turn revenue into a review decision:

- comparison currency is assumed to be `USD`;
- discount and cost ratios are assigned by material type;
- order `18622` is modeled as a high-value service contract with an 18% discount and 90% cost ratio;
- every enriched row is marked `TRAINING_ASSUMPTION` in `commercial_assumption`.

The order-review pipeline marks an order `PRIORITY_REVIEW` only when monthly revenue share is at least
50%, net sales are at least 1,000,000, and margin rate is below 20%. These thresholds are Workshop rules,
not accounting or sales policy.

### `ontology_materialization`

**Purpose:** Builds graph-ready ontology entity and relation tables from the SAP summary datasets and processed sales fact table.

The line-level graph is intended for the bundled sample scale, currently 3,413 order lines. For production-sized SAP exports, run this materialization in batches or switch to an aggregated order/material view before import. Missing material type or industry classifications are mapped to the `UNKNOWN` dimension node so sparse master data does not break graph construction.

| Step | Script | Input → Output | Role |
| ---- | ------ | -------------- | ---- |
| `build_ontology_tables` | `build_sap_ontology` | SAP summaries + `tb_sales` + `tb_sales_master` → `SAP_*` entities and relations | Materialize ontology import tables after upstream summaries finish |

---

## Ontology

| Type | Name | Relationship |
| ---- | ---- | ------------ |
| Entity | `sap_customer` | Customer dimension |
| Entity | `sap_sales_order` | Sales order header with quantity and net sales aggregates |
| Entity | `sap_sales_order_line` | Sales order line item with material, quantity, and net sales |
| Entity | `sap_country` | Country dimension |
| Entity | `sap_continent` | Continent dimension |
| Entity | `sap_order_date` | Order date dimension |
| Entity | `sap_order_month` | Order month dimension |
| Entity | `sap_order_year` | Order year dimension |
| Entity | `sap_material_type` | Material type dimension |
| Entity | `sap_material_industry` | Material industry dimension |
| Relation | `sap_located_in` | sap_customer → sap_country |
| Relation | `sap_customer_placed_order` | sap_customer → sap_sales_order |
| Relation | `sap_order_on_date` | sap_sales_order → sap_order_date |
| Relation | `sap_order_has_line` | sap_sales_order → sap_sales_order_line |
| Relation | `sap_line_material_type` | sap_sales_order_line → sap_material_type |
| Relation | `sap_line_material_industry` | sap_sales_order_line → sap_material_industry |
| Relation | `sap_part_of` | sap_country → sap_continent |
| Relation | `sap_placed_order` | sap_customer → sap_order_date |
| Relation | `sap_partof_year` | sap_order_date → sap_order_year |
| Relation | `sap_partof_month` | sap_order_date → sap_order_month |

---

## Sample Data

Tracked sample data is stored under `data/parquet/`. CSV exports are documented under `data/csv/README.md` and stored outside the repository.

Only the five raw SAP source tables are bundled. Derived datasets start empty and are rebuilt by the batch pipelines with snapshot reads and overwrite writes, so rerunning the workshop does not duplicate rows.

- `tb_sap_vbak.csv` — mandt, vbeln, audat, auart, vkorg, vtweg, spart, knumv, kunnr, recordstamp
- `tb_sap_vbap.csv` — mandt, vbeln, posnr, matnr, arktx, pstyv, kwmeng, vrkme, netwr, recordstamp
- `tb_sap_kna1.csv` — mandt, kunnr, name1, land1, ort01, regio, anred, recordstamp
- `tb_sap_mara.csv` — mandt, matnr, mtart, mbrsh, meins, recordstamp
- `tb_sap_makt.csv` — mandt, matnr, spras, maktx, maktg, recordstamp
