FI
SV
ENBest practices are usually created through trial and error. Digia's integration architect Viktor Candolin shares his learnings from working with Microsoft Dynamics CE and its OData API.
In brief
This is a loose collection of fairly in-depth technical details I have come across while creating integrations against Dynamics 365 CE, which I think would have been nice to know when I first started out. While there are lots of hits related to these kinds of topics while searching the web, this should be a good starting point from a system integrators perspective. Knowing these details beforehand may affect your architecture or technical decisions when working in an integration role against the Customer Engagement flavour of Dynamics.
What flavour of Dynamics?
This is the first thing you need to get straight, and it is non-trivial. What flavour of Dynamics are you working with? They are completely different products technically, with different API’s and approaches. This article only refers to D365 CE, with its OData API. The idea of them being the same product family is only true for the marketing materials. They also have some capability overlap which is not handled here.
- Dynamics 365 for Customer Engagement
- Commonly known as Dynamics CRM but that undersells it, as it is more than a Customer Relationship Management solution. Which is why most CE consultants are quick to correct you if you talk about it as the CRM.
- Dynamics 365 Unified Operations (UO)
- The product is D365 for Finance and Operations (D365FO in short), previously Dynamics AX
- Another product is D365 Human Resources, previously Dynamics Talent.
- Supports Common Data Service (CDS in short), which is the same OData API used by D365 CE, but it requires effort per entity and is not available by default.
- When working against this as an integrator, assume black box operation and work against the interface specs given to you by the FO consultants and architects. Its internals are not on display as with D365 CE.
- Dynamics 365 Business Central
- Previously Dynamics NAV.
- I have not yet had the opportunity to work against this Dynamics flavour and I am ill equipped to comment on it.
OData resources
A central point of this blog post is that you can figure out how D365 CE works and what its internal database structure is like purely by using API calls. It is also (mostly) consistent in that all entities work the same way and are visible through the OData API. It is even easier if you have rights to the actual D365 CE user interface.
A good quick reference sheet for the OData protocol can be found here.
The normal CRUD operations you would expect are available, but in addition the OData API standard and D365 CE’s implementation of it allow you to:
- Ask for linked entities related to the primary entity you are asking for.
- Get extended information of the entity properties you are fetching, including localized value strings.
- Perform compound inserts, where you insert both a primary entity and its hierarchical sub entities in one call.
- Perform batch operations in a transactional way.
Fetch data type display values
The internal data structure in D365 CE is somewhat more complex compared to most programming languages built in datatypes. Notable types are:
- Optionsets correspond to programming languages enum data types. But these are in practice a separate table in the database with a primary key. So, any entity instance that has a property of optionset type will have only the key as a value for that property. The cleartext value is in a different table and is subject to locale settings.
- Currency. If you have not yet heard it, now is a good time to say it: never ever represent currency values with floating point datatypes. The reason why is an entire blog post in itself, but this is a good start >>
- References, basically foreign keys to other tables (entities) in the database.
- And a number of other types that are not as fundamental to our discussion here.
When you query data from the CE, by default you only get the raw database ID’s for fields that are optionsets or links to other entities. In many cases what you want is not the internal key of the field value, but the display value of the field. This can be achieved by adding this parameter as a header to the request:
Prefer = odata.include-annotations="*"
Before using this header value, you will get these kinds of property value replies when querying for instance a sales order:
"_ownerid_value": "9fc426cd-538b-e916-a81a-010d3f3a6da8",
After adding the header, it will look like this:
"_ownerid_value@OData.Community.Display.V1.FormattedValue": "Dummy user",
"_ownerid_value@Microsoft.Dynamics.CRM.associatednavigationproperty": "ownerid",
"_ownerid_value@Microsoft.Dynamics.CRM.lookuplogicalname": "systemuser",
"_ownerid_value": "9fc426cd-538b-e916-a81a-010d3f3a6da8",
Get Optionset values
Even with the above header in place, you will only get the current value of any optionset property. To query all possible values for an optionset property you need to do a separate query per property, which looks like this:
https://your_crm_organisation_url/api/data/v9.1/EntityDefinitions(LogicalName='your_entity')/Attributes(LogicalName='your_optionset')/Microsoft.Dynamics.CRM.PicklistAttributeMetadata?$select=LogicalName&$expand=OptionSet($select=Options)
If your optionset is globally defined, not a custom property of a single entity, last part of the query (OptionSet) needs to look like this:
GlobalOptionSet($select=Options)
For the most part, the D365 CE OData API is pleasant to work with, but in this case not so much. The red fields are values you would need to replace with our own values. The response for most optionsets is too large to display here, and keep in mind that responses may contain localized cleartext versions, not just English values. The need to transfer all optionset values in an integration should be a red flag with regards to work estimates.
A sample response and more in-depth information can be found here.
FetchXML
FetchXML is a more capable querying language for the CRM. It does not have the same limitations as the OData querying options. The relevant differences are:
- Using the OData expand query parameter you can link entities one step away from the primary targeted entity.
- Using FetchXML you are in practice making an SQL JOIN statement in XML, so you can link much deeper hierarchies. The limit per query for FetchXML is 10 link statements. So, you can expand 10 linked entities at the first level, or you can dive into a hierarchy 10 levels deep.
- FetchXML supports aliasing, you can rename the fields in the query. It is essentially a “SQL SELECT n AS m syntax”. This can be convenient for further processing with XSLT or other integration tools.
- FetchXML flattens the hierarchy, you will not get responses with nested JSON that corresponds to the CRM entity hierarchy. This is more bother if you need to transfer the hierarchy, but convenient if you need to do flat mapping in an integration. You can easily use aliasing to name the response fields in such a way that the hierarchy is evident from the field names.
- FetchXML is for queries, not updating or creating records.
Compound (Batch) Query
This is basically batching multiple queries in one request and asking the system to process them all. There are at least three reasons why you might want to use this feature:
- You have many independent queries that you can submit simultaneously, instead of sequentially.
- You want to perform a query where the total URL length exceeds 2048 characters. This limit is easily reached when performing FetchXML queries which are URL-escaped and given as a query parameter.
- You want atomic / transactional operation. All batch operations in one call are one transaction, if one operation fails the transaction is not committed.
The batching is done at the HTTP protocol level, meaning you put multiple HTTP messages in the body of one HTTP message, with a given separator string between each message. Further details here>>
See also the “FetchXML over Batch Query” topic later in this article.
The secret tools of CE
XrmToolBox
This is a collection of plugins that makes life easier for you when integrating against D365 CE or CDS, in particular this component:
FetchXML Builder
This allows you to build FetchXML queries using a point and click interface, against the actual D365 CE instance you are working with, so custom entities are listed and can be easily used.
CRM SDK tool: Plugin Registration Tool
This tool allows you to set up connections to Azure Webhooks or Service Bus. It is also important because it shows you the GUID ID of these hooks, which can be used to query failed webhook calls. I will get back to this later.
LevelUp Chrome browser plugin
If you have access to a CE instance as a log-in user, this plugin is worth its weight in gold. Depending on case and customer you may only be given permissions that have API access rights but not UI user rights, in which case this will not be helpful.
- It can give you the database naming for all fields in a view in addition to the name that the users see.
- It can open any entity record you are currently viewing as a web-api URL, meaning as you would request that same info through the OData API. I recommend installing a JSON prettifier plugin in Chrome if you do not have one already, this makes the output of this function easily readable.
- It can display all fields for the entity you are currently viewing, so not only those fields that the D365 CE consultant has decided should be shown in any given form.
- It can display all optionset values related to any optionset field in the entity you are currently viewing.
Below is a screengrab of the plugin’s UI:
WebHook vs ServiceBus
My experience has been that ServiceBus is to be preferred over webhooks. The primary reason is failure management. When anything is wrong on the Azure platform side that would render the Logic App or possible proxy unavailable, you will not get any kind of notification that this has occurred. The CRM will record failed asynchronous calls internally, but typically monitoring these is not something that is considered when building an integration or its monitoring architecture. This means that there are forms of downtime for the Azure platform that leaves no trace in typical integration monitoring that they have happened. Events from the CRM can be easily missed using WebHooks.
To fully cover your bases using webhooks you would need to:
- Periodically poll the CRM for failed asynchronous calls
- Monitor the Azure App Service that runs the proxy you will need to bind D365 CE webhooks to Logic Apps HTTP triggers. If the Logic App is not responding, or giving a throttling response (code 429), you will need to handle that or emit an alert.
- Heartbeat monitoring of the App Service that runs the proxy. There are forms of downtime and platform service degradation that mean that no CRM WebHook messages reach your appservice, even if its logs show no apparent problems.
A service bus is more robust than a webhook, and has proper handover mechanisms that ensures that an incoming message will only disappear from the queue when it is truly being processed by a Logic app. There should be no “black holes” in the chain when using a Service Bus with D365. It has a built-in dead-letter functionality. Of course, you need to use these features and set them up correctly for it to be any better than webhooks.
Asyncronous calls, failure log
With regards to the previous topic, this topic shows you how to query the set of failed async calls for a webhook. To do this you will need to find out the ID of the event binding in D365 CE that fires the asynchronous call. This binding is typically in Digia done by the D365 CE team and controlling event bindings should not be done by the integration developer. The reasons for this are many, but primarily related to who is responsible for what, and documentation. The same tool that can be used to investigate the binding can be used to change them, but in most cases you as the integration developer should not do so. You can also cause never ending loops by binding ill-considered event triggers to integrations that write something back to the entity they trigger from. The tool is available from the D365 CE SDK, the Plugin Registration tool.
Once you have the tool installed, and connected to the relevant D365 CE instance, you can find the relevant service bus or webhook registration in the list. It should look something like this:
In this case this shows a Service Bus binding where it is fired when update and create events occur related to the msdynactual entity. The exact conditions that trigger it are seen by double clicking the event binding. What we are after is its StepId under properties:
Now that we know the StepId for this asynchronous call, we can query the D365 CE API for failed calls related to this step. Below is a sample FetchXML query that gets the latest 500 possible failed runs related to a given StepId.
<fetch top="500"> <entity name="asyncoperation"> <attribute name="errorcode" /> <attribute name="completedon" /> <attribute name="statecode" /> <attribute name="statuscode" /> <filter type="and"> <condition attribute="owningextensionid" operator="eq" value="7b0dfe42-1111-1111-1111-11113ad7d1ba" /> <condition attribute="statuscode" operator="eq" value="31" /> </filter> <order attribute="completedon" /> <link-entity name="quote" from="quoteid" to="regardingobjectid" link-type="outer"> <attribute name="dg_quotenumber" /> </link-entity> </entity></fetch>
In the code above we also link the regardingobjectid to any possible quote entity and pick its quotenumber should the property be named dg_quotenumber. This code is not usable in your case as is but shows the concept of getting the actual business object related to a failed event, which is referred to by regardingobjectid.
Architecture tip
The practice of having a property in relevant D365 CE entities that contains a timestamp of when the entity has last been processed by a specific integration is a good one. I suggest using that architecture for all D365 CE integrations, and work with the D365 CE team and customer to consider if the field should be visible to users. It can bring transparency to the otherwise hidden system integrations activities. But care needs to be taken with the event triggers since if your event triggers for any change to an entity, your own timestamp update will trigger the integration again, creating a never-ending loop. This is best avoided by explicitly setting each and every field in the entity which should trigger the integration event, and not just blanket trigger on any property change.
FetchXML over Batch Query
If you get into using FetchXML in your integrations, you will sooner or later run into a somewhat unexpected problem. The maximum length of an URL when querying the D365 CE OData API. The maximum length is 2048 characters, which you will likely run into far sooner than you will run into the maximum of 10 link-entity statements per FetchXML query.
The solution to this problem is to use Batch Queries, an API feature to perform a batch of multiple queries or one big one. The relevant feature for our purposes is that the query will be put in the body of the batch query message, and so will not run into the 2048-character hard limit. It works like this:
Batch body template
You will need to template your request in some way in any case, because you are sending raw HTML which should then contain your URL-escaped FetchXML query. The way I found convenient was to create a template document file that can be loaded from disk or made a project resource. That template looks like this: (batch_body_template.html)
--batch_xmlfetchContent-Type: application/httpContent-Transfer-Encoding: binary
GET {0}/api/data/v9.1/{1}?fetchXml={2} HTTP/1.1Content-Type: application/jsonOData-Version: 4.0OData-MaxVersion: 4.0Prefer: odata.include-annotations="*"
--batch_xmlfetch--
Into this batch query body template you can then perform a string replace, or do a format string call to insert:
0. The D365 CE instance address
1. The plural entity name your query is targeting (quotes, salesorders, or some other entity)
2. Your actual URL-escaped query
The red coloured fields in the template file shows the corresponding elements that are to be replaced.
FetchXML template
You could then take this templating a bit further, and template your FetchXML queries. So that the keyword you are looking for can easily be swapped in:
<?xml version="1.0"?><fetch distinct="false" version="1.0" mapping="logical" top="1"><entity name="account"> <filter type="or"> <condition attribute="accountnumber" operator="eq" value="{0}"/> </filter> <all-attributes/></entity></fetch>
You can then store these queries as files or resources of your integration project. If this sounds sensible to you, here is a ready C# function that loads the batch query template and your FetchXML template from an embedded resource and performs the query against your given D365 CE instance using the RESTSharp library.
D365 CE User Interface
A quick note about D365 CE. If you have access to a dev or test instance, you can find out a great deal of information you will need for creating an integration, without being dependent on other people. Follow this path:
1.
2. In the new window that opens, select Settings and Customizations:
3. Then pick this, and don’t worry, you are not yet at risk of changing anything:
4. You will get yet another new window, which is what we are after:
5. When you click the Entities list open, you have all entities defined in the system. Under Fields are the fields of the property, their data types and so on. Under relationships you can find to which entities this entity is linked or links to. You can also find out the UI name of fields that may be seen to you only by their database name, which is useful for communicating with customers.
Summary
In summary I think that D365 CE with its OData API is one of the more capable integration subjects I have worked with. It is one of the more open and easily integrated products in the modern enterprise cloud application landscapes. The OData API standard is also an open standard and could be implemented in your possible future API and microservice solutions.