MindsDB: Predicting Supply Chain Demand with Machine Learning using SQL

Community Author: Chandre Van Der Westhuizen

The Supply Chain industry requires forecasting to plan for demand. This is critical to ensure that requirements for demand are met so that the business can run smoothly and keep shortages to a minimum. MindsDB's machine learning models can help predict the demand to uphold a company's service delivery.

This tutorial will be exploring a Brazilian logistics company's dataset that has been collected during 60 days and predict the number of total orders. We will create and train a Regression predictive model and make predictions.

This tutorial will be exploring a Brazilian logistics company's dataset that has been collected during 60 days and predict the number of total orders. We will create and train a Regression predictive model and make predictions.

Pre-requisites

1. Access to MindsDB Cloud or local deployment via docker or pip installation.
2. Dataset: You can find the Supply chain and Demand dataset on the UCI Machine Learning Repository.

Connecting your Database to MindsDB.

To establish a database connection we will access MindsDB's GUI. MindsDB has a SQL Editor on Cloud and local via the URL 127.0.0.1:47334/. MindsDB enables connections to your favorite databases, data warehouses, data lakes, via the CREATE DATABASE syntax.

First, we need to connect MindsDB to the database where the Supply Chain data is stored:

• Access MindsDB GUI on either cloud or the URL 127.0.0.1:47334/
• On the default page, select the button Add Data or alternatively select the plug icon on the left sidebar.
• The 'Select your data source' page will populate for you to choose your database type. For this tutorial we will be selecting the postgres database button.

• Once you have selected the database type,the page will automatically navigate to the SQL Editor where the syntax to create a database connection will automatically populate for you to enter the required parameters.

The required parameters are:

• CREATE DATABASE display_name --- display name for database.
• WITH ENGINE = "postgres", --- name of the mindsdb handler
• PARAMETERS = {
  • "user": " ", --- Your database user.
  • "password": " ", --- Your password.
  • "host": " ", --- host, it can be an ip or an url.
  • "port": "5432", --- common port is 5432.
  • "database": " " --- The name of your database *optional. }

Select the Run button or Shift+Enter to execute the syntax. Once the Database connection is created the console will display a message 'Query successfully completed'.
​

Please note that some database connections require running a Ngrok tunnel to establish a connection.
Run the ngrok command in a terminal:

ngrok tcp [db-port]

for example,if your port number is 5433 you will see a similar output:

Session Status                online
Account                       myaccount (Plan: Free)
Version                       2.3.40
Region                        United States (us)
Web Interface                 http://127.0.0.1:4040
Forwarding                    tcp://6.tcp.ngrok.io:14789 -> localhost:5433

The forwarded address information will be required when connecting to MindsDB's GUI. Select and copy the 'Forwarding' information, in this case it is 6.tcp.ngrok.io:14789, where 6.tcp.ngrok.io will be used for the host parameter and 14789 as the port number.

Once the database integration is successful we can query the table from the database to ensure the data pulls through on MindsDB.

You can run queries directly on your database. The below is an example of the syntax used:

SELECT * 
FROM example_db.datasource.table_name
LIMIT 10;

We will run the query to preview the data:

SELECT * 
FROM mindsdb_predictions.data.demand
LIMIT 10;

Understanding the data.

+-------------------+-----------------+------------------+--------------+--------------+--------------+--------------+----------------------+-------------------------------------------+-----------------+-----------------+------------------+---------+
| Week_of_the_month | Day_of_the_week | Non_urgent_order | Urgent_order | Order_type_A | Order_type_B | Order_type_C | Fiscal_sector_orders | Orders_from_the_traffic_controller_sector | Banking_orders1 | Banking_orders2 | Banking_orders_3 | Target  |
+-------------------+-----------------+------------------+--------------+--------------+--------------+--------------+----------------------+-------------------------------------------+-----------------+-----------------+------------------+---------+
|                 2 |               2 |          171.297 |      127.667 |       41.542 |      113.294 |      162.284 |               18.156 |                                     49971 |           33703 |           69054 |            18423 |  317.12 |
|                 2 |               3 |           90.532 |      113.526 |       37.679 |       56.618 |       116.22 |                6.459 |                                     48534 |           19646 |           16411 |            20257 | 210.517 |
|                 2 |               4 |          110.925 |        96.36 |       30.792 |       50.704 |      125.868 |                 79.0 |                                     52042 |            8773 |           47522 |            24966 | 207.364 |
|                 2 |               5 |          144.124 |      118.919 |       43.304 |       66.371 |      153.368 |                  0.0 |                                     46573 |           33597 |           48269 |            20973 | 263.043 |
|                 2 |               6 |          119.379 |       113.87 |       38.584 |       85.961 |      124.413 |               15.709 |                                     35033 |           26278 |           56665 |            18502 | 248.958 |
+-------------------+-----------------+------------------+--------------+--------------+--------------+--------------+----------------------+-------------------------------------------+-----------------+-----------------+------------------+---------+

Where:

