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.”
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.
There are obviously three critical stages to integrating data from one system into Dynamics CRM in 'Real-Time':
In addition there are critical dimensions to the data changes:
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.
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:
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.
Scenario 1: 6000 Instant Updates
Actual Timings
Action | Time | Time to Run |
---|---|---|
Source SQL Table Updated | 16:11:23 | Start |
First Update Appears in Dynamics CRM | 16:11:25 | 3 Seconds From Start |
Last Update Completes in Dynamics CRM | 16.11.42 | 9 Seconds from Start |
Scenario 2: 5 Updates every 5 Seconds
Actual Timings
Action | Time | Time to Run |
---|---|---|
Source SQL Table Updated | 16:51:18 | Start |
First Update Appears in Dynamics CRM | 16:11:21 | 3 Seconds From Start |
Last Update Completes in Dynamics CRM | 16.11.22 | 4 Seconds from Start |
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.