Predictions in Dynamics CRM with custom Azure Machine Learning integrations

Earlier this year I wrote a post that showed how to perform sentiment analysis in Dynamics CRM using Microsoft Azure Text Analytics. Azure Text Analytics makes it incredibly easy to use sentiment analysis (with English text only), but the full Azure Machine Learning offering is much more powerful. In today's post I will show how to create a custom predictive web service in Azure ML and make predictions with it in Dynamics CRM.

Why am I doing this?

One of the exciting announcements about Dynamics CRM 2016 is that it includes some sort of integration with Azure ML, so what's the point of this blog post? Actually, here are two reasons:

  1. It's not clear (as of late November 2016) what capabilities the standard CRM-Azure ML integration will have. The approach demonstrated here allows for total customization.
  2. The approach below can be used right now in any version of CRM (online or on-premise) from CRM 2011 onward.

The demo data set

For this demonstration I am using data from the AdventureWorks data warehouse sample database to build a model to predict whether a contact in CRM is likely to be a bicycle buyer. I created a flat file of contacts that includes several independent variables and a yes/no flag for whether they purchased a bicycle. That sample file is included in my solution source linked at the bottom of this post.

You can also download the full AdventureWorks database from CodePlex here.

Getting started with Azure ML

As I was trying to get the hang of Azure ML, I found the Bluewater SQL blog tremendously helpful. If you're just getting started with Azure ML, I suggest you take a look at both of these posts before continuing. (These two posts also use the AdventureWorks data warehouse sample database, though I had already decided to use it for my demo before I found this site. I guess the AdventureWorks data is an obvious choice for this sort of thing.)


Creating the predictive web service

Now that the introductory bits are out of the way, here's an overview of how I set up my predictive web service in Azure ML. I've also shared my work in the Cortana Analytics Gallery, so if you just want to see how the experiments are set up without going through all the steps yourself, skip to the end of this post for the gallery links.

  1. Export your data set from CRM. I use a simple KingswaySoft SSIS package to do this. It's included in my sample project files.
  2. Upload your data set to Azure ML Studio. I am using the Azure ML free tier, which requires me to manually upload the data set for the initial upload and any subsequent updates. If I were using the standard tier, it would be possible to upload the data set to Azure blob storage as part of my SSIS package. This would be useful for periodically retraining the model with updated data in the future. Data set
  3. Create and run a new experiment to classify bike buyers. I basically followed the steps outlined in the two Bluewater SQL posts, so take a look at those if you want to see a more detailed explanation. Training experiment
  4. Save the trained model from this experiment to use in your predictive web service.
  5. Create a new experiment that will serve as the foundation for the predictive web service
  6. Add the following items to the new experiment canvas:
    1. Data set
    2. Trained model from the earlier experiment
    3. Score model component
    4. Project columns transformation between the data set and score model component to exclude the lpa_bikerbuyername column
    5. Project columns transformation after score model component to only include score labels
  7. Run the experiment.
  8. Create a predictive web service. Predictive experiment
  9. Run the experiment.
  10. Deploy the web service. Web services
  11. Open the web service and copy the API key to use later. Web service detail view
  12. Open the request/response page.
  13. Copy the post URL to use later. Leave off the "&details=true" at the end. Request/response API documentation
  14. Scroll down to find the JSON request/response samples and copy them for review later. JSON request

Making predictions from CRM

Once your predictive web service is set up, using it in Dynamics CRM is as simple as posting a JSON request to the web service and then parsing the JSON response. A few years ago, I wrote a code sample showing an easy way to make JSON requests from CRM custom assemblies that serves as the foundation for the workflow activity I'm using in this demo. There are only a few changes required to that sample:

  1. Add an authorization header that includes the bearer API key you copied earlier.
  2. Add input/output parameters that match the inputs and outputs for your predictive web service. I am also passing the API key and service endpoint as input parameters instead of hardcoding them.
  3. Modify the JSON request/response classes to allow for serialization to/deserialization from the request and response messages. Because of how Azure ML passes the inputs/outputs, these are actually fairly generic, so you can probably use mine with no or minimal changes.
  4. Send the correct request parameters and correctly handle the response parameters.

To demonstrate the custom workflow activity, I created a sample CRM dialog.
CRM dialog definition

Here's where I supply the input parameters.
Custom workflow activity

Here's what the dialog looks like when I run it from a contact record.
Bike buyer prediction

The code

You can get all the code for this demonstration from my GitHub repository here. The sample code includes the following:

  1. CRM solution containing:
    1. Contact entity with custom fields added and a system view for data export for Azure ML
    2. Sample dialog (you would need to update the parameters with your own API key and endpoint details)
    3. Compiled custom workflow activity to call the web service
  2. Contact data ready for import to CRM
  3. SSIS package solution for data export
  4. Custom workflow activity source code

I've also shared my training and prediction experiments in the Cortana Analytics Gallery. Here are the links where you can open and import them into your own workspace:

  1. Training experiment
  2. Prediction experiment

What do you think about this approach? Does it seem like it would be useful to you in a real-world situation? Let me know in the comments!

comments powered by Disqus