In a previous post I talked about how we used Azure APIM to implement some common lookups for our Dataverse which allowed me to encapsulate common searches for reuse by many Logic Apps. I also mentioned about how we benefit from not using the API Connector to avoid its throttling limits. There is another benefit in using APIM too when we can use the caching capabilities in APIM.
In Dataverse you also have to be cautious about the throttling limits of the Dataverse API which I believe are around 6000 calls per 5 minute period. This isnt that many when you have a lot of integrations and also when the nature of the Dataverse Web API tends to lean towards an integration needing to be quite chatty with the API. With this in mind we can use caching in APIM very easily for these common lookups so that it will reduce the hits on dataverse.
Its really as simple as 2 lines of code. I add line 1 in the inbound policy to return a cached response if there is one.
<cache-lookup vary-by-developer=”true” vary-by-developer-groups=”false” allow-private-response-caching=”true” downstream-caching-type=”none” />
I add the below line in the outbound policy so that when we have done a lookup from dataverse we cache the response for the next 10 minutes or until it is evicted from the cache.
<cache-store duration=”600″ />
We have a lot of messages to process which are batches of updates on railcars and orders and and other stuff which needs loading into CRM where we need to lookup an account from the dataverse to get the entity guid from the business partner id and we can reduce a lot of the calls to the datavserse API by implementing caching like this.
The full policy example is below.
APIM Policy
<policies>
<inbound>
<base />
<!-- Cache Lookup Added Here -->
<cache-lookup vary-by-developer="true" vary-by-developer-groups="false" allow-private-response-caching="true" downstream-caching-type="none" />
<!-- Gets the order id from the policy -->
<set-variable name="accountId" value="@(context.Request.MatchedParameters["business_partner_id"])" />
<!-- Rewrite url for downstream call to CRM -->
<rewrite-uri template="/api/data/v9.2/accounts" copy-unmatched-params="false" />
<!-- Inject the filter query to find the business partner -->
<set-query-parameter name="$filter" exists-action="override">
<value>@("accountnumber eq '" + (string)context.Variables["accountId"] + "'")</value>
</set-query-parameter>
<set-header name="Content-Type" exists-action="override">
<value>application/json</value>
</set-header>
<!-- This will expand the option sets -->
<set-header name="Prefer" exists-action="override">
<value>odata.include-annotations="*"</value>
</set-header>
</inbound>
<backend>
<base />
</backend>
<outbound>
<base />
<set-variable name="responseBody" value="@(context.Response.Body.As<string>(preserveContent: true))" />
<choose>
<when condition="@(((IResponse)context.Response).StatusCode == 200)">
<!-- We can simplify the response by a bit of json formatting in this shape -->
<set-variable name="formattedResponse" value="@{
JObject crmResponse = JObject.Parse((string)context.Variables["responseBody"]);
var crmResponseItems = (JArray)crmResponse["value"];
if(crmResponseItems.Count == 0)
{
throw new Exception("There are no records matching this id");
}
if(crmResponseItems.Count > 1)
{
throw new Exception("There is more than 1 record matching this id");
}
var response = new JObject();
foreach (var item in crmResponseItems)
{
var crmAccountEntityId = item["accountid"];
var crmAccountNumber = item["accountnumber"];
var crmAccountName = item["name"];
response.Add(new JProperty("AccountEntityId", crmAccountEntityId));
response.Add(new JProperty("AccountName", crmAccountName));
response.Add(new JProperty("AccountNumber", crmAccountNumber));
}
return response;
}" />
<set-header name="Content-Type" exists-action="override">
<value>application/json</value>
</set-header>
<set-body>@{
return ((JObject)context.Variables["formattedResponse"]).ToString();
}</set-body>
<!-- Save Response to Cache Here -->
<cache-store duration="600" />
</when>
<otherwise>
<!-- Do nothing as there was probably an error from CRM which we will just return -->
</otherwise>
</choose>
</outbound>
<on-error>
<base />
</on-error>
</policies>
