Preamble

This whitepaper briefly describes a recent project that used !Tsuite to resolve a client challenge encountered in the trucking industry, related to manual entry of hundreds of daily loads from spreadsheets into a dispatch system.

Client Profile

Kim-Tam, part of Samuel Steel Manufacturing, (www.kimtamtrucking.com) is a Canadian transportation company who provide third party logistics, rolled steel, flatbed and speciality transportation services in North America.

Client Challenge

The client challenge was managing the data entry processing for hundreds of daily loads that came from spreadsheets and had to be injected into a dispatch system. The spreadsheets were updated several times a week, making revision control a problem, and the number of sheets was problematic because of a high customer count. Upon upgrading to TruckMate® 2012 they wished to automate their dispatch entry.

Scope of Solutions

The solution was to create a skeleton bill from each row in the customer spreadsheets (with each customer sheet having a potentially different format and structure), and transform this data into a common format that could be injected into TruckMate® with integrity. The ability to correct bad entries and write forensic audit logs was critical.

Kim-Tam solution diagram

!Tsuite was deployed using custom jobs to draw and transform data from various spreadsheets into skeleton bills. That data was then injected into the target system via a stored procedure encapsulating the business logic necessary to make the data adhere to TruckMate® business rules, and to generate an audit log to forensically track the injected data. The Watcher part of !Tsuite was naturally able to trap pre-injection errors and allow for row-safe manual editing and retries for only those rows.

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

Ensuring variant spreadsheet structures were normalised in a way to create valid skeleton bills;
Ensuring the stored procedure respected the necessary internal business rules of the target TruckMate® system; and
Ensuring that error conditions were tracked and repairable in a way to ensure data integrity.

The Solution

!Tsuite was deployed with Loader jobs 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. Monitor was deployed to watch for the files at specified drop-points, and automate the job execution based upon file arrival. As well, Watcher was configured to allow row-level remedies for error conditions.

Products and Features Used

The product features used for the solution were:

!Tsuite, using the following components:

o Editor, to create Loader jobs to fulfil the necessary tasks;

o Monitor, to execute jobs as source data files arrived at specified drop-points;

o Launcher, to allow manual triggering of jobs outside the normal automation framework; and

o Watcher, to report on the success of the jobs and allow row-level resubmission after manual changes to individual error-rows.

Resulting Client Benefits

Resulting from the solution, Kim-Tam was able to benefit in several ways:

The automation of data injection reduced manual labour significantly, allowing for hundreds of freight bills to be created in less than two minutes with no human intervention;
Accuracy of data was ensured by eliminating the possibility for clerical copying errors, leading to improved empty miles tracking;
Customer service was improved by the increased accuracy and reduced time to analysis, leading to improved correction billing;
The potential for missed bills was eliminated by introducing an automated checked process;
Supportive system utilization was improved by shifting the effort away from mechanical processes to more analytical ones; and
Improved data compliance resulted from the introduction of a structured set of business rules executed to prevent mismatches between the data in the external spreadsheets and the dispatch system, which was non-compliant prior to implementation and was 100% compliant after.

Conclusions

By using !Tsuite, the Kim-Tam solution allowed the company to engage automated integration of their external and dispatch systems, reducing human effort to gain more immediate access to better data. This efficiency of process creates opportunities and improves the bottom line, allowing the human resources to be expended doing more productive tasks than copying data.