Now that you understand what a trip is, and how they are formed, let us 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 (such as type, color, fuels used, and hierarchy assignment), driver information (such as name, hire date, contact details, and Hours of Service rule type), and 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 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, and DTC flags triggered.
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 types, 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.