Follow us today>


Get industry insights, product updates, event invites, articles and latest NAV release to your inbox.


Using Times Series API in Microsoft Dynamics NAV 2017

Posted: 29/11/2016 3:46:10 PM by Dynamic Business IT Solutions

1. Introduction

With Microsoft Dynamics NAV 2017 release, Dynamics NAV enters into the world of machine learning and the first step into this journey is to bring the Time Series API to Dynamics NAV developers.

In this article you will get to see how to get an Azure Machine Learning experiment, publish an endpoint and use the time series API to get predictions.

The following steps are required:

  • Create a model.
  • Publish an endpoint for the model so that we can access it.
  • Train the model and get predictions.
  • Use the information to our advantage in the application.

2. Process

2.1. Create Model

There is a publically available model prepared by Microsoft team, aimed at time series predictions. With few click we can create a model from a public template.

So we will use this publically available model and open it in Azure Machine Learning Studio. Then we will copy the experiment to our personal workspace and then we will validate the experiment.

  • To access the model, click here.
  • Click Open in Studio.

  • You will be asked to select a workspace. You can choose to select a free or standard workspace and sign in to Microsoft Azure Machine Learning Studio.

  • To copy the experiment from the gallery, you can choose the default settings.

  • After couple of seconds, the experiment will be available in your workspace Microsoft Azure Machine Learning Studio. You can browse around in the model, zoom in or zoom out to see the different steps.

  • To be able to use the model, it must be validated, before it can be deployed as a web service. To do this click on the Run button.

2.2. Publish Endpoint

After the model was created and tested, we will now create an endpoint and generate an API key and a request URI so that we can consume the model.

On the Microsoft Azure Machine Learning Studio, click Deploy Web Service button. The system will then deploy the machine learning experiment as Web services and will provide API Key which can be consumed by wide range of services.

When the deployment finishes, the Web services dashboard opens. Perform the following steps:

  • Copy the API (and paste may be in Notepad).

  • Select the REQUEST/RESPONSE link to open the API page. On this page we require the Request URI, which you can copy (and paste may be in Notepad).

So at the end we have our API Key and Request URI:

2.3. Get Predictions

We will call the Time Series API from the Microsoft Dynamics NAV Development environment to get predictions on data coming from item Ledger Entry table and also check the quality of predictions programmatically, before it is presented to the end user.

Open the development environment and create a new code unit (say 50010, Forecast Sample).

In the Global Variables, create two text constants as shown below for API Key (say KEYTXT) and Request URI (say URITXT) and paste the values that you had copied earlier.

Create a new function, say CalculateForecast and specify the following Parameters, Return Value and local variables:

TimeSeriesLibrary, will help to prepare data, submit it to Azure Machine Learning and get back the prediction. The actual implementation is implemented as a .NET DLL and that DLL is wrapped by the TimeSeriesLibrary variable and provided us access and CAL functions to developers.

In this scenario we would like to get a prediction for Item sales, so we will get the data from the Item Ledger Entry table.

Enter the following code:

The whole task can be performed by 4 functions of TimeSeriesLibrary variable:

  • The Initialize function will be used to setup the connection.
  • The PrepareData function will transform any table data into a dataset which is ready for submission.
  • There is also a Forecast function, which will call the Azure Machine Learning that receives the predicted interval (in this case one month)
  • The GetForecast function returns a dataset of the forecast of the values

As an example, we will run the forecast of the item "8908-W" (Computer - Highline Package). Run the code unit and check the output:

As an out we can see the calculated forecast and the system expects the sales in next month to be about 3.

So how good is the prediction?

Let us create a list page based around Time Series Forecast.

Go back to the code unit to create another function, say CalculateForecastBulk, in which we add the following code, which will run the page:

This should actually run the forecast for all the items. Go ahead and run the code unit.

The following page appears with number of columns:

  • Group ID contains the item number.
  • Period No. is predicted period number.
  • The Value is the predicted Quantity.
  • The Delta explains range where the predicted value will be probability of 95%. For example, for the highlighted item it means that for 95% the sales next month will be between 1.5 - 5 and the central point is 3.27.s

2.4. Use the Information

Now let us use the received information in the application, for example to create a Purchase Order.

Again we move to the code unit and create a new function called CreatePurchaseOrder, which will create a purchase order depending on the variables that we pass.

NOTE: Call this function from CalculateForecastBulk function.

If you look closer at the function, you will notice that we calculate the ratio between the Predicted Quantity and the Delta. If it is bigger than Bar (which is set as a parameter), the system skips the line.

So depending on the predictions on our model, lines will either be added to our purchase order or not.

So go ahead and run the code unit and check the result. It should create a Purchase Order.