Using Times Series API in Microsoft Dynamics NAV 2017


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.


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.

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:

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

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.



Qualitative Methods

  • When a product is new, however, or when data are lacking for one reasons, you have to rely on judgement and intuition. In such cases, you are best advised to find the most experienced, market-savy, objective person – or better yet, group of experts – and rely on them for a rough estimate of likely demand.
  • A dose of intuition from a reliable source can be helpful even when working with plentiful data.
  • Five major types of qualitative forecasting are discussed here:
    • Personal insight.
    • Sales force consensus estimate
    • Management estimate (panel discussion)
    • Market research
    • Delphi method

Personal Insight

Forecasts may sometimes be based upon insight of the most experienced, most knowledgeable, or most senior person available.

Sales Force Consensus Estimate

  • The sales and marketing area (or areas) brings special expertise to forecasting, because they maintain the closest contact with customers. While they may participate in gathering data for quantitative forecasts, their special contribution comes at the qualitative level. Even when there is a quantitative forecast, the sales force should be given a chance to review it to see if it is consistent with their knowledge of the marketplace.
  • Bringing the entire field sales force together to create a consensus forecast provides the firm – and, at least indirectly, its supply chain partners – a view of the whole market, including all sectors or geographic regions. Demand, obviously, can vary greatly in different market segments.
  • Like all functional areas, sales and marketing may bring a special bias to their demand forecasts, generally an optimistic – sometimes an overlay optimistic – one.

Management Estimate

The management estimate relies upon a consensus of panel members. Generally, the panel of management-level experts conducts a series of forecasting meetings, with the results of one meeting providing the basis for the next until the panel reaches a consensus. In the process, the panel may rely upon various techniques, including pyramid forecasting and forecasting by historical analogy.

  1. Pyramid Forecasting

    Pyramid forecasting, or rationalizing high- and low-level forecasts, enables management to review and adjust forecasts made at an aggregate level and keep lower-level forecasts balanced. In the process, item forecasts first are aggregated by product group. Management then makes a new forecast for the group. The value is then transferred to individual item forecasts so that they are consistent with the aggregate plan.

  2. Historical Analogy

    When there are no data on a new product or service, forecasters may instead study past patterns of demand for a similar product or service.

Market Research

  • Market research (also known as marketing research) is the systematic gathering, recording, and analyzing of data about problems relating to the marketing of goods and services. Such research may be undertaken by impartial agencies or by business firms or their agents.
  • Agent as one who acts on behalf of another (the principal) in dealing with a third party. Examples include a sales agent and purchasing agent.

Market research includes the following approaches:

  • Market analysis, including product potential studies, which seeks to determine the size, location, nature, and characteristics of a market.
  • Sales analysis, or sales research, which undertakes the systematic study and comparison of sales data.
  • Consumer research, such as motivational research, focus groups, questionnaires, and other methods used to discover and analyze consumer attitudes, reactions, and preferences.
  • Test marketing, which introduces a product or service in a limited pilot area.

Note that when collecting information with questionnaires or surveys the number of responses compared to the number of nonresponses or incomplete answers should also be tracked to determine if the data are statistically valid.

Delphi Method

  • The Delphi method, like sales force and management estimate forecasting, relies upon a panel of experts in the field being studied. Also like other panel based methods, it relies upon the experience, wisdom, insight, and even the intuition of disciplined observers acting in concert.
  • In Delphi method, questionnaires are generally submitted to the individual experts for their anonymous responses in successive rounds. After responding to the questions in one round, the experts comment on replies from the previous round. After hearing replies and responses, the experts have a chance to revise their own previous work. This iterative process aims to reduce differences in thinking as the answers of experts converge, round by round, upon an increasingly accurate consensus forecast.
  • A key feature of the Delphi method is the maintenance of anonymity throughout the process. Instead of meeting face to face, the experts submit their responses, comments, and revisions to a panel director, who is empowered to delete irrelevant information. This reduces the defensiveness that can cause group members to resist changing mistaken views when challenged in person. It also reduces or eliminates the opposite problem: the “groupthink” effect that can cause even a collection of independent thinkers to become emotionally committed to an unrealistic forecast. This is especially likely to happen when a charismatic, opinionated member takes over leadership of the group and steers it in a mistaken direction.
  • The Delphi method suffers the mixed results as it is based on human judgement (like other forecasting systems).
  • The Delphi method can be time-consuming and is best for long-term forecasts.

Quantitative and Qualitative Forecasting

Quantitative Forecasting Techniques

(Definition) An approach to forecasting where historical demand data are used to project future demand. Extrinsic and Intrinsic techniques are typically used.

Qualitative Forecasting Techniques

