Supply Chain Demand Forecasting, Inventory Optimization & MRP Simulation

Overview

This project builds an end-to-end, automated supply chain analytics solution focused on demand forecasting, inventory optimization, MRP simulation, and fulfillment performance for a consumer goods product catalog. The analysis uses the DataCo Smart Supply Chain dataset (~180,000 order records) loaded into an Oracle Autonomous Database, processed through Excel Power Query, and visualized in a six-page Power BI dashboard.

What distinguishes this project is the automation layer built into every stage: Oracle stored procedures automate data extraction and MRP calculation on a nightly schedule, Power Query custom functions automate transformation logic across hundreds of SKUs, and Power BI scheduled refresh pushes updated dashboards to stakeholders without manual intervention. The result is a closed-loop system where reporting findings feed back into master data updates and the next refresh cycle automatically reflects those changes.

Business Context

Consumer goods manufacturers managing hundreds of SKUs across multiple channels need to answer: How much of each product should we produce? When do we reorder? Are we delivering on time? Where are our bottlenecks? Are planned values tracking against actuals? This project directly models those questions using the same tools and workflows used in production supply chain environments — SQL against Oracle for data extraction, Excel for analysis and modeling, and Power BI for dashboards and reporting.

Technical Stack

• Database / ERP Layer: Oracle Autonomous Database (Always Free tier) — SQL for extraction, joins, aggregation, view creation; stored procedures for automated refresh and MRP calculation; DBMS_SCHEDULER for nightly execution
• MRP / Supply Planning Simulation: Oracle SQL tables and views simulating MRP net requirements logic — gross requirements, projected on-hand, net requirements, planned order releases with lead time offsets and lot sizing rules
• Data Processing & ETL: Excel Power Query with custom M functions, parameters for dynamic switching, and query folding documentation
• Analysis & Modeling: Excel formulas, pivot tables, Power Pivot data model, Goal Seek, Solver for lot sizing optimization, What-If analysis
• Forecasting & Demand Planning: Excel Forecast Sheet (ETS algorithm), moving averages, forecast write-back to Oracle
• Dashboards & Reporting: Power BI Desktop (six-page report with DAX measures, slicers, drilldowns, data alerts, Row-Level Security)

Dataset

DataCo Smart Supply Chain Dataset (Kaggle) — ~180,000 order records with product categories, customer segments, order dates, shipping modes, scheduled vs. actual delivery dates, late delivery flags, geographic data, and financial metrics (sales, profit, discounts). Loaded into Oracle Autonomous Database with a normalized relational schema (ORDERS, ORDER_ITEMS, PRODUCTS, CUSTOMERS, SHIPMENTS, FORECAST_PLAN, MRP_REQUIREMENTS, INVENTORY_SNAPSHOT).

The Full Automated Pipeline: Oracle → Power Query → Power BI

1. Oracle DBMS_SCHEDULER executes the REFRESH_SUPPLY_CHAIN_DATA stored procedure at 2:00 AM — pulls fresh data, recalculates derived fields, reads the latest demand forecast, runs MRP net requirements calculation, writes planned orders, and updates all six reporting views.
2. Power BI Service scheduled refresh triggers at 5:00 AM — connects to Oracle views, Power Query custom functions and parameters execute automatically against updated data.
3. DAX measures recalculate all KPIs, forecasts, MRP coverage metrics, and classifications. Data alerts evaluate thresholds and send notifications if critical metrics breach targets.
4. By morning, the dashboard would reflect last night’s actuals, MRP recommendations would be current, and supply plan alerts would have been sent for any issues — enabling the analyst to focus on analysis and decision-making rather than data preparation.

Oracle SQL Automation

• Reporting Views: Six pre-calculated views (VW_DEMAND_TIMESERIES, VW_FULFILLMENT_KPI, VW_PRODUCT_MASTER, VW_PLANNED_VS_ACTUAL, VW_MRP_PLAN, VW_FORECAST_VS_ACTUAL) defined once, reused on every refresh
• Stored Procedure: REFRESH_SUPPLY_CHAIN_DATA wraps the full pipeline — staging table refresh, derived field calculation, MRP net requirements execution, view rebuild, and audit logging
• Scheduled Job: DBMS_SCHEDULER runs the procedure nightly at 2:00 AM, same mechanism as production Oracle Fusion Cloud
• CTEs & Window Functions: Multi-stage calculations (cumulative revenue for ABC, rolling demand variability, MRP gross-to-net) structured as readable, modular CTEs

Power Query Automation

• Custom M Functions: fn_CalculateSafetyStock, fn_ClassifyABC, fn_ClassifyXYZ, fn_CalculateReorderPoint, fn_CalculateNetRequirements — write once, invoke across all categories
• Parameters: Date (analysis window), Source (Oracle environment), Service Level (Z-score), Planning Horizon (3/6/12 months)
• Query Folding: Documented which steps fold to Oracle vs. execute locally for performance optimization

Power BI Automation

• Scheduled Refresh: Designed to align with the Oracle job schedule via Power BI Service — architecture is in place, pending live gateway connection
• DAX Measures: Auto-calculating KPIs — YoY growth, on-time rate, forecast accuracy (MAE, MAPE), inventory turnover, MRP coverage ratio, supply plan adherence
• Data Alerts: Threshold notifications for in-stock rate drops, late delivery spikes, MRP stockout projections, forecast bias
• Row-Level Security: Marketing sees sell-through data; Operations sees production, fulfillment, MRP, and inventory

Schema Design

The flat DataCo CSV (~180,000 rows, 53 columns) was decomposed into a normalized relational schema consisting of 5 transactional tables and 3 supply planning tables. A staging table (STG_DATACO) mirrors the flat CSV structure for initial data load via Oracle Database Actions import tool, after which INSERT/SELECT statements normalize the data into the production tables.

• ORDERS — Order header: order ID, order date, customer ID, status, market, region
• ORDER_ITEMS — Line-item detail: quantity, unit price, discount, profit, sales
• PRODUCTS — Product master: category, department, price, plus planning parameters (lead time days, lot sizing method, minimum order quantity, EOQ, safety stock, reorder point)
• CUSTOMERS — Customer master: segment, city, state, country, zip code
• SHIPMENTS — Fulfillment: shipping mode, shipping date, scheduled vs. actual days, delivery status, late delivery risk flag
• FORECAST_PLAN — Demand forecast output: category, forecast period, forecasted quantity, method, confidence bounds
• MRP_REQUIREMENTS — MRP output: gross requirements, scheduled receipts, projected on-hand, net requirements, planned order quantity/release date, lot sizing method, exception flags
• INVENTORY_SNAPSHOT — Simulated current inventory positions derived from order/shipment data

Primary keys, foreign keys, and indexes were defined across all tables. Planning parameter columns were added to PRODUCTS to enable MRP simulation directly within the Oracle layer.

PRODUCTS Table with Planning Parameters

CREATE TABLE PRODUCTS (
    PRODUCT_CARD_ID     NUMBER(10)      NOT NULL,
    PRODUCT_NAME        VARCHAR2(300)   NOT NULL,
    CATEGORY_ID         NUMBER(10)      NOT NULL,
    CATEGORY_NAME       VARCHAR2(150)   NOT NULL,
    DEPARTMENT_ID       NUMBER(10)      NOT NULL,
    DEPARTMENT_NAME     VARCHAR2(100)   NOT NULL,
    PRODUCT_PRICE       NUMBER(12,2)    NOT NULL,
    PRODUCT_STATUS      NUMBER(1)       DEFAULT 0,
    -- Planning parameters (populated during inventory optimization phase)
    LEAD_TIME_DAYS      NUMBER(5)       DEFAULT 7,
    LOT_SIZING_METHOD   VARCHAR2(20)    DEFAULT 'EOQ',
    MIN_ORDER_QTY       NUMBER(10)      DEFAULT 1,
    EOQ                 NUMBER(12,2),
    SAFETY_STOCK        NUMBER(12,2),
    REORDER_POINT       NUMBER(12,2),
    CONSTRAINT PK_PRODUCTS PRIMARY KEY (PRODUCT_CARD_ID)
);

What it does: Creates the product master table with both catalog attributes (name, category, price) and supply planning parameters (lead time, lot sizing method, EOQ, safety stock, reorder point).
Why it’s included: Embedding planning parameters directly on the product record allows the MRP stored procedure to read everything it needs from a single table — no separate planning parameter file or lookup table required.
Why it’s written this way: DEFAULT values (e.g., 7-day lead time, EOQ lot sizing) give every product sensible planning defaults on load, so MRP can run immediately without manual setup. Nullable columns like EOQ and SAFETY_STOCK are left empty until the inventory optimization phase populates them with calculated values.

MRP Requirements Table

CREATE TABLE MRP_REQUIREMENTS (
    MRP_ID              NUMBER(10)      GENERATED ALWAYS AS IDENTITY NOT NULL,
    CATEGORY_NAME       VARCHAR2(150)   NOT NULL,
    PLANNING_PERIOD     DATE            NOT NULL,
    GROSS_REQUIREMENTS  NUMBER(12,2)    DEFAULT 0,
    SCHEDULED_RECEIPTS  NUMBER(12,2)    DEFAULT 0,
    PROJECTED_ON_HAND   NUMBER(12,2)    DEFAULT 0,
    NET_REQUIREMENTS    NUMBER(12,2)    DEFAULT 0,
    PLANNED_ORDER_QTY   NUMBER(12,2)    DEFAULT 0,
    PLANNED_RELEASE_DATE DATE,
    LOT_SIZING_METHOD   VARCHAR2(20),
    EXCEPTION_FLAG      VARCHAR2(50),
    EXCEPTION_MESSAGE   VARCHAR2(500),
    CONSTRAINT PK_MRP_REQUIREMENTS PRIMARY KEY (MRP_ID)
);

What it does: Stores the output of every MRP run — one row per category per planning period — capturing gross requirements, scheduled receipts, projected on-hand, net requirements, planned order quantities, and any exception flags.
Why it’s included: Persisting MRP output as a table (rather than a transient calculation) enables Power BI to trend MRP results over time, compare successive runs, and surface exception alerts without re-executing the procedure.
Why it’s written this way: The identity column (GENERATED ALWAYS AS IDENTITY) auto-generates a surrogate key so the stored procedure can INSERT without managing sequences. Exception columns (flag + message) support the alerting workflow — the procedure writes EXPEDITE, RESCHEDULE, or SPLIT flags that Power BI data alerts monitor on each refresh.

Data Normalization — ROW_NUMBER Deduplication

-- Deduplicate customers from staging (one row per order item → one row per customer)
INSERT INTO CUSTOMERS (
    CUSTOMER_ID, FIRST_NAME, LAST_NAME, EMAIL, SEGMENT,
    CITY, STATE, COUNTRY, STREET, ZIPCODE, LATITUDE, LONGITUDE
)
SELECT
    CUSTOMER_ID, NVL(CUSTOMER_FNAME, 'Unknown'), NVL(CUSTOMER_LNAME, 'Unknown'),
    CUSTOMER_EMAIL, NVL(CUSTOMER_SEGMENT, 'Unknown'),
    CUSTOMER_CITY, CUSTOMER_STATE, CUSTOMER_COUNTRY,
    CUSTOMER_STREET, CUSTOMER_ZIPCODE, LATITUDE, LONGITUDE
FROM (
    SELECT s.*,
        ROW_NUMBER() OVER (PARTITION BY s.CUSTOMER_ID ORDER BY s.ORDER_DATE DESC) AS rn
    FROM STG_DATACO s
)
WHERE rn = 1;

What it does: Extracts one clean customer record per CUSTOMER_ID from the flat staging table, which contains duplicate customer rows (one per order item).
Why it’s included: The raw DataCo CSV is denormalized — customer data is repeated on every order line. This INSERT/SELECT normalizes customers into their own table, a prerequisite for building proper foreign key relationships and avoiding redundant storage.
Why it’s written this way: ROW_NUMBER() OVER (PARTITION BY CUSTOMER_ID ORDER BY ORDER_DATE DESC) keeps the most recent address for each customer, handling cases where a customer’s details changed across orders. NVL wraps ensure no NULLs slip into required fields, replacing missing names with ‘Unknown’ rather than failing the insert.

SQL Scripts: 01_schema_ddl.sql — Schema DDL (8 normalized tables + staging table) | 02_data_normalization.sql — INSERT/SELECT normalization from staging to production tables

SQL Query Categories

Five query groups extract, transform, and classify supply chain data across ~180K order items and 50 product categories. SQL techniques include CTEs, window functions (AVG OVER, SUM OVER, LAG, RANK, DENSE_RANK), date arithmetic, and cumulative calculations.

• Demand & Sales Extraction (03_demand_queries.sql) — Monthly/weekly order volume by category, rolling 3- and 6-month averages, MoM growth via LAG, cumulative revenue for Pareto analysis, and coefficient of variation for XYZ classification
• Inventory & Fulfillment Analytics (04_inventory_fulfillment_queries.sql) — ABC classification via cumulative revenue percentage, ABC-XYZ policy matrix, product master with planning parameters, on-time delivery rates, lead time variance, and late delivery root cause ranking
• Planned vs. Actual Analysis — Lead time variance, on-time delivery rate, late delivery root cause ranking
• MRP & Supply Planning — Gross requirements, projected on-hand, net requirements, planned order releases, exception flagging
• Forecast Accuracy — Forecast vs. actual comparison, MAE, MAPE, bias detection

Rolling Averages & Demand Variability (XYZ Classification)

-- Rolling 3-month and 6-month demand averages using window functions
WITH monthly_demand AS (
    SELECT
        TRUNC(o.ORDER_DATE, 'MM')       AS ORDER_MONTH,
        p.CATEGORY_NAME,
        SUM(oi.QUANTITY)                 AS TOTAL_UNITS,
        ROUND(SUM(oi.SALES), 2)         AS REVENUE
    FROM ORDER_ITEMS oi
    JOIN ORDERS o ON oi.ORDER_ID = o.ORDER_ID
    JOIN PRODUCTS p ON oi.PRODUCT_CARD_ID = p.PRODUCT_CARD_ID
    WHERE o.ORDER_STATUS NOT IN ('CANCELED', 'SUSPECTED_FRAUD')
    GROUP BY TRUNC(o.ORDER_DATE, 'MM'), p.CATEGORY_NAME
)
SELECT
    ORDER_MONTH, CATEGORY_NAME, TOTAL_UNITS, REVENUE,
    ROUND(AVG(TOTAL_UNITS) OVER (
        PARTITION BY CATEGORY_NAME ORDER BY ORDER_MONTH
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ), 2) AS ROLLING_3M_AVG_UNITS,
    ROUND(AVG(TOTAL_UNITS) OVER (
        PARTITION BY CATEGORY_NAME ORDER BY ORDER_MONTH
        ROWS BETWEEN 5 PRECEDING AND CURRENT ROW
    ), 2) AS ROLLING_6M_AVG_UNITS
