Predicting the Demand: Automating Demand Forecasting in Dynamics 365 Business Central Using Azure Logic Apps, Data Lake, and Databricks
Summary
Knowing how many products to keep in warehouses is tough for manufacturers and distributors. During busy seasons, customer orders can jump 10 times higher than normal. When teams rely on manual spreadsheets, they often run out of products or buy too much and run out of storage space.
This article explains a simple, automated solution built with Microsoft Dynamics 365 Business Central, Microsoft Azure, and Azure Databricks.
Table of Contents
What You Will Learn
- Why manual spreadsheets and static inventory numbers fail when demand spikes.
- How Azure Logic Apps fetches data from Business Central automatically by using scheduled triggers.
- How raw records are organized into Bronze, Silver, and Gold layers.
- How the Prophet model forecasts demand using yearly trends and weekly patterns.
- How dynamic safety stock gives purchasing teams clear replenishment recommendations.
1. The Problem: Swings in Customer Demand
Most manufacturers and distributors face a big challenge: customer demand is not steady throughout the year. Some months are quiet, while other months bring huge surges in orders.
| Season | Months | Demand Level | What Happens |
|---|---|---|---|
| Peak Busy Season | June – August | 8x – 10x Surge | Huge spike in customer orders. Suppliers take longer to deliver, risking major stockouts. |
| Mid-Year Rush | January | 3x – 4x Normal | Quick wave of replacement orders and new account setups. |
| Spring Planning | March – May | 2x Normal | Customers use annual budgets to place advance orders for summer projects. |
| Regular Season | Off-Peak Months | 1x Baseline | Standard, steady daily orders. |
Why Traditional Methods Fail:
- Static Rules: Standard ERP rules use fixed inventory numbers all year. These are too small for busy seasons (causing stockouts) and too large for slow seasons (wasting money).
- Longer Supplier Delays: When everyone orders at once during peak seasons, suppliers take weeks longer to deliver parts.
- Full Warehouses: Storing large boxes during slow months takes up valuable warehouse space and ties up cash.
- Manual Spreadsheet Errors: Planning teams spend hours copying and pasting data into Excel spreadsheets without automated forecasting tools.
2. The 5-Step Solution Overview
To solve this, we created an automated pipeline that connects daily ERP transactions to cloud forecasting and delivers clear inventory planning targets.
Dynamics 365 Business Central
Holds daily sales, purchases, items, and warehouse records.
Azure Logic Apps (Scheduled Ingestion)
Fetches data from Business Central automatically by using scheduled triggers without slowing down the ERP system.
Azure Data Lake (Cloud Storage)
Stores all historical files securely in one central place.
Azure Databricks (Prophet Model)
Cleans the data, runs Prophet forecasting models, and calculates the forecasted buffer stock needed for every item.
Visual Reports in Power BI
Forecasted demand and recommended safety stock are displayed in Power BI reports for clear decision-making.
3. How Data is Cleaned & Organized (Bronze, Silver, Gold)
In Azure Databricks, data moves through three simple stages known as the Medallion Architecture:
Raw Data
Stores exact copies of daily files directly from Business Central (sales, purchases, items, warehouses).
Keeps a complete, untouched history so nothing is ever lost.
Cleaned Data
Fixes missing dates, removes duplicates, and standardizes item numbers across all warehouses.
Separates real customer orders from internal warehouse transfers.
Forecasting Results
Combines daily sales into clear trends and calculates forecasted stock targets for each product.
Ready to feed interactive Power BI reports for planners and stakeholders.
4. How the Prophet Forecasting Model Works
The Prophet forecasting model analyzes four key factors from past sales:
The 3 Things the Model Learns:
- Overall Growth: Is customer demand growing year over year?
- Yearly Seasons: Which months have huge order spikes, and which months are quiet?
- Weekly Patterns: Do customers place most orders on weekdays compared to weekends?
By combining these patterns, the system calculates the recommended safety stock for every item and warehouse:
- Forecasted Safety Stock: The recommended buffer quantity to keep on hand to protect against unexpected surges or supplier delivery delays.
5. Clear Decision-Making with Forecasted Metrics
Instead of relying on guesswork in disconnected spreadsheets, supply chain planners have clear, data-driven targets calculated by Azure Databricks.
These forecasted metrics give purchasing and warehouse managers actionable recommendations:
- Projected Demand: Forward-looking estimates of how many units customers will need in upcoming months.
- Early Order Timing: Clear signals on when to order from suppliers before peak seasons begin.
- Warehouse Stock Balancing: Guidance on how much inventory to position across regional warehouse hubs.
6. Real Benefits for Manufacturers
7. Frequently Asked Questions (FAQ)
No. Data is copied automatically during quiet nighttime hours into Azure. All calculations happen in the cloud, so Business Central stays fast and responsive for everyday business.
Standard ERP rules use one fixed number for the entire year. The Prophet model automatically adapts to upcoming seasons, supplier lead times, and sales trends.
Planning and purchasing teams access these forecasted metrics directly through interactive Power BI reports, giving them clear recommendations for ordering quantities and replenishment timelines.
Ready to Modernize Your Supply Chain with Dynamics 365 & Azure?
Whether you are navigating big seasonal spikes, managing multiple warehouses, or moving away from spreadsheets, CloudFronts can help you build an automated forecasting solution.
Connect with CloudFronts