Case Study: Berry & Smith

Preamble

This whitepaper briefly describes a recent project that used !Tsuite to resolve two client challenges encountered in the trucking industry, one related to integrating data from onboard systems and the other related to submitting payment invoicing to an automated third-party system.

Client Profile

Berry & Smith (www.berryandsmith.com) is a western Canadian transportation company with no internal IT staff, who use TruckMate® as their main information system with a reliance on PeopleNet for their onboard computer systems to integrate fleet information to their operations.

Client Challenge

The first client challenge was specifically to connect two unlike systems and exchange data automatically between them to reduce human labour in terms of data entry, reading data from the onboard system to inject into DB2.

The second client challenge was specifically to allow their internal MS SQL Server database (TMW TMT®) to export billing information in a third-party defined format (XML fragments), for import into the third-party payment system.

Scope of Solutions

The primary solution was to access the data from an onboard system macro to update customer service notes, container location and driver location on a 10 minute cycle. As well, the intent was to read the TruckMate® tables necessary to validate data and update driver pay records every 4 hours. In the case of either job having errors there was a logging requirement to allow for adjustment of affected data and resubmission of only those error-bearing data packets.

Berry and Smith Solution Flow

The decision was made to deploy !Tsuite, using custom jobs to isolate the necessary onboard data and send it to a stored procedures containing relevant business logic.

The challenges in the scope of development of the primary solution were:

  1. Overcoming the complexity of parsing incoming data to a database friendly form;
  2. Ascertaining that the logical impact of data insertion would not negatively impact other system functions; and
  3. The need to test thoroughly to ensure that incoming data was reliable before the client risked introduction to production.

The secondary solution was to access existing invoicing data from numerous TMW TMT® tables, and to produce a third-party friendly XML file that would allow for automatic import of work order details to allow for effective payment processing.

Berry and Smith Solution Flow

An !Tsuite custom Output job was created to call a stored procedure on a periodic basis to produce an exported XML file of the necessary structure, as validated by the third-party documentation.

The challenges in the scope of development of the primary solution were:

  1. Ascertaining where in TMW TMT® the data required by the export process would be found, and how to transform data elements that were not as-expected for the external third-party system;
  2. Working with the third-party documentation and systems to ensure the resultant output of XML was compliant with their schema requirements; and
  3. The need to test thoroughly to ensure that incoming data was reliable before the client risked introduction to production.

The Solutions

!Tsuite was deployed for the primary solution, with a scheduled Loader job executing the necessary data gathering and transformations before executing a stored procedure that isolated the necessary technical and business logic to inject the data into the appropriate tables.

The same technology was deployed for the secondary solution, with an Output job forming XML to the standard of the third-party system for subsequent import.

The flexibility of !Tsuite was illustrated by this solution-set, because the same core product was used to resolve both a distributed data aggregation challenge and a payment system challenge.

Products and Features Used

The product features used for the solutions were:

  • !Tsuite, using the following components:

o    Editor, to create jobs to fulfil the necessary tasks (including Output and Loader jobs);

o    Scheduler, to execute jobs as needed; and

o    Watcher, to report on the success of the jobs.

Resulting Client Benefits

Resulting from the solutions, Berry & Smith was able to benefit in several ways:

  • The automated processing of 200+driver macros per day eliminated 6 to 8 hours of clerical work and ensured system updates regardless of office hours;
  • Repeatable automation ensured the integrity of data without incidence of human error, reducing bad data conditions (assuming the incoming data is not faulty in some way, of course);
  • Driver pay times were improved by loading the data directly, and again reducing human clerical processes;
  • Customer service was improved by better operational data flow, allowing management to focus on data value rather than expending effort on processing data for analysis;
  • The automatic generation of XML to export work orders for payment processing reduced clerical effort and improved pay time latency; and
  • Ultimately the two solutions collectively improved operational efficiency by offloading mechanical processes from human beings, providing more opportunity to effectively leverage Human Resources.

Conclusions

By using !Tsuite, the Berry & Smith solutions allowed the company to engage automated integration across external and internal systems, reducing human effort to gain more immediate access to better data, and streamlining the submission of payable data to a third-party system. This efficiency of process creates opportunities and improves the bottom line.