February 2022

Purpose

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.

Reports

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:

1. Be aware of report limitations and leverage correct groups and data access segregation to send the same report to the entire fleet.
2. If there's a need for additional data, verify what is included in the Data tab of the report.
3. A report can be run manually, it can be shown on the Dashboard or it can be emailed based on a schedule to identified users.
4. Additional reports requests for Custom reports or new data for Default reports can be made with the Solutions Engineering team or Support.

Scheduled Reports

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).

Emailed Report Limits

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.

1. Attachments cannot exceed 20 MB
2. The total data usage of emails per hour cannot exceed 1000 MB (1 GB)
3. The total number of emails sent in an hour per rule cannot exceed 1000
4. The total number of emails sent in an hour per report cannot exceed 1000

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. fleetmanagers@company.com, where “fleetmanagers” is an emailed distribution list of “company”).

Disabled Reports

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

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.

On-Demand Reports

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

Scalability

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:

1. Use the more summarized built-in report that still provides the required data. For example: Risk Management or Exceptions Summary instead of Exceptions Detail or Trips Summary instead of Trips Detail.
2. The reports that provide log type data (e.g. Engine Status, Debug or Audit Log) should not be used unless the data in the report can be filtered down to infrequent logs. Filtering the data should be done at the MyGeotab level and not within the Excel report when possible (e.g. only having the useful exceptions included in the report settings for the custom report).

If performance issues persist, the following troubleshooting steps can be implemented:

3. Check if any template macros used in the report are functioning correctly, as they may not scale well
4. Check if any unnecessary calculations are being performed on the Report sheet, and remove as necessary
5. Remove any columns in the Report/Data sheet that is not needed for the report to work
6. Reduce the date range of the data pulled
7. Split up the report into multiple reports that run for different groups or smaller date ranges at different times

Excel Reporting Limitations and SDK Reporting

Excel reports have shown some limitations where another reporting approach must be considered. These limitations are:

1. Maximum of 100,000 rows of data
2. Files with over 30,000 rows of data can slow down the system and impede working performance with them
3. Only capable of calling data from columns in a single data table, therefore it cannot combine columns from different reports

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:

Using the MyGeotab python API to create custom reports

Excel Version Compatibility and Formulas

MyGeotab Excel processor only understands formulas from Excel 2021 or older. Any Formulas that Microsoft Excel will launch after this version may not work on the dashboard reports. This means that the content will not be updated until the report is downloaded and run on Excel.

Currently, the only limited Excel FUNCTIONS (formulas) in the Geotab ecosystem are:

1. WEBSERVICE
2. INFO
3. FILTERXML
4. IMPORTXML
5. IMPORTFEED
6. IMPORTHTML
7. IMPORTRANGE
8. IMAGE

Rules

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:

1. Check if the use case can be solved without a custom rule (ex. Instead of creating 3 different speeding rules, change the Risk Management report Speed Bands in System Settings), and check if the use case is already solved by another user’s custom rule.
2. Check if the custom rule already exists in the database.
3. Add more than one condition to the rule (ex. adding a time duration to a speeding rule).
4. Use the grouping structure to apply specific rules to the desired group of vehicles.
5. Test rules in phases to make sure the logic is sound - apply the custom rule to a smaller group of vehicles first and monitor if the right number/type of exceptions are generated.

Reprocessing Rules

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.

Rule Edits/Deletes

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.

Customization Checklist