Semantic Layer & Metrics Specification¶

Strategic Decision: This implementation follows ADR-006: Semantic Layer (DRY).

The Semantic Layer provides a unified, business-friendly abstraction over raw costing data ingested via CSV (Masters) or XLSX (Manual Templates).

Why a Semantic Layer?¶

Problem: Raw Data Complexity¶

Your Excel costing sheets contain: - 50+ data points per part (material specs, operations, times, costs) - Multiple calculation stages (raw → conversion → packing → final) - Time-series data (12, 24, 36 month projections) - Derived metrics (efficiency %, YoY reduction, cost per piece)

Without a semantic layer: - Every report/dashboard recalculates metrics differently - Business users need to understand database schema - Changes to formulas require updating multiple places - No single source of truth for "Total Manufacturing Cost"

Semantic Layer Architecture¶

Concept¶

The semantic layer sits between raw data and business users, providing business-friendly metric definitions.

graph LR
    RawData[(Raw Data<br/>Tables)] --> SL[Semantic Layer]
    SL --> Metrics[Business Metrics]
    Metrics --> Dashboard[Dashboards]
    Metrics --> Reports[Reports]
    Metrics --> API[API Queries]

    style SL fill:#fce4ec,stroke:#e91e63,stroke-width:3px
    style Metrics fill:#fff3e0
    style Dashboard fill:#c8e6c9

Three-Layer Model¶

graph TD
    subgraph "Business Layer"
        KPI[KPIs & Dashboards]
        Analysis[Cost Analysis]
    end

    subgraph "Semantic Layer"
        Metrics[Metric Definitions]
        Calc[Calculation Logic]
    end

    subgraph "Data Layer"
        Raw[Raw Tables]
        Facts[Fact Tables]
    end

    KPI --> Metrics
    Analysis --> Metrics
    Metrics --> Calc
    Calc --> Facts
    Facts --> Raw

    style Metrics fill:#fce4ec,stroke:#e91e63,stroke-width:2px

Metrics Catalog Structure¶

Metric Definition Schema¶

metric_id: material_cost_per_piece
display_name: "Material Cost per Piece"
description: "Total raw material cost including scrap, calculated per finished piece"
category: material_costs
data_type: currency
unit: INR
formula: "(gross_weight * rm_rate) / yield_percent"
dependencies:
  - gross_weight
  - rm_rate
  - yield_percent
aggregation: sum
dimensions:
  - part_number
  - material_grade
  - time_period
business_owner: "Costing Team"
update_frequency: "On material rate change"

Core Metrics from Your Excel¶

Excel Template Structure¶

CostEngine imports data from structured Excel templates covering material costs, operations, tooling, and packing.

📊 Visual Template Reference
For detailed visual examples of the Excel template structure with annotated screenshots, see:
Excel Template Reference →

The templates contain rich, detailed data across multiple sections that map to the semantic layer metrics below.

1. Material Metrics¶

2. Operation Metrics¶

graph LR
    A[Operation Data] --> B[Setup Time]
    A --> C[Cycle Time]
    A --> D[Efficiency %]

    B --> E[Setup Cost]
    C --> F[Cycle Cost]
    D --> G[Effective MHR]

    E --> H[Operation Cost]
    F --> H
    G --> H

    style H fill:#c8e6c9,stroke:#4caf50,stroke-width:2px

3. Tooling Metrics¶

From Image 4:

4. Packing & Logistics Metrics¶

From Image 2:

5. Aggregate Metrics¶

Dimensional Model¶

Dimensions¶

erDiagram
    DIM_PART {
        uuid part_id PK
        string part_number
        string drawing_no
        string category
    }

    DIM_MATERIAL {
        uuid material_id PK
        string grade "SAE4140"
        string specification
        string unit
    }

    DIM_OPERATION {
        uuid operation_id PK
        string operation_name "Cutting, Turning, etc"
        string operation_category
    }

    DIM_MACHINE {
        uuid machine_id PK
        string machine_name
        decimal mhr
    }

    DIM_TIME {
        date date PK
        string time_period "After 12/24/36 months"
        int month
        int quarter
        int year
    }

    DIM_CUSTOMER {
        uuid customer_id PK
        string customer_name
        string grade "Bright Bar+, CG"
    }

    FACT_COST {
        uuid fact_id PK
        uuid part_id FK
        uuid material_id FK
        uuid time_id FK
        decimal gross_weight
        decimal net_weight
        decimal rm_rate
        decimal scrap_rate
        decimal raw_material_cost
        decimal conversion_cost
        decimal packing_cost
        decimal overhead_cost
        decimal total_cost
    }

    FACT_OPERATION {
        uuid fact_id PK
        uuid part_id FK
        uuid operation_id FK
        uuid machine_id FK
        decimal setup_time
        decimal cycle_time
        decimal efficiency_percent
        decimal cost
    }

    FACT_COST }o--|| DIM_PART : "for"
    FACT_COST }o--|| DIM_MATERIAL : "uses"
    FACT_COST }o--|| DIM_TIME : "in"
    FACT_OPERATION }o--|| DIM_PART : "for"
    FACT_OPERATION }o--|| DIM_OPERATION : "is"
    FACT_OPERATION }o--|| DIM_MACHINE : "on"

Metric Calculation Engine¶

Calculation Graph¶

