MindsDB: Predicting Supply Chain Demand with Machine Learning using SQL
Tags: machinelearning, ai, mindsdb, sql
Published: 2022-06-27 18:16:52

*Community Author:* [**Chandre Van Der Westhuizen**](https://github.com/chandrevdw31)

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](https://mindsdb.com/) 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](https://docs.mindsdb.com/setup/cloud/) or local deployment via [docker](https://docs.mindsdb.com/setup/self-hosted/docker/) or [pip installation.](https://docs.mindsdb.com/setup/self-hosted/pip/windows/)
2. Dataset: You can find the Supply chain and Demand dataset on the [UCI Machine Learning Repository](https://archive-beta.ics.uci.edu/ml/datasets/daily+demand+forecasting+orders).

## 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](https://docs.mindsdb.com/sql/create/databases/) 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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/m7qmwgqvwse6crj0aksl.png)

- 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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/at6n4gm5lz9hry3afh1j.png)

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:
> ```bash
> ngrok tcp [db-port]
> ```
> for example,if your port number is 5433 you will see a similar output:
> ```console
> 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:

```sql
SELECT * 
FROM example_db.datasource.table_name
LIMIT 10;
```

We will run the query to preview the data:
```sql
SELECT * 
FROM mindsdb_predictions.data.demand
LIMIT 10;
```
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kyysyuc8u1bc7qw6iv9z.png) 

### Understanding the data.

```sql
+-------------------+-----------------+------------------+--------------+--------------+--------------+--------------+----------------------+-------------------------------------------+-----------------+-----------------+------------------+---------+
| 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`](https://docs.mindsdb.com/sql/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:

```sql
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'.**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yo3cva63p54qxmln1wue.png)
 

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

```sql
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%.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/go0cn3p8evblrn5c8s0b.png)
 

>*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`](https://docs.mindsdb.com/sql/api/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:

```sql
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:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/j0jari3x85q8s5xneah1.png)
 

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`](https://docs.mindsdb.com/sql/api/join/) clause:

```sql
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;
```

```sql
+--------------+--------------------+-------------------+-----------------+------------------+--------------+--------------+--------------+--------------+----------------------+-------------------------------------------+-----------------+-----------------+------------------+
| 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](https://cloud.mindsdb.com/signup?utm_medium=community&utm_source=ext.%20blogs&utm_campaign=blog-crop-detection) and join our community!
Engage with MindsDB community on [Slack](https://join.slack.com/t/mindsdbcommunity/shared_invite/zt-o8mrmx3l-5ai~5H66s6wlxFfBMVI6wQ) or [Github](https://github.com/mindsdb/mindsdb/discussions) to ask questions, share and express ideas and thoughts!

Make sure to check out the official website of [MindsDB](https://mindsdb.com/). For more check out other [tutorials and MindsDB documentation](https://docs.mindsdb.com/).