Customer Solution Case Study
/ IT Services Firm Collects, Cleans, and Quickly Distributes Data to Support Better Decisions
“Through SQL Server 2008 Integration Services and our custom development, we can transform a large volume of data that we receive at half-hour intervals every day.”
James Salim, Senior Consultant for Solution Operations, Innovapost
Innovapost needed to collect data every half hour from multiple Canada Post mail-sorting machines. To do so, Innovapost built an application based on Microsoft SQL Server 2008 Integration Services. The application turns raw files from multiple sources into clean and dependable data, quickly and reliably. Reports on the data are often available to sorting facilities just 35 minutes later, helping Canada Post make business decisions.
This case study is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.Document published March 2012
Business Needs
Innovapost is the IT service provider for the Canada Post group of companies, including Canada Post and Purolator. Founded in 2002, Innovapost is headquartered in Ottawa and has about 700 employees.
Canada Post is in the midst of updating its infrastructure. In addition to efficiency, the new mail-processing equipment produces richer, more precise, and more accurate data. Canada Post, which serves 15 million addresses each day is using the data to optimize processing and restructure routes, thus improving both the quality and cost-effectiveness of its services.
But in order to do so, Canada Post needs data to be reliable as well as quicklyavailable. This is why the company asked Innovapost to develop a process to extract, transform, and load that data.
“We had to design an application to load data every half hour, 24 hours a day and seven days a week,” says Jacqueline Kunkel, Director for Solution Operations at Innovapost. “The timeliness of the data was critical to Canada Post, and our solution needed to catch up if there was an outage of 8 or 10 hours that interrupted the flow of incoming data.”
Stability was vital. Kunkel says, “Our solution could not fail. It had to run on time. The data had to be recoverable and accurate, and it had to quickly get to the people who need to make decisions.” So Innovapost needed data management software with powerful integration capabilities that could meet these challenges.
Solution
Innovapost looked at several options. Steven Sun, Technical Consultant and Database Administrator at Innovapost, says, “We were familiar with Microsoft and impressed with its integrated package.” Sun determined that Innovapost could upgrade to Microsoft SQL Server 2008 Enterprise data management software (from the 2005 release it had previously used) and take advantage of SQL Server 2008 Integration Services.
Innovapost worked on the solution throughout 2009, with the help of Jean-René Roy, the president of Technologies SoftDesign. A Microsoft SQL Server Most Valuable Professional (MVP), Roy says, “SQL Server 2008 Integration Services was a very good tool for the high-performance needs of Innovapost.”
Over the course of three months, Sun and Roy designed the application to run in parallel. “Instead of running multiple instances of SQL Server 2008 Integration Services, we have four identical worker threads running at the same time, with the data merged at the end,” says Sun.
The application loads, validates, updates, and aggregates data from the Innovapost Enterprise Data Broker generated by more than 150 pieces of mail-sorting equipment from across the country. It uses SQL Server 2008 Integration Services to validate and transfer 10–15 gigabytes of data per day into the staging area of the Innovapost database. SQL Server 2008, running on a Dell PowerEdge R710 server computer, then uses data cubes to produce reports that are available to the sorting facilities often just 35 minutes after the data arrived.
Innovapost implemented the application in February 2010, and it has run smoothly ever since at more than 99.99 percent availability.
Benefits
Innovapost has used SQL Server 2008 Integration Services to provide dependable, clean, timely, and reliable data, which helps Canada Post make better decisions.
Clean, Dependable Data
Innovapost used SQL Server 2008 Integration Services to build an application that transforms raw files from multiple sources into clean and dependable data. “The raw data we get is not always 100 percent clean and usable,” says James Salim, Senior Consultant for Solution Operations at Innovapost. “With SQL Server 2008 Integration Services, we can validate and transform the data into a usable format.”
Furthermore, the data is so accurate that it is now frequently used for purposes beyond the original scope. “Other teams have come to us asking for data so that they can do various analyses,” Kunkel says. “There’s trust across the company that this data is dependable and accurate.”
Quick, Reliable Processing
With SQL Server 2008 Integration Services, Innovapost meets Canada Post requirements for stability, accuracy, and, most important of all, speed. “Through SQL Server 2008 Integration Services and our custom development, we can transform a large volume of data that we receive at half-hour intervals every day and have it be available in a timely manner,” Salim says.
Kunkel says, “There have been times when we haven’t received data for 10 hours, so we’ve had to double and triple the load of data. We’ve had to severely tax the application, and it has come through without any issues whatsoever.”
Effective Business Decisions
Innovapost uses SQL Server 2008 Integration Services to provide a great deal of accurate data that helps Canada Post make effective business decisions. “Because the data gets back to the facility so quickly, they can make timely business decisions about how they organize the work on the plant floor,” says Kunkel.
John Farnand, Vice President of Engineering and Postal Transformationat Canada Post, agrees, saying, “Having trustworthy, reliable data quickly available has been essential to supporting our business.”
This case study is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.Document published March 2012