flowchart TD
    Start[Raw Data Input] --> Parse[Parse Excel]
    Parse --> Validate[Validate Data]

    Validate --> M1[Calculate Material Metrics]
    Validate --> M2[Calculate Operation Metrics]
    Validate --> M3[Calculate Tooling Metrics]
    Validate --> M4[Calculate Packing Metrics]

    M1 --> A1[Material Cost]
    M1 --> A2[Scrap Cost]
    M1 --> A3[Yield %]

    M2 --> B1[Setup Cost]
    M2 --> B2[Cycle Cost]
    M2 --> B3[Efficiency Adj]

    B1 --> B4[Total Op Cost]
    B2 --> B4
    B3 --> B4

    M3 --> C1[Tool Cost/Piece]
    M4 --> D1[Packing Cost/Piece]

    A1 --> Rollup[Roll up to Total Cost]
    A2 --> Rollup
    B4 --> Rollup
    C1 --> Rollup
    D1 --> Rollup

    Rollup --> OH[Apply Overheads]
    OH --> Final[Final Cost Metrics]

    Final --> Store[Store in Semantic Layer]
    Store --> Expose[Expose via API]

    style M1 fill:#fff3e0
    style M2 fill:#e3f2fd
    style M3 fill:#fce4ec
    style Store fill:#c8e6c9

Implementation: Metrics Metadata Table¶

Database Schema¶

CREATE TABLE metrics_catalog (
    metric_id VARCHAR(100) PRIMARY KEY,
    display_name VARCHAR(255),
    description TEXT,
    category VARCHAR(50),
    data_type VARCHAR(20),
    unit VARCHAR(20),
    formula TEXT,
    sql_expression TEXT,
    aggregation_method VARCHAR(50),
    business_owner VARCHAR(100),
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

CREATE TABLE metric_dependencies (
    metric_id VARCHAR(100) REFERENCES metrics_catalog(metric_id),
    depends_on_metric_id VARCHAR(100) REFERENCES metrics_catalog(metric_id),
    dependency_type VARCHAR(50)
);

CREATE TABLE metric_dimensions (
    metric_id VARCHAR(100) REFERENCES metrics_catalog(metric_id),
    dimension_name VARCHAR(100),
    dimension_table VARCHAR(100),
    join_key VARCHAR(100)
);

Example Metric Definition¶

INSERT INTO metrics_catalog VALUES (
    'raw_material_cost',
    'Raw Material Cost per Piece',
    'Total cost of raw material including yield loss',
    'material_costs',
    'decimal',
    'INR',
    '(gross_weight * rm_rate) / yield_percent',
    'SELECT (f.gross_weight * m.rm_rate) / f.yield_percent 
     FROM fact_cost f 
     JOIN dim_material m ON f.material_id = m.material_id',
    'SUM',
    'Costing Team',
    NOW(),
    NOW()
);

API Layer for Metrics¶

Metrics Query API¶

# Pseudo-code for Metrics API

GET /api/v1/metrics/{metric_id}/values
  ?part_id=abc-123
  &time_period=after_12_months
  &group_by=material_grade

Response:
{
  "metric_id": "raw_material_cost",
  "display_name": "Raw Material Cost per Piece",
  "unit": "INR",
  "data": [
    {
      "material_grade": "SAE4140",
      "time_period": "after_12_months",
      "value": 229.22,
      "breakdown": {
        "gross_weight": 2.304,
        "rm_rate": 99.5,
        "yield_percent": 100
      }
    }
  ]
}

Excel Import Pipeline with Semantic Layer¶

sequenceDiagram
    participant U as User
    participant UI as Upload UI
    participant Parser as Excel Parser
    participant Validator as Data Validator
    participant Mapper as Schema Mapper
    participant Calc as Calculation Engine
    participant SL as Semantic Layer
    participant DB as Database

    U->>UI: Upload Excel
    UI->>Parser: Parse file
    Parser->>Parser: Extract sheets
    Parser->>Parser: Identify structure

    Parser->>Validator: Validate data
    Validator->>Validator: Check required fields
    Validator->>Validator: Validate formulas

    Validator->>Mapper: Map to schema
    Mapper->>Mapper: Map "Diameter (mm)" → gross_weight
    Mapper->>Mapper: Map "Raw Material Rate" → rm_rate
    Mapper->>Mapper: Map "Cutting" → operation_id

    Mapper->>Calc: Trigger calculations
    Calc->>SL: Fetch metric definitions
    SL-->>Calc: Return formulas
    Calc->>Calc: Calculate all metrics

    Calc->>DB: Store raw data
    Calc->>DB: Store calculated metrics
    Calc->>SL: Update metric cache

    SL-->>UI: Import complete
    UI-->>U: Show cost summary

Benefits of Semantic Layer¶

For Business Users¶

For Developers¶

Time-Series & Projections¶

From your Excel, you have YoY reduction scenarios:

graph LR
    SOP[SOP Launch] --> M12[After 12 months]
    M12 --> M24[After 24 months]
    M24 --> M36[After 36 months]

    M12 -->|Cost Reduction| M12Val[₹524.13]
    M24 -->|Cost Reduction| M24Val[₹518.58]
    M36 -->|Cost Reduction| M36Val[₹513.15]

    style SOP fill:#fff3e0
    style M36Val fill:#c8e6c9

Semantic Layer handles this via: - dim_time table with period definitions - Metrics can be filtered/aggregated by time period - Projection scenarios as separate metric versions

Next Steps¶

1. Define Metric Catalog - Document all 50+ metrics
2. Build Parser - Excel → Semantic Layer mapping
3. Create API - Expose metrics via REST
4. Build Dashboard - Visualize key metrics
5. Implement Caching - Pre-calculate for performance

← System Architecture | Data Pipeline →