R Web API from Dynamics 365 FinOps

Microsoft gives us a fair number of options to seamlessly connect machine learning models to our production code, and I honestly love using them all. AzureML is fantastic for many use cases, and with the Data Factory, Databricks and Data Lakes combo virtually every possible scenario can be covered really nicely.

Except of course if the model you need to use is hosted by a 3rd party which does not support any of these services. Then again, you might want to quickly test a few models first in a POC context before committing to “productizing” these into AzureML. Perhaps you just don’t want all your eggs in one vendor basket, or all your raindrops in one single cloud.

Worse, you might have a requirement to call an R API from D365 FinOps. In this blog post I’ll show you how.

First things first, let’s build a simple R model using the Prophet library from Facebook to do forecasting. This uses a data frame with two columns, y & ds to feed a time series set of values (y) based on time (ds). Prophet supports a lot of parameters for seasonality and such and I suggest reading up on it.

For our example I’ll keep things simple, and assume the R script won’t be doing any munging or wrangling as such. Clean data frame goes in, Prophet predicts, but instead of returning the y-hat values (Ŷ) we’ll make it interesting and return a set of base64 encoded PNG plots containing the forecast and seasonality trends instead.

So there are a number of challenges for us:

  • We need to host this R model as an API
  • We need to grab the resulting plot predictions created by Prophet
  • Encode the plots to base64 and return it from the API as JSON
  • Call and display this all in D365 from a form

The best way I’ve found to host R as an API is to use the Plumber library. So I’ve deployed a Linux environment in my cloud of choice and installed all the required R libraries, including Plumber, and set up NGINX to route incoming traffic on port 80 to Plumber which listens on port 8000. To call this API from D365 you’ll need to install a certificate as only HTTPS will do between D365 and our Linux box.

The R code is shown below, detailing how we grab the plots and encode it to base64. We also receive our data frame as part of the call so we need to URIDecode it. This will do for small data sets; if you want to tackle a large data set, use a different mechanism of passing a reference to the data, perhaps a POST call with the data in the body as JSON. In our case our API returns JSON containing three base64 encoded plots.

library(prophet)
library(dplyr)
library(ggplot2)
library(png)
library(plumber)
library(urltools)

encodeGraphic <- function(g) {
  png(tf1 <- tempfile(fileext = ".png"))
  print(g)
  dev.off()
  encoded <- RCurl::base64Encode(readBin(tf1, "raw", file.info(tf1)[1, "size"]), "txt")
  return(encoded)
}

#* Do a forecast
#* @param data a CSV containing ordered, orderdate
#* @get /forecast
function(data="")
{
  json = '{"forecast":"'
  tmp<-URLdecode(data)
  stats <- read.csv(text=tmp, header=TRUE, sep=',',colClasses = c('numeric','Date'))
  names(stats) <- c("y","ds")
  stats$ds <- as.Date(stats$ds) # coerce to ensure date type

  m <- prophet(stats, yearly.seasonality=TRUE)
  future <- make_future_dataframe(m, periods = 4, freq="m")
  forecast <- predict(m, future)

  g<-plot(m, forecast) +
    xlab("Date") +
    ylab("Data") +
    theme_grey() +
    theme_grey() +
    theme(panel.grid.major = element_blank(),
          panel.grid.minor = element_blank(),
          axis.line = element_line(colour = "black")) +
    ggtitle("Sales Forecast");

  encodedForecast<-encodeGraphic(g)
  json <- paste(json, encodedForecast,sep='')
  g<-prophet_plot_components(m, forecast)
  json <- paste(json, '","trend":"', sep='')
  encodedTrend <- encodeGraphic(g[1])
  json<-paste(json, encodedTrend,sep='')
  json<-paste(json,'","yearly":"', sep='')
  encodedYearly <- encodeGraphic(g[2])
  json<-paste(json, encodedYearly,sep='')
  json<-paste(json, '"}', sep='')
  return(json)
}

 

