Description And Purpose
The purpose of this document is to provide a working manual for Vusion’s clients using the Vusion Extract Agent. Under contract with Vusion, ASR Solutions created the Extract Agent for Vusion’s TMWSuite Database clients. The Agent allows IFTA Processing to a near real-time process. ASR Solutions works to provide installation and support services for the Agent for TMWSuite data extraction since its original development.
Vusion Automated Data Extraction Agent
The Vusion TMW Suite Data Extraction Agent is a small utility that was written and maintained by ASR Solutions LLC, for Vusion. The utility is a .NET 4.0 windows service and supporting Microsoft SQL Server Database, it uses dynamic stored procedures to extract information from the TMW Suite database, store it, and then format it for transmission to Vusion via FTP. No data from TMWSuite is altered in anyway, it only extracts and transmits the information.
Extraction Parameters are dictated by Vusion and settings may be for Dispatch, Fuel, Tractor Master or GPS; pulled daily or monthly based on their instructions. 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 Vusion software. The configuration of these parameters (or settings) for the Agent are determined based on what criteria Vusion 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. Vusion 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. Vusion 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. Vusion 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
Installation Of The Agent
There is a zip folder that contains the most recent version of the Vusion Agent. The Agent is Microsoft SQL based and uses FTP, installs need access to SQL and an outbound FTP connection. The Agent is not required to be installed on the same server as TMWSuite. The Agent is then pointed to the proper server that houses the TMWSuite database. For new installs, extraction settings are entered for the data Vusion has requested be pulled. For instances where the Agent already exists, ASR can verify that the data values are correct. Once the Agent is properly configured, then an extraction can be performed to verify it is functioning properly.
How The Vusion Agent Functions
The Vusion Agent extracts data from the TMWSuite database. The Agent stages the data into a SQL Database. The Agent pulls the data and writes it to an XML file in a temp directory. The Agent then compresses the XML file and opens an FTP connection. The Agent uploads the file to the FTP site as a randomly named TMP file. The Agent checks the upload against the local file and renames the file to the proper ZIP file name. Then that file is marked as uploaded and doesn't transmit again. Vusion will need to check the data they are receiving on their end.
A PDF of this article is also available for download: