How Retail Businesses on Microsoft Dynamics NAV Can unlock Smarter Inventory Management with Data Engineering

The Inventory Problem Hiding in Plain Sight

For most retail businesses running on Microsoft Dynamics NAV — now rebranded as Business Central — the inventory problem isn't a shortage of data. NAV faithfully records every purchase order, goods receipt, sales shipment, inter-location transfer, and stock adjustment. Over five years of operation, that's potentially hundreds of millions of ledger entries sitting in a SQL Server database that most businesses only ever query through NAV's own built-in reports.

The result is a painful paradox: data-rich but insight-poor. Buyers make replenishment decisions from spreadsheets. Warehouse managers rely on gut feel for safety stock levels. Finance teams reconcile inventory valuations manually each month-end. And when a stockout happens, the post-mortem almost always reveals the signal was there — buried in the data — weeks before the shelves went empty.

The answer is a modern data engineering layer that sits alongside NAV, continuously extracts inventory data, transforms it into analytics-ready structures, and delivers real-time visibility and predictive intelligence — without touching or disrupting NAV itself.

Why NAV Data Is So Hard to Work With

Anyone who has tried to query a NAV database directly will recognise the frustration. Microsoft Dynamics NAV's data model is powerful but deliberately abstract — optimised for transactional processing, not analytical queries. Several characteristics make it particularly challenging for data engineers.

Highly normalised, non-intuitive schema

The core inventory story in NAV is told across dozens of related tables: Item, Item Ledger Entry, Value Entry, Purchase Header/Line, Sales Header/Line, Transfer Header/Line, and more. There is no single "current stock" table. To know how many units of SKU-12345 are in Warehouse B right now, you must sum Item Ledger Entry rows going back to the beginning of time — or rely on the Item table's cached quantity fields, which can lag in certain configurations.

Cryptic field names and company-prefixed tables

NAV field names are frequently abbreviated — No_ instead of Number, Qty__on_Hand, Unit_Cost_LCY. Every table name includes the company name as a prefix. A standard deployment for "Retail Ltd" stores the Item table as [dbo].[RETAIL LTD$Item]. Multi-company deployments multiply this complexity significantly.

No native change data feed

NAV does not expose a change stream natively. Without additional tooling, there is no reliable way to ask "what changed since 3pm?" without full table scans or custom timestamp-based polling — both of which create performance risk on the same SQL Server instance serving live users.

The Architecture: NAV to a Modern Data Platform

The solution is an extraction layer that reads from NAV non-invasively and delivers data to a modern analytical platform — Delta Lake on Databricks or Azure Synapse. The architecture follows the medallion model: Bronze (raw), Silver (clean), Gold (aggregated) — with NAV-specific extraction patterns feeding the ingestion layer.

MICROSOFT DYNAMICS NAV / BUSINESS CENTRAL
  ├─ SQL Server (on-prem)  → Read replica via SQL Always On
  ├─ OData Web Services    → NAV pages exposed as REST endpoints
  ├─ Business Central API  → v2.0 REST API (cloud BC only)
  └─ SQL CDC               → SQL Server Change Data Capture (near real-time)
         │
         ▼
EXTRACTION LAYER
  ├─ Azure Data Factory    (incremental copy pipelines, self-hosted IR)
  ├─ Python Extractor      (watermark-based, handles NAV schema quirks)
  └─ CDC Listener          (Debezium on SQL Server → Kafka → landing zone)
         │
         ▼
