Save more time and money with the MyGeotab API Adapter Data Optimizer

Solution overview

The MyGeotab API Adapter (API=Application Programming Interface) is an open-source solution that utilizes the Geotab API to stream data from a MyGeotab database into a SQL Server, PostgreSQL, Oracle or SQLite database in your environment. The Data Optimizer enhances the API Adapter by optimizing the data for downstream consumption. It helps developers to address one of the primary challenges faced by those seeking to integrate with Geotab — linking discrete data points from various sources.

This post provides high-level information about the solution, along with links to additional resources.

What is the API Adapter?

The MyGeotab API Adapter uses data feeds to pull the most common data sets from a MyGeotab database (GCP=Google Cloud Platform) and stream the data into tables within a PostgreSQL, SQL Server, Oracle or SQLite database. It is comprised of a configurable application and a database, which are deployed in your environment, as shown in the following diagram:

The MyGeotab API Adapter uses the Geotab API to write to or retrieve data from the relevant MyGeotab database instance(s).

The API Adapter application itself is platform-independent. Pre-packaged versions are made available for Windows and Linux-based environments with each release that is published to GitHub. Scripts are provided for each of the supported database types.

Types of data that can be retrieved

Geotab data extracted by the API Adapter is largely classified into one of two categories — reference data and feed data, each of which is described below.

Reference data

Reference data includes data that changes infrequently, or that is static in nature. This is generally user-added data. Records in reference data tables can change over time, and only the latest version of each record is maintained. Record counts in reference data tables tend to be small and relatively stable over time. 

Feed data

Feed data generally consists of data points collected from vehicles via Geotab GO or other devices. Records in feed data tables are not modified once written to the database. These tables can accumulate vast quantities of records within short periods.

List of tables in the adapter database

The following is a list of reference data and feed data tables included in the adapter database. It provides the data category assignment for each table along with a description linking to the corresponding Geotab API object type(s) from which the data is obtained. Additional tables may be added in the future. Refer to the List of Tables in the main MyGeotab API Adapter guide for the latest list at any given time.

Staging database limitations

The adapter database has been designed as a staging database to allow data streamed through the API Adapter to be written to tables as quickly as possible. As such, there are several limitations, including:

• No physical relationships have been implemented between tables where logical relationships exist.
• The GeotabId columns, which represent object identifiers in the Geotab system, are character-based and not indexed.
• No data clean-up mechanisms have been implemented to prevent unrestricted database growth.

As a result of this design, it is not viable to have applications connect directly to the adapter database, and querying it directly or adding views for analysis purposes is ill-advised. Instead, it is necessary to implement a process that moves data from the adapter database into a data warehouse or data lake, as outlined in the solution and implementation guide.

This is where the Data Optimizer really delivers value. 

What is the Data Optimizer?

The Data Optimizer enhances the adapter solution, by following the suggested strategy outlined in the API Adapter guide. It includes an application that migrates data from the adapter database into an “optimizer database” that can then be queried directly by applications. 

Additional services are included to enhance the data and overcome certain challenges such as linking data points with different timestamps from different tables. For example, the StatusData and FaultData tables have added Latitude, Longitude, Speed, Bearing, Direction and DriverId columns that can optionally be populated using LogRecords and interpolation techniques. By extension, in addressing these challenges, the Data Optimizer can add considerable time and cost savings, allowing integrators to focus on their business instead of the mechanics of extracting and harmonizing data from their MyGeotab database.

The following diagram provides an overview of the Data Optimizer architecture integrated within the overall MyGeotab API Adapter solution.

The Data Optimizer is a separate application consisting of two types of services–processors and optimizers–and an optimizer database. 

Optimizer Database

The optimizer database contains tables similar to the adapter database but with added indexes to facilitate the creation of custom views and direct querying by applications or integrations. Some of the tables contain extra value-added columns that the optimizer services can optionally populate. Although named the “optimizer database,” the tables all have slightly different names than their “adapter database” counterparts. Both sets of tables can technically be included in the same physical database if desired.

Processors

Processor services iteratively move batches of data from tables in the adapter database to their counterpart tables in the optimizer database. Once a batch of records has been successfully written to an optimizer database table, those records are deleted from the corresponding table in the adapter database.

Optimizers

Optimizer services perform any processing necessary to populate value-added columns that have been added to tables in the optimizer database. For example, the StatusData Optimizer can be configured to populate the Latitude, Longitude, Speed, Bearing, Direction and DriverId columns that have been added to the StatusDataT table.

The processor and optimizer services are highly-configurable and can even be run on separate machines in order to improve the performance of individual services and operate within hardware limits.

Deployment options

Given the highly-configurable nature of the MyGeotab API Adapter solution, there are numerous possibilities when it comes to deploying the solution in a physical environment. This section outlines a few of the possible deployment options.

One application server and one database

The diagram above is one of the simplest MyGeotab API Adapter deployment options. In this scenario, the adapter and optimizer applications are both installed on a single application server, while the adapter database tables and optimizer database tables are deployed to a single physical database. In this scenario, SQL Server is currently the only database option since the optimizer database only supports SQL Server.

One application server and two databases

While it is technically possible to use a single physical database to host the adapter and optimizer tables, this approach is likely to prove impractical. Due to the number of different applications and processes simultaneously accessing the database, the performance of the database and any downstream integrations will be sub-optimal. To address this concern, the adapter and optimizer tables can be separated into two physical databases, as shown in the above diagram. This also introduces the possibility of different database types. While the optimizer database can only be SQL Server (for now), the adapter database could be any one of the supported database types. See the MyGeotab API Adapter linked earlier in this document.

Two application servers and two databases

While forming part of a single overarching solution, the core MyGeotab API Adapter and the Data Optimizer are, in fact, two distinct executable applications.

Given that large quantities of data are likely to be processed on a sustained basis by both applications, it may make sense to run them on two separate machines, as shown in the diagram above. This type of configuration can facilitate greater performance and throughput by making more computing resources available to each application than might otherwise be available if both applications are sharing the resources of a single application server.

Three or more application servers and two databases

For implementations involving larger fleets, the Data Optimizer application can be distributed across a number of physical application servers (as shown in the previous diagram) to potentially achieve even greater performance and throughput.

The future of the Data Optimizer

In the initial release of the MyGeotab API Adapter solution, including the Data Optimizer, data is moved from only a limited set of the adapter database tables to corresponding tables in the optimizer database. Future releases will include the processing of additional tables until all tables are eventually handled. Additional optimizer services will also be included in future releases. Tables will include more value-added columns, linkages and indexes. Ultimately, the Data Optimizer will simplify work for those seeking to integrate Geotab data into their data warehouses, data lakes, applications, and data science initiatives.

More information

To learn more about the MyGeotab API Adapter solution, please refer to the following:

• MyGeotab API Adapter on GitHub (source code and published releases)
• MyGeotab API Adapter — Solution and Implementation Guide
• MyGeotab API Adapter — Data Optimizer  Solution and Implementation Guide
• MyGeotab API Adapter (presentation)

Get notified about new releases

Any time a new release of the MyGeotab API Adapter is published to GitHub, an update will be posted to Geotab’s Integrator’s Hub. To stay current on new releases, click the Join Group button on the Integrator’s Hub page to join, and choose the desired notification frequency (Every Post, Daily Digest, Weekly Digest, etc.)

Need more information about using Geotab APIs to synchronize your fleet management platform with other data-driven applications? Visit the MyGeotab Software Integration page.