Next up we’ll create an extensible control in D365 to host our plots. I like wrapping things in extensible controls as it gives me the ability to obfuscate the JavaScript to protect any commercial IP. So I try to keep as little as possible in X++ and as much as possible in JavaScript.

Here is the code for our BuildControl, just a single CSV property is defined:

[FormDesignControlAttribute("Forecast")]
class ForecastControlBuild extends FormBuildControl
{
    str csv = "";

    [FormDesignPropertyAttribute("CSV","Forecast")]
    public str parmCSV(str _csv=csv)
    {
        if (prmIsDefault(_csv))
        {
            csv = _csv;
        }
        return csv;
    }
}

 

Followed by the code for our Control class that contains our CSV property that we will populate from our X++ form.

[FormControlAttribute('Forecast','',classstr(ForecastControlBuild))]
class ForecastControl extends FormTemplateControl
{
    FormProperty csv;

    public void new(FormBuildControl _build, FormRun _formRun)
    {
        super(_build, _formRun);
        this.setTemplateId('Forecast');
        this.setResourceBundleName('/resources/html/Forecast');
        csv = properties.addProperty(methodStr(ForecastControl, parmCSV), Types::String);
    }

    [FormPropertyAttribute(FormPropertyKind::Value, "CSV")]
    public str parmCSV(str _value = csv.parmValue())
    {
        if (!prmIsDefault(_value))
        {
            csv.setValueOrBinding(_value);
        }
        return csv.parmValue();
    }

    public void applyBuild()
    {
        super();
        ForecastControlBuild build = this.build();

        if (build)
        {
            this.parmCSV(build.parmCSV());
        }
    }
}

 

We’ll add a minimal control HTML file to host our image placeholders. Three simple DIV controls with image controls with their ID’s set to forecastImage, trendImage and yearlyImage respectively, so we can get hold of them from our JavaScript code.

Finally the JavaScript for our control containing the actual Ajax call to our R API.

(function () {
    'use strict';
    $dyn.controls.Forecast = function (data, element) {
        $dyn.ui.Control.apply(this, arguments);
        $dyn.ui.applyDefaults(this, data, $dyn.ui.defaults.Forecast);
    };

    $dyn.controls.Forecast.prototype = $dyn.ui.extendPrototype($dyn.ui.Control.prototype, {
        init: function (data, element) {
            var self = this;
            $dyn.ui.Control.prototype.init.apply(this, arguments);
            $dyn.observe(data.CSV, function (csv)
            {
                document.getElementById('forecastImage').style.display = "none";
                document.getElementById('trendImage').style.display = "none";
                document.getElementById('yearlyImage').style.display = "none";
                if (csv.length>0)
                {
                    var url = 'https://yourboxhere.australiaeast.cloudapp.azure.com/forecast?data=' + csv;
                    $.ajax({
                        crossOrigin: true,
                        url: url,
                        success: function (data) {
                            var obj = JSON.parse(data);
                            var forecast = obj.forecast;
                            var trend = obj.trend;
                            var yearly = obj.yearly;

                            document.getElementById('forecastImage').src = 'data:image/png;base64,' + forecast;
                            document.getElementById('trendImage').src = 'data:image/png;base64,' + trend;
                            document.getElementById('yearlyImage').src = 'data:image/png;base64,' + yearly;
                            document.getElementById('forecastImage').style.display = "block";
                            document.getElementById('trendImage').style.display = "block";
                            document.getElementById('yearlyImage').style.display = "block";
                        }
                    });
                }
            })
        }
    });
})();

 

So far it’s all fairly simple, and we can add a demo form in X++ to use our extensible control. We’ll grab some sales orders from D365, URI encode it manually and then send it off to our extensible control to pass to our R API sitting somewhere outside the D365 cloud.

class ForecastFormClass
{
    ///

    ///
    ///

 

    ///
    ///
    [FormControlEventHandler(formControlStr(ForecastForm, FormCommandButtonControl1), FormControlEventType::Clicked)]
    public static void FormCommandButtonControl1_OnClicked(FormControl sender, FormControlEventArgs e)
    {
        FormCommandButtonControl callerButton = sender as FormCommandButtonControl; 
        FormRun form = callerButton.formRun();
        ForecastControl forecastControl;
        forecastControl = form.control(form.controlId("ForecastControl1"));

        SalesLine   SalesLine;
        date        varStartPeriodDT    = mkdate(1, 1, 2015);
        date        varEndPeriodDT      = mkDate(1,7,2016);
        str         csv                 = "ordered%2Corderdate%0D%0A";

        while select sum(QtyOrdered), ShippingDateRequested  from SalesLine group by ShippingDateRequested
            where SalesLine.ShippingDateRequested >= varStartPeriodDT && SalesLine.ShippingDateRequested <= varEndPeriodDT &&  SalesLine.ItemId == 'T0001'
        {
            csv = csv + int2str(SalesLine.QtyOrdered) + "%2C" + date2str(SalesLine.ShippingDateRequested,321,2,3,2,3,4) + "+00%3A00%3A00%0D%0A";
        }
        forecastControl.parmCSV(csv);
    }
}

 

A second or two later and we receive our plots.

AXForecast

Pretty simple stuff. We can extend this further by passing various parameters to the R API, for example, which time-series model we would like to use, whether to return the predicted values (Ŷ) or not, seasonality parameters and anything else we need.

Advertisements

Visualize 3D Models in D365 FinOps

In this short blog post I’m going to show you how to build a 3D extensible control using the Extensible Control Framework in Dynamics 365 FinOps (AX). This can come in handy for ISV’s working in the manufacturing or additive manufacturing space (3D Printing).

Being able to fully visualize and interact with 3D models of parts within D365 brings us one step close to having a full end to end ERP > 3D printing interface, which is a side project I am working on.

Extensible controls allows us to build self-contained visual controls that we can share and allow other developers to simply drop onto a form. There are basically 3 main parts to it, the HTML, optional JavaScript, and the X++ class for the control itself, which allows us to communicate between the web browser front-end and the X++ back-end side of things.

For this post I’ll focus on the STL file format, arguably the most popular of the 3D formats, and widely used by 3D printers. We’ll add some basic properties to the control, including the URL of the STL file we want to visualize, object color and control height and width. This can be extended further of course, but we’ll keep things simple for a start.

We’ll start with the X++ class (or classes, in this case) which consists of the Control and BuildControl classes. The BuildControl class is where we define our controls public properties that once dropped on a form can be set by the X++ developer, and maintained during runtime. The source for our class is shown below.

/// <summary>
/// Build Control for 3D STL Viewer
/// </summary>
[FormDesignControlAttribute("XalSTL")]
class XalSTLControlBuild extends FormBuildControl
{
    str url = "";
    int innerHeight = 540;
    int innerWidth = 1024;
    int objectColor = 925765; //dark blue
    int objectShininess = 200;

}

[FormDesignPropertyAttribute("URL","XalSTL")]
public str parmURL(str _url=url)
{
    if (prmIsDefault(_url))
    {
        url = _url;
    }
    return url;
}

[FormDesignPropertyAttribute("InnerHeight","XalSTL")]
public int parmInnerHeight(int _innerHeight=innerHeight)
{
    if (prmIsDefault(_innerHeight))
    {
        innerHeight = _innerHeight;
    }
    return innerHeight;
}

[FormDesignPropertyAttribute("InnerWidth","XalSTL")]
public int parmInnerWidth(int _innerWidth=innerWidth)
{
    if (prmIsDefault(_innerWidth))
    {
        innerWidth = _innerWidth;
    }
    return innerWidth;
}

[FormDesignPropertyAttribute("ObjectColor","XalSTL")]
public int parmObjectColor(int _objectColor=objectColor)
{
    if (prmIsDefault(_objectColor))
    {
        objectColor = _objectColor;
    }
    return objectColor;
}

[FormDesignPropertyAttribute("ObjectShininess","XalSTL")]
public int parmObjectShininess(int _objectShininess=objectShininess)
{
    if (prmIsDefault(_objectShininess))
    {
        objectShininess = _objectShininess;
    }
    return objectShininess;
}

 

