Smart Solutions 7: Predictive analysis with Time Series, and intro to R for C/AL developers

Predicting the future

Time Series is a topic on its own in machine learning, as it is one of the cleanest examples of predicting the future. All you need is a dataset that contains a date and a number. Time Series then uses this to predicting values for future periods.

In Dynamics NAV, we already use it in Inventory Forecasting as described in the blog post Smart Solutions 4.

Take a Date and a Decimal

The default version of Dynamics has more than 300 tables that contain date and decimal fields, including all the history tables. So there is a good potential that we can turn some of this data into Time Series to use for a machine learning algorithm for things like these:

  • Risk Assessment
  • Forecasting
  • Budgetting
  • Predictive analysis
  • and so on

Apply R

In this post we will pull some data out of Dynamics NAV, then run R script on it to see what we get. The idea is to get a look at your Dynamics NAV data in a new tool that is designed for data analysis. So in this post we will spend most of our time in R.

Environments

In this example we use RStudio, downloaded from here: https://www.rstudio.com/. There are many other environments you can use to run R, so pick your own favourite. And, if you prefer, use Python instead of R (but the code sample below is all R).

Get some data

Attached is a codeunit which will generate a bit of data. It will insert a new item, LVASE “Lyngby Vase”, and some entries for it. This data will not be business valid as it has not been posted, so don’t try to use it for any other purposes, and only run this codeunit in a demo company!

Once the codeunit has been run to insert data, use the same codeunit to dump the data to c:\Temp\RData.csv. Or skip the Dynamics NAV steps and just use the dataset in the RData.csv file in the attached ZIP: SmartSolutions7

  1. Start R Studio or your preferred R Editor.
  2. Load the data:
    dataset1 <- read.csv2("C:\\Temp\\RData.csv",header=FALSE,sep=";")

    read.csv is designed for US formats. To get data in the European formats, create the relevant CSV file, and adjust the parameters to match your data format. Check that it looks structured by returning just the top rows:

    head(dataset1)

  3. Change column names to be have meaningful titles:
    colnames(dataset1) <- c("Item","Date","Quantity")
  4. format “Date” so that R recognises it as a date field (adjust as needed to match your format):
    dataset1$Date <- as.Date(dataset1$Date, format='%d-%m-%y')
  5. Sort the dataset by Date:
    dataset1 <- dataset1[order(dataset1$Date),]

Predict the future

Now with the data cleaned and prepared, run a model to predict future periods. Here, we will use “Autoregressive Integrated Moving Average models”, abbreviated to ARIMA. Online there will be lots of documentation and courses just in this model. This is how to run it:

  1. Load it into R:
    library(forecast)

    If you use RStudio then you may need to also go to “Tools” -> “Install packages” and type in “forecast” and install it here first.

  2. Run an ARIMA forecast model on your data and copy your forecast into a new dataset:
    ar <- modelArima(dataset1[,3],12,35)

    The parameters here are, apart from your dataset (selecting just column 3), the number of periods you want to forecast, confidence level.

  3. Look at your new forecast:
    ar
  4. Visualize it:
    plot(ar$mean)

Or why not use TBATS method (Exponential smoothing state space model with Box-Cox transformation, ARMA errors, Trend and Seasonal components). That’s a model which takes seasonality better into consideration. Read more about it here:
https://www.rdocumentation.org/packages/forecast/versions/8.4/topics/tbats
https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104381326

The good thing is that a lot of the hard work has been done for us already, and made available with a simple R command or two. So continue your R project with a few more lines:

TBatResult = tbats(dataset1[,3])
TBatResult
forecast(TBatResult)
plot(TBatResult)
plot(forecast(TBatResult))

If you enable “Inventory and Sales Forecasting” in Dynamics NAV as described in the link above, then hopefully you will see similar predictions in Dynamics NAV. But (also following the link above) looking at the R code that Dynamics NAV runs, by now you may get some ideas of what the R script does. Of course it does much more than shown here, including some further data-cleaning (assign missing values) and trying out different prediction models to find the best results.

Next steps

Hopefully this post shows a simple way to get your Dynamics NAV data into another world: The world of Data Science. By bringing it over here you open up for an enormous pool of resources, prediction models, chart capabilities, and much more. And by following the “Smart Solutions” series here on this blog, you will see how to integrate R models back into your NAV as part of a business solution.