Written by Martyna Slawinska, Software Engineer at MindsDB and Patricio Cerda-Mardini, ML Research Engineer at MindsDB.
In the world of data and artificial intelligence, understanding how things change over time is crucial. Time-series models leverage historical data to make forecasts about the future, making them indispensable in various fields, ranging from finance to weather forecasting.
There are many AI frameworks for time-series forecasting but they may perform differently on different types of data. How to determine which model is the best for your own data? The short answer is - you should experiment.
In this article, we'll explore how to do such experiments directly inside your database and save time on data extraction and transformation. In our example, we’ll use various time-series models developed by Nixtla, including StatsForecast, NeuralForecast, and TimeGPT. We'll show how to benchmark these models against one another and examine their relative performance.
You can do the same with other models, including setting up real-time automation to regularly measure their performance over your live data. We will make a detailed tutorial for this in the future. You can subscribe to our blog to get notified.
What is Nixtla?
Nixtla is a time-series research and deployment company. It provides a comprehensive open-source time-series ecosystem, the Nixtlaverse, that aims to forecast and analyze future events based on historical data.
StatsForecast was developed to overcome the shortcomings of speed, accuracy, and scaling encountered with current Python alternatives for statistical models. StatsForecast provides fast and accurate implementations of AutoARIMA, AutoETS, AutoCES, MSTL, and Theta models in Python. Its use cases include probabilistic forecasting, anomaly detection, and more. StatsForecast provides the possibility to evaluate its performance by cross-validation.
NeuralForecast, as its name indicates, uses neural networks such as Multilayer Perceptron (MLP) and Recurrent Neural networks (RNN), as well as novel proven methods like Neural Basis Expansion Analysis for Time Series (NBEATS), Neural Hierarchical Interpolation for Time Series (NHITS), and Temporal Fusion Transformer (TFT). Depending on the implementation, neural networks may offer enhanced accuracy and efficiency. NeuralForecast is a library of proven neural network models that enable probabilistic forecasting, automatically choosing the best-fit model.
TimeGPT, where GPT stands for Generative Pre-trained Transformer, is a foundational time series model, much like GPT models from OpenAI, but for time-series data. It covers probabilistic forecasting, anomaly detection, multivariate forecasting, and more. TimeGPT can make forecasts without prior training, however, you can finetune it to fit your specific use case.
Check out this blog post that details TimeGPT.
All these models are powered by CoreForecast and have companion libraries that make time series R&D easier, like UtilsForecast and DatasetsForecast. In effect, Nixtla offers a complete ecosystem for time-series forecasting.
What is MindsDB?
MindsDB is the middleware for building custom AI, enabling smarter organizations. It works by connecting any source of data with any AI/ML model or framework and automating how real-time data flows between them.
MindsDB allows you to easily:
Connect to any store of data or end-user application.
Pass data to an AI model from any store of data or end-user application.
Plug the output of an AI model into any store of data or end-user application.
Fully automate these workflows to build AI-powered features and applications.
With MindsDB, you can use Nixtla's models with data from one or more data sources - without the need for creating and maintaining data pipelines for each data source.
In the following chapters, we'll benchmark Nixtla's models against one another using the capabilities of MindsDB.
Data and Models Setup in MindsDB
MindsDB bridges the gap between data and AI, providing numerous integrations with data sources and AI frameworks. You can easily connect any store of data or end-user application and use it to train the model and make predictions.
Basic knowledge of SQL is required to create and deploy AI models with MindsDB. In the following, we’ll set up data and models before proceeding to benchmark the forecasts made by each of the models.
Data Setup
To benchmark time-series models against one another, you’ll use the historical_expenditures table that has 3 columns and 2961 rows.
To access the historical_expenditures table, connect to the sample MySQL database from the MindsDB editor.
CREATE DATABASE mysql_demo_db
WITH ENGINE = "mysql",
PARAMETERS = {
"user": "user",
"password": "MindsDBUser123!",
"host": "db-demo-data.cwoyhfn6bzs0.us-east-1.rds.amazonaws.com",
"port": "3306",
"database": "public"
};
And query the historical_expenditures table.
SELECT *
FROM mysql_demo_db.historical_expenditures
LIMIT 10;
Data used to train the models will exclude values for the last 12 months for each category. Create a view to store the training data.
CREATE VIEW training_data (
SELECT * FROM mysql_demo_db.historical_expenditures
WHERE month NOT IN ('2016-10-01', '2016-11-01', '2016-12-01',
'2017-01-01', '2017-02-01', '2017-03-01',
'2017-04-01', '2017-05-01', '2017-06-01',
'2017-07-01', '2017-08-01', '2017-09-01')
);
Please note that the last 12 months’ expenditure data are excluded from the training data so models will make forecasts for these dates. These forecasts will be compared with real values for these dates.
Now that the input training data is ready, let’s proceed to creating, training, and deploying AI models.
Models Setup
The models that will be benchmarked against one another include the following:
StatsForecast was developed to overcome the shortcomings of speed, accuracy, and scaling encountered with current Python alternatives for statistical models by choosing the best-fit model for a particular use case. It is the time-series framework developed by Nixtla that is designed to tackle time-series problems and optimized for high performance and scalability.
NeuralForecast uses a collection of neural network models, automatically chosen for a particular use case, that may offer enhanced accuracy and efficiency. It is the time-series framework developed by Nixtla that handles time-series problems using a large collection of neural forecasting models.
TimeGPT is the foundational time-series model developed by Nixtla. It is a Generative Pre-trained Transformer (GPT) model trained to forecast time series data without the need for training the model beforehand, similar to GPT models from OpenAI
Here is how to create, train, and deploy each model within MinsdDB:
StatsForecast
CREATE ML_ENGINE statsforecast
FROM statsforecast;
CREATE MODEL statsforecast_model
FROM mindsdb
(SELECT * FROM training_data)
PREDICT expenditure
ORDER BY month
GROUP BY category
WINDOW 120
HORIZON 12
USING ENGINE = 'statsforecast';
NeuralForecast
CREATE ML_ENGINE neuralforecast
FROM neuralforecast;
CREATE MODEL neuralforecast_model
FROM mindsdb
(SELECT * FROM training_data)
PREDICT expenditure
ORDER BY month
GROUP BY category
WINDOW 120
HORIZON 12
USING ENGINE = 'neuralforecast';
TimeGPT
CREATE ML_ENGINE timegpt
FROM timegpt
USING
timegpt_api_key = 'timegpt-api-key';
CREATE MODEL timegpt_model
FROM mindsdb
(SELECT * FROM training_data)
PREDICT expenditure
ORDER BY month
GROUP BY category
HORIZON 12
USING ENGINE = 'timegpt';
The CREATE MODEL statement is used to create, train, and deploy AI models within MindsDB.
It may take up to a few minutes to train the models. You can check the status of the models using the DESCRIBE command.
In this case, the training times for StatsForecast, NeuralForecast, and TimeGPT are 318.595 seconds, 28.937 seconds, and 23.885 seconds respectively.
Forecasted vs. True Values
Having data and models, you can now query each model for the expenditure forecasts and compare them with each other and with real values.
Here are the real values for the dates excluded from the training data:
SELECT substring(month, 1, 10) AS month, category, expenditure
FROM mysql_demo_db.historical_expenditures
WHERE category = 'industry'
AND (month = '2016-10-01' OR month = '2016-11-01' OR month = '2016-12-01'
OR month = '2017-01-01' OR month = '2017-02-01' OR month = '2017-03-01'
OR month = '2017-04-01' OR month = '2017-05-01' OR month = '2017-06-01'
OR month = '2017-07-01' OR month = '2017-08-01' OR month = '2017-09-01');
| month | category | expenditure |
|---|---|---|
| 2016-10-01 | industry | 25974.6 |
| 2016-11-01 | industry | 26781.1 |
| 2016-12-01 | industry | 33100.2 |
| 2017-01-01 | industry | 25306.2 |
| 2017-02-01 | industry | 22615 |
| 2017-03-01 | industry | 25113.5 |
| 2017-04-01 | industry | 24583.1 |
| 2017-05-01 | industry | 25133.2 |
| 2017-06-01 | industry | 25167.5 |
| 2017-07-01 | industry | 25278.6 |
| 2017-08-01 | industry | 25275.4 |
| 2017-09-01 | industry | 25348.7 |
Here are the forecasts made by the StatsForecast engine:
SELECT substring(m.month, 1, 10) AS month, m.category, m.expenditure
FROM training_data AS d
JOIN statsforecast_model AS m
WHERE d.category = 'industry'
AND d.month > LATEST
LIMIT 12;
| month | category | expenditure |
|---|---|---|
| 2016-10-01 | industry | 26166.021484375 |
| 2016-11-01 | industry | 26689.072265625 |
| 2016-12-01 | industry | 32733.255859375 |
| 2017-01-01 | industry | 25656.771484375 |
| 2017-02-01 | industry | 23547.6796875 |
| 2017-03-01 | industry | 25459.3984375 |
| 2017-04-01 | industry | 24843.978515625 |
| 2017-05-01 | industry | 25192.634765625 |
| 2017-06-01 | industry | 25113.376953125 |
| 2017-07-01 | industry | 25594.673828125 |
| 2017-08-01 | industry | 25598.19921875 |
| 2017-09-01 | industry | 25972.87109375 |
Here are the forecasts made by the NeuralForecast engine:
SELECT substring(m.month, 1, 10) AS month, m.category, m.expenditure
FROM training_data AS d
JOIN neuralforecast_model AS m
WHERE d.category = 'industry'
AND d.month > LATEST
LIMIT 12;
| month | category | expenditure |
|---|---|---|
| 2016-10-01 | industry | 25457.98046875 |
| 2016-11-01 | industry | 25772.787109375 |
| 2016-12-01 | industry | 25750.142578125 |
| 2017-01-01 | industry | 25917.263671875 |
| 2017-02-01 | industry | 25732.49609375 |
| 2017-03-01 | industry | 25811.69140625 |
| 2017-04-01 | industry | 25947.197265625 |
| 2017-05-01 | industry | 25974.912109375 |
| 2017-06-01 | industry | 26014.865234375 |
| 2017-07-01 | industry | 26072.509765625 |
| 2017-08-01 | industry | 26136.51171875 |
| 2017-09-01 | industry | 26540.87109375 |
Here are the forecasts made by the TimeGPT engine:
SELECT substring(m.month, 1, 10) AS month, m.category, m.expenditure
FROM training_data AS d
JOIN timegpt_model AS m
WHERE d.category = 'industry'
AND d.month > LATEST
LIMIT 12;
| month | category | expenditure |
|---|---|---|
| 2016-10-01 | industry | 25942.7890625 |
| 2016-11-01 | industry | 27140.45703125 |
| 2016-12-01 | industry | 32551.65625 |
| 2017-01-01 | industry | 25264.654296875 |
| 2017-02-01 | industry | 23348.390625 |
| 2017-03-01 | industry | 24804.380859375 |
| 2017-04-01 | industry | 24388.9296875 |
| 2017-05-01 | industry | 24913.115234375 |
| 2017-06-01 | industry | 24980.32421875 |
| 2017-07-01 | industry | 25446.759765625 |
| 2017-08-01 | industry | 25411.853515625 |
| 2017-09-01 | industry | 25723.41015625 |
Let’s put all forecasts together with real values to compare how accurate the models are.
| month | category | true_value | statsforecast | diff | neuralforecast | diff | timegpt | diff |
|---|---|---|---|---|---|---|---|---|
| 2016-10-01 | industry | 25974.6 | 26166.021484375 | 0.74% | 25457.98046875 | 1.99% | 25942.7890625 | 0.12% |
| 2016-11-01 | industry | 26781.1 | 26689.072265625 | 0.34% | 25772.787109375 | 3.77% | 27140.45703125 | 1.34% |
| 2016-12-01 | industry | 33100.2 | 32733.255859375 | 1.11% | 25750.142578125 | 22.21% | 32551.65625 | 1.66% |
| 2017-01-01 | industry | 25306.2 | 25656.771484375 | 1.39% | 25917.263671875 | 2.41% | 25264.654296875 | 0.16% |
| 2017-02-01 | industry | 22615 | 23547.6796875 | 4.12% | 25732.49609375 | 13.79% | 23348.390625 | 3.24% |
| 2017-03-01 | industry | 25113.5 | 25459.3984375 | 1.38% | 25811.69140625 | 2.78% | 24804.380859375 | 1.23% |
| 2017-04-01 | industry | 24583.1 | 24843.978515625 | 1.06% | 25947.197265625 | 5.55% | 24388.9296875 | 0.79% |
| 2017-05-01 | industry | 25133.2 | 25192.634765625 | 0.24% | 25974.912109375 | 3.35% | 24913.115234375 | 0.88% |
| 2017-06-01 | industry | 25167.5 | 25113.376953125 | 0.22% | 26014.865234375 | 3.37% | 24980.32421875 | 0.74% |
| 2017-07-01 | industry | 25278.6 | 25594.673828125 | 1.25% | 26072.509765625 | 3.14% | 25446.759765625 | 0.67% |
| 2017-08-01 | industry | 25275.4 | 25598.19921875 | 1.28% | 26136.51171875 | 3.41% | 25411.853515625 | 0.54% |
| 2017-09-01 | industry | 25348.7 | 25972.87109375 | 2.46% | 26540.87109375 | 4.7% | 25723.41015625 | 1.48% |
The forecasts made by TimeGPT are closest to the real values, as analyzed in the following sections.
Please note that this comparison has been made based on a specific dataset and parameters defined at model creation time. Therefore, running such comparison on other datasets and parameters may provide a different conclusion.
Here is the query used to get the above statistics altogether in MindsDB:
SELECT
-- values of month and category, and real values of expenditures
realvalues.month AS month, realvalues.category, realvalues.expenditure AS true_value,
-- values of expenditures forecasted with statsforecast and percentage difference
statsforecast.expenditure AS statsforecast_value,
round(abs(cast(realvalues.expenditure as double)-cast(statsforecast.expenditure as double))/cast(realvalues.expenditure as double)*100, 2) AS statsforecast_diff_percentage,
-- values of expenditures forecasted with neuralforecast and percentage difference
neuralforecast.expenditure AS neuralforecast_value,
round(abs(cast(realvalues.expenditure as double)-cast(neuralforecast.expenditure as double))/cast(realvalues.expenditure as double)*100, 2) AS neuralforecast_diff_percentage,
-- values of expenditures forecasted with timegpt and percentage difference
timegpt.expenditure AS timegpt_value,
round(abs(cast(realvalues.expenditure as double)-cast(timegpt.expenditure as double))/cast(realvalues.expenditure as double)*100, 2) AS timegpt_diff_percentage
FROM
-- table that stores real values
(SELECT substring(month, 1, 10) AS month, category, expenditure
FROM mysql_demo_db.historical_expenditures
WHERE category = 'industry'
AND (month = '2016-10-01' OR month = '2016-11-01' OR month = '2016-12-01'
OR month = '2017-01-01' OR month = '2017-02-01' OR month = '2017-03-01'
OR month = '2017-04-01' OR month = '2017-05-01' OR month = '2017-06-01'
OR month = '2017-07-01' OR month = '2017-08-01' OR month = '2017-09-01')) AS realvalues
-- table that stores statsforecast values
JOIN (SELECT substring(m.month, 1, 10) AS month, m.category, m.expenditure
FROM training_data AS d
JOIN statsforecast_model AS m
WHERE d.category = 'industry'
AND d.month > LATEST
LIMIT 12) AS statsforecast
ON realvalues.month = statsforecast.month
-- table that stores neuralforecast values
JOIN (SELECT substring(m.month, 1, 10) AS month, m.category, m.expenditure
FROM training_data AS d
JOIN neuralforecast_model AS m
WHERE d.category = 'industry'
AND d.month > LATEST
LIMIT 12) AS neuralforecast
ON realvalues.month = neuralforecast.month
-- table that stores timegpt values
JOIN (SELECT substring(m.month, 1, 10) AS month, m.category, m.expenditure
FROM training_data AS d
JOIN timegpt_model AS m
WHERE d.category = 'industry'
AND d.month > LATEST
LIMIT 12) AS timegpt
ON realvalues.month = timegpt.month;
Performance Metrics to Evaluate Time-Series Models
Let’s evaluate all the models using the performance metrics for time-series models, including Mean Absolute Error (MAE) and Root Mean Squared Deviation (RMSD).
Both MAE and RMSD help us see how close to real values the forecasts are on average. Imagine you have some predictions that are way off compared to the real values. MAE kind of smooths out these big errors because it just looks at the average. But RMSE makes these big errors stand out more because it squares them before averaging them. So, RMSE gives larger importance to outliers.
Let’s look at the MAE and RMSD values for the considered time-series models.
Mean Absolute Error
The Mean Absolute Error (MAE) is a mathematical metric used to quantify the average of the absolute value of differences between forecasted and actual values.
In other words, the closer the forecasted value is to the true value, the smaller the MAE. And the smaller the MAE, the better the accuracy of the model. However, the MAE value itself depends strongly on data.
You can calculate the MAE values in MindsDB using this syntax:
EVALUATE mean_absolute_error
FROM (SELECT column_name_that_stores_real_value AS actual,
column_name_that_stores_predicted_value AS prediction
FROM table);
Here are the calculated values:
MAE for StatsForecast: 326.419
MAE for NeuralForecast: 1600.176
MAE for TimeGPT: 275.377
The MAE values are counted in hundreds in this case due to the nature of the input data. However, it is clear that the MAE of TmeGPT is significantly lower than for the other models.
Root Mean Squared Deviation
The Root Mean Squared Deviation (RMSD), also called Root Mean Squared Error (RMSE), is used to measure the differences between values predicted by a model and the values observed using the below formula.
Similar to MAE, the closer the forecasted value is to the true value, the smaller the RMSD.
Here are the calculated values:
RMSD for StatsForecast: 55.26
RMSD for NeuralForecast: 169.48
RMSD for TimeGPT: 53.85
The RMSD values are counted in hundreds in this case due to the nature of the input data. However, it is clear that the RMSD of TmeGPT is significantly lower than the RMSD of NeuralForecast and slightly lower than the RMSD of StatsForecast.
Real-Time Forecasts with MindsDB
In real-world scenarios, the data is commonly dynamic, that is, updated regularly. Therefore, to keep the accuracy and performance of the models up-to-date, it is recommended to retrain or finetune the models periodically with new data.
MindsDB offers a custom Jobs feature that enables you to schedule the execution of tasks on time-based or event-based triggers. In this example, the job is going to retrain the models and make fresh forecasts. Finally, it’ll insert the forecasts as Slack notifications.
CREATE JOB get_real_time_forecasts (
-- retraining models using the latest historical data
RETRAIN statsforecast_model
FROM mysql_demo_db
(SELECT * FROM historical_expenditures)
USING
join_learn_process = true;
RETRAIN neuralforecast_model
FROM mysql_demo_db
(SELECT * FROM historical_expenditures)
USING
join_learn_process = true;
RETRAIN timegpt_model
FROM mysql_demo_db
(SELECT * FROM historical_expenditures)
USING
join_learn_process = true;
-- sending forecasts to slack
-- how to connect slack to mindsdb: https://docs.mindsdb.com/integrations/app-integrations/slack#method-2-chatbot-responds-on-a-defined-slack-channel
INSERT INTO slack_app.channels (channel, text)
VALUES("expenditure-forecasts", "Here are the expenditure forecasts for the next 12 months made by StatsForecast:");
INSERT INTO slack_app.channels (channel, text)
SELECT "expenditure-forecasts" AS channel,
concat(m.month, ' --> ', m.expenditure) AS text
FROM mysql_demo_db.historical_expenditures
JOIN statsforecast_model AS m
WHERE d.category = 'industry'
AND d.month > LATEST
LIMIT 12;
INSERT INTO slack_app.channels (channel, text)
VALUES("expenditure-forecasts", "Here are the expenditure forecasts for the next 12 months made by NeuralForecast:");
INSERT INTO slack_app.channels (channel, text)
SELECT "expenditure-forecasts" AS channel,
concat(m.month, ' --> ', m.expenditure) AS text
FROM mysql_demo_db.historical_expenditures
JOIN neuralforecast_model AS m
WHERE d.category = 'industry'
AND d.month > LATEST
LIMIT 12;
INSERT INTO slack_app.channels (channel, text)
VALUES("expenditure-forecasts", "Here are the expenditure forecasts for the next 12 months made by TimeGPT:");
INSERT INTO slack_app.channels (channel, text)
SELECT "expenditure-forecasts" AS channel,
concat(m.month, ' --> ', m.expenditure) AS text
FROM mysql_demo_db.historical_expenditures
JOIN timegpt_model AS m
WHERE d.category = 'industry'
AND d.month > LATEST
LIMIT 12;
)
EVERY 1 month;
Follow this article to see more examples of jobs in action.
Conclusion
In conclusion, selecting the ideal time-series AI model demands meticulous experimentation and evaluation, as showcased by the comparison of Nixtla's StatsForecast, NeuralForecast, and TimeGPT within MindsDB's framework. Each model offers distinct advantages, from StatsForecast's speed and accuracy to NeuralForecast's neural network prowess and TimeGPT's foundational forecasting capabilities. Leveraging MindsDB's seamless integration with diverse data sources and models and its automation features ensures ongoing model refinement and adaptation to dynamic datasets. As organizations navigate the evolving data landscape, these tools empower them to unlock the full potential of time-series data, driving informed decision-making and innovation.
Top comments (0)