DEV Community

Cover image for MindsDB: Predicting Supply Chain Demand with Machine Learning using SQL
Chandre Van Der Westhuizen
Chandre Van Der Westhuizen

Posted on • Edited on

MindsDB: Predicting Supply Chain Demand with Machine Learning using SQL

#machinelearning #ai #mindsdb #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.

Image description

  • 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. }

Image description

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;
Enter fullscreen mode Exit fullscreen mode

We will run the query to preview the data:

SELECT * 
FROM mindsdb_predictions.data.demand
LIMIT 10;
Enter fullscreen mode Exit fullscreen mode

Image description

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 |
+-------------------+-----------------+------------------+--------------+--------------+--------------+--------------+----------------------+-------------------------------------------+-----------------+-----------------+------------------+---------+
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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'.

Image description

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'
Enter fullscreen mode Exit fullscreen mode

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

Image description

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;
Enter fullscreen mode Exit fullscreen mode

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

Image description

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;
Enter fullscreen mode Exit fullscreen mode 
+--------------+--------------------+-------------------+-----------------+------------------+--------------+--------------+--------------+--------------+----------------------+-------------------------------------------+-----------------+-----------------+------------------+
| 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            |
+--------------+--------------------+-------------------+-----------------+------------------+--------------+--------------+--------------+--------------+----------------------+-------------------------------------------+-----------------+-----------------+------------------+
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (0)

Read next

dev_ar profile image

Machine Learning in Algorithmic Trading: The Global Impact and India’s Rising Role

Ayan Rastogi -

shawn_cao_fe12dd5b6a429ed profile image

Fina Categorization API made publicly free

Shawn Cao -

trixsec profile image

Part 12: Building Your Own AI - Model Evaluation and Tuning for Optimal Performance

Trix Cyrus -

mikeyoung44 profile image

Exposing LLM-Controlled Robots' Vulnerability to Jailbreaking Physical Attacks

Mike Young -