Description And Purpose
The purpose of this document is to provide a working manual for Comdata’s clients using the Inter-Tax Automated Data Extraction Agent.
ASR Solutions developed the Agent under contract with Inter-Tax for clients running the TWMSuite product and associated database. This includes traditional TMWSuite modules such as Visual Dispatch, and updated modules such as Fuel Dispatch and TMW Operations.
Inter-Tax was seeking to move their IFTA Processing to a near real-time process. Previously to get Fuel/Dispatch/GPS/Tractor data, they waited for a manual upload of information from the TMS provided by the client and faced significant lag in obtaining and processing the data. Their vision was to make an automated extraction that would push data to them, so it could be reviewed near-real-time and provide much more active management. They were the first IFTA processing service (to our knowledge) to pursue this strategy.
ASR Solutions works to provide installation and support services for the Inter-Tax Agent for TMWSuite data extraction since its original development.
Inter-Tax is (now) a division of Comdata, after their acquisition several years ago. They provide reporting, analytics, and IFTA processing for several hundred Carriers, a subset of which we provide incident support for (the others are on different TMS platforms besides TMWSuite, including Innovative, McLeod, etc, and supported by other Agents from other contracts.)
Inter-Tax Automated Data Extraction Agent
The Inter-tax Agent retrieves, validates, and transmits information from the clients’ TMWSuite database on a scheduled basis and automatically transfers this information to ComData for processing. It is an Automated Data Extraction Agent that is installed when files are placed onto a client system. No data from TMWSuite is altered in anyway, it only extracts and transmits the information to ComData via SFTP.
The agent runs as a windows service, essentially a scheduling and processing agent. It stores its connection string using Symmetric Key AES Encryption (256 Bit) via the Cryptography namespace in .NET 4.6, which is still quite strong even by today’s standards. The actual extraction of data is performed by a secondary database that can reside on the primary SQL Server, or a secondary SQL Server (assuming you allow a Linked Server connection), and the loading routines take place entirely within SQL Procedures invoked against that secondary database.
The user account initiating the connection to the secondary database is typically set up with DENY DATA WRITER SQL permissions, prohibiting any possibility of data manipulation in the TMWSuite database even if the credentials were somehow compromised within the agent. The secondary database caches only the minimal data used for extraction. When the extracted data is written, compressed, and uploaded, it has very little identifying information for your transactions or master data. Specifically, Customer Names, Driver Names, Shipper/Consignee names are never transmitted. Only locations, driver codes, and tractor numbers are sent, heavily limiting the actual information that could be compromised.
Software Parameters
Static parameters (often also referred to as the extraction parameters) were created to specify the behavior of the Inter-Tax software. The configuration of these parameters (or settings) for the Agent are determined based on what criteria ComData requests; including Dispatch, GPS, Fuel or Tractor Master files, and if the files will be pulled as daily or monthly transmissions.
The installation allows the agent to run as a service, and it is installed to an associated database to one of the clients’ existing servers, then configured with the extraction parameter requests. Each extract process operates within a specified window of time, an ExtractWindow, which is calculated at run time. ComData then uses the information transmitted to them to gain more details on the client and provide enhanced assistance.
Dispatch Extraction
The Dispatch data describes tractor travel in hauling loads between origins and destinations. ComData uses this information to analyze tractor travel, assemble route lines, and calculate miles on a load-by-load basis.
The software shall examine Dispatch data with CompletionDateTimes within the DispatchExtractWindow and extract completed dispatches. The software shall not include any cancelled or voided dispatches. It will also not include any LTL loads that duplicate other travel. If necessary, the software shall combine any LTL loads into one trip that represents the combined travel of the LTL loads together.
Below is a list of the Dispatch field mappings for client consumption.
GPS Extraction
The GPS data also describes tractor travel in hauling loads between origins and destinations. ComData also uses this GPS information to analyze tractor travel, assemble route lines, and calculate miles on a load-by-load basis.
The software shall examine GPS data with DateTimes within the GPSExtractWindow. The software shall use and report the DateTime of the tractor location reading, not any other time such as the time the record was received at the GPS vendor data center.
Below is a list of the GPS field mappings for client consumption.
Fuel Extraction
The Fuel data describes the dates, quantities and locations of tractor and reefer fuel purchases.
The software shall extract Fuel purchases within the FuelExtractWindow and the FuelExtractLaggingWindow. It is expected that fuel purchases will be extracted more than once in successive extractions. The software shall extract, and often re-extract, fuel data in the past. For this purpose, the software shall construct a FuelExtractLaggingWindow based on the FuelExtractWindow and the FuelExtractLaggingWidth configuration parameter.
Below is a list of the Fuel field mappings for client consumption.
Tractor Master Extraction
The Tractor Master data describes the characteristics of the tractor, e.g, fleet affiliation, home terminal, IFTA state, etc.
The software shall examine Tractor Master data as of the date the extract is run. It will extract tractors in service as of extract activation. A reasonable StartServiceDate shall accompany each Tractor Master record. The software shall extract Tractor Master records for tractors that were terminated within TractorMasterExtractChangedWindow. The EndServiceDate for these records shall contain the termination date.
Below is a list of the Tractor field mappings for client consumption.
File Transmission
The Agent was designed for minimal memory and processor usage, working primarily during the single daily extraction routine. Thus, it can often be placed on an existing server or workstation with little impact.
The database is preferred to be installed on the same SQL Server as the current TMWSuite database, as it will pull the data from TMW and store the transmitted data for a set period after extraction. However, if required, it can be stored on an alternate SQL Server, as long as the servers are linked (it will use the four-part linked server naming convention in the extraction routines).
The only additional requirements are that the machine where the Agent is installed has permission to make outbound connections via TCP port 50022 (SFTP) for the transmission of the data to ComData, and the .NET Framework 4.6 be installed as a prerequisite (Since the agent was written in .NET 4.6).
A PDF of this article is also available for download: