0 mins to read
Best Practices for Large Fleet Reporting
0 mins to read
Learn more about the best practices to follow for large fleet reporting, including recommendations on improving performance for both scheduled and on-demand reports, as well as ideas on how to improve their scalability. Discover new insights and tips on how to ensure custom rules are leveraged efficiently, along with information about bulk rule deletions and reprocessing.
Minor grammatical edits, signals formatting added for Important, Note, and Caution text blocks
Purpose section added, Scalability section updated
Added Customization Checklist
This document outlines the best practices to follow for large fleet reporting. It includes recommendations on improving performance for both scheduled and on-demand reports, as well as ideas on how to improve their scalability. Insights and tips on how to ensure custom rules are leveraged efficiently are provided, along with information about bulk rule deletions and reprocessing.
! IMPORTANT: It is recommended that only experienced MyGeotab users are granted access to create custom reports (by the “Manage custom reports” security clearance) and custom rules (by the “Administer exception rules” security clearance)
Reports are one of the best tools available in MyGeotab. They contain aggregated data that provide insight of all things in the database and can be sent to the intended users. You can use standard reports to monitor activities such as deliveries per driver, average fuel consumption, drivers who speed, or to identify those drivers who frequently arrive late and depart early. If the range of standard reports does not meet your needs, you can create limitless custom reports in Microsoft Excel. Downloading your reports as PDF or Microsoft Excel files gives you further ability to examine and work with your data.
Some points to keep in mind in regards to reporting:
Reports can be scheduled to be sent to a determined number of users within the database at different times as needed. Both Dashboard reports and Emailed reports are scheduled reports. The Product Guide offers the tasks required to set these reports up.
If a database has multiple large reports, it is best to schedule them to be sent at different times to ensure the reports processor has enough time to start creating the reports and sending them at the required time.
The scheduled time is when the reports processor starts processing the report; when a report finishes processing depends on its size. Larger more detailed reports will take longer to process. That is why it is recommended that one does not create large scheduled reports that all run at the same time on a given day. Scheduling large reports at intervals of 30 minutes to 1 hour will help limit the load on the reports processor at a given time.
The best time to schedule large reports to run is when the database is the most inactive and not in use. This can vary from customer to customer, but a good recommended timeframe is the same window the Geotab ServerOps team uses for maintenance (11 p.m. to 6 a.m. ET).
In order to preserve the quality of service when using the application there are a number of limitations applied to the number of emails which can be sent at a time, and the size of each individual message.
Note: If an email limit is exceeded, any further emails will be disabled for 60 minutes
All of these limits must be satisfied for an emailed report to send. For example, if a report has a size of 1 MB, it can be sent to up to 1000 users. However, if the report has a size of 20 MB, it can only be sent to up to 50 users.
For reports that need to be sent to a very large number of users and that exceed the emailed reports limits, it is recommended that a customer use a distribution list within their own organization to send these reports which has the email provider resend these reports (ex. firstname.lastname@example.org, where “fleetmanagers” is an emailed distribution list of “company”).
A report will be disabled if it violates the email limitations or becomes too slow to open. If this happens, the following notification will be displayed:
If the report is disabled, check the size of the report by running it on-demand and downloading it manually. Confirm that it does not exceed the email limit criteria. If the report conforms to the criteria, see the Scalability section below for other troubleshooting steps.
Dashboard reports can provide required reporting with proper images on the dashboard of any database. They are scheduled reports and can be downloaded at any time.
The refresh rate of a dashboard report should be chosen to logically reflect what data needs to be shown. It is important to not load the server with report requests that run every day or multiple times a day that are not needed. For example running a report that pulls data for the past 7 days with a refresh period of a day makes sense. However, a dashboard report that is looking at data from the last month but has a refresh rate of every 30 minutes does nothing more than tax the server.
✱ NOTE: In testing a dashboard report, it is important to verify that the amount of time the report takes to load is not greater than the set refresh rate of the report. If the report refresh rate is less than the load time, the dashboard report will not display anything.
Reports can be run manually on any database without the need of scheduling. However it is still important to keep in mind the amount of data an on-demand report has to process. On-demand reports can time out in some cases, especially if they are run when the database is very active.
For example, running an engine measurements report for all the vehicles in a fleet for a large period of time will most likely make the database time out and not provide the information. The recommendation in cases like these is to schedule these large data reports instead.
There is a workaround for running on-demand reports when they time out. See the Community post: MyGeotab - Workaround for long running/large reports
In large fleet databases, to reduce performance issues, loading time, and to avoid exceeding the report row limit the following steps can be taken into account:
If performance issues persist, the following troubleshooting steps can be implemented:
CAUTION! Do not edit the column headings on the Data sheet of a report. This could render the report corrupt and unusable. If deleting columns on the Data sheet, do not delete columns that are used as the main measurement in the report. Examples include but are not limited to Device, User, Date/Time, etc. Ensure to also delete the columns on the Report sheet that referenced the now deleted Data columns.
Excel reports have shown some limitations where another reporting approach must be considered. These limitations are:
If the resulting report exceeds these limits, the SDK can be used to create a CSV with the required data. You can get an idea of how to do this in the following link:
MyGeotab Excel processor only understands formulas from Excel 2013 or older. Some formulas from Excel 2016, 2017 or newer will not work for automated dashboards. This means that the content will not be updated until the report is downloaded and run on Excel. For example:
Rules are one of the most important and used features across MyGeotab. When used properly they can provide valuable insight into areas of opportunity for the whole fleet; however if used incorrectly they can create numerous issues for a database. Some useful considerations in regards to rule creation are:
Reprocessing rules is a useful tool when used correctly as new rules that have been thought of after becoming more experienced with the MyGeotab platform can be retroactively applied to vehicles to evaluate performance. Reprocessing rules will take a long time to run as they only run during maintenance hours (11 p.m. to 6 a.m. ET, provided the date range is more than a week), and therefore it is recommended to run them for a subset of vehicles and for a short period of time.
An item to keep in mind is that the reprocess timeline is based on how long you set the date range to reprocess. If the date range is set for less than a week, the reprocess gets queued up by the back end immediately. Anything more than a week and the reprocess is pushed towards the database off peak hours.
! IMPORTANT: It is imperative that a large number of devices should not be reprocessed in a single batch if the reprocess time period is less than a week, as the reprocess will be queued up immediately and may cause performance issues to the database.
When editing a condition of a rule, the MyGeotab UI does present a warning that all the previous instances of the rule will be deleted. The item that needs to be considered here is the size of the database and how many exceptions are linked to the rule in question. If the number of exceptions linked to the rule is exceptionally large then the deletion process that happens in the back end could cause stress/lag to the system (especially if multiple large occurrence rules are deleted at the same time). Hence if there are any kind of mass deletes that need to be executed, the customer should reach out to Technical Support who will coordinate them with the Server Operations team. Then each batch of deletions that needs to be executed will be done in an incremental manner with the Server Operations engineer giving the go-ahead for the next batch. It is also advisable that these deletions be done during off peak hours of the database so there is minimal impact to the MyGeotab portal.
Hidden Data sheet has only necessary columns
Formulas should not reference anything on the Hidden Data Sheet
Formulas are not hard coded to the range in template, but flexible to encompass the entire possible range
Dashboard Graphics include date scope of the report
While testing, the report is not hitting our 100,000 row limit
Macros are not used for a Dashboard Report
No Formulas launched after 2013 are used for dashboard reports