FROM monthly_demand
ORDER BY CATEGORY_NAME, ORDER_MONTH;

What it does: Aggregates order quantity and revenue by month and category, then calculates 3-month and 6-month rolling averages using window functions.
Why it’s included: Rolling averages smooth out month-to-month noise, giving demand planners a clearer trend signal. The 3-month window captures short-term momentum while the 6-month window reveals broader seasonal patterns — both are inputs to the forecasting comparison in Excel.
Why it’s written this way: A CTE (monthly_demand) first aggregates the raw order data, then the outer SELECT applies window functions over the pre-aggregated rows. This two-stage approach keeps the window frame simple and avoids mixing aggregation with windowing in the same query. The ROWS BETWEEN clause is explicit rather than using the default RANGE to ensure exactly N prior rows are included regardless of gaps in the date series.

-- Demand variability for XYZ classification (coefficient of variation)
WITH monthly_demand AS ( ... ),
variability AS (
    SELECT
        CATEGORY_NAME,
        ROUND(AVG(MONTHLY_UNITS), 2)    AS AVG_MONTHLY_DEMAND,
        ROUND(STDDEV(MONTHLY_UNITS), 2) AS STDDEV_DEMAND,
        ROUND(STDDEV(MONTHLY_UNITS) / NULLIF(AVG(MONTHLY_UNITS), 0), 4)
                                         AS COEFF_OF_VARIATION
    FROM monthly_demand
    GROUP BY CATEGORY_NAME
)
SELECT CATEGORY_NAME, AVG_MONTHLY_DEMAND, COEFF_OF_VARIATION,
    CASE
        WHEN COEFF_OF_VARIATION <= 0.5 THEN 'X'   -- Stable demand
        WHEN COEFF_OF_VARIATION <= 1.0 THEN 'Y'   -- Moderate variability
        ELSE                                 'Z'    -- Highly volatile
    END AS XYZ_CLASS
FROM variability
ORDER BY COEFF_OF_VARIATION;

What it does: Calculates the coefficient of variation (standard deviation / mean) for each category’s monthly demand, then classifies it as X (stable, CV ≤ 0.5), Y (moderate, CV ≤ 1.0), or Z (volatile).
Why it’s included: XYZ classification is the demand-predictability half of the ABC-XYZ inventory matrix. Categories with stable demand (X) can use lean safety stock and automated replenishment, while volatile categories (Z) need larger buffers or manual review — this directly drives the inventory policy recommendations downstream.
Why it’s written this way: NULLIF(AVG(...), 0) prevents division-by-zero for categories with no demand history. The CASE thresholds (0.5 and 1.0) follow standard supply chain practice for CV-based classification. Stacking two CTEs (monthly_demand → variability) keeps each calculation layer readable and testable independently.

ABC Classification (Pareto Analysis) & ABC-XYZ Policy Matrix

-- ABC Classification by cumulative revenue percentage
WITH category_revenue AS (
    SELECT p.CATEGORY_NAME, ROUND(SUM(oi.SALES), 2) AS TOTAL_REVENUE,
           SUM(oi.QUANTITY) AS TOTAL_UNITS
    FROM ORDER_ITEMS oi
    JOIN PRODUCTS p ON oi.PRODUCT_CARD_ID = p.PRODUCT_CARD_ID
    JOIN ORDERS o ON oi.ORDER_ID = o.ORDER_ID
    WHERE o.ORDER_STATUS NOT IN ('CANCELED', 'SUSPECTED_FRAUD')
    GROUP BY p.CATEGORY_NAME
),
ranked AS (
    SELECT CATEGORY_NAME, TOTAL_REVENUE, TOTAL_UNITS,
        ROUND(SUM(TOTAL_REVENUE) OVER (
            ORDER BY TOTAL_REVENUE DESC
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) * 100.0 / SUM(TOTAL_REVENUE) OVER (), 2) AS CUMULATIVE_PCT
    FROM category_revenue
)
SELECT CATEGORY_NAME, TOTAL_REVENUE, CUMULATIVE_PCT,
    CASE
        WHEN CUMULATIVE_PCT <= 80 THEN 'A'
        WHEN CUMULATIVE_PCT <= 95 THEN 'B'
        ELSE 'C'
    END AS ABC_CLASS
FROM ranked
ORDER BY TOTAL_REVENUE DESC;

What it does: Ranks product categories by total revenue, computes a running cumulative revenue percentage, and assigns each category an ABC class: A (top 80% of revenue), B (next 15%), or C (bottom 5%).
Why it’s included: ABC classification is the foundation of Pareto-based inventory management — it identifies which categories drive the bulk of revenue so the business can allocate planning effort accordingly. A-class items get the tightest controls; C-class items get the lightest touch.
Why it’s written this way: The cumulative percentage is computed with a window SUM ordered by revenue descending, divided by the total SUM across all rows. Using ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ensures a strict running total. Two CTEs (category_revenue → ranked) separate the aggregation from the cumulative calculation, making the query easier to debug and adapt if classification thresholds change.

-- ABC-XYZ matrix with automated inventory policy recommendations
SELECT a.CATEGORY_NAME, a.ABC_CLASS, x.XYZ_CLASS,
    a.ABC_CLASS || '-' || x.XYZ_CLASS AS MATRIX_CELL,
    CASE
        WHEN a.ABC_CLASS = 'A' AND x.XYZ_CLASS = 'X'
            THEN 'JIT/Kanban – low safety stock, frequent small orders'
        WHEN a.ABC_CLASS = 'A' AND x.XYZ_CLASS = 'Y'
            THEN 'Moderate safety stock – demand-driven replenishment'
        WHEN a.ABC_CLASS = 'B' AND x.XYZ_CLASS = 'X'
            THEN 'Standard replenishment – EOQ with periodic review'
        WHEN a.ABC_CLASS = 'C' AND x.XYZ_CLASS = 'Z'
            THEN 'Evaluate for discontinuation – high risk, low reward'
        ...
    END AS INVENTORY_POLICY
FROM abc a
JOIN xyz x ON a.CATEGORY_NAME = x.CATEGORY_NAME
ORDER BY a.ABC_CLASS, x.XYZ_CLASS;