Column	Description	Data Type	Usage
Week_of_the_month	The how manyth week of the month it is [1,2,3,4,5]	integer	Feature
Day_of_the_week	What day of the week it is, Monday to Friday [1,2,3,4,5,6]	integer	Feature
Non_urgent_order	Orders that are not urgent/emergency.	integer	Feature
Urgent_order	Orders that are an emergency to deliver.	integer	Feature
Order_type_A	Orders categorized under type A.	integer	Feature
Order_type_B	Orders categorized under type B.	integer	Feature
Order_type_C	Orders categorized under type A.	integer	Feature
Fiscal_sector_orders	Orders that are for the Fiscal sector.	integer	Feature
Orders_from_the_traffic_controller_sector	Orders from the traffic controller sector	integer	Feature
Banking_orders1	Orders categorized under Banking_orders1.	integer	Feature
Banking_orders2	Orders categorized under Banking_orders2.	integer	Feature
Banking_orders3	Orders categorized under Banking_orders3.	integer	Feature
Target	The total orders in demand.	integer	Label

A label is the thing we're predicting—the y variable in simple linear regression.
A feature is an input variable—the x variable in simple linear regression.

Creating a Machine Learning Predictive model.

To make predictions, we will create a machine learning model using the CREATE PREDICTOR statement and make a Regression prediction.
In the syntax we will specify which columns/ features to use for training and what column the model should learn to predict as our target/label. In this case we will select all the data to train with and use the column 'Target' as our target variable/label that we want to predict.

In the SQL Editor, the below syntax will be executed:

CREATE PREDICTOR supply_demand
FROM mindsdb_predictions
(SELECT * FROM data.demand)
PREDICT Target;

Select the Run button or Shift+Enter to execute the syntax. If there are no issues with creating the query you will receive the message in the console 'Query successfully completed'.

The model can take a while to train. The status of the model's training can be checked with the below syntax:

SELECT * FROM mindsdb.predictors WHERE name='supply_demand'

Once the model is finished training,the status will show complete. Below you can see that the model has an accuracy of 99%.

The Predictor Status Must be 'complete' Before Making a Prediction

Making a Prediction

When the status of the predictor shows complete, we can go ahead and make a prediction. Predictions are made using the SELECT statement by querying the predictor as if it is a table. We will provide the features with parameters to get a result.

The below syntax will be used to make a prediction:

SELECT Target, Target_explain FROM supply_demand WHERE Week_of_the_month=1 AND Day_of_the_week=5 
AND Non_urgent_order=128.633 AND Urgent_order=96.042
AND Order_type_A=38.058 AND Order_type_B=56.037 AND Order_type_C=130.580 
AND Fiscal_sector_orders=0 AND Orders_from_the_traffic_controller_sector=40419 
AND Banking_orders1=21399 AND Banking_orders2=89461 AND Banking_orders_3=7679;

Run the above syntax and you will see the below results:

The model predicted that with these parameters provided the result for the total orders that will be demanded is 235.725 and by using Target_explain we can see the confidence levels which shows that the confidence_lower_bound is 223.9888 and confidence_higher_bound is 247.463. This lays out that the logistics company should plan for a demand of 224-248 orders.

Making Batch Predictions.

The model can also make batch predictions using the JOIN clause:

SELECT a.Target as total_orders, b.Target as predicted_orders,  a.Week_of_the_month, a.Day_of_the_week 
,a.Non_urgent_order, a.Urgent_order, a.Order_type_A, a.Order_type_B, a.Order_type_C, 
a.Fiscal_sector_orders, 
a.Orders_from_the_traffic_controller_sector, a.Banking_orders1, a.Banking_orders2, a.Banking_orders_3
FROM mindsdb_predictions.data.demand as a
JOIN mindsdb.supply_demand as b limit 5;

+--------------+--------------------+-------------------+-----------------+------------------+--------------+--------------+--------------+--------------+----------------------+-------------------------------------------+-----------------+-----------------+------------------+
| total_orders | predicted_orders   | Week_of_the_month | Day_of_the_week | Non_urgent_order | Urgent_order | Order_type_A | Order_type_B | Order_type_C | Fiscal_sector_orders | Orders_from_the_traffic_controller_sector | Banking_orders1 | Banking_orders2 | Banking_orders_3 |
+--------------+--------------------+-------------------+-----------------+------------------+--------------+--------------+--------------+--------------+----------------------+-------------------------------------------+-----------------+-----------------+------------------+
| 317.12       | 322.1392172468534  | 2                 | 2               | 171.297          | 127.667      | 41.542       | 113.294      | 162.284      | 18.156               | 49971                                     | 33703           | 69054           | 18423            |
| 210.517      | 206.24671425495697 | 2                 | 3               | 90.532           | 113.526      | 37.679       | 56.618       | 116.22       | 6.459                | 48534                                     | 19646           | 16411           | 20257            |
| 207.364      | 202.6728809487407  | 2                 | 4               | 110.925          | 96.36        | 30.792       | 50.704       | 125.868      | 79.0                 | 52042                                     | 8773            | 47522           | 24966            |
| 263.043      | 261.41091237022783 | 2                 | 5               | 144.124          | 118.919      | 43.304       | 66.371       | 153.368      | 0.0                  | 46573                                     | 33597           | 48269           | 20973            |
| 248.958      | 250.0993082475708  | 2                 | 6               | 119.379          | 113.87       | 38.584       | 85.961       | 124.413      | 15.709               | 35033                                     | 26278           | 56665           | 18502            |
+--------------+--------------------+-------------------+-----------------+------------------+--------------+--------------+--------------+--------------+----------------------+-------------------------------------------+-----------------+-----------------+------------------+

Want to try it out for yourself? Sign up for a free MindsDB account and join our community!
Engage with MindsDB community on Slack or Github to ask questions, share and express ideas and thoughts!

Make sure to check out the official website of MindsDB. For more check out other tutorials and MindsDB documentation.