DEV Community

Cover image for How to pick the best-performing time-series AI model for your specific data
MindsDB Team for MindsDB

Posted on • Updated on • Originally published at mindsdb.com

How to pick the best-performing time-series AI model for your specific data

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.

Nixtla MindsDB diagram

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

And query the historical_expenditures table.

SELECT *
FROM mysql_demo_db.historical_expenditures
LIMIT 10;
Enter fullscreen mode Exit fullscreen mode

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

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

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

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

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

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

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

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)