What it does: Joins the ABC and XYZ classification results to build a 3×3 matrix, then maps each cell (e.g., A-X, B-Y, C-Z) to a specific inventory policy recommendation.
Why it’s included: The combined ABC-XYZ matrix is the decision framework that turns two separate analytics (revenue rank and demand stability) into actionable replenishment strategies. Without it, the ABC and XYZ classifications exist in isolation and don’t translate into concrete operational guidance.
Why it’s written this way: A CASE expression maps each matrix cell to a plain-English policy (JIT/Kanban, EOQ with periodic review, evaluate for discontinuation, etc.) so the output is immediately interpretable by supply chain stakeholders. Concatenating ABC_CLASS || ’-’ || XYZ_CLASS into a MATRIX_CELL column provides a compact label for Power BI slicers and conditional formatting.

On-Time Delivery Rate & Late Delivery Root Cause Ranking

-- Monthly on-time delivery rate
SELECT
    TRUNC(o.ORDER_DATE, 'MM') AS ORDER_MONTH,
    COUNT(s.SHIPMENT_ID) AS TOTAL_SHIPMENTS,
    ROUND(
        SUM(CASE WHEN s.DELIVERY_STATUS IN ('Shipping on time', 'Advance shipping')
            THEN 1 ELSE 0 END) * 100.0 / NULLIF(COUNT(s.SHIPMENT_ID), 0), 2
    ) AS ON_TIME_PCT,
    ROUND(
        SUM(CASE WHEN s.DELIVERY_STATUS = 'Late delivery'
            THEN 1 ELSE 0 END) * 100.0 / NULLIF(COUNT(s.SHIPMENT_ID), 0), 2
    ) AS LATE_PCT
FROM SHIPMENTS s
JOIN ORDER_ITEMS oi ON s.ORDER_ITEM_ID = oi.ORDER_ITEM_ID
JOIN ORDERS o ON oi.ORDER_ID = o.ORDER_ID
GROUP BY TRUNC(o.ORDER_DATE, 'MM')
ORDER BY ORDER_MONTH;

What it does: Calculates the monthly on-time delivery rate and late delivery rate as percentages of total shipments, using conditional aggregation (CASE WHEN inside SUM).
Why it’s included: On-time delivery rate is a core supply chain KPI — trending it monthly reveals whether fulfillment performance is improving or degrading, and provides the baseline metric for the Power BI fulfillment dashboard page and data alerts.
Why it’s written this way: Conditional CASE expressions inside SUM avoid the need for subqueries or self-joins, keeping the query compact and performant over 180K order items. NULLIF in the denominator guards against division by zero in months with no shipment data. Both on-time and late percentages are computed in the same pass to ensure they’re always consistent.

-- Late delivery root cause ranking by category using DENSE_RANK
SELECT p.CATEGORY_NAME,
    COUNT(s.SHIPMENT_ID) AS TOTAL_SHIPMENTS,
    SUM(CASE WHEN s.LATE_DELIVERY_RISK = 1 THEN 1 ELSE 0 END) AS LATE_DELIVERIES,
    ROUND(
        SUM(CASE WHEN s.LATE_DELIVERY_RISK = 1 THEN 1 ELSE 0 END)
        * 100.0 / NULLIF(COUNT(s.SHIPMENT_ID), 0), 2
    ) AS LATE_DELIVERY_PCT,
    DENSE_RANK() OVER (
        ORDER BY SUM(CASE WHEN s.LATE_DELIVERY_RISK = 1 THEN 1 ELSE 0 END)
        * 100.0 / NULLIF(COUNT(s.SHIPMENT_ID), 0) DESC
    ) AS LATE_RANK
FROM SHIPMENTS s
JOIN ORDER_ITEMS oi ON s.ORDER_ITEM_ID = oi.ORDER_ITEM_ID
JOIN PRODUCTS p ON oi.PRODUCT_CARD_ID = p.PRODUCT_CARD_ID
GROUP BY p.CATEGORY_NAME
ORDER BY LATE_DELIVERY_PCT DESC;

What it does: Ranks product categories by their late delivery percentage using DENSE_RANK, so operations teams can immediately see which categories have the worst fulfillment performance.
Why it’s included: Identifying the highest-risk categories by late delivery rate enables targeted root cause analysis — instead of investigating all categories equally, the operations team focuses on the top offenders first. This feeds directly into the fulfillment improvement recommendations.
Why it’s written this way: DENSE_RANK (rather than RANK or ROW_NUMBER) ensures that categories with identical late delivery rates receive the same rank without gaps, giving an honest picture when multiple categories are tied. The ranking is applied as a window function over the aggregated result, avoiding a separate subquery or CTE layer.

Reporting Views

Six views pre-aggregate data for Power Query and Power BI, eliminating repeated complex joins at the dashboard layer: VW_DEMAND_TIMESERIES (monthly demand with rolling averages), VW_FULFILLMENT_KPI (on-time rate, lead time, late delivery by category/mode), VW_PRODUCT_MASTER (SKU-level revenue, demand CV, ABC/XYZ inputs, planning parameters), VW_PLANNED_VS_ACTUAL (lead time and delivery date variances), VW_MRP_PLAN (gross/net requirements, projected on-hand, planned orders, exceptions), VW_FORECAST_VS_ACTUAL (forecast accuracy with MAE, MAPE, bias).

VW_DEMAND_TIMESERIES — Monthly Demand with Rolling Averages & YoY Comparison

CREATE OR REPLACE VIEW VW_DEMAND_TIMESERIES AS
WITH monthly_raw AS (
    SELECT
        TRUNC(o.ORDER_DATE, 'MM')       AS ORDER_MONTH,
        p.CATEGORY_NAME,
        COUNT(oi.ORDER_ITEM_ID)         AS ITEM_COUNT,
        SUM(oi.QUANTITY)                 AS TOTAL_UNITS,
        ROUND(SUM(oi.SALES), 2)         AS REVENUE,
        COUNT(DISTINCT o.ORDER_ID)      AS ORDER_COUNT
    FROM ORDER_ITEMS oi
    JOIN ORDERS o ON oi.ORDER_ID = o.ORDER_ID
    JOIN PRODUCTS p ON oi.PRODUCT_CARD_ID = p.PRODUCT_CARD_ID
    WHERE o.ORDER_STATUS NOT IN ('CANCELED', 'SUSPECTED_FRAUD')
    GROUP BY TRUNC(o.ORDER_DATE, 'MM'), p.CATEGORY_NAME
)
SELECT ORDER_MONTH, CATEGORY_NAME, TOTAL_UNITS, REVENUE,
    ROUND(AVG(TOTAL_UNITS) OVER (
        PARTITION BY CATEGORY_NAME ORDER BY ORDER_MONTH
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ), 2) AS ROLLING_3M_AVG_UNITS,
    ROUND(AVG(TOTAL_UNITS) OVER (
        PARTITION BY CATEGORY_NAME ORDER BY ORDER_MONTH
        ROWS BETWEEN 5 PRECEDING AND CURRENT ROW
    ), 2) AS ROLLING_6M_AVG_UNITS,
    LAG(TOTAL_UNITS, 12) OVER (
        PARTITION BY CATEGORY_NAME ORDER BY ORDER_MONTH
    ) AS UNITS_PRIOR_YEAR,
    CASE
        WHEN LAG(TOTAL_UNITS, 12) OVER (
            PARTITION BY CATEGORY_NAME ORDER BY ORDER_MONTH) > 0 THEN
            ROUND((TOTAL_UNITS - LAG(TOTAL_UNITS, 12) OVER (
                PARTITION BY CATEGORY_NAME ORDER BY ORDER_MONTH
            )) * 100.0 / LAG(TOTAL_UNITS, 12) OVER (
                PARTITION BY CATEGORY_NAME ORDER BY ORDER_MONTH), 2)
        ELSE NULL
    END AS YOY_CHANGE_PCT
FROM monthly_raw;

What it does: Creates a reporting view that pre-calculates monthly demand by category with rolling 3- and 6-month averages, prior-year units, and year-over-year percentage change — all in a single queryable object.
Why it’s included: This view is consumed directly by Power BI, eliminating the need to re-join ORDER_ITEMS, ORDERS, and PRODUCTS and re-compute rolling averages on every dashboard refresh. It also gives the demand forecasting page its trend-line and YoY comparison data.
Why it’s written this way: LAG(TOTAL_UNITS, 12) looks back exactly 12 monthly rows per category to find the same month in the prior year. The YoY calculation is wrapped in a CASE to return NULL when there is no prior-year data (instead of dividing by zero). Defining this as a CREATE OR REPLACE VIEW means the stored procedure can rebuild it on every nightly run without needing DROP/CREATE logic.

VW_FORECAST_VS_ACTUAL — Forecast Accuracy with Error Metrics

CREATE OR REPLACE VIEW VW_FORECAST_VS_ACTUAL AS
SELECT
    f.CATEGORY_NAME, f.FORECAST_PERIOD, f.FORECASTED_QTY,
    f.FORECAST_METHOD, f.CONFIDENCE_LOWER, f.CONFIDENCE_UPPER,
    d.ACTUAL_UNITS, d.ACTUAL_REVENUE,
    ROUND(f.FORECASTED_QTY - d.ACTUAL_UNITS, 2)            AS FORECAST_ERROR,
    ROUND(ABS(f.FORECASTED_QTY - d.ACTUAL_UNITS), 2)       AS ABS_ERROR,
    CASE WHEN d.ACTUAL_UNITS > 0 THEN
        ROUND(ABS(f.FORECASTED_QTY - d.ACTUAL_UNITS) * 100.0
              / d.ACTUAL_UNITS, 2)
    ELSE NULL END                                           AS APE,
    CASE
        WHEN f.FORECASTED_QTY > d.ACTUAL_UNITS THEN 'OVER'
        WHEN f.FORECASTED_QTY < d.ACTUAL_UNITS THEN 'UNDER'
        ELSE 'EXACT'
    END AS BIAS_DIRECTION
FROM FORECAST_PLAN f
LEFT JOIN (
    SELECT p.CATEGORY_NAME, TRUNC(o.ORDER_DATE, 'MM') AS ORDER_MONTH,
           SUM(oi.QUANTITY) AS ACTUAL_UNITS,
           ROUND(SUM(oi.SALES), 2) AS ACTUAL_REVENUE
    FROM ORDER_ITEMS oi
    JOIN ORDERS o ON oi.ORDER_ID = o.ORDER_ID
    JOIN PRODUCTS p ON oi.PRODUCT_CARD_ID = p.PRODUCT_CARD_ID
    WHERE o.ORDER_STATUS NOT IN ('CANCELED', 'SUSPECTED_FRAUD')
    GROUP BY p.CATEGORY_NAME, TRUNC(o.ORDER_DATE, 'MM')
) d ON f.CATEGORY_NAME = d.CATEGORY_NAME
    AND f.FORECAST_PERIOD = d.ORDER_MONTH;

What it does: Creates a reporting view that joins forecasted quantities from FORECAST_PLAN against actual order data, computing the forecast error, absolute error, absolute percentage error (APE), and bias direction (over/under/exact) for each category and period.
Why it’s included: Forecast accuracy metrics (MAE, MAPE, bias) are critical for evaluating and improving demand planning. This view provides the data behind the Power BI forecast accuracy dashboard page and enables data alerts when forecast bias exceeds acceptable thresholds.
Why it’s written this way: A LEFT JOIN from FORECAST_PLAN to actuals ensures forecast periods without matching actuals still appear (e.g., future forecast periods). The actuals subquery filters out canceled and fraud orders so accuracy metrics reflect genuine demand. APE uses a CASE guard on ACTUAL_UNITS > 0 to avoid division by zero for periods with no sales.

SQL Scripts: 03_demand_queries.sql | 04_inventory_fulfillment_queries.sql | 05_reporting_views.sql

Stored Procedure & Scheduling

The REFRESH_SUPPLY_CHAIN_DATA procedure packages the full data refresh and MRP calculation into a single callable command: truncate staging tables, reload transactional data, recalculate derived fields, run MRP net requirements (gross-to-net netting, lot sizing, lead time offsetting, exception flagging), write planned orders to MRP_REQUIREMENTS, refresh all six reporting views, and log audit timestamps. DBMS_SCHEDULER runs this nightly at 2:00 AM.

MRP Net Requirements Calculation — Core Loop

-- For each category with forecast data, loop through planning periods
FOR period_rec IN (
    SELECT FORECAST_PERIOD, FORECASTED_QTY
    FROM FORECAST_PLAN
    WHERE CATEGORY_NAME = v_category
    ORDER BY FORECAST_PERIOD
) LOOP
    v_gross_req    := period_rec.FORECASTED_QTY;
    v_sched_rcpt   := 0;

    -- Projected On-Hand = prior on-hand - gross + scheduled receipts
    v_proj_on_hand := v_prior_on_hand - v_gross_req + v_sched_rcpt;

    -- Net Requirements = MAX(0, gross - prior on-hand - sched receipts)
    v_net_req := GREATEST(0, v_gross_req - v_prior_on_hand - v_sched_rcpt);

    -- Planned Order Qty (apply lot sizing: EOQ, FIXED_LOT, or LOT_FOR_LOT)
    IF v_net_req > 0 THEN
        CASE v_lot_method
            WHEN 'EOQ' THEN
                v_planned_qty := CEIL(v_net_req / GREATEST(v_lot_size, 1))
                                 * GREATEST(v_lot_size, 1);
            WHEN 'LOT_FOR_LOT' THEN
                v_planned_qty := v_net_req;
            ...
        END CASE;
        v_proj_on_hand := v_proj_on_hand + v_planned_qty;
    END IF;

    -- Exception Flagging
    IF v_proj_on_hand < v_safety_stock THEN
        v_exception := 'EXPEDITE';
    END IF;
    IF v_release_date < TRUNC(SYSDATE) THEN
        v_exception := 'RESCHEDULE';
    END IF;

    INSERT INTO MRP_REQUIREMENTS (...) VALUES (...);
    v_prior_on_hand := v_proj_on_hand;  -- carry forward
END LOOP;

What it does: Iterates through each planning period for a product category, performing the classic MRP gross-to-net calculation: projected on-hand = prior on-hand − gross requirements + scheduled receipts, net requirements = max(0, shortfall), then applies lot sizing (EOQ, fixed lot, or lot-for-lot) to determine the planned order quantity.
Why it’s included: This is the core engine of the supply planning simulation — it translates demand forecasts into actionable planned orders with release dates, and flags exceptions (expedite if below safety stock, reschedule if past due) that surface as alerts in the Power BI dashboard.
Why it’s written this way: A PL/SQL cursor loop (rather than pure SQL) is used because MRP is inherently sequential — each period’s projected on-hand depends on the prior period’s result, which cannot be expressed in a single set-based query. The CASE on lot sizing method makes the procedure data-driven: changing a product’s LOT_SIZING_METHOD column automatically changes how planned orders are calculated on the next run.

DBMS_SCHEDULER Job Creation

BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name        => 'JOB_REFRESH_SUPPLY_CHAIN',
        job_type        => 'STORED_PROCEDURE',
        job_action      => 'REFRESH_SUPPLY_CHAIN_DATA',
        start_date      => TRUNC(SYSDATE + 1) + INTERVAL '2' HOUR,  -- Next day 2:00 AM
        repeat_interval => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0; BYSECOND=0',
        enabled         => TRUE,
        auto_drop       => FALSE,
        comments        => 'Nightly refresh: inventory snapshot recalculation, '
                        || 'MRP net requirements calculation, and reporting view '
                        || 'data update. Runs at 2:00 AM daily.'
    );
END;

What it does: Registers a DBMS_SCHEDULER job that executes the REFRESH_SUPPLY_CHAIN_DATA stored procedure every night at 2:00 AM automatically.
Why it’s included: Scheduling is what turns a manual SQL workflow into an automated pipeline. With this job in place, fresh data, updated MRP results, and rebuilt reporting views are ready before Power BI’s 5:00 AM scheduled refresh — stakeholders see current dashboards without anyone running scripts manually.
Why it’s written this way: DBMS_SCHEDULER is Oracle’s built-in job scheduler (the same mechanism used in production Oracle ERP environments like Fusion Cloud), so this mirrors real-world enterprise automation. The start_date uses TRUNC(SYSDATE + 1) + INTERVAL ’2’ HOUR to begin the next day at 2 AM, and auto_drop => FALSE ensures the job persists across database restarts.

SQL Script: 06_stored_procedure_mrp.sql

Core Transformation Steps

Four Oracle view CSV exports were imported into Excel as structured tables. Transformations applied include datetime type fixes (set datetime type first, then Date Only as a separate step to avoid DataFormat.Error), null handling, text standardization, and proper data type assignment. Derived time features were created for downstream analysis: month, quarter, day of week, and fiscal period.

Custom M Functions

• fn_CalculateSafetyStock — Inputs: avg demand, demand std dev, lead time, service level Z-score
• fn_ClassifyABC — Input: cumulative revenue %; Output: A/B/C classification
• fn_ClassifyXYZ — Input: coefficient of variation; Output: X/Y/Z classification
• fn_CalculateReorderPoint — Combines avg daily demand, lead time, and safety stock
• fn_CalculateNetRequirements — Gross requirements minus projected on-hand minus scheduled receipts

Parameters & Query Folding

A Parameters sheet was created with named ranges — ServiceLevel, ZScore, PlanningHorizon, AnalysisStart, and AnalysisEnd — enabling dynamic switching between analysis windows, Oracle environments, and planning horizons (3/6/12 months) without modifying query logic. Query folding was documented for each transformation step, identifying which steps fold to Oracle (execute server-side) vs. execute locally in the Power Query engine.

Excel Workbook: SupplyChain_Analysis_V11.xlsx — Complete Excel workbook containing all Power Query ETL logic with custom M functions, the Power Pivot data model, demand forecasting worksheets (ETS, MA_3, MA_6, WMA_3), ABC/XYZ classification matrix, inventory optimization calculations (EOQ, safety stock, reorder points), MRP simulation and what-if analysis via Goal Seek and Solver, dynamic array lookup tools (FILTER, XLOOKUP, SORT), and the parameter sheet for dynamic analysis switching.

Demand Forecasting & Method Comparison

Demand forecasts were built for the top 9 product categories (by ABC classification) using Excel's FORECAST.ETS function (exponential triple smoothing), 3-month moving average (MA_3), 6-month moving average (MA_6), and 3-month weighted moving average (WMA_3). All categories and methods are consolidated in a single structured Demand_Forecast worksheet. SORTBY/FILTER was used to extract and sort category data by date, avoiding the numeric-sort bug that arises from separate SORT/FILTER formulas.

Confidence intervals (ETS_LOWER, ETS_UPPER) were generated using FORECAST.ETS.CONFINT scoped to forecast periods only. A key learning: October 2017 showed anomalously low units due to being a partial month in the dataset — excluding it from ETS historical ranges corrected forecasts from unrealistic to credible values. Forecast outputs were formatted for write-back to Oracle's FORECAST_PLAN table (see 07_forecast_plan_writeback.sql).

Forecast Write-Back & MRP Cross-Check

-- Insert ETS forecast data into Oracle (7 A-class categories x 6 months = 42 rows)
INSERT INTO FORECAST_PLAN (
    CATEGORY_NAME, FORECAST_PERIOD, FORECASTED_QTY,
    CONFIDENCE_LOWER, CONFIDENCE_UPPER,
    FORECAST_METHOD, MAPE_PCT, NOTES
) VALUES (
    'Fishing', DATE '2017-10-01', 505,
    471.25, 537.65,
    'ETS', 5.78, 'Auto-seasonality; 6-month horizon'
);
-- ... 41 additional INSERT statements for remaining categories/periods

-- Cross-check: Forecast vs MRP Gross Requirements (variance should be 0)
SELECT
    fp.CATEGORY_NAME, fp.FORECAST_PERIOD,
    fp.FORECASTED_QTY                          AS "Forecast Qty",
    mr.GROSS_REQUIREMENTS                      AS "MRP Gross Req",
    fp.FORECASTED_QTY - mr.GROSS_REQUIREMENTS  AS "Variance"
FROM FORECAST_PLAN fp
LEFT JOIN MRP_REQUIREMENTS mr
    ON fp.CATEGORY_NAME = mr.CATEGORY_NAME
    AND fp.FORECAST_PERIOD = mr.PLANNING_PERIOD
ORDER BY fp.CATEGORY_NAME, fp.FORECAST_PERIOD;

What it does: Inserts Excel-generated ETS forecast data into Oracle’s FORECAST_PLAN table (42 rows: 7 A-class categories × 6 forecast months), then runs a cross-check query comparing forecast quantities against MRP gross requirements to verify the two systems are in sync.
Why it’s included: This closes the loop between Excel analysis and Oracle automation — once forecasts are written back to the database, the nightly stored procedure can read them as MRP gross requirements without any manual handoff. The cross-check query validates that the MRP procedure consumed the forecasts correctly (variance should be zero).
Why it’s written this way: Explicit INSERT VALUES (rather than bulk load) keeps each forecast row auditable and easy to review or modify individually. The cross-check uses a LEFT JOIN so that forecast periods without a matching MRP row surface as NULLs rather than being silently dropped — a missing MRP row would indicate a procedure failure that needs investigation.

ABC/XYZ Classification

ABC classification was based on cumulative revenue percentage (A = top 80%, B = next 15%, C = bottom 5%). XYZ classification was based on coefficient of variation of demand (X = stable/predictable, Y = moderate variability, Z = erratic). Classification was computed at the Oracle layer for all 50 product categories, then summarized in the ABC_XYZ_Matrix worksheet using COUNTIFS/SUMIFS grids. A key finding: all A-class categories are X-class (stable demand), representing the highest-revenue, most-predictable segment — ideal candidates for automated replenishment.

Inventory Optimization (EOQ, Safety Stock, Solver)

EOQ (Economic Order Quantity), safety stock, and reorder points were calculated per category using custom Power Query M functions (fn_CalculateSafetyStock, fn_CalculateReorderPoint). The service level parameter (Z-score) is driven from the Parameters sheet for dynamic what-if analysis. The Inventory_Optimization worksheet consolidates all metrics per category into a single actionable view.

MRP Scenario Analysis Workbook

The MRP_Simulation worksheet performs net requirements calculation (gross requirements minus projected on-hand minus scheduled receipts) using fn_CalculateNetRequirements. The What_If_Analysis worksheet provides sensitivity analysis on service levels and planning horizons using Goal Seek and Solver. An Inventory_Tools worksheet offers dynamic array-based lookup tools (FILTER, XLOOKUP, SORT) for the Operations team to query inventory status by category.

Inventory Tools & Dynamic Arrays

Power Pivot Data Model

The underlying Power Pivot data model uses a star schema with the following dimension tables feeding into the fact tables above:

Three fact tables store the transactional and aggregated metrics:

Excel Workbook: SupplyChain_Analysis_V11.xlsx — Contains all worksheets referenced above: Demand_Forecast (all categories and methods), ABC_XYZ_Matrix (COUNTIFS/SUMIFS classification grids), Inventory_Optimization (EOQ, safety stock, reorder points per category), MRP_Simulation (net requirements calculation), What_If_Analysis (sensitivity analysis via Goal Seek and Solver), Inventory_Tools (dynamic array lookup tools), and the Parameters sheet for dynamic switching.

Data Model

A star schema was built in Power BI with fact and dimension tables, enabling efficient slicing and cross-filtering across all six dashboard pages.

Page 1: Executive KPI Overview

This is the leadership landing page, designed to answer one question in under 30 seconds: is the supply chain healthy? Six KPI cards across the top row display Total Orders, Total Revenue, On-Time Delivery Rate, Late Delivery Rate, YoY Growth %, and Average Profit Margin. Below them, a monthly revenue trend line chart tracks performance over the Jan 2015–Jan 2018 period, a top-10 categories horizontal bar chart ranks revenue contribution, a shipment distribution donut chart breaks down volume by shipping mode, and an on-time rate by shipping mode bar chart highlights where fulfillment is succeeding or failing. Year and ABC Class slicers allow cross-filtering so executives can isolate A-class performance or drill into a specific year. The page answers: “Where should we focus attention this cycle?” and “Are the metrics moving in the right direction?”

Page 2: Demand Analysis & Forecasting

This page is the analytical centerpiece of the project, directly demonstrating demand planning skills relevant to S&OP and IBP roles. It shows monthly demand trends by category as interactive line charts with ETS forecast overlays and confidence intervals, making it easy to see where actuals diverge from predictions. A forecast accuracy summary displays MAE and MAPE by category so planners can quickly identify which product lines have reliable forecasts and which need attention. A forecast method comparison visual shows side-by-side accuracy of ETS, MA-3, and WMA-3 per A-class category, enabling demand planners to select the best method for each product line. Category-level slicers allow drilling into individual categories for deeper analysis. The page answers: “What does demand look like by category?”, “Which forecast method is most accurate?”, and “Are any categories trending up or down?”

Page 3: Inventory Optimization & ABC/XYZ Analysis

This page translates the ABC/XYZ classification from the Excel analysis into an interactive Power BI view, making inventory segmentation actionable for planners. It features an ABC-XYZ classification matrix heatmap showing item counts per cell, an inventory health status table with days of supply versus target for each A-class category (with status flags: Healthy, Monitor, or Review), EOQ and safety stock parameters per category, and in-stock rate KPI cards. A service level slicer (90%/95%/99%) and ABC classification filter allow dynamic exploration of how inventory policies change at different service targets. The page answers: “Which categories are overstocked or understocked?”, “Where is working capital tied up unnecessarily?”, and “What are the recommended inventory policies by segment?”

Page 4: Fulfillment & Logistics Performance

This page provides a deep dive into outbound delivery performance, designed for operations teams to identify and resolve fulfillment bottlenecks. It includes on-time vs. late delivery rate trends over time as a stacked bar chart, lead time variance analysis comparing planned vs. actual delivery by shipping mode, a shipping mode performance comparison matrix, regional delivery performance with revenue-at-risk calculations, and a late delivery root cause breakdown. Geographic filters and shipping mode slicers enable targeted drill-down into specific markets or carrier types. The page answers: “Which shipping modes and regions are underperforming?”, “What is the revenue at risk from late deliveries?”, and “Are fulfillment issues structural or seasonal?”

Page 5: Supply Plan & MRP Analysis

This page visualizes the MRP simulation output, making it the most technically distinctive page in the dashboard. It displays time-phased net requirements across the 6-month planning horizon (Oct 2017–Mar 2018) for all A-class categories, showing gross requirements from ETS demand forecasts, scheduled receipts, projected on-hand inventory, net requirements, and planned order receipts/releases calculated using EOQ lot sizing. An MRP exception log surfaces any categories where projected on-hand falls below safety stock. KPI cards show total planned orders, MRP coverage ratio, number of exceptions, and average days of supply. The page answers: “Is the supply plan covering forecasted demand?”, “Are there any exceptions that need analyst intervention?”, and “Where should we release purchase orders this cycle?”

Page 6: Insights, Recommendations & Closed-Loop Actions

This page synthesizes findings from all five preceding dashboard pages into a consolidated executive-ready view. It features insight cards summarizing the four major analysis areas (demand forecasting accuracy, inventory health, fulfillment performance, and MRP plan status), each with supporting data and trend indicators. The key differentiator is the closed-loop design: recommendations on this page map directly to master data updates in Oracle (revised lead times, adjusted safety stock levels, updated lot sizing rules, forecast method selections) that take effect in the next automated refresh cycle via the REFRESH_SUPPLY_CHAIN_DATA stored procedure. This page answers: “What are the top 3–5 priorities for the supply chain team this cycle?” and “How do analysis findings translate into operational changes?”

DAX Measures

Key DAX measures created for the dashboard:

• Growth & Revenue: YoY Growth %, Total Revenue, Total Orders
• Fulfillment: On-Time Delivery Rate, Late Delivery Rate
• Forecast Accuracy: MAE, MAPE, Forecast Bias
• Inventory: Inventory Turnover, Days of Supply, In-Stock Rate
• Supply Planning: MRP Coverage Ratio, Supply Plan Adherence

All DAX measures auto-recalculate on every scheduled refresh cycle.

Data Alerts & Row-Level Security

Data alerts were configured on critical thresholds: in-stock rate drops, late delivery spikes, projected stockouts, MRP exception counts, and forecast bias. Row-Level Security (RLS) was implemented with two roles — the Marketing team sees sell-through and demand data only, while the Operations team sees production, fulfillment, MRP, and inventory data. Both roles use the same dashboard; Power BI filters automatically based on role assignment.

