Data Pipeline Documentation for Interstar

Date Pipelines

The pipelines created run through the BDW custom data ETL tool. The tool is installed in the ApplicationDeployment folder on the primary server. Configuration and logs can be found within the tools installation directory. The configuration file holds settings related to each data source, destination, and individual pipeline jobs.

Production Reports

This pipeline moves/transforms records from the individual production reports to the on-premise SQL Express instance.

Unbilled / Aging

This pipeline moves/transforms records from Landstar’s Unbilled/Aging reports to the on-premise SQL Express instance.

The “hot report” is an additional pipeline that executes nightly along with the Unbilled/Aging pipelines. If new records for Aging or Unbilled have been loaded on the same day, then new hot reports are generated and emailed through Send Grid.

Payroll Reconciliation

This pipeline differs significantly from the other pipelines for extracting data for PowerBI. This pipeline is for automating the payroll reconciliation project. After processing the weekly payroll report (dropped in the folder from the Access payroll program), the pipeline then updates individual production reports to “green-off” loads coming back from Landstar.

Missing Load Reconciliation

This pipeline is initiated manually through a scheduled task, which triggers a batch file to execute a Linqpad script. The script retrieves the latest reconcile exception report and generates a secondary report for reconciliation. The standard payroll reconciliation process is then applied to this secondary file. This procedure is exclusively designed to re-reconcile loads that were overlooked in the production reports, eliminating the need for Landstar staff to manually reconcile loads in instances where an agent books a load through Landstar but neglects to update their Production Report. Currently, there is no automatic logging for this manual process.

Hot Report

The Hot Report is a scheduled task that runs nightly. It looks at the data in the Aging / Unbilled tables to determine if new data is available. If found, then the console application will put together a spreadsheet for each agent that indicates their current Aging and Unbilled records. Those are attached and emailed to the agents. Updating the Aging / Unbilled data is done through dropping the latest files on the file share as described above.

Loads Needing Handoff

This pipeline has a unique trigger. There is a Windows Service registered (“Dump Truck Trigger”) that monitors the LoadsNeedingHandoff folder. When a new TMS report is dropped in there (typically emailed from Landstar directly) the service will trigger the console application. Much like the Hot Report, the console app will load the results from the TMS report and will then email each agent directly with the loads they need to hand off.

Power BI Gateway

The MS on-premise data gateway is installed on the same server as the SQL Express instance. This gateway is connected to the Interstar Power BI workspace. All reports/dashboards are set to refresh daily.