Mulesoft: How to Retrieve Millions of Records From Microsoft Dynamics CRM

This post will provide an overview of how the Mulesoft Microsoft CRM Dynamics connector leverages Microsoft CRM Dynamics API and the ramifications of pulling large data sets via the Mulesoft Connector.

Mulesoft Microsoft Dynamics CRM Connector Overview

The Mulesoft connector supports DataSense Queries, Native Queries (FetchXML), setting the fetch size, and returning specific pages. 

DataSense or Native Queries

For retrieving large datasets, use the Native Query Language (FetchXML), as Mulesoft will cache metadata by default if you use DataSense queries. This can run your Mulesoft JVM out of memory with even small calls.

Setting the Fetch Size

Controlling the fetch size is important and can impact your performance of requesting data from CRM. When pulling large datasets, it is best to run small performance tests to select the best size, which can range from one to 5,000.

Returning Specific Pages

The Mulesoft Connector also allows you to requests a certain page from CRM. However, our local testing has shown if the page request is over 10,000 records CRM will return an error stating the paging cookie is missing. This may be a bug, and a case has been submitted to Mulesoft for research.

Microsoft Dynamics Details

Microsoft Dynamics can take minutes to return pages of over 1,000 records.

How to Query Microsoft CRM Dynamics for Large Datasets

Option 1: Mulesoft Batch

Pros

• All records are stored into the Mulesoft object store queue, so processing can start again if the engine fails.

Cons

• All records must be pulled down from CRM and loaded into the Mulesoft object store queue. This requires parsing the XML and creating records to be stored, which will flush to disk for large datasets.

Option 2: Asynch Mulesoft Batch

This option has no difference from Option 1 in how Mulesoft processes the record set from CRM.

Option 3: For Each

Pros

• Records will begin to process when a page is returned. This will speed up processing over using the batch since you are skipping the step of writing it to the Mulesoft object store.

Cons

• Not able to begin processing where left off if the engine fails during processing.
• The Mulesoft connector will not make the request for the next page until all the records have been processed from the page.

Option 4: Asynch for Each

This option has the same behavior as Option 3

Option 5: Parallel For Each or Mulesoft Batches

This option will require the fetchXML to have a distinct clause that will not have overlapping records.

Pros

• Running multiple requests to CRM this way will limit the time spent waiting for pages to finish processing. 
• This approach can be done with batch or for each, depending on requirements.

Cons

• Must understand your data in order to not have overlaps .

Mulesoft Champions Program Review

Mulesoft Champions Program Review

What is the Mulesoft Champions program?

Mulesoft defines the program as:

The Champions Program is all about you. Grow your skills, get recognized, get rewarded, and network with the top MuleSoft developers and advocates around the world. - https://developer.mulesoft.com/champions 

The program allows you to create an account and participate discussions and challenges.  The challenges range from validating that you read an article/watched a video, participating in a Linkedin/Facebook/Forum discussion or submitting code for challenges.

How well does the Champions Program meet its stated goals?

Grow your skills

The coding challenges provide some basic uses cases that are good when you are starting out, however when you working on specific use cases they may not help with your skill development.

The weekly articles validations are where some of the real gems are which are blogs or articles written by real MuleSoft practitioners and will gain new insights

Recognition

I submitted a blog post which was tweeted, but they are not leveraging the Linkedin MuleSoft group which would provide better exposure to the community

Rewards

There is a wide range of awards and I think most people will find something they like.  The points required doesn't seem out of line for the rewards either.

Networking

The program has had little impact on my networking

Summary  

I think the program is an interesting way to get a community to work more closely together and I think they are only scratching the surface of how it can be used to really improve the MuleSoft and adaption

Mulesoft Microsoft CRM Dynamics Connector TIps

This blog post will review tips using the Mulesoft Microsoft Dynamics Connector to integrate to legacy systems that my team has run across.

Java Cryptography Extensions Not Installed

When doing a test connection for the MS Dynamics Connector inside of Anypoint Studio you may see the following exception - "Test connection failed: Java Cryptography Extensions not installed..."

The user guide states the following as the fix:

    "Install the Java Cryptography Extensions (JCE) package before installing the connector. Download JCE for Java 7 from Oracle’s Java 7 JCE Download site. Download JCE for Java 8 from Oracle’s Java 8 JCE Download site."

This may not resolve the exception above and the following must be done to clear up the connection error:

• Update the AnypointStudio.ini  — ${Anypoint_Studio}/AnypointStudio.ini  
  
  
  • Add the following lines after the line "plugins/org.eclipse.equinox…"
    
    
    • -vm.
    • ${JDK_HOME}/bin/javaw.exe — This needs to be the java install where the JCE files have been updated.

Example AnypointStudio.ini:

Mavenize Your Mulesoft MS CRM Dynamics Project 

When a Mulesoft MS CRM Dynamics Project is Mavenized, you will need to have access to the Mulesoft EE Repo to pull down the MS CRM Dynamic dependencies.  You will need to work with your Mulesoft Success Manager to request access to the Mulesoft Nexus EE Repo, and Mulesoft will request that you have your own Nexus set up to store dependencies so you are not pulling from their Nexus for local builds.

Retrieve Multiple Records by Query

The team has observed excessive memory usage when retrieving multiple records via DataSense. In one instance, a query for 20,000 records with four fields returned ran a 10 GB JVM out of memory.  The workaround has been to use native FetchXML in lieu of DataSense.  

It looks like this maybe a feature of the connector as stated in the Mulesoft meta-data caching documentation:

"The connector does cache metadata by default."

"Data sense: As data sense requires all entities to be retrieved, the entities retriever does that and caches it by entity to be reused between different calls. In addition, data sense also exposes attributes for each entity, and this is done by an attributes retriever. For data sense the attributes retriever that is used gets all entitiy’s attributes and caches it (for each one of the entities for which you perform a data sense operation)."

MS Dynamics Execute Multiple Limitations 

Per the MS Dynamics Documentation:

• No recursion is allowed — ExecuteMultipleRequest cannot invoke ExecuteMultipleRequest. An ExecuteMultipleRequest found in the request collection will generate a fault for that request item.

• Maximum batch size – there is a limit to how many requests can be added to a request collection. If that limit is exceeded, a fault is thrown before the first request is ever executed. A limit of 1000 requests is typical though this maximum amount can be set for the Microsoft Dynamics CRM deployment. The deployment setting for this limit is BatchSize.

• Throttling of concurrent calls – for Microsoft Dynamics CRM Online there is a limit of 2 concurrent ExecuteMultipleRequest executions per organization. If that limit is exceeded, a “Server Busy” fault is thrown before the first request is ever executed. For an on-premises deployment, throttling is not enabled by default. The deployment setting for this limit is ExecuteAsyncPerOrgMaxConnectionsPerServer.

Tools to test fetchXML

Dynamics CRM DevTools for Chrome