Power BI File: SupplyChain_Dashboard_V1.pbix — Complete Power BI Desktop file containing the six-page interactive dashboard, star schema data model with fact and dimension tables, all DAX measures (YoY growth, on-time rate, MAE, MAPE, inventory turnover, MRP coverage ratio, supply plan adherence), configured data alerts for critical threshold monitoring, and Row-Level Security roles for Marketing and Operations teams. Designed for scheduled refresh integration with Oracle views via the Power BI Service.

The dashboard pages above show what the analysis found. This section describes how the system would operate on an ongoing basis — the weekly cadence of reporting, master data maintenance, and supply plan refinement that turns a one-time analysis into a continuous improvement engine. Every step below maps directly to responsibilities described in the Supply Chain Planning Analyst role: planned vs. actual reporting, master data maintenance, inventory health monitoring, and the closed-loop process between reporting and execution.

Weekly Operating Cadence

In a production environment with live data, the automated pipeline would run on a nightly schedule: the Oracle stored procedure (REFRESH_SUPPLY_CHAIN_DATA) executes at 2:00 AM, refreshing inventory snapshots, recalculating MRP net requirements against the latest demand forecasts, and rebuilding all six reporting views. Power BI scheduled refresh would trigger at 5:00 AM, pulling updated data through Power Query and recalculating all DAX measures. By the time the planning team arrives in the morning, the dashboard would reflect last night’s actuals and the MRP plan would be current. Every component of this pipeline has been built and validated — connecting it to a live data source is the only step between the current state and full autonomous operation.

The analyst’s weekly workflow built on top of this automation would look like:

Monday: Review & Triage

Open Page 1 (Executive KPI Overview) for a pulse check on on-time rates, revenue trends, and any KPI cards that have shifted since last week. Check data alert notifications — any in-stock rate drops below threshold, any MRP exceptions flagged, any forecast bias alerts triggered. Open Page 5 (Supply Plan & MRP Analysis) to review the exception log and confirm planned order releases for the week are still valid. If a large incoming customer order has appeared in the data, flag it to the team immediately — the MRP gross requirements will reflect it in the next cycle, but lead time offsets may require expedited PO placement.

Midweek: Analyze & Adjust

Use Page 2 (Demand & Forecasting) to check whether actual demand is tracking within forecast confidence intervals. If a category is consistently over- or under-forecasting, update the forecast method parameters in the Demand_Forecast workbook and re-export to Oracle via the Forecast_Export table — the next nightly MRP run will consume the revised forecasts automatically. Use Page 3 (Inventory Health) to check days of supply against targets. If any A-class category has drifted above target (as Camping & Hiking, Fishing, and Water Sports did in this analysis at ~51 days vs. a 30-day target), initiate the appropriate action: promotional markdown, redistribution to another channel, or production hold. Update safety stock or reorder point parameters in Oracle master data if the analysis warrants it.

End of Week: Report & Close the Loop

Pull Page 4 (Fulfillment Performance) for the weekly shipping mode performance summary to share with logistics. Use Page 6 (Insights & Closed-Loop Actions) as the starting point for the weekly supply chain review meeting with the VP of Planning — the page consolidates the top priorities and maps each one to a specific master data update or process change. After the meeting, execute any agreed-upon parameter changes (revised lead times, adjusted lot sizing rules, updated safety stock levels) directly in Oracle. The next nightly refresh cycle picks up those changes, the MRP recalculates, and the following Monday’s dashboard reflects the impact. That’s the closed loop: report → analyze → update master data → automated refresh → measure impact → repeat.

Master Data Updates This Analysis Would Trigger

Based on the findings surfaced across the six dashboard pages, the following master data changes would be executed in the first operating cycle:

• Forecast method: Update FORECAST_PLAN parameters to use WMA-3 as the primary method for all A-class categories. Retain ETS as a secondary validation tool for seasonality detection. Re-export the 42-row forecast table to Oracle.
• Safety stock (Cardio Equipment): Increase safety stock buffer for Cardio Equipment to compensate for its elevated ETS MAPE (9.51%) — this category’s higher forecast uncertainty warrants a wider buffer relative to the other six A-class categories.
• Lot sizing (Fishing, Camping & Hiking, Water Sports): Evaluate switching these three lower-volume categories from EOQ to Fixed Lot or Lot-for-Lot, since their current days of supply (~51 days) significantly exceed the 30-day target. The What-If Analysis lot sizing comparison showed that Lot-for-Lot reduces holding cost substantially at the trade-off of higher ordering frequency — a viable option for categories with stable, predictable demand (all three are AX-classified).
• Carrier SLAs (First Class, Second Class): Flag for logistics team review — First Class late rates (~95%) and Second Class late rates (~85%) indicate either unrealistic delivery window commitments or underperforming carrier partners. Recommend adjusting quoted delivery timelines in the short term and issuing carrier RFPs for Europe and Latin America (the two highest revenue-at-risk markets).
• Excess inventory (3 categories): Initiate promotional or redistribution action for Camping & Hiking, Fishing, and Water Sports to bring days of supply back toward the 30-day target and free up the estimated ~$597K in tied-up working capital.

How the Automation Validates Each Change

Every master data update listed above is self-validating through the automated pipeline. Once a parameter change is made in Oracle, the next nightly stored procedure run recalculates MRP requirements with the updated inputs, the next Power BI refresh surfaces the impact in the dashboard, and the analyst can measure whether the intervention had the intended effect — without building a single new report or running any manual queries. If Cardio Equipment’s increased safety stock prevents a projected stockout that would have triggered an MRP exception, that shows up as a clean exception log on Page 5. If the lot sizing change for Water Sports brings days of supply from 51 down toward 30, that shows up on Page 3’s inventory health table. The dashboard doesn’t just report — it closes the loop.

Repository Structure

• sql/ — Oracle SQL scripts:
  • 01_schema_ddl.sql — Schema DDL (8 normalized tables + staging table)
  • 02_data_normalization.sql — INSERT/SELECT normalization from staging to production tables
  • 03_demand_queries.sql — Demand & sales extraction queries (monthly/weekly aggregations, rolling averages, demand variability)
  • 04_inventory_fulfillment_queries.sql — Inventory analytics (ABC/XYZ classification, product master) & fulfillment performance (on-time rates, lead time variance, root cause ranking)
  • 05_reporting_views.sql — Six pre-calculated reporting views for Power BI consumption
  • 06_stored_procedure_mrp.sql — REFRESH_SUPPLY_CHAIN_DATA stored procedure & DBMS_SCHEDULER job
  • 07_forecast_plan_writeback.sql — Forecast write-back logic from Excel to Oracle
• excel/ — SupplyChain_Analysis_V11.xlsx — Excel workbook with Power Query ETL, Power Pivot data model, demand forecasting, ABC/XYZ classification, inventory optimization, MRP simulation, and what-if analysis
• powerbi/ — SupplyChain_Dashboard_V1.pbix — Six-page interactive Power BI dashboard with star schema, DAX measures, data alerts, and Row-Level Security
• images/ — Dashboard screenshots, star schema diagram, Excel sheets screenshots, ABC/XYZ matrix visualization

GitHub Repository: nadeaujonny/nadeaujonny.github.io/projects/supply-chain-oracle-powerbi