(Definition) An approach to forecasting that is based on intuitive or judgmental evaluation. It is used generally when data are scarce, not available, or no longer relevant. Common types of qualitative techniques include: personal insight, sales force estimates, panel consensus, market research, visionary forecasting, and the Delphi method. Examples include developing long-range projections and new product introduction.

**Combination Methods

  • Good forecasting is best done with combination of quantitative and qualitative considerations.
  • When using combination methods, both the quantitative forecast and the qualitative adjusted forecast can be measured separately for error to determine the degree to which qualitative methods are helping or hindering forecasting.

Demand Forecasting Concepts

  • (Definition) Forecasting is the business function that attempts to predict sales and use of products so they can be purchased or manufactured in appropriate quantities in advance.
  • (Definition) Demand Forecasting is forecasting the demand for a particular good, component, or service.
  • Forecasts are subject to uncertainty, and this uncertainty is one potential contributor to the bullwhip effect.

Principles of Forecasting

  • Forecasts are (almost) always wrong
    • A forecast is at the best an estimate of what may happen in the future – if there are no surprises.
    • Circumstances and minds can change. For this reason, forecasts require regular review.
    • Forecasting techniques should be subject to alteration if forecast errors grow too large.
  • Forecasts should include an estimate of error
    • Demand forecasts should include an estimate of how large the forecast error is likely to be.
    • Statistical analysis of the variability of demand around the average demand provides the basis for this error estimate.
    • Error estimates should also be given in terms of the monetary value of the error so that the errors with the most dollars at risk can be addressed first.
  • Forecasts are more accurate for groups than for single items
    • Accuracy generally increases with the size of a product group, assuming that forecasts for each item in the group are as likely to be too high as too low. The low forecasts tend to balance out the high forecasts, at least in sizable groups.
    • (Definition) Mix Forecast is a forecast of the proportion of products that will be sold within a given product family, or the proportion of options offered within a product line, even though the appropriate level of units is forecasted for a given product line, an inaccurate mix forecast can create material shortages and inventory problems.
    • The general principle at work in these cases is risk pooling – taking individual risks and combining them into a pool. The overall risk for the pool tends to be less than the average of all the risks that flow into the pool.
  • Forecasts of near-term demand are more accurate than long-term forecasts
    • The further you extend your forecast into the future, the more likely that chance and change will derail your estimates.
    • Hence, the need for periodic review and update of demand forecasts in comparison to actual results.
    • Long-term forecasts are generally reviewed on an annual or quarterly basis.
    • Medium-term forecasts are generally reviewed on monthly basis.
    • Short-term forecasts are generally reviewed on a weekly basis.
    • In addition to regular reviews, taking steps to shorten the required lead time for items can shorten the forecasting period and thus improve the accuracy of the forecasts.

Components of Demand

The core components of demand include the following:

  • Trend
    • Demand can stay the same, or it can rise or fall.
    • (Definition) A Trend is general upward or downward movement of a variable over time (for example: demand, process attributes).
  • Seasonality
    • Demand may fluctuate depending on time of the year, for example holidays, weather, or other seasonal events.
    • (Definition) Seasonality, also known as seasonal variation, is a repetitive pattern of demand from year to year (or other repeating time interval) with some periods considerably higher than others.
  • Random Variation
    • Many factors affect demand during specific time periods and occur on a random basis.
    • The size of this variation can usually be measured.
    • (Definition) Random Variation is a fluctuation in data that is caused by uncertain or random occurrences. These random changes are generally very short-term, mere bumps and dips on the road up or down a trend line.
  • Cycle
    • Long-term upward and downward cyclical moves generally correlate with the business cycle, but the duration of these economic trends is difficult to predict and is therefore generally left to economists.
    • Publications such as the Purchasing Managers Index (PMI) can be used to predict economic trends.

The difference between seasonality and cycles can be clarified as follows:

  • Seasonality is a demand pattern that, based on history, will repeat itself on calendar basis such as month, week, day of the week, hour of the day, etc., and therefore can be predicted.
  • Cycles are demand patterns that repeat but follow a wavelike pattern that can span multiple years and therefore cannot be predicted easily.

Independent and Dependent Demand

  • Demand can be classified into two types:
    • Dependent
    • Independent
  • Demand for finished product is independent; demand for a component used in making the product is dependent.
  • (Definition) Dependent Demand that is directly related to or derived from the bill of material structure for other items or end products. Such demands are therefore calculated and need not and should not be forecast.
  • (Definition) Independent Demand is the demand for an item that is unrelated to the demand for other items. Demand for finished goods, parts required for destructive testing, and service parts requirements are examples of independent demand.
  • Forecasting should be done only for the independent demand; dependent demand can then be calculated from the forecast using material requirements planning.
  • A given item, however, may be subject to both types of demand. For example, the demand for automobile tires is dependent in relation to new cars but independent when considered as replacement item to be stocked in a repair shop.