LANDING ZONE  (Azure Data Lake Gen2 / Blob Storage)
  /nav-raw/{company}/{table}/{date}/*.parquet
         │
         ▼  Databricks Auto Loader / Azure Synapse pipelines
BRONZE LAYER  (Delta Lake · append-only · raw NAV schema preserved)
  bronze.nav_item_ledger_entry  |  bronze.nav_item
  bronze.nav_purchase_line      |  bronze.nav_sales_line
  bronze.nav_transfer_line      |  bronze.nav_value_entry
         │
         ▼  dbt / Delta Live Tables transformations
SILVER LAYER  (Typed · Joined · Business-logic applied)
  silver.inventory_positions    silver.item_master
  silver.purchase_orders        silver.sales_orders
  silver.stock_movements        silver.inventory_valuation
         │
         ▼  Aggregation and ML pipelines
GOLD LAYER  (Inventory KPIs · Forecasting · Alerts)
  gold.stock_on_hand_by_location   gold.slow_moving_stock
  gold.days_cover_by_sku           gold.reorder_signals
  gold.demand_forecast             gold.stockout_risk_score
         │
         ▼
CONSUMERS
  Power BI Dashboards  |  Buying Team Alerts  |  ML Models  |  NAV Write-back

Extracting Data from NAV: Four Proven Methods

There is no single "correct" way to extract data from NAV. The right method depends on your version, whether you are on-premises or cloud, your latency requirements, and your team's technical capabilities. Here are the four approaches we use at Lucent Innovation, ranked by recommendation for most retail deployments.

Method 1: SQL Server read replica (recommended for on-prem NAV)

For on-premises NAV deployments, setting up an Always On Availability Group read replica is the cleanest extraction path. Your analytics workloads run against the secondary — zero impact on the primary instance serving NAV users. Watermark-based incremental extraction then runs on the key tables.

# Python — Watermark-based incremental extraction from NAV SQL Server
import pyodbc, pandas as pd
from datetime import datetime

conn = pyodbc.connect(
    "DRIVER={ODBC Driver 18 for SQL Server};"
    "SERVER=nav-replica.internal,1433;"
    "DATABASE=NAV_PROD;"
    "Authentication=ActiveDirectoryServicePrincipal;"
)

company   = "RETAIL LTD"
watermark = get_watermark("item_ledger_entry")  # from control table

query = f"""
SELECT
    [Entry No_]              AS entry_no,
    [Item No_]               AS item_no,
    [Posting Date]           AS posting_date,
    [Entry Type]             AS entry_type,
    [Location Code]          AS location_code,
    [Quantity]               AS quantity,
    [Remaining Quantity]     AS remaining_qty,
    [Cost Amount (Actual)]   AS cost_amount,
    [Document No_]           AS document_no,
    GETDATE()                AS _extracted_at
FROM [{company}$Item Ledger Entry]
WHERE [Entry No_] > {watermark}
ORDER BY [Entry No_]
"""

df = pd.read_sql(query, conn)
df.to_parquet(f"abfss://nav-raw/item_ledger_entry/{datetime.utcnow():%Y/%m/%d}/batch.parquet")
update_watermark("item_ledger_entry", int(df["entry_no"].max()))

Method 2: SQL Server Change Data Capture (CDC)

For near-real-time extraction, SQL Server's built-in CDC feature captures row-level changes — inserts, updates, deletes — to designated tables into a change log you consume continuously. Combined with Debezium on a small VM, you get a Kafka topic per NAV table streaming every inventory change in under 30 seconds.

-- T-SQL — Enable CDC on NAV Item Ledger Entry
USE NAV_PROD;
GO

-- Enable CDC at database level (one-time)
EXEC sys.sp_cdc_enable_db;
GO

-- Enable CDC on the key inventory table
EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'RETAIL LTD$Item Ledger Entry',
    @role_name     = NULL,
    @supports_net_changes = 1;
GO

Method 3: NAV OData Web Services

NAV exposes standard pages as OData REST endpoints — no direct database access required. Ideal when SQL access is unavailable (hosted environments) or for Business Central on-premises. The trade-off is throughput: OData is significantly slower than direct SQL for large-volume extraction and is best suited to low-frequency, low-volume objects.

Method 4: Business Central API v2.0 (cloud BC only)

For Business Central cloud, the v2.0 REST API is the supported extraction path. It uses OAuth 2.0 via Azure AD, supports $filter and $deltatoken for incremental sync, and requires no additional configuration. Coverage is limited to standard BC entities — custom extensions require custom API pages built by your BC partner.

Building the Inventory Data Model

The NAV schema contains everything you need to build a comprehensive inventory data model — but it requires careful assembly. Here are the key NAV tables and how they map to the Silver analytical layer.

The Gold layer: current inventory position

The most important Gold table — and the one NAV's native reporting gets wrong most often — is the current inventory position: how many units of each SKU are at each location right now, adjusted for committed sales orders and inbound purchase orders.

-- SQL — Gold: current inventory position with availability calculation
CREATE OR REPLACE TABLE gold.inventory_position AS
SELECT
    im.item_no,
    im.description,
    im.category_code,
    im.vendor_no,
    im.reorder_point,
    im.safety_stock_qty,
    im.lead_time_days,
    lm.location_code,
    lm.location_name,

    -- Physical stock on hand (sum of all Item Ledger Entry movements)
    COALESCE(SUM(sm.quantity), 0)             AS qty_on_hand,

    -- Stock committed to open sales orders not yet shipped
    COALESCE(SUM(so.outstanding_qty), 0)      AS qty_committed,

    -- Stock on open purchase orders not yet received
    COALESCE(SUM(po.outstanding_qty), 0)      AS qty_on_order,

    -- Available to promise = on_hand - committed + on_order
    COALESCE(SUM(sm.quantity), 0)
      - COALESCE(SUM(so.outstanding_qty), 0)
      + COALESCE(SUM(po.outstanding_qty), 0)  AS qty_available,

    -- Days of cover based on 90-day average daily demand
    CASE WHEN d.avg_daily_demand > 0
         THEN COALESCE(SUM(sm.quantity), 0) / d.avg_daily_demand
         ELSE NULL
    END                                       AS days_cover,

    -- Reorder flag
    CASE WHEN COALESCE(SUM(sm.quantity), 0) <= im.reorder_point
         THEN TRUE ELSE FALSE
    END                                       AS reorder_required,

    current_timestamp()                       AS _refreshed_at

FROM silver.item_master     im
CROSS JOIN silver.location_master lm
LEFT JOIN silver.stock_movements  sm ON sm.item_no = im.item_no
                                    AND sm.location_code = lm.location_code
LEFT JOIN silver.sales_orders     so ON so.item_no = im.item_no
                                    AND so.location_code = lm.location_code
                                    AND so.status IN ('Open','Released')
LEFT JOIN silver.purchase_orders  po ON po.item_no = im.item_no
                                    AND po.location_code = lm.location_code
                                    AND po.status IN ('Open','Released')
LEFT JOIN gold.avg_daily_demand   d  ON d.item_no = im.item_no
                                    AND d.location_code = lm.location_code
GROUP BY
    im.item_no, im.description, im.category_code, im.vendor_no,
    im.reorder_point, im.safety_stock_qty, im.lead_time_days,
    lm.location_code, lm.location_name, d.avg_daily_demand;

Real-Time Stock Visibility Across Locations

Multi-location retail is where NAV's built-in reporting typically breaks down entirely. Understanding stock positions across five stores, a central warehouse, and a 3PL simultaneously — refreshed in near real-time — requires the data engineering layer described above.

Once the pipeline is running, inventory questions that previously required a phone call to a warehouse manager or a manual spreadsheet reconciliation become self-service Power BI queries refreshing every few minutes:

• What is the exact on-hand quantity of SKU-12345 at each location right now?
• Which SKUs are below their reorder point across any location?
• How many units are still in transit from the supplier on open purchase orders?
• Which locations are overstocked on slow-moving lines that could be transferred?
• What is the total inventory value by category, by location, by supplier?

Predictive Inventory: From Reactive to Proactive

Real-time visibility solves the what is happening now problem. Predictive inventory solves the harder problem: what will happen in the next 4–12 weeks, and what should we order today? With 2–3 years of historical Item Ledger Entry data in the Silver layer, you have everything needed to train demand forecasting models at the SKU-location level.

# Python — Feature engineering for SKU-level demand forecasting
# Built on top of silver.stock_movements in Databricks

from pyspark.sql import functions as F
from pyspark.sql.window import Window

# Daily demand series per SKU per location (sales movements only)
daily_demand = (
    spark.table("silver.stock_movements")
    .filter(F.col("entry_type") == 1)        # 1 = Sale
    .filter(F.col("quantity") < 0)           # Sales reduce stock
    .withColumn("demand", F.abs("quantity"))
    .groupBy("item_no", "location_code", "posting_date")
    .agg(F.sum("demand").alias("units_sold"))
)

# Rolling demand features for ML model input
w7  = Window.partitionBy("item_no","location_code").orderBy("posting_date").rowsBetween(-6,0)
w28 = Window.partitionBy("item_no","location_code").orderBy("posting_date").rowsBetween(-27,0)
w90 = Window.partitionBy("item_no","location_code").orderBy("posting_date").rowsBetween(-89,0)

features = (
    daily_demand
    .withColumn("avg_demand_7d",  F.avg("units_sold").over(w7))
    .withColumn("avg_demand_28d", F.avg("units_sold").over(w28))
    .withColumn("avg_demand_90d", F.avg("units_sold").over(w90))
    .withColumn("dow",            F.dayofweek("posting_date"))
    .withColumn("week_of_year",   F.weekofyear("posting_date"))
    .withColumn("demand_trend",   F.col("avg_demand_7d") - F.col("avg_demand_28d"))
)

features.write.mode("overwrite").saveAsTable("gold.demand_forecast_features")

The output feeds a forecasting model — LightGBM or Prophet depending on series length and seasonality — producing a 12-week forward demand forecast at the SKU-location level. Combine that with current on-hand quantity, supplier lead times from the item master, and safety stock rules to generate automated reorder recommendations that buyers can review in Power BI, or write directly back to NAV as purchase requisitions via the API.

Key Challenges and How to Solve Them

Business Impact: What You Can Achieve

The business case for a NAV data engineering investment is straightforward when you quantify what poor inventory management actually costs. Based on deployments at Lucent Innovation across multiple retail clients on Dynamics NAV, these are the outcomes we consistently see:

• 30–40% reduction in stockouts through automated reorder signals and demand-driven safety stock levels, replacing static reorder points set months ago and rarely reviewed.
• 15–25% reduction in inventory carrying costs by identifying slow-moving and obsolete stock early, enabling clearance pricing decisions before items become write-offs.
• Elimination of monthly inventory reconciliation — a process that typically consumes 2–4 days of finance team effort each period — through continuous automated stock valuation in the data platform.
• Buyer productivity gains: replenishment decisions that previously took 30 minutes per buyer per day reduced to a 5-minute review of a pre-built reorder recommendation dashboard.
• Multi-location transfer optimisation: visibility of overstock at one location alongside stockout risk at another enables inter-location transfers that reduce both emergency orders and end-of-season markdowns.

How to Get Started

A production NAV inventory data engineering project typically runs 8–12 weeks, depending on NAV version, schema customisation depth, and how many locations and companies are in scope. At Lucent Innovation, we follow a phased delivery model that gets value into buyers' hands within the first two weeks.

Whether you're on NAV 2016, NAV 2018, or have recently migrated to Business Central cloud, the architectural patterns described here apply across the board. The extraction method changes; the data model and analytical value do not.

At Lucent Innovation, we specialise in building data platforms alongside ERP systems — turning the transaction data your business already generates into operational intelligence that drives better buying decisions, leaner inventory, and stronger margins. If you're ready to get more from your NAV data, let's talk.