This section provides information about the vehicle IoT tables and their results derived from other result tables. Vehicle IoT tables consist of the Telematics tables, to which the data is sourced from the Telematics device attached to the vehicles, other vehicle monitoring systems, and vehicle service records.
Topics:
· Deploying Stream Data Tables on Hive
· Populating Stream Data Dimension Tables
· Populating Stream Data T2T Result Tables
Telematics is a generic term used to refer to an interdisciplinary field in Information Technology that combines telecommunications and informatics. An electronic device designed using Telematics is installed in the vehicles and it can telecommunicate computerized information. OIDF supports sourcing of the Telematics data along with other data from the devices such as Tachograph and driver behavior monitoring systems in the Auto Insurance.
Vehicle IoT (Internet of Things) Result data flow is used to design the OIDF tables that capture processed outputs of the trip level details, vehicle performance, and vehicle driver behavior details.
Topics:
· About Telematics Dimension Tables
· About Telematics T2Ts (Result Tables)
· Vehicle IoT (Internet of Things) Result Data Flow
· Populating the FCT_TRIP_DETAILS Table
· Populating the FCT_TRIP_SUMMARY Table
· Population of the FCT_VEH_PERFORMANCE_SUMMARY Table
· Results derived in the FCT_DRIVER_BEHAVIOUR_SUMMARY Tables
This illustration represents the vehicle IoT data flow in OIDF.
Figure 95: Vehicle IoT data flow diagram
The vehicle IoT data flow consists of the following main stages:
1. Data Sourcing and Processing
2. Modeling Platform
3. Underwriting and Actuarial decision-makers
The explanation to the vehicle IoT data flow is as follows:
1. Data Sourcing and Processing: This stage consists of these two methods through which the vehicle and driver behavior data can be sourced into the OIDF Staging area:
§ With IoT platform: The data sourced from multiple devices is expected to be standardized on the IoT platform. The standardized data can be loaded into the OIDF Staging area using the DIH ETL connectors.
§ Without the IoT platform: The following data of different categories can be sourced directly into the OIDF Staging area:
— Device Data
— External data: This data is available to third-party vendors.
— Claims data
— Vehicle service data
Insurers can use the OIDF Staging to create entities that simulate the telematics and other monitoring device data or use DIH to standardize the data definition or structures and then load them to the OIDF Staging area. If the IoT platform does not facilitate the data standardization, then the Insurers can use DIH to standardize the data definition or structures.
2. Modeling Platform: OFS Enterprise Modeling platform supported by the OFSAAI can be used for modeling operations. Oracle Financial Services Enterprise Modeling leverages popular statistical platforms such as the R platform and presents a framework for developing, deploying, and managing models at the enterprise level for financial institutions. Models in the repository may then be woven into analytical application flows crafted by mixing data management tasks, model execution, and deterministic business logic. As the use of models proliferate and as modeling becomes a self-service idea within financial institutions, authorized modelers may publish techniques, parameterized templates of models that serve as building blocks or standardized blueprints for models -so that the best ideas from experienced modelers are captured and reused within the firm. For more information, see the Oracle Financial Services Enterprise Modeling User Guide Release 8.1.0.0.0.
3. Underwriting and Actuarial decision-makers: Based on the resultant data during the modeling operations, the Insurance Underwriters and Actuaries can analyze statistics and calculate the insurance risks, and then revise the premium rates.
The output data from the vehicle IoT data flow can be used in the User-based Insurance (UBI), ensure that the vehicle is serviced on time, and doing the cost-benefit analysis for vehicle runs.
Telematics Dimension table names and their description are given here.
Logical Dimension Table Name |
Dimension Table Description |
|---|---|
Ignition Status Dimension |
This table stores the ignition status code information. Sample values are IAS, IACS, ICCS, IOS, IRCS. |
On Board Diagnostic Parameter Dimension |
This table stores the onboard diagnostic codes used to request data from a vehicle and used as a diagnostic tool. |
Road Terrain Sub Type Dimension |
This table stores the road terrain sub type details. |
Road Terrain Type Dimension |
This table stores the road terrain type details. Sample values are Level, Rolling, and Mountainous. |
Tachograph Card Status Dimension |
This table stores the tachograph card status details. Sample values are Pending, Act, Inact, and Closed. |
Tachograph Driver Card Dimension |
This table stores the tachograph driver card identifier details. Tachograph card is a device fitted to a vehicle that automatically records vehicle speed and distance traveled, and the driver activity selected from a choice of modes. |
Telematics Device Dimension |
This table stores the telematics device details. |
Telematics Event Dimension |
This table stores the telematics event details. |
Trip Dimension |
This table stores the trip identifier details generated at the trip level for a given time, road type, duration, distance traveled, and time spent primarily. |
Vehicle On Board Diagnostic Dimension |
This table stores the vehicle onboard diagnostic code information. The table contains the actual diagnostic code reported by the device. Sample values are ALV, ATP, AESVP, APP, AAT, AIS, BP, CELV, and so on. |
Vehicle Service Status Dimension |
This table stores the vehicle service status code information. Sample values are DP, MO, OP, and POUT. |
Tachograph Event Type Dimension |
This table stores the tachograph event type details. |
Telematics Device Status Dimension |
This table stores the telematics device status code information. This code reports the status of a telematics device. Sample values are Connect, Disconnect, and Heartbeat. |
Data Stream Type Dimension |
This table stores the data stream type details. Sample Value are EncodedText, PlainText, and TypeCDATA. |
Driving Style Class Dimension |
This table stores the list of codes that provide the driving style assessment of a particular driver. Sample values are Risk, Aggressive, Moderate, Careful Driving, and so on. |
Driving Style Parameters Dimension |
This table stores the list of parameters used to decide the driving style classification. Sample values are Speed, Acceleration, Collisions, Braking, and so on. |
Device Software Type Dimension |
This table stores the software type information installed on the device. Sample values are Application, Firmware, Middleware, OperatingSystem, and Telecommunication. |
Service Reason Dimension |
This table stores the service reason code information. Sample values are BD, RS, MS, and RP. |
Vehicle Device Event Type Dimension |
This table stores the device event type details. Sample values are C, OBR, RS, SSTART, and SSTOP. |
Telematics Software Dimension |
This table stores the telematics software details such as software name, issuer, and so on. |
Telematics Device Type Dimension |
This table stores the telematics device type details. The telematics device type code identifies the type of telematics device. Sample values are BPD, DPN, ND, OEM, OpenOEM, OtherOnboard, Smartphone, and SmartphoneApp. |
The mapping details for the Telematics Dimension tables are given here.
Map Reference Number |
Source Table Name |
Logical Stage Table Name |
Dimension Table Name |
Logical Dimension Table Name |
|---|---|---|---|---|
826 |
STG_IGNITION_STATUS_MASTER |
Stage Ignition Status Master |
DIM_IGNITION_STATUS |
Ignition Status Dimension |
827 |
STG_OBD_PARAMETER_MASTER |
Stage On Board Diagnostic Parameter Master |
DIM_OBD_PARAMETER |
On Board Diagnostic Parameter Dimension |
828 |
STG_ROAD_TERRIAN_STYPE_MASTER |
Stage Road Terrain Sub Type Master |
DIM_ROAD_TERRIAN_STYPE |
Road Terrain Sub Type Dimension |
829 |
STG_ROAD_TERRIAN_TYPE_MASTER |
Stage Road Terrain Type Master |
DIM_ROAD_TERRIAN_TYPE |
Road Terrain Type Dimension |
830 |
STG_TACHOGRPH_CARD_STTS_MASTER |
Stage Tachograph Card Status Master |
DIM_TACHOGRPH_CARD_STTS |
Tachograph Card Status Dimension |
831 |
STG_TACHOGRPH_DRVR_CARD_MASTER |
Stage Tachograph Driver Card Master |
DIM_TACHOGRPH_DRVR_CARD |
Tachograph Driver Card Dimension |
832 |
STG_TELEMATIC_DEVICE_MASTER |
Stage Telematics Device Master |
DIM_TELEMATIC_DEVICE |
Telematics Device Dimension |
833 |
STG_TELEMATIC_EVENT_MASTER |
Stage Telematics Event Master |
DIM_TELEMATIC_EVENT |
Telematics Event Dimension |
834 |
STG_TRIP_MASTER |
Stage Trip Master |
DIM_TRIP |
Trip Dimension |
837 |
STG_VEH_OBD_MASTER |
Stage Vehicle On Board Diagnostic Master |
DIM_VEH_OBD |
Vehicle On Board Diagnostic Dimension |
840 |
STG_VEH_SERVICE_STATUS_MASTER |
Stage Vehicle Service Status Master |
DIM_VEH_SERVICE_STATUS |
Vehicle Service Status Dimension |
842 |
STG_TACHOGRPH_EVNT_TYPE_MASTER |
Stage Tachograph Event Type Master |
DIM_TACHOGRPH_EVNT_TYPE |
Tachograph Event Type Dimension |
843 |
STG_TLMTC_DEVICE_STATUS_MASTER |
Stage Telematics Device Status Master |
DIM_TLMTC_DEVICE_STATUS |
Telematics Device Status Dimension |
844 |
STG_DATA_STREAM_TYPE_MASTER |
Stage Data Stream Type Master |
DIM_DATA_STREAM_TYPE |
Data Stream Type Dimension |
845 |
STG_DRIVING_STYLE_CLASS_MASTER |
Stage Driving Style Class Master |
DIM_DRIVING_STYLE_CLASS |
Driving Style Class Dimension |
846 |
STG_DRIVING_STYLE_PARAM_MASTER |
Stage Driving Style Parameters Master |
DIM_DRIVING_STYLE_PARAM |
Driving Style Parameters Dimension |
847 |
STG_DVC_SOFTWARE_TYPE_MASTER |
Stage Device Software Type Master |
DIM_DVC_SOFTWARE_TYPE |
Device Software Type Dimension |
848 |
STG_SERVICE_REASON_MASTER |
Stage Service Reason Master |
DIM_SERVICE_REASON |
Service Reason Dimension |
849 |
STG_VEH_DVC_EVENT_TYPE_MASTER |
Stage Vehicle Device Event Type Master |
DIM_VEH_DVC_EVENT_TYPE |
Vehicle Device Event Type Dimension |
850 |
STG_TELEMATIC_SWARE_MASTER |
Stage Telematics Software Master |
DIM_TELEMATIC_SWARE |
Telematics Software Dimension |
851 |
STG_TELEMATIC_DVC_TYPE_MASTER |
Stage Telematics Device Type Master |
DIM_TELEMATIC_DVC_TYPE |
Telematics Device Type Dimension |
Telematics T2Ts and their description are given here.
T2T Name |
T2T Description |
|---|---|
T2T_FCT_TELEMATICS_DEVICE_DETAILS |
This T2T stores the event log details. There are numerous events posted by a set of telematics devices. The location, accelerometer, or On-board Diagnostics Data (OBD) can also be shared as an event. Therefore, this T2T can be used when data input is not structured, and the information classification happens in OIDF. |
T2T_FCT_VEHICLE_DEVICE_EVENT_DTLS |
This T2T stores the vehicle device event details generated at the trip level for a given time, road type, duration, distance traveled, and time spent primarily. The Fleet Management System (FMS) is an international standardized interface for commercial vehicles. With the FMS standard, it is possible to retrieve manufacturer-independent data to compare vehicles and drivers, independent of the brand or type. |
T2T_FCT_VEHICLE_FLEET_MAP |
This T2T stores the fleet vehicle map details. Fleet Management is a function, which allows companies that rely on transportation in business to remove or minimize the risks associated with vehicle investment, improving efficiency, productivity, and reducing their overall transportation and staff costs, providing 100% compliance with government legislation (duty of care) and more. The purpose of the Fact Vehicle Fleet Map table is to store the owner and ownership details of a fleet of vehicles. |
T2T_FCT_VEHICLE_SERVICES_DTLS |
This T2T stores the vehicle servicing details, which is required during the claims process to determine the vehicle condition. |
T2T_FCT_VEHICLE_TACHOGRPH_DATA |
This T2T stores data from the tachograph, which is a device fitted to a vehicle that automatically records its speed and distance, and the driver activity selected from a choice of modes. |
T2T_FCT_VEH_ACCELEROMETER_DATA |
This T2T stores the vehicle accelerometer data. This data is stored as acceleration across the X, Y, and Z-axis. This T2T also stores shake threshold, free-fall detection, and so on. |
T2T_FCT_VEH_LOCATION_DATA |
This T2T stores the vehicle location data in a specified time frame or on a trip or in an event in line with the GPS (Global Positioning System) data. |
T2T_FCT_TRIP_DETAILS |
This T2T stores processed outputs generated for n times at trip level, road type, duration, distance traveled, and time spent primarily. |
T2T_FCT_TRIP_FLEET_SUMMARY |
This T2T stores processed outputs generated for a given time at trip level, road type, duration, distance traveled, and time spent primarily. |
T2T_FCT_TRIP_SUMMARY |
This T2T stores processed outputs generated for a given time at the trip level for a fleet, road type, duration, distance traveled, and time spent primarily. |
T2T_FCT_DRIVER_BEHAVIOUR_SUMMARY |
This T2T stores processed outputs generated for a given time at the driver level. The time spent is primarily on driving skills of the driver, for a given trip, event or throughout the specified time frame. |
T2T_FCT_VEH_PERFORMANCE_SUMMARY |
This T2T stores processed outputs generated at vehicle level from the vehicle purchase date. The output is based on its performance from the start date. |
The mapping details for the Telematics T2Ts are given here.
Source Table Name |
Logical Stage Table Name |
Fact Table Name |
Logical Fact Table Name |
T2T Name |
|---|---|---|---|---|
STG_TELEMATICS_DEVICE_DETAILS |
Stage Telematics Device Details |
FCT_TELEMATICS_DEVICE_DETAILS |
Fact Telematics Device Details |
T2T_FCT_TELEMATICS_DEVICE_DETAILS |
STG_VEHICLE_DEVICE_EVENT_DTLS |
Stage Vehicle Device Event Details |
FCT_VEHICLE_DEVICE_EVENT_DTLS |
Fact Vehicle Device Event Details |
T2T_FCT_VEHICLE_DEVICE_EVENT_DTLS |
STG_VEHICLE_FLEET_MAP |
Stage Vehicle Fleet Map |
FCT_VEHICLE_FLEET_MAP |
Fact Vehicle Fleet Map |
T2T_FCT_VEHICLE_FLEET_MAP |
STG_VEHICLE_SERVICES_DTLS |
Stage Vehicle Services Details |
FCT_VEHICLE_SERVICES_DTLS |
Fact Vehicle Services Details |
T2T_FCT_VEHICLE_SERVICES_DTLS |
STG_VEHICLE_TACHOGRPH_DATA |
Stage Vehicle Tachograph Data |
FCT_VEHICLE_TACHOGRPH_DATA |
Fact Vehicle Tachograph Data |
T2T_FCT_VEHICLE_TACHOGRPH_DATA |
STG_VEH_ACCELEROMETER_DATA |
Stage Vehicle Accelerometer Data |
FCT_VEH_ACCELEROMETER_DATA |
Fact Vehicle Accelerometer Data |
T2T_FCT_VEH_ACCELEROMETER_DATA |
STG_VEH_LOCATION_DATA |
Stage Vehicle Location Data |
FCT_VEH_LOCATION_DATA |
Fact Vehicle Location Data |
T2T_FCT_VEH_LOCATION_DATA |
STG_VEHICLE_DEVICE_EVENT_DTLS |
Stage Vehicle Device Event Details |
FCT_TRIP_DETAILS |
Fact Trip Details |
T2T_FCT_TRIP_DETAILS |
STG_VEHICLE_DEVICE_EVENT_DTLS |
Stage Vehicle Device Event Details |
FCT_TRIP_SUMMARY |
Fact Trip Summary |
T2T_FCT_TRIP_FLEET_SUMMARY |
STG_VEHICLE_FLEET_MAP |
Stage Vehicle Fleet Map |
FCT_TRIP_SUMMARY |
Fact Trip Summary |
T2T_FCT_TRIP_FLEET_SUMMARY |
STG_VEHICLE_SERVICES_DTLS |
Stage Vehicle Services Details |
FCT_TRIP_SUMMARY |
Fact Trip Summary |
T2T_FCT_TRIP_FLEET_SUMMARY |
STG_VEHICLE_DEVICE_EVENT_DTLS |
Stage Vehicle Device Event Details |
FCT_TRIP_SUMMARY |
Fact Trip Summary |
T2T_FCT_TRIP_SUMMARY |
STG_VEH_LOCATION_DATA |
Stage Vehicle Location Data |
FCT_TRIP_SUMMARY |
Fact Trip Summary |
T2T_FCT_TRIP_SUMMARY |
STG_VEHICLE_SERVICES_DTLS |
Stage Vehicle Services Details |
FCT_TRIP_SUMMARY |
Fact Trip Summary |
T2T_FCT_TRIP_SUMMARY |
FCT_TRIP_SUMMARY |
Fact Trip Summary |
FCT_DRIVER_BEHAVIOUR_SUMMARY |
Fact Driver Behavior Summary |
T2T_FCT_DRIVER_BEHAVIOUR_SUMMARY |
FCT_TRIP_SUMMARY |
Fact Trip Summary |
FCT_VEH_PERFORMANCE_SUMMARY |
Fact Vehicle Performance Summary |
T2T_FCT_VEH_PERFORMANCE_SUMMARY |
FCT_VEHICLE_SERVICES_DTLS |
Fact Vehicle Services Details |
FCT_VEH_PERFORMANCE_SUMMARY |
Fact Vehicle Performance Summary |
T2T_FCT_VEH_PERFORMANCE_SUMMARY |
The data from the telematics and On-Board Diagnostics systems are sourced into a set of tables. The calculations are carried out in those tables and the processed outputs are stored in the FCT_TRIP_DETAILS, FCT_TRIP_SUMMARY, FCT_DRIVER_BEHAVIOUR_SUMMARY, and FCT_VEH_PERFORMANCE_SUMMARY tables for both the individual and fleet of vehicle scenarios. The same is depicted in the following illustration.
Figure 96: Vehicle IoT Result Data Flow
The same flow is depicted in the following tabular column.
The sequence in the Run chart |
Table Names |
Use Case Description |
Applicable to both individual and fleet of vehicles? |
Remarks |
|---|---|---|---|---|
1 |
FCT_TRIP_DETAILS |
These are processed outputs generated for n times at trip level, road type, duration, distance traveled, and time spent primarily |
Yes |
|
2 |
FCT_TRIP_SUMMARY |
These are processed outputs generated for a given time at the trip level for a fleet, road type, duration, distance traveled, and time spent primarily. |
Yes |
For individual vehicle, data is sourced from: · STG_VEHICLE_DEVICE_EVENT_DTLS · STG_VEH_LOCATION_DATA For fleet of vehicles, data is sourced from: · STG_VEHICLE_DEVICE_EVENT_DTLS · STG_TACOGRAPH_DATA |
3 |
FCT_DRIVER_BEHAVIOUR_SUMMARY |
These are processed outputs generated for a given time at the driver level. The time spent is primarily on driving skills of the driver, for a given trip, event or throughout the specified time frame. |
Yes |
The results are derived and sourced from the FCT_TRIP_SUMMARY table. |
4 |
FCT_VEH_PERFORMANCE_SUMMARY |
These are processed outputs generated at the vehicle level from the vehicle purchase date. The output is based on its performance from the start date. |
Yes |
The results are derived and sourced from the FCT_TRIP_SUMMARY and FCT_VEHICLE_SERVICES_DTLS tables. |
In a UI defined table called FCT_DRIVER_BEHAVIOUR_GUIDELINE, you can define parameters and set a custom range for each parameter as the driver behavior guidelines.
This section provides information about the calculation results derived in the FCT_TRIP_DETAILS table using the telematics device for the time taken and distance traveled by an individual vehicle.
From the base table STG_VEHICLE_DEVICE_EVENT_DTLS, the following list of essential columns is used to calculate the time taken and the distance traveled by the vehicle for a trip.
Column Name |
Purpose of this column |
Sample of value stored in the column |
|---|---|---|
V_TRIP_ID |
Stores the trip ID automatically generated by the telematics device. |
Trip001 |
V_VEHICLE_ID |
Stores the ID of the vehicle used for this particular trip |
Vehicle1 |
V_DRIVER_ID |
Stores the ID of the driver who is driving this vehicle for the current trip |
Driver001 |
V_VEH_IGNITION_STATUS_CD |
Stores the vehicle ignition status in the form of codes. · When the vehicle is started, the ignition status code stored in this column is IRCS. · When the vehicle stops, the ignition status code stored in this column is OFF. The vehicle ignition status can be either IRCS or OFF for a given timestamp. |
· IRCS when the ignition on. · OFF when the engine or ignition is off. |
V_TELEMATIC_EVENT_ID |
Stores the event ID generated by the telematics device for each event. This column value is dependent on the value of the V_VEH_IGNITION_STATUS_CD column as follows: · When the vehicle is started, the ignition status code stored in the column V_VEH_IGNITION_STATUS_CD is IRCS. This is considered as an event on a trip. Therefore, the event ID in the column V_TELEMATIC_EVENT_ID increases by one. · When the vehicle stops, the ignition status code stored in this column V_VEH_IGNITION_STATUS_CD is OFF. This is considered as an event on a trip. Therefore, the event ID in the column V_TELEMATIC_EVENT_ID increases by one. When the ignition is ON, it is one event. When the engine is OFF, it is another event. |
For example: · Event ID is Event001 when the ignition status is IRCS. · Event ID is Event002 when the ignition status is OFF. |
V_TELEMATIC_DEVICE_ID |
Stores the ID of the telematics device attached to the vehicle. |
TelematicDevice1 |
D_DATA_FEED_TIME_STAMP |
Stores the real date and time for each second. When an event occurs, the recorded timestamp from this column is used for calculations. |
|
V_DISTANCE_PER_UNIT_OF_FUEL |
Stores the distance traveled by the vehicle per standard unit of fuel. |
|
FIC_MIS_DATE |
Stores the date and time on which the Run is processed. |
6/30/20 12:00 AM |
For the calculations, consider the following scenario:
1. The telematics device TelematicDevice1 is attached to Vehicle1. The driver ID of the Vehicle1 is Driver001.
2. The driver starts the vehicle.
a. Therefore, the TelematicDevice1 records the vehicle ignition status as IRCS and the value IRCS is stored in the V_VEH_IGNITION_STATUS_CD column.
b. Also, as the vehicle ignition status is changed from OFF to ON, the TelematicDevice1 records this as an event and the value Event001 is stored in the V_TELEMATIC_EVENT_ID column.
c. The timestamp is stored in the D_DATA_FEED_TIME_STAMP column.
3. After driving for a certain amount of time, the driver stops the Vehicle1.
a. Therefore, the TelematicDevice1 records the vehicle ignition status as OFF and the value OFF is stored in the V_VEH_IGNITION_STATUS_CD column.
b. Also, as the vehicle ignition status is changed from on to off, the TelematicDevice1 records this as an event and the value Event002 is stored in the V_TELEMATIC_EVENT_ID column.
c. The timestamp continues to store in the D_DATA_FEED_TIME_STAMP column.
This scenario is considered as one trip represented here by the trip ID Trip001. Events and triggers are expected to be different for different device vendors.
Topic:
· Results Derived using the Calculations
This section provides information about how the time taken and the distance traveled by the vehicle during a trip are calculated.
Topics:
· Calculation of the Time Taken for a Trip
· Calculation of the Distance Travelled by the Vehicle during a Trip
The calculation of the time taken for a trip is explained as follows.
Sequence Number |
Calculation Stage Description |
Remarks |
|---|---|---|
1 |
The difference in the timestamp values between Event001 and Event002 results in the time taken between two events. |
This time difference is obtained using the D_DATA_FEED_TIME_STAMP column. |
2 |
The preceding calculation is repeated for the consecutive timestamp values for a trip. |
For the date range between the columns D_TRIP_START_DATE and D_TRIP_END_DATE. |
3 |
The summation of the preceding calculation results in the time taken for a trip. |
The result is stored in the column FCT_TRIP_DETAILS.N_DRIVING_TIME. |
Therefore, at any given time this gives time taken for a trip so far. In a similar method, the time taken for different trips for different vehicles and drivers can be calculated.
This section provides information about how the distance traveled by the vehicle during a trip is calculated.
In addition to the Calculation of the Time Taken for a Trip in the previous section, for illustration, consider the following:
· The mileage of the Vehicle1 is 15 kmpl (kilometer per liter of fuel).
· The total fuel (diesel in this illustration) consumed for Trip001 is 2 liter (for the distance traveled from the start point to the endpoint).
· The time taken for the Trip001 is stored in the FCT_TRIP_DETAILS.N_DRIVING_TIME column in seconds.
Therefore, the total distance traveled for Trip001 on 2 liters of fuel is 30 kilometer. This value is stored in the column FCT_TRIP_DETAILS.N_DRIVING_DISTANCE.
The different calculation stages of the distance traveled for a trip is explained as follows.
Sequence Number |
Calculation Stage Description |
Remarks |
|---|---|---|
1 |
The time taken for a trip. |
Stored in the column FCT_TRIP_DETAILS.N_DRIVING_TIME for the date range between the columns D_TRIP_START_DATE and D_TRIP_END_DATE. |
2 |
Using the time taken from the previous step and standard mileage of the vehicle, the distance traveled for a trip is calculated as illustrated in the beginning of this section. |
The result is stored in the column FCT_TRIP_DETAILS.N_DRIVING_DISTANCE. |
This section provides information about the results derived into the FCT_TRIP_SUMMARY table using the telematics and On-board Diagnostics (OBD) system data for a fleet of vehicles and for individual vehicles.
From the base table STG_VEHICLE_DEVICE_EVENT_DTLS, the following list of essential columns is used for derivations.
Column Name |
Purpose of this column |
Sample of value(s) stored in the column |
|---|---|---|
V_TRIP_ID |
Stores the trip ID automatically generated by the telematics device. |
Trip001 |
V_VEHICLE_ID |
Stores the ID of the vehicle used for this particular trip. · For an individual vehicle, the Vehicle ID is sourced from the STG_VEH_LOCATION_DATA table. · For a fleet of vehicles, the Vehicle ID is sourced from the STG_VEHICLE_TACHOGRPH_DATA table. |
Vehicle1 |
V_DRIVER_ID |
Stores the ID of the driver who is driving this vehicle for the current trip. |
Driver001 |
V_TRIP_START_LOCATION_CODE |
Stores the location information, from where the vehicle current trip begins, in the form of a code. |
|
V_TRIP_END_LOCATION_CODE |
Stores the location information, where the vehicle current trip ends, in the form of a code. |
|
N_TOP_GEAR_VALUE |
Stores the top gear value of a vehicle. |
|
D_DATA_FEED_TIME_STAMP |
Stores the real date and time for each second or for a decided unit of time. When an event occurs, the recorded timestamp from this column is used for calculations. |
|
D_TRIP_START_DATE |
Stores the trip start date for a vehicle and the trip. |
|
D_TRIP_END_DATE |
Stores the trip end date for a vehicle and the trip. |
|
N_ENGINE_LOAD_PERCENT |
Stores the percentage of the rated load of a vehicle engine for the trip. |
|
N_TOT_TIME_CONSUMD_IN_IDLE |
Stores the total amount of time during which the vehicle was in the idle condition during a trip. The unit stored is in seconds. |
|
For the calculations, consider the following scenario:
1. Consider that the Vehicle1 driver is Driver001. When the driver starts the vehicle, the trip start date is stored in the D_TRIP_START_DATE column and the timestamp is stored in the D_DATA_FEED_TIME_STAMP column.
2. After the trip is completed by the driver, the trip end date is stored in the D_TRIP_END_DATE column and the timestamp is stored in the D_DATA_FEED_TIME_STAMP column.
This scenario is considered as one trip represented here by the trip ID Trip001.
NOTE:
The following sections are applicable to both the individual vehicle and a fleet of vehicle scenarios.
The result derivations into the FCT_TRIP_SUMMARY table is divided into the following categories:
· Results Derived using Calculations
· Results Derived using Aggregation
The following sections contain information about how the results are derived using the calculations.
· Distance Covered in the Top Gear by the Vehicle during a Trip
· Average Miles Traveled Per Day and Per Trip
· Average Speed per Hour of the Vehicle on a Trip
· Total and Average Count of Trips
This section provides information about how the distance covered by the vehicle in top gear during a trip is calculated.
For calculation, the following columns are used along with the main columns in the STG_VEHICLE_DEVICE_EVENT_DTLS table:
Source Column Name |
Purpose of this column |
|---|---|
N_TOP_GEAR_VALUE |
Stores the top gear value of a vehicle. |
N_NO_OF_MIN_IN_TOP_GEAR |
Stores the number of minutes the vehicle is in top gear for a trip. The On-board Diagnostics (OBD) system is expected to provide the number of minutes the vehicle is in top gear when driving. This data transformation accumulates the minutes to get the total number of minutes the vehicle is in top gear. |
N_FUEL_CONSMD_IN_TOP_GEAR |
Stores the amount of fuel consumed in top gear for a trip. The On-board Diagnostics (OBD) system is expected to provide the amount of fuel consumed in liters by the vehicle in top gear when driving. This data transformation accumulates the quantity of liters to get the total amount of fuel consumed in liters by the vehicle in top gear. |
The calculation is explained as follows.
Sequence Number |
Calculation Stage Description |
Type or purpose of the source or result column |
|---|---|---|
1 |
The On-board Diagnostics (OBD) system is expected to provide the number of minutes the vehicle is in top gear when driving. |
The result is stored in the N_NO_OF_MIN_IN_TOP_GEAR column. |
2 |
The On-board Diagnostics (OBD) system is expected to provide the amount of fuel consumed in liters by the vehicle in top gear when driving. |
The result is stored in the N_FUEL_CONSMD_IN_TOP_GEAR column. |
3 |
Similar to the Calculation of the Distance Travelled by the Vehicle during a Trip section, using the amount of time and the amount of fuel consumed in the top gear by the vehicle, the calculation results in the distance covered in the top gear by the vehicle during a trip. |
The result is stored in the column FCT_TRIP_SUMMARY.N_DISTANCE_COVERED_IN_TOP_GEAR. |
This section provides information about how the average distance traveled in miles by a vehicle is calculated for the following scenarios:
· Per day
· Per trip
For calculation, the columns from the main columns in the STG_VEHICLE_DEVICE_EVENT_DTLS table are used.
The calculation of average distance traveled per day is explained as follows.
Sequence Number |
Calculation Stage Description |
Remarks |
|---|---|---|
1 |
The distance traveled during a trip for a vehicle is calculated as depicted in the Calculation of the Distance Travelled by the Vehicle during a Trip section. |
The resultant distance traveled for the trip is stored in the column FCT_TRIP_DETAILS.N_DRIVING_DISTANCE. |
2 |
The time taken for a trip is calculated as depicted in the Calculation of the Time Taken for a Trip section. |
The resultant time taken for the trip is stored in the column FCT_TRIP_DETAILS.N_DRIVING_TIME. |
3 |
The total distance for the trip is converted into miles. |
The result is the number of miles of distance covered during the trip. |
4 |
The total time taken for the trip is converted into days. |
The result is the total number of days taken for the trip. |
5 |
The total distance covered in miles for the trip is divided by the number of days of the trip. |
The result is the average distance covered in miles by the vehicle per day and is stored in the column FCT_TRIP_SUMMARY.N_AVG_MILES_TRVLD_PER_DAY |
The calculation of average distance traveled per trip is explained as follows.
Sequence Number |
Calculation Stage Description |
Remarks |
|---|---|---|
1 |
The distance traveled during a trip for a vehicle is calculated as depicted in the Calculation of the Distance Travelled by the Vehicle during a Trip section. |
The resultant distance traveled for the trip is stored in the column FCT_TRIP_DETAILS.N_DRIVING_DISTANCE. |
2 |
The preceding calculation is repeated for all the trips taken by the vehicle. |
The result is the total distance traveled by the vehicle for all trips combined. |
3 |
The resultant distance is converted into the unit of miles. |
|
4 |
The total distance in miles is divided by the count of trips. |
The result is the average distance covered in miles by the vehicle per trip and is stored in the column FCT_TRIP_SUMMARY.N_AVG_MILES_TRVLD_PER_TRIP. |
This section provides information about how the average speed per hour of the vehicle during a trip is calculated.
For calculation, the following columns are used along with the main columns in the STG_VEHICLE_DEVICE_EVENT_DTLS table:
Column Name |
Purpose of this column |
|---|---|
N_ENGINE_SPEED_PH |
Stores the vehicle engine speed (speed per hour) calculated for each second of the trip or for a standard time unit. |
The calculation is explained as follows.
Sequence Number |
Calculation Stage Description |
Remarks |
|---|---|---|
1 |
All the values of the vehicle engine speed (speed per hour) are summed up. This is the total value of the vehicle engine speed (per hour) on a trip. |
The N_ENGINE_SPEED_PH column value for the range between D_TRIP_START_DATE and D_TRIP_END_DATE. |
2 |
The time taken for a trip is calculated as depicted in the Calculation of the Time Taken for a Trip section. |
The resultant time taken for the trip is stored in the column FCT_TRIP_DETAILS.N_DRIVING_TIME. |
3 |
The total time taken for the trip is converted into hours. |
The result is the total number of hours taken for the trip. |
4 |
The total value of the vehicle engine speed (per hour) is divided by the total number of hours taken for the trip. |
The result is the average speed of the vehicle on the trip and stored in the column FCT_TRIP_SUMMARY.N_AVG_SPEED_OF_VEH_ON_TRIP. |
This section provides information about how the following counts are found:
· Total count of trips
· Average count of trips per month
For calculation, the following columns are used along with the main columns in the STG_VEHICLE_DEVICE_EVENT_DTLS table:
Column Name |
Purpose of this column |
Sample of value(s) stored in the column |
|---|---|---|
V_TRIP_ID |
Stores the trip ID automatically generated by the telematics device. |
Trip001 |
FIC_MIS_DATE |
Stores the date and time on which the Run is processed. |
|
The calculation of the total count of trips is explained as follows.
Sequence Number |
Calculation Stage Description |
Remarks |
|---|---|---|
1 |
When the Run is processed, the highest value of the generated trip ID can be found. |
Run is processed for the FIC_MIS_DATE. |
2 |
The preceding calculation results in the total trip count. |
The result is stored in the column FCT_TRIP_SUMMARY.N_COUNT_OF_TRIPS. |
The calculation of the average count of trips per month is explained as follows.
Sequence Number |
Calculation Stage Description |
Remarks |
|---|---|---|
1 |
The trip IDs of 30 days period is calculated. |
Run is processed for the FIC_MIS_DATE with any start date and end date difference of 30 days. |
2 |
The count of 30 days of trip IDs is divided by a period of one month (in days). |
|
3 |
The preceding calculation results in the average count of trips per month. |
The result is stored in the column FCT_TRIP_SUMMARY.N_AVERAGE_COUNT_OF_TRIP_PM. |
The following sections contain information about how the results are derived using the aggregation:
· Driving Minutes and Rest Duration of the Vehicle
· Total Number of Minutes and Fuel Consumed in the Top Gear by the Vehicle during a Trip
· Average duration of Trips per month
· Driver Behavior when Driving
· Driving Behavior and the Aggressive Driving Behavior
· Count of Garage Visits for Vehicle Service
This section provides information about how the following results are derived and aggregated for a vehicle:
· Continuous driving time
· Current duration of a trip in days
· Total number of hours the engine is utilized
· Cumulative driving minutes for the previous and current year
· Cumulative rest duration in minutes
For information about how the time taken by the vehicle for a trip or between two events is calculated, see the section Calculation of the Time Taken for a Trip.
For derivation using aggregation, the following essential columns are used along with the main columns in the STG_VEHICLE_DEVICE_EVENT_DTLS table section.
Column Name |
Purpose of this column |
Sample of value(s) stored in the column |
|---|---|---|
V_VEH_IGNITION_STATUS_CD |
Stores the vehicle ignition status in the form of codes. · When the vehicle is started, the ignition status code stored in this column is IRCS. · When the vehicle stops, the ignition status code stored in this column is OFF. The vehicle ignition status can be either IRCS or OFF for a given timestamp. |
· IRCS when the ignition on. · OFF when the engine or ignition is off. |
D_DATA_FEED_TIME_STAMP |
Stores the real date and time for each second or for a decided unit of time. When an event occurs, the recorded timestamp from this column is used for calculations. |
|
The result derivation using aggregation is explained as follows.
Scenario type |
Source Table Name |
Remarks (for Source Table and Column) |
Target Table and Column Name |
Remarks (for Target Table and Column) |
|---|---|---|---|---|
Continuous driving time |
From the STG_VEHICLE_DEVICE_EVENT_DTLS table, these columns are used V_VEH_IGNITION_STATUS_CD, D_TRIP_START_DATE, D_TRIP_END_DATE, and D_DATA_FEED_TIME_STAMP. |
For a trip, the number of driving minutes is accumulated for the time lapse between the two consecutive time stamp data D_DATA_FEED_TIME_STAMP when the engine is ON and for the date range between the D_TRIP_START_DATE and D_TRIP_END_DATE. |
FCT_TRIP_SUMMARY.N_COUNTINOUS_DRIVING_TIME_MIN |
The continuous driving time of a trip is stored in the target column. |
From the STG_VEHICLE_DEVICE_EVENT_DTLS table, these columns are used V_VEH_IGNITION_STATUS_CD, D_TRIP_START_DATE, D_TRIP_END_DATE, and D_DATA_FEED_TIME_STAMP. |
The duration of a trip in minutes is accumulated for the time lapse between the two consecutive time stamp data D_DATA_FEED_TIME_STAMP when the engine is ON and for the date range between the D_TRIP_START_DATE and D_TRIP_END_DATE, and converted into days. |
FCT_TRIP_SUMMARY.N_CURR_DURATION_OF_TRIP |
The current duration of a trip in days is stored in the target column. |
|
From the STG_VEHICLE_DEVICE_EVENT_DTLS table, these columns are used V_VEH_IGNITION_STATUS_CD, D_TRIP_START_DATE, D_TRIP_END_DATE, and D_DATA_FEED_TIME_STAMP. |
For a trip, the number of minutes the vehicle engine is utilized is accumulated for the time lapse between the two consecutive time stamp data D_DATA_FEED_TIME_STAMP when the engine is ON and for the date range between the D_TRIP_START_DATE and D_TRIP_END_DATE, and converted into hours. |
FCT_TRIP_SUMMARY.N_TOTAL_ENGINE_UTILIZED_HRS |
The total number of hours the engine is utilized during a trip is stored in the target column. |
|
Cumulative driving minutes for the previous and current year |
From the STG_VEHICLE_DEVICE_EVENT_DTLS table, these columns are used V_VEH_IGNITION_STATUS_CD, D_TRIP_START_DATE, D_TRIP_END_DATE, D_DATA_FEED_TIME_STAMP, and V_TRIP_ID. |
The number of driving minutes is accumulated for the time lapse between the two consecutive time stamp data D_DATA_FEED_TIME_STAMP when the engine is ON and for the date range between the previous year and the current year to the present day. |
FCT_TRIP_SUMMARY.N_CUM_DRIVING_MIN_PRVS_CUR |
The cumulative driving minutes for the previous year and the current year to the present day is stored in the target column. |
Cumulative rest duration in minutes |
From the STG_VEHICLE_DEVICE_EVENT_DTLS table, these columns are used V_VEH_IGNITION_STATUS_CD, D_TRIP_START_DATE, D_TRIP_END_DATE, and D_DATA_FEED_TIME_STAMP. |
For a trip, the number of minutes the vehicle is not running is accumulated for the time lapse between the two consecutive time stamp data D_DATA_FEED_TIME_STAMP when the engine is OFF and for the date range between the D_TRIP_START_DATE and D_TRIP_END_DATE. |
FCT_TRIP_SUMMARY.N_CUM_REST_DUARATION_IN_MIN |
The cumulative vehicle rest duration in minutes for a trip is stored in the target column. |
This section provides information about how the following results are derived and aggregated:
· Total number of minutes the vehicle was in the top gear
· Total amount of fuel consumed by the vehicle in the top gear
For derivation using aggregation, the following columns are used along with the main columns in the STG_VEHICLE_DEVICE_EVENT_DTLS table:
Column Name |
Purpose of this column |
|---|---|
N_TOP_GEAR_VALUE |
Stores the top gear value of a vehicle. |
N_NO_OF_MIN_IN_TOP_GEAR |
Stores the number of minutes the vehicle is in top gear for a trip. The On-board Diagnostics (OBD) system is expected to provide the number of minutes the vehicle is in top gear when driving. This data transformation accumulates the minutes to get the total number of minutes the vehicle is in top gear. |
N_FUEL_CONSMD_IN_TOP_GEAR |
Stores the amount of fuel consumed in top gear for a trip. The On-board Diagnostics (OBD) system is expected to provide the amount of fuel consumed in liters by the vehicle in top gear when driving. This data transformation accumulates the quantity of liters to get the total amount of fuel consumed in liters by the vehicle in top gear. |
The result derivations using aggregation are explained as follows.
Result derivation type |
Source Table and Column Name |
Remarks (for Source Table and Column) |
Target Table and Column Name |
Remarks (for Target Table and Column) |
|---|---|---|---|---|
Total number of minutes the vehicle was in the top gear |
STG_VEHICLE_DEVICE_EVENT_DTLS.N_NO_OF_MIN_IN_TOP_GEAR |
The number of minutes the vehicle is in top gear during a trip, which is sourced by the On-board Diagnostics (OBD) system is accumulated for the time lapse between the two consecutive time stamp data D_DATA_FEED_TIME_STAMP and the date range between the columns D_TRIP_START_DATE and D_TRIP_END_DATE. |
FCT_TRIP_SUMMARY.N_TOT_NO_OF_MIN_IN_TOP_GEAR |
The result is the total number of minutes the vehicle is in top gear during the trip and is stored in the target column. |
Total amount of fuel consumed in liters by the vehicle in the top gear |
STG_VEHICLE_DEVICE_EVENT_DTLS.N_FUEL_CONSMD_IN_TOP_GEAR |
The amount of fuel consumed in liters in the top gear during a trip, which is sourced by the On-board Diagnostics (OBD) system is accumulated for the time lapse between the two consecutive time stamp data D_DATA_FEED_TIME_STAMP and the date range between the columns D_TRIP_START_DATE and D_TRIP_END_DATE. |
FCT_TRIP_SUMMARY.N_TOT_FUEL_CONSMD_TOP_GEAR |
The result is the total amount fuel consumed by the vehicle in top gear during the trip and is stored in the target column. |
This section provides information about how the cumulative distance covered to date is calculated.
For derivation using aggregation, the following columns are used along with the main columns in the STG_VEHICLE_DEVICE_EVENT_DTLS table:
Column Name |
Purpose of this column |
|---|---|
V_DISTANCE_PER_UNIT_OF_FUEL |
Stores the distance covered per a standard unit of fuel. |
The result derivation using aggregation is explained as follows.
Result derivation type |
Source Table and Column Name |
Remarks (for Source Table and Column) |
Target Table and Column Name |
Remarks (for Target Table and Column) |
|---|---|---|---|---|
Cumulative fuel quantity consumed on a trip |
STG_VEHICLE_DEVICE_EVENT_DTLS.V_DISTANCE_PER_UNIT_OF_FUEL |
The amount of distance covered during a trip, which is sourced by the On-board Diagnostics (OBD) system is accumulated for the time lapse between the two consecutive time stamp data D_DATA_FEED_TIME_STAMP and the date range between the columns D_TRIP_START_DATE and D_TRIP_END_DATE. |
FCT_TRIP_SUMMARY.N_CUM_DISTANCE_COVRED_TRIP |
The result is the cumulative distance covered for the trip and is stored in the target column. |
This section provides information about how the cumulative fuel consumed for the following scenarios are derived:
For derivation using aggregation, the following columns are used along with the main columns in the STG_VEHICLE_DEVICE_EVENT_DTLS table:
Column Name |
Purpose of this column |
|---|---|
N_FUEL_CONSUMED_IN_LTRS |
Stores the quantity of fuel consumed by the vehicle in liters. The On-board Diagnostics (OBD) system is expected to provide the amount of fuel consumed in liters when driving. This data transformation accumulates the measurement of liters to get the total amount of fuel consumed in liters. |
The result derivations using aggregation are explained as follows.
Result derivation type |
Source Table and Column Name |
Remarks (for Source Table and Column) |
Target Table and Column Name |
Remarks (for Target Table and Column) |
|---|---|---|---|---|
STG_VEHICLE_DEVICE_EVENT_DTLS.N_FUEL_CONSUMED_IN_LTRS |
The amount of fuel consumed in liters when driving, which is sourced by the On-board Diagnostics (OBD) system is accumulated for the time lapse between the two consecutive time stamp data D_DATA_FEED_TIME_STAMP and the date range between the columns D_TRIP_START_DATE and D_TRIP_END_DATE. |
FCT_TRIP_SUMMARY.N_CUM_FUEL_USED_ON_TRIP |
The result is the cumulative fuel used for a trip and is stored in the target column. |
|
STG_VEHICLE_DEVICE_EVENT_DTLS.N_FUEL_CONSUMED_IN_LTRS |
The amount of fuel consumed in liters when driving, which is sourced by the On-board Diagnostics (OBD) system is accumulated for the time lapse between the two consecutive time stamp data D_DATA_FEED_TIME_STAMP and the date range of 24 hours between the columns D_TRIP_START_DATE and D_TRIP_END_DATE. |
FCT_TRIP_SUMMARY.N_CUM_FUEL_USED_IN_24HRS |
The result is the cumulative fuel used in 24 hours for a trip and is stored in the target column. |
This section provides information about how the average trip duration per month is aggregated.
Consider the following details for this aggregation:
· The difference between the columns D_TRIP_END_DATE and D_TRIP_START_DATE results in the number of days per trip (for Trip001 in this calculation).
For illustration, consider that the Driver001 takes 5 such trips in a month tabulated as follows.
V_DRIVER_ID |
V_TRIP_ID |
Number of days during a trip (the difference between the columns D_TRIP_END_DATE and D_TRIP_START_DATE) |
|---|---|---|
Driver001 |
Trip001 |
1 |
Driver001 |
Trip002 |
3 |
Driver001 |
Trip003 |
4 |
Driver001 |
Trip004 |
4 |
Driver001 |
Trip005 |
3 |
|
Total number of trips = 5 |
Total number of days for total number of trips = 15 |
From the illustration, average number of days per trip is 3 days (Days traveled divided by the number of trips taken).
In general, the average duration of trips per month is found as follows:
Source Table and Column Name |
Remarks (for Source Table and Column) |
Target Table and Column Name |
Remarks (for Target Table and Column) |
|---|---|---|---|
From the STG_VEHICLE_DEVICE_EVENT_DTLS table, these columns are used D_TRIP_END_DATE, D_TRIP_START_DATE, D_DATA_FEED_TIME_STAMP, and V_TRIP_ID. |
The average duration per trip per month is found. |
FCT_TRIP_SUMMARY.N_AVERAGE_DURATION_OF_TRIP_PM |
The result is derived and stored in this column. |
This section provides driver behavior information when driving the vehicle. This section provides information about how the total amount of time for the following driver behaviors is derived:
· Smoking
For derivation using aggregation, the following columns are used along with the main columns in the STG_VEHICLE_DEVICE_EVENT_DTLS table:
Column Name |
Purpose of this column |
|---|---|
N_NO_OF_MIN_SMOKING_WHL_DRIVE |
Stores the number of minutes spent smoking by the driver when driving during a trip. The On-board Diagnostics (OBD) system is expected to provide minutes of smoking when driving. This data transformation accumulates the minutes to get the total number of minutes for this behavior. |
N_NO_OF_MIN_CELL_USG_WHL_DRIVE |
Stores the number of minutes spent by the driver using the cell phone when driving during a trip. The On-board Diagnostics (OBD) system is expected to provide minutes of cell phone usage when driving. This data transformation accumulates the minutes to get the total number of minutes for this behavior. |
N_NO_OF_MIN_TEXTING_WHL_DRIVE |
Stores the number of minutes spent by the driver texting on the cell phone when driving during a trip. The On-board Diagnostics (OBD) system is expected to provide minutes of texting when driving. This data transformation accumulates the minutes to get the total number of minutes for this behavior. |
The result derivations using aggregation are explained as follows.
Driver behavior type |
Source Table and Column Name |
Remarks (for Source Table and Column) |
Target Table and Column Name |
Remarks (for Target Table and Column) |
|---|---|---|---|---|
STG_VEHICLE_DEVICE_EVENT_DTLS.N_NO_OF_MIN_SMOKING_WHL_DRIVE |
The number of minutes the driver was smoking when driving, which is sourced by the On-board Diagnostics (OBD) system is accumulated for the time lapse between the two consecutive time stamp data D_DATA_FEED_TIME_STAMP and the date range between the columns D_TRIP_START_DATE and D_TRIP_END_DATE. |
FCT_TRIP_SUMMARY.N_TOT_NO_OF_MIN_SMKNG_WHL_DRVN |
The summation of the sourced calculation results in the total number of minutes spent smoking by the driver when driving during a trip. The result is stored in the target column. |
|
STG_VEHICLE_DEVICE_EVENT_DTLS.N_NO_OF_MIN_CELL_USG_WHL_DRIVE |
The number of minutes the driver was using the cell phone when driving, which is sourced by the On-board Diagnostics (OBD) system is accumulated for the time lapse between the two consecutive time stamp data D_DATA_FEED_TIME_STAMP and the date range between the columns D_TRIP_START_DATE and D_TRIP_END_DATE. |
FCT_TRIP_SUMMARY.N_TOT_MIN_CELL_USG_WHL_DRVNG |
The summation of the sourced calculation results in the total number of minutes spent by the driver using the cell phone when driving during a trip. The result is stored in the target column. |
|
STG_VEHICLE_DEVICE_EVENT_DTLS.N_NO_OF_MIN_CELL_USG_WHL_DRIVE |
The number of minutes the driver was texting on the phone when driving, which is sourced by the On-board Diagnostics (OBD) system is accumulated for the time lapse between the two consecutive time stamp data D_DATA_FEED_TIME_STAMP and the date range between the columns D_TRIP_START_DATE and D_TRIP_END_DATE. |
FCT_TRIP_SUMMARY.N_NO_OF_MIN_TEXTING_WHL_DRIVE |
The summation of the sourced calculation results in the total number of minutes spent texting on the phone by the driver when driving during a trip. The result is stored in the target column. |
This section provides information about how the following driving behavior is derived:
· Total number of brakes applied
This section also provides information about how the following aggressive driving behaviors is derived:
· Last Year-to-Date count of harsh brakes
· Last Year-to-Date count of accidents
· Total count of harsh accelerations
· Last Year-to-Date count of harsh accelerations
For derivation using aggregation, the following column is used along with the main columns in the STG_VEHICLE_DEVICE_EVENT_DTLS table:
Column Name |
Purpose of this column |
Sample of value(s) stored in the column |
|---|---|---|
F_BRAKE_SWITCH_ACTIVE_FLAG |
Every time the brakes are applied causing the brake switch to turn ON during a trip, this flag saves the value as Y. On-board Diagnostics (OBD) system is expected to update the value of this flag. This data transformation calculates the number of times when this flag is streamed as active and accumulates the count for a given trip. |
Y |
V_DEVICE_EVENT_TYPE |
Stores the type of event that happened during the trip for an individual vehicle. |
Accident, Cornering, SUDDENACLRTN, SUDDENBRKNG, OFF, OBR, Shutdown. |
STG_VEHICLE_MASTER.D_PURCHASE_DATE |
Stores the purchase date of the vehicle. |
|
N_HARSH_BRAKING_COUNT |
Stores count of the brakes applied harshly during a trip. In the event of brakes applied, if the percentage deceleration (N_ACCELARATION_PERCENT) reaches or crosses 5, then it qualifies as harsh braking and the On-board Diagnostics (OBD) system is expected to provide the count of brakes applied harshly when driving. |
Count of SUDDENBRKNG |
N_HARSH_ACCELARATION_COUNT |
Stores count of the harsh accelerations during a trip. In the event of harsh acceleration, if the percentage acceleration (N_ACCELARATION_PERCENT) reaches or crosses 5, then it qualifies as harsh acceleration and the On-board Diagnostics (OBD) system is expected to provide the count of harsh acceleration when driving. |
Count of SUDDENACLRTN |
The result derivations using aggregation are explained as follows.
Event type and period |
Source Table and Column Name |
Remarks (for Source Table and Column) |
Target Table and Column Name |
Remarks (for Target Table and Column) |
|---|---|---|---|---|
Total number of brakes applied during a trip |
STG_VEHICLE_DEVICE_EVENT_DTLS.F_BRAKE_SWITCH_ACTIVE_FLAG |
The total number of times the F_BRAKE_SWITCH_ACTIVE_FLAG column is set to Y during a trip is sourced by the On-board Diagnostics (OBD) system for the time lapse between the two consecutive time stamp data D_DATA_FEED_TIME_STAMP and the date range between the columns D_TRIP_START_DATE and D_TRIP_END_DATE. |
FCT_TRIP_SUMMARY.N_TOT_NO_OF_BRAKE_IN_EVENT |
The result is the total number of times the brakes were applied by the driver during a trip and stored in the target column. |
| (Total count) |
STG_VEHICLE_DEVICE_EVENT_DTLS.N_HARSH_BRAKING_COUNT |
The total number of times the harsh brakes applied during a trip is sourced by the On-board Diagnostics (OBD) system for the time lapse between the two consecutive time stamp data D_DATA_FEED_TIME_STAMP and the date range between the columns D_TRIP_START_DATE and D_TRIP_END_DATE. |
FCT_TRIP_SUMMARY.N_TOT_COUNT_OF_HARSH_BRAKING |
The result of the total count of harsh braking during a trip is stored in the target column. |
| (The count for the last Year-to-Date period) |
STG_VEHICLE_DEVICE_EVENT_DTLS.N_HARSH_BRAKING_COUNT |
The total number of times the harsh brakes applied during a trip is sourced by the On-board Diagnostics (OBD) system for the date range from the beginning date of the previous financial year to the present day. |
FCT_TRIP_SUMMARY.N_LST_YR_TO_DATE_HARSH_BRAKING |
The result of the total number of harsh braking applied for the last Year-to-Date period is stored in the target column. |
| (Total count) |
STG_VEHICLE_DEVICE_EVENT_DTLS.V_DEVICE_EVENT_TYPE and STG_VEHICLE_MASTER.D_PURCHASE_DATE |
In the event of an accident, the value Accident is stored in the V_DEVICE_EVENT_TYPE column. The total number of accident events occurred during a trip is sourced by the V_DEVICE_EVENT_TYPE column for the date range from the vehicle purchase date (STG_VEHICLE_MASTER.D_PURCHASE_DATE) to the present day. |
FCT_TRIP_SUMMARY.N_TOTAL_COUNT_OF_ACCIDENTS |
The result of the total number of accidents is stored in the target column. |
| (The count for the last Year-to-Date period) |
STG_VEHICLE_DEVICE_EVENT_DTLS.V_DEVICE_EVENT_TYPE |
In the event of an accident, the value Accident is stored in the V_DEVICE_EVENT_TYPE column. The total number of accident events occurred is sourced by the V_DEVICE_EVENT_TYPE column for the date range from the beginning date of the previous financial year to the present day. |
FCT_TRIP_SUMMARY.N_LST_YR_TO_DATE_COUNT_OF_ACC |
The result of the total number of accidents occurred for the last Year-to-Date period is stored in the target column. |
| (Total count) |
STG_VEHICLE_DEVICE_EVENT_DTLS.N_HARSH_ACCELARATION_COUNT |
The total number of times the harsh accelerations occurred during a trip is sourced by the On-board Diagnostics (OBD) system for the time lapse between the two consecutive time stamp data D_DATA_FEED_TIME_STAMP and the date range between the columns D_TRIP_START_DATE and D_TRIP_END_DATE. |
FCT_TRIP_SUMMARY.N_TOT_COUNT_OF_HARSH_ACCELRTNS |
The result of the total count of harsh accelerations during a trip is stored in the target column. |
| (The count for the last Year-to-Date period) |
STG_VEHICLE_DEVICE_EVENT_DTLS.N_HARSH_ACCELARATION_COUNT |
The total number of times the harsh accelerations occurred during a trip is sourced by the On-board Diagnostics (OBD) system for the date range from the beginning date of the previous financial year to the present day. |
FCT_TRIP_SUMMARY.N_LST_YR_TO_DATE_HARSH_ACCLRTN |
The result of the total number of harsh accelerations for the last Year-to-Date period is stored in the target column. |
This section provides information about how the following count of garage visits for the vehicle service is aggregated and derived:
For derivation using aggregation, the following columns are used:
Column Name |
Purpose of this column |
Sample of value(s) stored in the column |
|---|---|---|
STG_VEHICLE_SERVICES_DTLS.V_VEHICLE_ID |
Stores the ID of the vehicle used for the particular trip. · For an individual vehicle, the Vehicle ID is sourced from the STG_VEH_LOCATION_DATA table. · For a fleet of vehicles, the Vehicle ID is sourced from the STG_VEHICLE_TACHOGRPH_DATA table. |
Vehicle1 |
STG_VEHICLE_SERVICES_DTLS.FIC_MIS_DATE |
Stores the date and time on which the Run is processed for the vehicle service details. |
|
STG_VEHICLE_SERVICES_DTLS.D_SERV_START_DATE |
Stores the vehicle service start date. |
|
STG_VEHICLE_SERVICES_DTLS.D_SERV_END_DATE |
Stores the vehicle service end date. |
|
D_DATA_FEED_TIME_STAMP |
Stores the vehicle service real date and time for each second or for a decided unit of time. This gives the time spent by the vehicle in a garage for a selected vehicle service date range. |
|
The result derivation using aggregation is explained as follows.
Result derivation type |
Source Table Name |
Remarks (for Source Table and Column) |
Target Table and Column Name |
Remarks (for Target Table and Column) |
|---|---|---|---|---|
The above-mentioned columns from the STG_VEHICLE_SERVICES_DTLS table are used along with the column STG_VEHICLE_MASTER.D_PURCHASE_DATE. |
The number of vehicle garage visits is accumulated for the period of the vehicle purchase date (STG_VEHICLE_MASTER.D_PURCHASE_DATE) to the present day. |
FCT_TRIP_SUMMARY.N_TOTAL_GARAGE_VISITS |
The total count of garage visits by a vehicle is stored in the target column. |
|
The above-mentioned columns from the STG_VEHICLE_SERVICES_DTLS table are used. |
The number of vehicle garage visits is accumulated for the period of the current financial year. |
FCT_TRIP_SUMMARY.N_ANNUAL_GARAGE_VISITS |
The count of annual garage visits by a vehicle is stored in the target column. |
The following sections contain information about how the key data elements are located and the results are derived for a vehicle:
· Location Co-ordinates at the Start and End of the Trip
· Odometer Readings at the Start and the End of the Trip
· Previous Odometer Reading and the Fuel Used Reset Date
· Last Known Location Information
· Maximum and Minimum Engine RPM during a Trip
This section provides information about how the following vehicle location coordinate values at the trip start and end are found (for the accurate GPS location calculation):
· Longitude
· Latitude
· Altitude
Consider the following 2 use cases for find the longitude, latitude, and altitude.
In this calculation, first step is to arrive at the start of trip, which is done as below:
For an individual vehicle, consider the following:
From the Telematics device, consider the following events, parameters, and values:
1. The Telematics deviceTelematicDevice1 is attached to Vehicle1.
2. The driver with the ID Driver001 starts the vehicle.
3. The trip start date is stored in the D_TRIP_START_DATE column.
4. The trip is completed by the driver.
5. The trip end date is stored in the D_TRIP_END_DATE column.
6. This is considered as one trip represented here by the trip ID Trip001.
For a fleet of vehicles, consider the following:
From the Tachograph device, consider the following events, parameters, and values:
1. The Tachograph device VehTacho1 is attached to Vehicle1.
2. The driver with the ID Driver101 and the tachograph driver ID DrvrCRD1 is appointed.
3. The trip is started by the driver.
4. The trip start date and time are stored in the D_EVENT_START_DATETIME column.
5. As the vehicle ignition status is changed from off to on, the VehTacho1 records this as an event, and the V_TACHOGRAPH_EVENT_TYPE column stores the value EvntTacho001.
6. The trip is completed by the driver.
7. The trip end date and time are stored in the D_EVENT_END_DATETIME column.
8. As the vehicle ignition status is changed from on to off, the VehTacho1 records this as an event, and the V_TACHOGRAPH_EVENT_TYPE column stores the value EvntTacho002.
9. This is considered as one trip represented here by the trip ID Trip001.
The following columns are used for vehicle location derivation along with the main columns in the STG_VEHICLE_DEVICE_EVENT_DTLS table:
Vehicle Coordinate Type |
Source Table Name |
Source Column Name |
Purpose of this column |
Applicable to an individual vehicle or a fleet of vehicles? |
|---|---|---|---|---|
Longitude |
STG_VEH_LOCATION_DATA |
V_VEHICLE_LONGITUDE_VALUE |
Stores the longitude value of the vehicle location. |
Individual vehicle |
Latitude |
STG_VEH_LOCATION_DATA |
V_VEHICLE_LATITUDE_VALUE |
Stores the latitude value of the vehicle location. |
Individual vehicle |
Altitude |
STG_VEH_LOCATION_DATA |
V_VEHICLE_ALTITUDE_VALUE |
Stores the altitude value of the vehicle location. |
Individual vehicle |
Longitude |
STG_VEHICLE_TACHOGRPH_DATA |
V_VEHICLE_LONGITUDE_VALUE |
Stores the longitude value of the vehicle location. |
Fleet of vehicles |
Latitude |
STG_VEHICLE_TACHOGRPH_DATA |
V_VEHICLE_LATITUDE_VALUE |
Stores the latitude value of the vehicle location. |
Fleet of vehicles |
Altitude |
STG_VEHICLE_TACHOGRPH_DATA |
V_VEHICLE_ALTITUDE_VALUE |
Stores the altitude value of the vehicle location. |
Fleet of vehicles |
|
STG_VEHICLE_DEVICE_EVENT_DTLS |
D_DATA_FEED_TIME_STAMP |
Minimum value timestamp is used to identify trip start time. Maximum value timestamp is used to identify trip end time. |
Individual vehicle |
|
STG_VEHICLE_TACHOGRPH_DATA |
D_EVENT_START_DATETIME |
Stores the trip start date and time. |
Fleet of vehicles |
|
STG_VEHICLE_TACHOGRPH_DATA |
D_EVENT_END_DATETIME |
Stores the trip end date and time. |
Fleet of vehicles |
Finding the trip start and end longitude, latitude, and altitude coordinates of the vehicle location is explained as follows.
Business Element |
Trip start or end event |
Target Table Name |
Target Column Name |
Remarks (for an individual vehicle) |
Remarks (for a fleet of vehicles) |
|---|---|---|---|---|---|
Longitude of the vehicle location |
Start |
FCT_TRIP_SUMMARY |
V_TRIP_START_LONGITUDE |
The trip start longitude value is derived from the column STG_VEH_LOCATION_DATA.V_VEHICLE_LONGITUDE_VALUE and stored in the target column. |
The trip start longitude value is derived from the column STG_VEHICLE_TACHOGRPH_DATA.V_VEHICLE_LONGITUDE_VALUE and stored in the target column for the trip start time. |
Longitude of the vehicle location |
End |
FCT_TRIP_SUMMARY |
V_TRIP_END_LONGITUDE |
The trip end longitude value is derived from the column STG_VEH_LOCATION_DATA.V_VEHICLE_LONGITUDE_VALUE and stored in the target column. |
The trip end longitude value is derived from the column STG_VEHICLE_TACHOGRPH_DATA.V_VEHICLE_LONGITUDE_VALUE and stored in the target column for the trip end time. |
Latitude of the vehicle location |
Start |
FCT_TRIP_SUMMARY |
V_TRIP_START_LATITUDE |
The trip start latitude value is derived from the column STG_VEH_LOCATION_DATA.V_VEHICLE_LATITUDE_VALUE and stored in the target column. |
The trip start latitude value is derived from the column STG_VEHICLE_TACHOGRPH_DATA.V_VEHICLE_LATITUDE_VALUE and stored in the target column for the trip start time. |
Latitude of the vehicle location |
End |
FCT_TRIP_SUMMARY |
V_TRIP_END_LATITUDE |
The trip end latitude value is derived from the column STG_VEH_LOCATION_DATA.V_VEHICLE_LATITUDE_VALUE and stored in the target column. |
The trip end latitude value is derived from the column STG_VEHICLE_TACHOGRPH_DATA.V_VEHICLE_LATITUDE_VALUE and stored in the target column for the trip end time. |
Altitude of the vehicle location |
Start |
FCT_TRIP_SUMMARY |
V_TRIP_START_ALTITUDE |
The trip start altitude value is derived from the column STG_VEH_LOCATION_DATA.V_VEHICLE_ALTITUDE_VALUE and stored in the target column. |
The trip start altitude value is derived from the column STG_VEHICLE_TACHOGRPH_DATA.V_VEHICLE_ALTITUDE_VALUE and stored in the target column for the trip start time. |
Altitude of the vehicle location |
End |
FCT_TRIP_SUMMARY |
V_TRIP_END_ALTITUDE |
The trip end altitude value is derived from the column STG_VEH_LOCATION_DATA.V_VEHICLE_ALTITUDE_VALUE and stored in the target column. |
The trip end altitude value is derived from the column STG_VEHICLE_TACHOGRPH_DATA.V_VEHICLE_ALTITUDE_VALUE and stored in the target column for the trip end time. |
This section provides information about finding the following key data elements:
· Odometer reading at the trip start date
· Odometer reading at the trip end date
The following columns are used for finding the odometer reading at the trip start and end date along with the main columns in the STG_VEHICLE_DEVICE_EVENT_DTLS table:
Column Name |
Purpose of this column |
Sample of value(s) stored in the column |
|---|---|---|
N_ODOMETER_READING |
Stores the Odometer reading of the vehicle. |
|
F_TRIP_START_FLAG |
When the trip starts and the ignition status is IRCS, this flag stores the value Y. |
Y |
F_TRIP_END_FLAG |
When the trip ends and the ignition status is OFF, this flag stores the value Y. |
Y |
Finding the odometer reading at the trip start and end date of the vehicle are explained as follows.
Business Element |
Target Table Name |
Target Column Name |
Source table, column and condition |
Remarks (for Target Column) |
|---|---|---|---|---|
FCT_TRIP_SUMMARY |
N_ODOMETER_READING_AT_START |
When the ignition status is IRCS, the F_TRIP_START_FLAG column stores the value Y. The odometer reading of the vehicle from the N_ODOMETER_READING column is used. |
The result is the odometer reading of the vehicle at the trip start date and it is stored in the target column. |
|
Odometer reading at the trip end date |
FCT_TRIP_SUMMARY |
N_ODOMETER_READING_AT_END |
When the ignition status is OFF, the F_TRIP_END_FLAG column stores the value Y. The odometer reading of the vehicle from the N_ODOMETER_READING column is used. |
The result is the odometer reading of the vehicle at the trip end date and it is stored in the target column. |
This section provides information about finding the following key data elements:
· The previous odometer reading
· The fuel used reset date:
In this scenario, the On-board Diagnostics system is expected to set the flag F_ODOMETER_RESET_FLAG to Y. Based on this flag value and date feed time stamp, the data transformation updates the date. With this transformation, the end user can reflect the odometer resets in fuel consumption analysis.
The following column is used for finding the previous odometer reading and the fuel used reset date along with the main columns in the STG_VEHICLE_DEVICE_EVENT_DTLS table:
Column Name |
Purpose of this column |
|---|---|
STG_VEHICLE_DEVICE_EVENT_DTLS.F_ODOMETER_RESET_FLAG |
The Fuel Reset Flag is set to Y whenever the vehicle fuel becomes empty. As a result, this F_ODOMETER_RESET_FLAG flag is set to Y. On-board Diagnostics system is expected to set the flag F_ODOMETER_RESET_FLAG to Y. Based on this flag value and date feed time stamp, the data transformation updates the date. |
Finding the previous odometer reading and the fuel used reset date are explained as follows.
Business Element |
Target Table Name |
Target Column Name |
Source table, column and condition |
Remarks (Result for an individual vehicle) |
Remarks (Result for a fleet of vehicles) |
|---|---|---|---|---|---|
FCT_TRIP_SUMMARY |
N_PREVIOUS_ODOMETER_READING |
When the source column STG_VEH_LOCATION_DATA.F_ODOMETER_RESET_FLAG is set to Y. |
The odometer reading is stored in the target column. |
The current odometer reading for the flag set date is stored in the target column. |
|
Fuel used date is reset |
FCT_TRIP_SUMMARY |
N_FUEL_USED_RESET_DATE_SKEY |
When the source column STG_VEH_LOCATION_DATA.F_ODOMETER_RESET_FLAG is set to Y. |
The SKey of the timestamp is stored in the target column. |
The flag set date is stored in the target column. |
This section provides information about how the last known vehicle location information, when the vehicle is at rest, is found.
The following column is used for finding the last known vehicle location information along with the main columns in the STG_VEHICLE_DEVICE_EVENT_DTLS table:
Column Name |
Purpose of this column |
|---|---|
STG_VEH_LOCATION_DATA.V_STOP_LOCATION_CODE |
Stores the location code of the vehicle where it stopped. NOTE: This is applicable to an individual vehicle. |
The last known location code of the vehicle that is at rest or not on any trip helps an insurer to know of the active time and current location of the vehicle versus the expected normal area of servicing. This helps the insurers to arrive at usage analysis conveying if the vehicle is used for right purpose and is running for a minimum distance required.
Finding the vehicle last known location information is explained as follows.
Business Element |
Target Table Name |
Target Column Name |
Source table, column and remarks |
Remarks (for Target Column) |
|---|---|---|---|---|
Vehicle last known location information |
FCT_TRIP_SUMMARY |
F_LAST_KNOWN_LOCATION_FLAG |
1. For the date in the column STG_VEHICLE_DEVICE_EVENT_DTLS.D_TRIP_END_DATE, the value in the column STG_VEH_LOCATION_DATA.V_STOP_LOCATION_CODE is verified. 2. Then the same location code is verified against the DIM_LOCATION entity. |
If the location code is present in the DIM_LOCATION entity, then the value in the column FCT_TRIP_SUMMARY.F_LAST_KNOWN_LOCATION_FLAG is set to Y, else it is set to N. |
This section provides information about how the following vehicle engine Rotations per Minute (RPM) is found for a trip:
· Maximum RPM
· Minimum RPM
The following column is used for finding the maximum and minimum engine RPM along with the main columns in the STG_VEHICLE_DEVICE_EVENT_DTLS table:
Column Name |
Purpose of this column |
|---|---|
N_ENGINE_RPM_PER_MINUTE |
Stores the value of the vehicle engine Rotations per Minute (RPM). |
Finding the vehicle engine maximum and minimum RPM values are explained as follows.
Business Element |
Target Table Name |
Target Column Name |
Remarks (for Target Column) |
|---|---|---|---|
Maximum RPM |
FCT_TRIP_SUMMARY |
N_MAX_ENGINE_RPM_PER_MINUTE |
The maximum RPM value is stored in the target column. |
Minimum RPM |
FCT_TRIP_SUMMARY |
N_MIN_ENGINE_RPM_PER_MINUTE |
The minimum RPM value is stored in the target column. |
This section provides information about the results derived into the FCT_VEH_PERFORMANCE_SUMMARY table using the telematics and On-board Diagnostics (OBD) system data for a fleet of vehicles and for individual vehicles.
From the base table STG_VEHICLE_DEVICE_EVENT_DTLS, the following list of essential columns is used for derivations.
Column Name |
Purpose of this column |
Sample of value(s) stored in the column |
|---|---|---|
V_TRIP_ID |
Stores the trip ID automatically generated by the telematics device. |
Trip001 |
V_VEHICLE_ID |
Stores the ID of the vehicle used for this particular trip. · For an individual vehicle, the Vehicle ID is sourced from the STG_VEH_LOCATION_DATA table. · For a fleet of vehicles, the Vehicle ID is sourced from the STG_VEHICLE_TACHOGRPH_DATA table. |
Vehicle1 |
V_DRIVER_ID |
Stores the ID of the driver who is driving this vehicle for the current trip. |
Driver001 |
V_TRIP_START_LOCATION_CODE |
Stores the location information, from where the vehicle current trip begins, in the form of a code. |
|
V_TRIP_END_LOCATION_CODE |
Stores the location information, where the vehicle current trip ends, in the form of a code. |
|
D_DATA_FEED_TIME_STAMP |
Stores the real date and time for each second. When an event occurs, the recorded timestamp from this column is used for calculations. |
|
D_TRIP_START_DATE |
Stores the trip start date for a vehicle and the trip. |
|
D_TRIP_END_DATE |
Stores the trip end date for a vehicle and the trip. |
|
N_ENGINE_LOAD_PERCENT |
Stores the percentage of the rated load of a vehicle engine for the trip. |
|
N_TOT_TIME_CONSUMD_IN_IDLE |
Stores the total amount of time during which the vehicle was in the idle condition during a trip. The unit stored is in seconds. |
|
NOTE:
The following sections are applicable to both the individual vehicle and a fleet of vehicle scenarios.
Topics:
· Results Derived using Aggregation
The following sections contain information about how the results are derived using the aggregation:
· Direct Column Result Derivations from the FCT_TRIP_SUMMARY table
· Total and Average Service Costs
· Total Breakdown Time of the Vehicle
This section contains information about the results derived directly from the FCT_TRIP_SUMMARY table columns into the FCT_VEH_PERFORMANCE_SUMMARY table columns.
The type of result derived into the FCT_VEH_PERFORMANCE_SUMMARY table |
FCT_VEH_PERFORMANCE_SUMMARY table column into which the result is derived |
FCT_TRIP_SUMMARY table column from which the result is derived |
Reference to the FCT_TRIP_SUMMARY section that contains the derived aggregation result |
|---|---|---|---|
Cumulative number of hours the engine is utilized for the current financial year beginning date to the present day is accumulated. |
N_CUM_ENGINE_HRS |
N_TOTAL_ENGINE_UTILIZED_HRS |
See the Total number of hours the engine is utilized result derivation type in the Result Derivation of the Driving Minutes and Rest Duration of the Vehicle section. |
Average duration of trips per month for the period of the current financial year to the present day. |
N_AVERAGE_DURATION_OF_TRIP_PM |
N_AVERAGE_DURATION_OF_TRIP_PM |
See the result derivation from aggregation in the Result Derivation of the Average duration of Trips per month section. |
Total trip count for the period of current financial year to the present day. |
N_COUNT_OF_TRIPS |
N_COUNT_OF_TRIPS |
See the result derivation from calculation for the Total count of trips in the Calculation of Total and Average Count of Trips section. |
Average count of trips for the period of the current financial year to the present day. |
N_AVERAGE_COUNT_OF_TRIP_PM |
N_AVERAGE_COUNT_OF_TRIP_PM |
See the result derivation from calculation for the Average count of trips per month in the Calculation of Total and Average Count of Trips section. |
Total count of harsh brakes for the period of vehicle purchase date to the present day. |
N_TOT_COUNT_OF_HARSH_BRAKING |
N_TOT_COUNT_OF_HARSH_BRAKING |
See the result derivation from aggregation of the Total count of harsh brakes in the Result Derivation of the Driving Behavior and the Aggressive Driving Behavior section. |
Last Year-to-Date count of harsh brakes for the period of previous financial year beginning date to the present day. |
N_LST_YR_TO_DATE_HARSH_BRAKING |
N_LST_YR_TO_DATE_HARSH_BRAKING |
See the result derivation from aggregation of the Last Year-to-Date count of harsh brakes in the Result Derivation of the Driving Behavior and the Aggressive Driving Behavior section. |
Total count of accidents for the period of vehicle purchase date to the present day. |
N_TOTAL_COUNT_OF_ACCIDENTS |
N_TOTAL_COUNT_OF_ACCIDENTS |
See the result derivation from aggregation of the Total count of accidents in the Result Derivation of the Driving Behavior and the Aggressive Driving Behavior section. |
Last Year-to-Date count of accidents for the period of previous financial year beginning date to the present day. |
N_LST_YR_TO_DATE_COUNT_OF_ACC |
N_LST_YR_TO_DATE_COUNT_OF_ACC |
See the result derivation from aggregation of the Last Year-to-Date count of accidents in the Result Derivation of the Driving Behavior and the Aggressive Driving Behavior section. |
Total count of accidents for the period of vehicle purchase date to the present day. |
N_TOT_COUNT_OF_HARSH_ACCELRTNS |
N_TOT_COUNT_OF_HARSH_ACCELRTNS |
See the result derivation from aggregation of the Total count of harsh accelerations in the Result Derivation of the Driving Behavior and the Aggressive Driving Behavior section. |
Last Year-to-Date count of accidents for the period of previous financial year beginning date to the present day. |
N_LST_YR_TO_DATE_HARSH_ACCLRTN |
N_LST_YR_TO_DATE_HARSH_ACCLRTN |
See the result derivation from aggregation of the Last Year-to-Date count of harsh accelerations in the Result Derivation of the Driving Behavior and the Aggressive Driving Behavior section. |
Total count of garage visits for the period of the vehicle purchase date to the present day. |
N_TOTAL_GARAGE_VISITS |
N_TOTAL_GARAGE_VISITS |
See the result derivation from aggregation of the Total garage visits in the Result Derivation of the Count of Garage Visits for Vehicle Service section. |
The count of annual garage visits for the period of the current financial year. |
N_ANNUAL_GARAGE_VISITS |
N_ANNUAL_GARAGE_VISITS |
See the result derivation from aggregation of the Annual garage visits in the Result Derivation of the Count of Garage Visits for Vehicle Service section. |
This section provides information about how the following types of service costs of the vehicle are aggregated and derived:
· Total service cost
· Annual service cost
· Part replacement total service cost
· Part replacement annual service cost
· Total labor service cost
· Annual labor service cost
For derivation using aggregation, the following columns are used:
Column Name |
Table Name |
Purpose of this column |
|---|---|---|
N_TOTAL_SERVICE_REPAIR_COST |
FCT_VEHICLE_SERVICES_DTLS |
Stores the total service repair cost of the vehicle per service. |
N_SERVICE_PART_REPLACMNT_COST |
FCT_VEHICLE_SERVICES_DTLS |
Stores the service part replacement cost of the vehicle per service. |
N_SERVICE_LABOUR_COST |
FCT_VEHICLE_SERVICES_DTLS |
Stores the service labor cost of the vehicle per service. |
D_SERV_START_DATE |
STG_VEHICLE_SERVICES_DTLS |
Stores the vehicle service start date. |
D_SERV_END_DATE |
STG_VEHICLE_SERVICES_DTLS |
Stores the vehicle service end date. |
D_PURCHASE_DATE |
STG_VEHICLE_MASTER |
Stores the vehicle purchase date. |
The result derivations using aggregation are explained as follows.
Service cost type |
Source Table and Column Name |
Remarks (for Source Table and Column) |
Target Table and Column Name |
Remarks (for Target Table and Column) |
|---|---|---|---|---|
Total service cost |
FCT_VEHICLE_SERVICES_DTLS.N_TOTAL_SERVICE_REPAIR_COST |
For a vehicle that is serviced, the service cost is aggregated for the period of the vehicle purchase date to the present day. |
FCT_VEH_PERFORMANCE_SUMMARY.N_TOTAL_SERVICE_COST |
The result is the total service cost of the vehicle and it is stored in the target column. |
Annual service cost |
FCT_VEHICLE_SERVICES_DTLS.N_TOTAL_SERVICE_REPAIR_COST |
For a vehicle that is serviced, the service cost is aggregated for the period of the current financial year beginning date to the present day. |
FCT_VEH_PERFORMANCE_SUMMARY.N_ANNUAL_SERVICE_COST |
The result is the annual service cost of the vehicle and it is stored in the target column. |
Part replacement total service cost |
FCT_VEHICLE_SERVICES_DTLS.N_SERVICE_PART_REPLACMNT_COST |
For a vehicle that is serviced, the part replacement service cost is aggregated for the period of the vehicle purchase date to the present day. |
FCT_VEH_PERFORMANCE_SUMMARY.N_TOTAL_PART_REPLACEMENT_COST |
The result is the part replacement total service cost of the vehicle and it is stored in the target column. |
Part replacement annual service cost |
FCT_VEHICLE_SERVICES_DTLS.N_SERVICE_PART_REPLACMNT_COST |
For a vehicle that is serviced, the part replacement service cost is aggregated for the period of the current financial year beginning date to the present day. |
FCT_VEH_PERFORMANCE_SUMMARY.N_ANNUAL_PART_REPLACEMENT_COST |
The result is the part replacement annual service cost of the vehicle and it is stored in the target column. |
Total labor service cost |
FCT_VEHICLE_SERVICES_DTLS.N_SERVICE_LABOUR_COST |
For a vehicle that is serviced, the labor service cost is aggregated for the period of the vehicle purchase date to the present day. |
FCT_VEH_PERFORMANCE_SUMMARY.N_TOTAL_LABOR_COST |
The result is the total labor service cost of the vehicle and it is stored in the target column. |
Annual labor service cost |
FCT_VEHICLE_SERVICES_DTLS.N_SERVICE_LABOUR_COST |
For a vehicle that is serviced, the labor service cost is aggregated for the period of the current financial year beginning date to the present day. |
FCT_VEH_PERFORMANCE_SUMMARY.N_ANNUAL_LABOR_COST |
The result is the annual labor service cost of the vehicle and it is stored in the target column. |
This section provides information about how the total breakdown time of the vehicle is aggregated and derived.
For derivation using aggregation, the following column is used:
Column Name |
Table Name |
Purpose of this column |
|---|---|---|
D_BREAKDOWN_TIME |
FCT_VEHICLE_SERVICES_DTLS |
Stores the breakdown time of the vehicle. |
The result derivation using aggregation is explained as follows.
Purpose |
Source Table and Column Name |
Remarks (for Source Table and Column) |
Target Table and Column Name |
Remarks (for Target Table and Column) |
|---|---|---|---|---|
Total Breakdown Time of the Vehicle |
For the data from these columns V_VEH_IGNITION_STATUS_CD with OFF code, D_TRIP_END_DATE, and D_DATA_FEED_TIME_STAMP (from the STG_VEHICLE_DEVICE_EVENT_DTLS table), the vehicle breakdown time is recorded and stored in the column FCT_VEHICLE_SERVICES_DTLS.D_BREAKDOWN_TIME. |
The number of minutes the vehicle is not running is aggregated for the time lapse between the two consecutive time stamp data D_DATA_FEED_TIME_STAMP when the engine is OFF (corresponding to the time lapse between FCT_VEHICLE_SERVICES_DTLS.D_BREAKDOWN_TIME and the vehicle ignition IRCS time). |
FCT_VEH_PERFORMANCE_SUMMARY.N_TOTAL_BREAKDOWN_TIME_MIN |
The result is the total amount of vehicle breakdown time in minutes and it is stored in the target column. |
The following sections contain information about how the key data elements are located, and the results are derived for a vehicle:
· Initial and Previous Odometer Reading
This section provides information about how the odometer flag reset value is found.
The following column is used along with the main columns in the STG_VEHICLE_DEVICE_EVENT_DTLS table:
Column Name |
Purpose of this column |
|---|---|
STG_VEHICLE_SERVICES_DTLS.F_ODOMETER_RESET_FLAG |
The Fuel Reset Flag is set to Y whenever the vehicle fuel becomes empty. As a result, this F_ODOMETER_RESET_FLAG flag is set to Y. In the current scenario, the F_ODOMETER_RESET_FLAG flag is set to Y during the vehicle service. |
Finding the odometer flag reset value is explained as follows.
Business Element |
Target Table Name |
Target Column Name |
Source table, column and condition |
Remarks (for Target Column) |
|---|---|---|---|---|
Odometer flag reset value |
FCT_VEH_PERFORMANCE_SUMMARY |
F_ODOMETER_RESET_FLAG |
When the STG_VEHICLE_SERVICES_DTLS.F_ODOMETER_RESET_FLAG column is set to Y. The date is verified and the current odometer reading is reset. |
The result of odometer flag reset value is stored in the target column. |
This section contains information about how the following key data elements are derived:
· Initial odometer reading
· Previous odometer reading
The results are derived directly from the FCT_TRIP_SUMMARY table columns into the FCT_VEH_PERFORMANCE_SUMMARY table columns.
The business element derived into the FCT_VEH_PERFORMANCE_SUMMARY table |
FCT_VEH_PERFORMANCE_SUMMARY table column into which the result is derived |
FCT_TRIP_SUMMARY table column from which the result is derived |
Reference to the FCT_TRIP_SUMMARY section that contains the derived aggregation result |
|---|---|---|---|
Initial odometer reading of the vehicle. |
N_ODOMETER_READING |
N_ODOMETER_READING_AT_START |
See the vehicle Odometer reading at the trip start date section (in the How the Odometer Readings at the Start and the End of the Trip are found section), which is derived into the result column FCT_VEH_PERFORMANCE_SUMMARY.N_ODOMETER_READING. |
Previous odometer reading. |
N_PREVIOUS_ODOMETER_READING |
N_PREVIOUS_ODOMETER_READING |
See the vehicle previous odometer reading section (in the How the Previous Odometer Reading and the Fuel Used Reset Date are found section), which is derived into the result column FCT_VEH_PERFORMANCE_SUMMARY.N_PREVIOUS_ODOMETER_READING. |
This section provides information about how the age of the vehicle is found.
Column Name |
Purpose of this column |
|---|---|
STG_VEHICLE_MASTER.D_PURCHASE_DATE |
Stores the purchase date of the vehicle. |
Finding the age of the vehicle is explained as follows.
Business Element |
Target Table Name |
Target Column Name |
Remarks |
|---|---|---|---|
Age of the vehicle |
FCT_VEH_PERFORMANCE_SUMMARY |
N_VEHICLE_AGE |
The difference between the D_PURCHASE_DATE of the vehicle and the current date results in the age of the vehicle and it is stored in the target column. |
This section provides information about how the average engine load percent of the vehicle is found.
Column Name |
Purpose of this column |
|---|---|
N_ENGINE_LOAD_PERCENT |
Stores the percentage of the rated load of a vehicle engine for the trip. The On-board Diagnostics (OBD) system is expected to provide the percentage of vehicle engine load when driving. This data transformation accumulates the engine load percentage to get the total amount of vehicle engine load percentage. |
Finding the average engine load percent of the vehicle is explained as follows.
Business Element |
Target Table Name |
Target Column Name |
Remarks (for Source) |
Remarks (for Target Column) |
|---|---|---|---|---|
Average engine load percent of the vehicle |
FCT_VEH_PERFORMANCE_SUMMARY |
N_ENGINE_LOAD_PERCENT |
For the period of the current financial year start date and the present day: 3. The total amount of vehicle engine load percentage, which is sourced by the On-board Diagnostics (OBD) system is accumulated. 4. The output is divided by the number of trips. |
The result is the average engine load percent for each trip for the current financial year and it is stored in the target column. |
This section provides the driver behavior information during a trip derived from the telematics and On-board Diagnostics (OBD) system data.
This section contains information about the result derivations in the FCT_DRIVER_BEHAVIOUR_SUMMARY tables. The data into the FCT_DRIVER_BEHAVIOUR_SUMMARY columns are derived from the FCT_TRIP_SUMMARY table columns.
The column in the FCT_TRIP_SUMMARY table |
Purpose of the column |
The resultant column in the FCT_DRIVER_BEHAVIOUR_SUMMARY table |
|---|---|---|
N_TOT_IDLE_TIME_ON_TRIP |
Stores the calculated total time idle on a trip. |
N_DRIVER_IDLE_TIME_ON_TRIP |
Stores the total number of braking events during a trip. |
N_NO_OF_BRAKES_APPLIED |
|
Stores the maximum engine RPM value. This provides the maximum speed reached. |
N_MAX_ENGINE_RPM_PER_MINUTE |
|
Stores the total amount of time spent smoking by the driver when driving during a trip. |
N_TOT_NO_OF_MIN_SMKNG_WHL_DRVN |
|
Stores the total amount of time spent using the cell phone by the driver when driving during a trip. |
N_TOT_MIN_CELL_USG_WHL_DRVNG |
|
Stores the total amount of time spent by the driver texting on the phone when driving during a trip. |
N_TOT_MIN_TEXTING_WHL_DRIVE |
|
Stores the cumulative distance covered to date. |
N_CUM_DIST_COVRED_IN_TRIP_MTRS |
|
Stores the cumulative amount of the fuel used on a trip. |
N_CUM_FUEL_CONSUMED_IN_LTRS |
|
Stores the total number of vehicle engine utilization hours. This is the total driving hours. |
N_CUM_HR_ON_DRIVING |
|
Stores the average speed of the vehicle during a trip. |
N_AVG_SPEED_OF_VEH_ON_TRIP |
|
Stores information about the current duration of a trip for a vehicle. |
N_AVG_HRS_SPNT_PER_TRIP |
This section contains information to locate the key data elements and derive results.
This section provides information about how the age of the driver is found.
Finding the age of the driver is explained as follows.
Business Element |
Target Table Name |
Target Column Name |
Remarks (for Source) |
Remarks (for Target Column) |
|---|---|---|---|---|
Age of the driver |
FCT_DRIVER_BEHAVIOUR_SUMMARY |
N_DRIVER_AGE |
The vehicle driver age is derived from the STG_PARTY_MASTER table by using the FIC_MIS_DATE. |
The result is stored in the target column. |
All RDBMS related Result tables can also be deployed on Hive (Stage and Results). Deploy the Hive T2Ts using the Rules Run Framework. For more information, see the Rules Run Framework section in the Oracle Financial Services Advanced Analytical Applications Infrastructure User Guide Release 8.1.1.0.0.
NOTE:
In general, Stage and Result tables are also supported in Hive. However, there are some exceptions. For a list of tables that are not supported in Hive, see List of Unsupported T2Ts
Follow this SCD process to populate data into a Dimension table:
NOTE:
You can also follow this SCD process to populate data into any Hive-related Dimension table.
1. To populate data into a Dimension table, execute the SCD batch. For a detailed procedure, see the Slowly Changing Dimension (SCD) Process.
2. To check the SCD batch execution status of a Dimension table, follow the procedure Check the Execution Status of the SCD Batch.
3. To verify log files, and check the error messages (if any), follow the procedure Verify Log Files and Check Error Messages.
Follow this T2T process to populate data into any T2T Result table:
NOTE:
Only RDBMS T2Ts can be executed using the PMF.
1. To populate data into any T2T Result table, execute the PMF process for that T2T. For a detailed procedure, see the following sections:
a. Prerequisites for loading T2T.
b. Select the Run Parameters and Execute the Run.
2. To check the T2T execution status and verify the log files of any Result table, follow the procedure in the Verify the Run Execution section.
3. To check the error messages, if any, follow the procedure in the Check Error Messages section.
You can see the following topics related to other function-specific tables: