Monday, May 19, 2014

Enrich CRM Data using the Azure Scheduler

Really the focus is to show another alternative for running a scheduled job against Dynamics CRM. Currently if you need to run something on a schedule you're probably using one of the following methods:

Since CRM doesn't yet have a built in scheduling engine you end up relying on something external to handle recurrence, and that something has to run on a server someplace. Obviously the bulk delete method doesn't rely on anything outside of CRM but still isn't quite perfect because the additional entities and records you need to maintain and you can only run it once a day. If you're a developer and looking to get something in place on the cheap and don't have a server available this example will show how you can use some of Azure's free features to make it happen. Specifically we'll create a Web API application to communicate with CRM and our external provider running on a shared (free) Azure web site and also utilize the Azure Scheduler to manage the recurrence pattern.

The free version of the Azure Scheduler limits us to execution no more than once an hour and a maximum of 5 jobs. If you have a lot going on $20 a month will get you executions every minute and up to 50 jobs - which sounds like a pretty good deal. 

In this scenario we'll look to integrate with Yelp and update CRM Account records with the Yelp rating value.

Getting the Project Set Up


Check out my previous post Simple CRM Lead Capture using Azure & Web API for the basic Web API project setup and don't forget to include the connection string information in the web.config.
In CRM on the Account record you'll need to create 2 fields:
  1. Yelp Id - Single Line of Text
  2. Yelp Rating - Decimal
For the sake of argument we'll assume you have some other process in place to populate the value for Yelp Id. The value can be found in the URL for the given business.

image

To simplify the connection to the Yelp API we are going to use the YelpSharp library. So go ahead and search NuGet for YelpSharp and install it into your project.

1

Add the following reference DLLs to the project:
  • Microsoft.Xrm.Client (located in the SDK bin folder)
  • Microsoft.Xrm.Sdk (located in the SDK bin folder)

Show Me the Codez


Once you are set, go ahead and add a new Web API 2 Controller with read/write actions to your project - I called mine AccountEnrichmentController.cs.
To the top add the following:
  • using Microsoft.Xrm.Client;
  • using Microsoft.Xrm.Client.Services;
  • using Microsoft.Xrm.Sdk;
  • using Microsoft.Xrm.Sdk.Query;
  • using YelpSharp;
  • using YelpSharp.Data;

At this point replace the contents of the class with the following code:

private static string _connection;
private OrganizationService _orgService;

public void Get()
{
    _connection = 
        ConfigurationManager.ConnectionStrings["CRMOnlineO365"].ConnectionString;
    CrmConnection connection = CrmConnection.Parse(_connection);

    EntityCollection results = GetYelpAccounts();
    if (!results.Entities.Any()) return;

    using (_orgService = new OrganizationService(connection))
    {
        foreach (Entity entity in results.Entities)
        {
            GetYelpData(entity.Id, 
                entity.GetAttributeValue<string>("lat_yelpid"));
        }
    }
}

private void GetYelpData(Guid id, string yelpId)
{
    var options = new Options
    {
        AccessToken = ConfigurationManager.AppSettings["YelpAPIToken"],
        AccessTokenSecret = 
            ConfigurationManager.AppSettings["YelpAPITokenSecret"],
        ConsumerKey = ConfigurationManager.AppSettings["YelpAPIConsumerKey"],
        ConsumerSecret = 
            ConfigurationManager.AppSettings["YelpAPIConsumerSecret"]
    };

    var yelp = new Yelp(options);
    Business business = yelp.GetBusiness(yelpId).Result;
    if (business != null)
        UpdateAccount(id, business.rating);
}

private EntityCollection GetYelpAccounts()
{
    CrmConnection connection = CrmConnection.Parse(_connection);
    using (_orgService = new OrganizationService(connection))
    {
        var query = new QueryExpression
        {
            EntityName = "account",
            ColumnSet = new ColumnSet("lat_yelpid"),
            Criteria = new FilterExpression
            {
                Conditions =
                {
                    new ConditionExpression
                    {
                        AttributeName = "lat_yelpid",
                        Operator = ConditionOperator.NotNull
                    }
                }
            }
        };

        return _orgService.RetrieveMultiple(query);
    }
}

private void UpdateAccount(Guid id, double rating)
{
    var account = new Entity("account") { Id = id };
    account["lat_yelprating"] = rating;

    CrmConnection connection = CrmConnection.Parse(_connection);
    using (_orgService = new OrganizationService(connection))
    {
        _orgService.Update(account);
    }
}

First thing we are doing is performing a query of CRM Account records to find all the records that have the new Yelp Id field populated as those are going to be the ones we are going to try and pull the ratings for. Keep in mind that Yelp provides free API access up to 10,000 calls per day so if you think you might go over that you'll need to work something out with them. Once we have the list of potential Accounts it is just a matter of requesting the data from Yelp and making the update to the CRM record.

The Real Magic - Azure Scheduler


Once you've got this running locally deploy it to a free Azure website. When that is complete, take note of the URL to invoke the process. In this case it might look something like: http://crmaccountenrichmentexample.azurewebsites.net/api/AccountEnrichment

In the Azure Portal - find Scheduler. If you haven't done so you'll need to create a new Job Collection.

2

And then as part of the same process we'll create the job to pull the Yelp data. In this case we are making a GET request to the Web API application - this is the URL we noted earlier.

3

At this point the only left is to define a schedule. Since this data isn't likely to change very often, 1 time per day is plenty. Don't forget to keep in mind API usage when determining the schedule. If you don't have a set date you want this job to end, just enter a value here, once the job is created you will have the option to go back into the configuration and specify that it doesn't have an end date.

4

And that's it. The job should now be running on the schedule you've set up. The Scheduler dashboard will give you a high level overview of what's going on with all your jobs, or if you want you can select a specific job and check out the execution history.

You can down load the source for this project here:

https://github.com/jlattimer/CRMWebAPIYelpExample