Database Structure

Now that you understand what a trip is, and how they are formed, we will explore the database structure used to store information about trips. These analytics server databases consist of a series of fact and dimension tables.

A fact table contains information about specific trips, and what happened to the vehicles and drivers as they carried out these trips. A dimension table, on the other hand, contains general data about markers, drivers and vehicles, including processed dates and times, vehicle details (type, color, fuels used, hierarchy assignment etc.) and driver information (name, hire date, contact details, Hours of Service rule type etc.) as well as information about any markers traveled to/from during trips.

The dimension tables contain only meta data, as they describe the vehicle, driver and locations visited, but do not provide any information about what was encountered during a trip (this information is instead contained in the fact tables).

The fact tables in the analytics server are:

Trip. The trip table contains columns of information about trips as they are processed, including information about their duration, point locations (LatLon coordinates representing a geographical location) passed along during the trip (at a minimum each trip must include start and end points); how much fuel was consumed while the trip was carried out, information about any events detected (such as speeding or failure to use the driver's seat belt), vehicle diagnostic trouble codes (DTCs) logged, and the vehicle's odometer at the start and end of each trip.
Driver_Day. The driver_day table contains information about what happened to the driver in the vehicle while the trip was performed, including the distance traveled, violations recorded against them, how much fuel they were responsible for using, the number of engine hours they logged, and unproductive idling detected.
Vehicle_Day. The vehicle_day table contains information about what happened to a vehicle during a given day, such as distance traveled, violations detected, quantity of fuel used, DTC flags triggered etc.

The dimension tables within the analytics server are:

Driver. The driver table contains information about drivers, such as names, addresses, contact details (phone numbers), employee numbers, hire dates, license type(s), insurance details, Hours of Service rules, position within the Fleet hierarchy, and team associations.
Vehicle. The vehicle table contains information about vehicles, such as name, model, type, year of production, VIN, registration, color, vehicle position within the Fleet hierarchy, fleet associations and vehicle start and end times and dates.

The analytics server also provides the scorecard and driverscorecard views. See the Driver Scorecards section for more information about using these views.