Dynamics CRM Real-Time Integration

It is more and more important to businesses to have up to date data to act upon. Customer service representatives informing their clients 'If you wait a couple of minutes you should be able to log-in' or 'Your balance has not been updated yet, can I take a number or can you call back?' are not what successful businesses are made of.

Earlier on this year an Aberdeen Group report discovered that business leaders wanted more and more access to real-time data.

David Linthicum, the author wrote “The report noted that 89 percent of enterprises that use real-time integration have the power to provide managers with accurate information when it is needed, as opposed to only 73 percent of organizations that do not use real-time services”

This on face value looks like a 16% difference in capability (or competitive advantage). In reality, it is a huge difference due to the source of data no longer being from clean, managed data store but from core transactional systems.

This includes updating the real time data that is flowing to the decision maker, as the data changes over a given period of time. For instance, the ability to track factory production over an afternoon, as the production data changes minute-to-minute.

“In other words, we are moving from a report-oriented mentality to a dashboard-oriented mentality.”

Who really needs Real-Time?

Finance and banking solutions are very reliant on systems being in-sync due to the timely nature of balances, pricing and transactions. Information such as available balances, current status of accounts and the actual cost of a transaction are required seconds after their change. As mentioned above, businesses who want to be able to build new processes should think hard of the benefits of not having to wait until later or worse an overnight update to make decisions.

What is involved in integrating in Real-Time?

There are obviously three critical stages to integrating data from one system into Dynamics CRM in 'Real-Time':

1. Detection of the change in the source data.
2. Identifying if this change impacts on a Dynamics CRM destination entity.
3. Updating or Inserting data in the Dynamics CRM destination entity.

In addition there are critical dimensions to the data changes:

• The amount of entities changed in the source entity (eg. Start of Day balance updates) in a time window.
• The time it takes to process an update or inserts vs the next required update window (eg. If there are 100 changes in 30 seconds and the integration solution takes 0.8 seconds to process a change serially then they system will never be in sync).

Critically Real-Time integrations have to be 'in sync' before starting the real-time data integration process. This is possible with preparation and good data management but the most difficult moment is when the source system or the Dynamics CRM system are unavailable. There must be a process by which once reconnected, the system will bring themselves back in sync. Recovery from unavailability, is the hardest problem in real-time synchronisation.

An example of Dynamics CRM Integration

As a demonstration I am going to configure a Real-Time synchronisation between a Microsoft SQL Server Table and a Dynamics CRM entity.

For the example to be realistic we are going to test two scenarios and record the shortest and longest time from the change in the source systems to the update completing in the target system:

1. Simulate a 'Large Single Update' such as a balance update in the morning or mass change in data. Updating 3000 records in the SQL Table a Dynamics CRM entity with 6000 items.
2. Simulate a 'Regular Change' of data in the source system: 5 updates every 5 Seconds from a table with 6000 records targeting a Dynamics CRM entity with 6000 items .

Detection of the change in the source data

To detect the change in the source table I attached a DS3 Monitoring Trigger (this is not a SQL 'Trigger' attached to the table). This monitoring trigger activates a DS3 Integration project when it detects a insert or update to the source table. There are many Monitoring triggers built into DS3 Server (File Monitor, SharePoint List, Http result etc.) This should table about 1 minute to set up .

Identifying if this change impacts on a Dynamics CRM destination entity.

Create a new DS3 project mapping between the source SQL table and the target entity.

Updating or Inserting data in the Dynamics CRM destination entity.

I uploaded the DS3 project into DS3 Server. I can now connect the Trigger to the project.

Recovery

We don't have to worry about recovery in this example as DS3 detects the last successful sync and automatically synchronises from there. This technique is only possible if you have a high performance insert/update process as you have to 'catch-up' i.e. Insert/update 100x faster that required to keep the system in sync.

Results

Scenario 1: 6000 Instant Updates

Actual Timings

Scenario 2: 5 Updates every 5 Seconds

Actual Timings

Conclusion

If we are having updates every 5 seconds we would probably do away with the change detection and just schedule the updates every 5 seconds, thus reducing the load on the source. DS3 Server is also doing some clever stuff like stopping the trigger running another insert when detecting changes and making sure any updates that happened during a sync were processed.

Real-Time Dynamics CRM synchronisation is possible and manageable, I set this up in about 15 minutes and ran the tests over an hour. When we implement these systems with our clients, we also run a reconciliation process which verifies all the updates and delete have been completed. This is a simple job we schedule nightly and allows us to also manage deletes elegantly and gives the business managers a solid reconciliation position.

If you would like to discuss with us how you can increase the productivity of you Dynamics CRM solution through Real-Time integration, give us a call or email : support@simego.com.