Supply Chain Management with Fabric: Demand Volatility Monitoring in Real-Time
In communities you find numerous discussions if ABC-XYZ analysis makes sense. In this article we assume that the reader applies ABC-XYZ in a reasonable context.
The focus is here to apply ABC-XYZ using Fabric in a real-time manner. Microsoft Fabric is meant to be an efficient tool set for exercising business
process analytics with all the benefits of in-memory databases and distributed computing. Thus, we want to check if we can quickly set up
a near to real-time monitoring of the volatility of our demand using this method.
For our reasoning we will take a path as follows:
We take real demand data and apply the usual ABC-XYZ methodology. The method is standard in every supply planning or IBP system. We want to figure out how we can quickly setup this method with near to real-time events in Microsoft Fabric |
Power Automate will pull the events and Fabric will be used to do the real-time analytics, to see how the demand classifies according to ABC-XYZ |
So, this is a little exercise about simulation on the Power Platform and Fabric as well.
Implementation
Custom Event Hub Setup in Microsoft Fabric
The event hub setup follows event hub setup.
Key implementation details:
You can setup a custom event hub in Fabric to process any streaming data source |
Once you have specified the JSON of your order line event, a KQL database table is setup to capture the events as they come in |
Order line polling with Power Automate
Key implementation details:
You have a REST API available where you can poll a number or order lines from |
The HTML process step in Power Automate gives you a list of JSON objects with order line details |
The order line objects are sent as event to the Fabric event hub |
Stream Analytics with KQL
Fabric uses KQL databases for event capturing. Naturally, KQL (Kusto Query Language) is the query language of choice to analyze
the incoming data. KQL is tailored to stream analytics and structure-wise it resembles Power Query or F# pipelines
(and these are languages that are functional languages, although F# has mutable features).
In this simple example the order line object has a SKU, a date, and a quantity.
Key implementation details:
The order event table in the code is just a placeholder for the real table |
The ABC analysis as per unit, as in the simple example here no turnover figures per order line are submitted. X is the SKUs that account for 70% of the turnover descending by number of units in the regarded period |
XYZ uses the standard deviation by SKU divided by the mean as coefficient of variance. A variance lower than 0.5 is X and so on. “L” is used for “Launch,” when first events come in and a standard deviation cannot yet be calculated |
The lookback period is one month |
The result is a table with the columns SKU, ABC and XYZ |
This can easily be used in Power BI report showing the number of SKUs per
classification as a toy visualization:
In summary, the Fabric Data Activator workload lets you quickly setup a real-time monitoring by means of the ABC-XYZ method.
This is just a simplistic starting point. You can easily imagine an alert system where SKUs change to a more volatile classification.
As mentioned in the beginning, this is no judgement of the method as such.
The goal was to check if we can implement such method swiftly in a real-time fashion in the Fabric framework.
Supply Chain Management with Fabric: Optimal Order Quantity
In a former post, I tried to use PyTorch parallel computing on the newsvendor problem. A key takeaway was that for thousands of SKUs and for analyzing hundreds of thousands of order lines distributed high-speed computing comes quickly into play.
For an explanation what the newsvendor problem is, please check the beginning of the post Newsvendor problem. Briefly, if you assume a normal distribution for the demand of a specific SKU, and you have figured the stock-out cost and holding cost for a unit of this SKU, you can estimate the optimal order size for this SKU.
One prominent choice to do this is Microsoft Fabric. Fabric uses Spark and we will use PySpark notebooks in the following.
In our example scenario we use 100.000 order lines, which is about one month’s worth of order lines. In the regarded period there has been demand for 1878 SKUs. As mentioned, the simplified assumption is that the demand per SKU is distributed according to a normal distribution. So, we need the mean and standard deviation for every SKU. In PySpark this can be easily achieved by a “Group By” expression and built in SQL functions:
For every SKU we add holding costs and stockout costs per unit. They have been calculated elsewhere and are not subject to this consideration. The holding costs are operational costs for keeping a unit in stock, the stockout costs are the opportunity costs for not selling a unit.
Per SKU we draw 1000 values from a standard normal distribution and transform these values according to the mean and standard deviation per SKU to a sampled demand value.
Please note that the cross-join operation is a distributed operation, and we keep the benefits of the Spark setup.
On a side note, we use the same random numbers for every SKU and then transform by mean and variance. This is not an issue, because it is more a question if the sample size (here: 1000) is sufficient and not what the sampled numbers are specifically.
At this stage we have samples of demand per SKU and if our order size does not hit the demand exactly, we have stockout costs for the order size being lower than the actual demand or holding costs for the order size being higher than the actual demand:
Now the average per SKU and order size for the total cost is taken and the order size with the minimum average total cost is taken. We can write the result to a lake house delta table:
Finally, we can use the notebook in a Fabric data pipeline to schedule an update of the delta table:
Key Takeaways
Microsoft Fabric provides an efficient notebook setup with Spark to approach statistical supply chain problems in a scalable manner |
Notebooks can be scheduled in a pipeline to automate the creation of optimal order quantities per SKU |
Although the newsvendor problem has simplified assumptions, it is always a good entry point for this type of calculations.