Next up is our main control class, with source below. Not much happening here, just basic framework stuff.

/// <summary>
/// Defines a 3D STL Viewer Control
/// </summary>
[FormControlAttribute('XalSTL','',classstr(XalSTLControlBuild))]
class XalSTLControl extends FormTemplateControl
{
    FormProperty url;
    FormProperty innerHeight;
    FormProperty innerWidth;
    FormProperty objectColor;
    FormProperty objectShininess;

}

protected void new(FormBuildControl _build, FormRun _formRun)
{
    super(_build, _formRun);
 
    this.setTemplateId('XalSTL');
    this.setResourceBundleName('/resources/html/XalSTL');

    url = properties.addProperty(methodStr(XalSTLControl, parmURL), Types::String);
    innerHeight = properties.addProperty(methodStr(XalSTLControl, parmInnerHeight), Types::Integer);
    innerWidth = properties.addProperty(methodStr(XalSTLControl, parmInnerWidth), Types::Integer);
    objectColor = properties.addProperty(methodStr(XalSTLControl, parmObjectColor), Types::Integer);
    objectShininess = properties.addProperty(methodStr(XalSTLControl, parmObjectShininess), Types::Integer);
}

[FormPropertyAttribute(FormPropertyKind::Value, "URL")]
public str parmURL(str _value = url.parmValue())
{
    if (!prmIsDefault(_value))
    {
        url.setValueOrBinding(_value);
    }
    return url.parmValue();
}

[FormPropertyAttribute(FormPropertyKind::Value, "InnerHeight")]
public int parmInnerHeight(int _value = innerHeight.parmValue())
{
    if (!prmIsDefault(_value))
    {
        innerHeight.setValueOrBinding(_value);
    }
    return innerHeight.parmValue();
}

[FormPropertyAttribute(FormPropertyKind::Value, "InnerWidth")]
public int parmInnerWidth(int _value = innerWidth.parmValue())
{
    if (!prmIsDefault(_value))
    {
        innerWidth.setValueOrBinding(_value);
    }
    return innerWidth.parmValue();
}

[FormPropertyAttribute(FormPropertyKind::Value, "ObjectColor")]
public int parmObjectColor(int _value = objectColor.parmValue())
{
    if (!prmIsDefault(_value))
    {
        objectColor.setValueOrBinding(_value);
    }
    return objectColor.parmValue();
}

[FormPropertyAttribute(FormPropertyKind::Value, "ObjectShininess")]
public int parmObjectShininess(int _value = objectShininess.parmValue())
{
    if (!prmIsDefault(_value))
    {
        objectShininess.setValueOrBinding(_value);
    }
    return objectShininess.parmValue();
}

public void applyBuild()
{
    super();
 
    XalSTLControlBuild build = this.build();
 
    if (build)
    {
        this.parmURL(build.parmURL());
        this.parmInnerHeight(build.parmInnerHeight());
        this.parmInnerWidth(build.parmInnerWidth());
    }
}

 

We also add the control HTML which contains little more than a DIV which we will use as a canvas for our 3D viewer. I reference four additional files containing a modified version of the THREEJS library, which I’ll share upon request.

<meta name="viewport" content="width=1024, user-scalable=no, initial-scale=0.5, minimum-scale=0.2, maximum-scale=0.5">

src="/resources/scripts/three.js">
src="/resources/scripts/STLLoader.js">
src="/resources/scripts/Detector.js">
src="/resources/scripts/OrbitControls.js">
id="XalSTL" style="max-height:400px;" data-dyn-bind="visible: $data.Visible">
/>
/>
src="/resources/scripts/XalSTL.js">

 

Finally, our control JavaScript contains the nuts and bolts that ties all this down into our control and makes this all work, fast and efficient, in D365. You’ll notice that our control has a URL parameter, and this allows us to store our (large) 3D models in Azure Blob Storage or via the dedicated storage account available within D365, via X++ code.

(function () {
    'use strict';
    $dyn.controls.XalSTL = function (data, element) {
        $dyn.ui.Control.apply(this, arguments);
        $dyn.ui.applyDefaults(this, data, $dyn.ui.defaults.XalSTL);
    };
 
    $dyn.controls.XalSTL.prototype = $dyn.ui.extendPrototype($dyn.ui.Control.prototype, {
        init: function (data, element) {
            var self = this;

            var _url = "";
            var _innerHeight = 540;
            var _innerWidth = 1024;
            var _objectColor = 0x0e2045;
            var _objectShininess = 200;

            $dyn.ui.Control.prototype.init.apply(this, arguments);
 
            if (!Detector.webgl) Detector.addGetWebGLMessage();
            var camera, scene, renderer;
            scene = new THREE.Scene();
            scene.add(new THREE.AmbientLight(0x999999));
            camera = new THREE.PerspectiveCamera(35, _innerWidth / _innerHeight, 1, 500);
            camera.up.set(0, 0, 1);
            camera.position.set(0, -9, 6);
            camera.add(new THREE.PointLight(0xffffff, 0.8));
            scene.add(camera);
            var grid = new THREE.GridHelper(25, 50, 0xffffff, 0x555555);
            grid.rotateOnAxis(new THREE.Vector3(1, 0, 0), 90 * (Math.PI / 180));
            scene.add(grid);
            renderer = new THREE.WebGLRenderer({ antialias: true });
            renderer.setClearColor(0x999999);
            renderer.setPixelRatio(window.devicePixelRatio);
            renderer.setSize(_innerWidth, _innerHeight);
            $(".XalSTL").context.activeElement.appendChild(renderer.domElement)

            $dyn.observe(data.URL, function (url) {
                if (url.toString().length > 0) {
                    _url = url;
                    RefreshModel();
                }
            });

            $dyn.observe(data.InnerHeight, function (innerHeight) {
                _innerHeight = innerHeight;
                RefreshModel();
            });

            $dyn.observe(data.InnerWidth, function (innerWidth) {
                _innerWidth = innerWidth;
                RefreshModel();
            });

            $dyn.observe(data.ObjectColor, function (objectColor) {
                _objectColor = objectColor;
                RefreshModel();
            });

            $dyn.observe(data.ObjectShininess, function (objectShininess) {
                _objectShininess = objectShininess;
                RefreshModel();
            });

            function RefreshModel()
            {
                if (_url.toString().length > 0) {
                    var loader = new THREE.STLLoader();
                    var material = new THREE.MeshPhongMaterial({ color: _objectColor, specular: 0x111111, _objectShininess: 200 });
                    var controls = new THREE.OrbitControls(camera, renderer.domElement);
                    loader.load(_url, function (geometry) {
                        var mesh = new THREE.Mesh(geometry, material);
                        mesh.position.set(0, 0, 0);
                        mesh.rotation.set(0, 0, 0);
                        mesh.scale.set(.02, .02, .02);
                        mesh.castShadow = true;
                        mesh.receiveShadow = true;
                        scene.add(mesh);
                        render();
                        controls.addEventListener('change', render);
                        controls.target.set(0, 1.2, 2);
                        controls.update();
                        window.addEventListener('resize', onWindowResize, false);
                    });
                }
            }

            function onWindowResize() {
                camera.aspect = _innerWidth / _innerHeight;
                camera.updateProjectionMatrix();
                renderer.setSize(_innerWidth, _innerHeight);
                render();
            }

            function render() {
                renderer.render(scene, camera);
            }
        }
    });
})();

 

We can construct a basic demo dialog form as shown below, hit the Reload button and wait for the magic. Using the mouse, we can zoom in and out, and rotate the object in 3D.

XalSTL

Adding a Fabricate button can trigger an event to kick-start our 3D printing process. To tie this all together we can use the rest of the services in D365 for a proper end to end manufacturing pipeline containing CRM, BOM, Invoicing, Projects and everything else normally involved in manufacturing, without leaving the D365 UI.

For a demo video showing this control in action, click here