OVERVIEW
Andrews Sykes Group (ASG) is engaged in hiring, sale and installation of a range of equipment, including pumping, portable heating, air conditioning, drying and ventilation equipment.
ASG had previously implemented CRM 2011 through a Microsoft Partner, with a significant component of the project being the extract, transform and load (ETL) solution. Used by their field sales force, CRM is their source of information when they’re on the road as they do not have access to the central business Hire system.
For this integration project, ASG partnered with Cranhill Insight, a freelance consultancy services firm specializing in Microsoft Dynamics CRM 2013, 2011 and 4.0 alongside SharePoint, Reporting Services and Integration Services.
Target:
Provide the sales team with mobile access to customer information, quotes, and contracts
Challenge:
Solution must be reliable and scalable
Required SSIS Integration Toolkit:
As my first personal interaction with KingswaySoft, although I had used the product several times before, I am left with a very good impression of the level of skill and expertise.
I would say that the product is very competent and I feel it deserves the reputation and recognition of some of the more expensive alternatives.
~Rob Dawson
Dynamics CRM Consultant for Andrew
Sykes Group, Director of Cranhill Insight
THE CHALLENGE
The field sales team required access to customer and prospect company records, quotes for equipment hire as well as hire contracts, and required all this information when mobile. Therefore, data ETL along with the CRM mobility solution met this requirement. Before ASG started their project, they had to consider the significant variation in data volumes which posed a challenge. Their legacy ETL solution had some reliability issues but more notably,it did not scale. While under regular daily processing, the capacity was sufficient. Under batch refresh scenarios, it was not unusual for processing to take up to 15 hours to complete. Data volumes were less than 750,000 records in batch refresh cases.
ASG’s legacy ETL solution was single threaded and the version they were using could not make use of the ExecuteMultiple class introduced with CRM 2011 UR12. This, added with the reliability issues, forced them to redevelop the ETL solution using another platform.
THE SOLUTION
SSIS Integration Toolkit for:
Several options were considered but none of them seemed to be a good fit due to licensing costs or proprietary syntax.
Owing to the number of other SQL Server Integration Services platform ETL services in use at ASG, KingswaySoft seemed to be a good fit. It was scalable, multi-threaded by taking advantage of the SSIS Balanced Data Distributor, and made use the ExecuteMultiple CRM class. It also had a familiarity for their internal IT team as it is SSIS based.
THE RESULTS
There was a marked improvement in throughput when compared to the legacy ETL process. Since deployment to their production environment, the run-time of the regular daily processing (which was scheduled to begin every 15 minutes) had reduced from 15-20 minutes to just 90 seconds using SSIS and KingswaySoft. They also found that pre-sale consultancy offered by KingswaySoft’s team was very helpful and at an expert level.