Support Document

0 mins to read

User Guide

EN - CS-CZ - DE-DE - ES-ES - ES-LATAM - FR-CA - FR-FR - IT-IT - JA-JP - KO-KR - NL-NL - PL-PL - PT-BR - SV-SE

Updated March 2024




Introduction

The Geotab Data Connector is a tool designed for fleet managers to import curated data from numerous Geotab data sources, sourced from their own fleet, into their preferred BI/visualization tool. The tool allows fleet managers to access aggregated data in their preferred BI tool without having to manually leverage MyGeotab reports. In addition, unlike the MyGeotab SDK, this tool allows fleet managers to customize their reports without the need for coding.


Additional Resources

Resource

Description

Link

Data Schema and Dictionary

Detailed definitions of the columns available in each of the data assets.

Geotab Data Connector – Data Schema and Dictionary

Template Guide and Library

This document provides an overview of BI tool templates and instructions on how to use and download the templates.

Geotab Data Connector – Template Guide

Geotab Data Connector Predictive Safety Analytics and Benchmark User Guide



This document explains two pivotal aspects of modern fleet management: Predictive Safety Analytics and Safety Benchmarking.

Geotab Data Connector Predictive Safety Analytics and Benchmark User Guide

Geotab Data Connector Maintenance User Guide

This document details the individual maintenance-related fault codes included such as Electrical System Ratings, Water in Fuel, Anti-lock Brake Systems, Engine Coolant, and more.

Geotab Data Connector Maintenance Insights User Guide

Geotab Data Connector - Linking Multiple Databases Guide (PowerBI only)

This document outlines a procedure for connecting up to 7 databases to the same Power BI dashboard.

Geotab Data Connector Linking Multiple Database Use Case Guide


Requirements

  1. Valid MyGeotab credentials (database name, username, and password).
  2. The Data Connector Add-In is enabled in MyGeotab.
    1. You can check the Add-In status on the Data Connector Marketplace page.
    2. You can enable Data Connector directly from Marketplace, or contact your reseller if you don’t have access to Marketplace.
    3. If the Add-In is installed, you should be able to see it on MyGeotab on the side menu or under the Administration > Geotab Data Connector (Requires Security Clearance - Launch Custom Reports or Add-Ins and View “Geotab Data Connector” Add-In). If you don’t see this menu option in MyGeotab, please contact your database administrator or reseller.
  3. One of the following supported BI/visualization tools:


Tool Name

Supported

Microsoft Power BI

Microsoft Excel (via Get Data/Power Query)

Tableau

MicroStrategy

SAP Business Objects


NOTE: The Geotab Data Connector officially supports only the latest version of the above tools, but will likely work for some older versions as well. However, if you are having issues with an older version, we might not be able to provide you with support.

The Geotab Data Connector allows users to connect to their data using OData. In order to connect, you must authenticate with your MyGeotab credentials. Keep in mind that the access URL may differ based on your MyGeotab database.

! IMPORTANT: The above table only indicates that the connection method was officially tested with the Geotab Data Connector Add-In. Although some of the listed tools that are marked unsupported may provide support for OData, we cannot guarantee functionality when using a supported tool with an unsupported connection method.


Start with Dashboard Templates

We provide dashboard templates for quick and impactful insights to accelerate your dashboard development. You can get started by using one of the sample dashboards that we provide by loading the dashboard, entering your MyGeotab credentials, verifying the Access URL, and refreshing the data. All of our templates can be found in the Geotab Data Connector Add-In. For more information on how to use the templates and a complete list of available templates, refer to the Template Guide.

Access

General access via OData protocol

Find Your Base Access URL

For Power BI and Excel users, you are able to use the unified access URL as both Power BI and Excel support server redirection.

! IMPORTANT: For Tableau users, the Data Connector needs to be accessed through an alternate access URL as Tableau does not support server redirection. The URL can be found in the Geotab Data Connector Add-In. The URL contains a specific serverNumber to be used for accessing the Data Connector.

Base Access URL

  1. Simplified URL for Power BI and Excel: https://data-connector.geotab.com/odata/v4/svc/
  2. For Tableau-specific URLs - for databases on Data Connector server <serverNumber>: https://odata-connector-<serverNumber>.geotab.com/odata/v4/svc/
  3. Username: <MyGeotab Database Name>/<MyGeotab Username>

    Password: <MyGeotab Password>

    Login Example

    MyGeotab Login Details

    Data Connector Login Details

    BI tool: Power BI or Excel



    MyGeotab Database Name: other_dbname

    MyGeotab Username: email@domain.com

    MyGeotab Password: SecurePassword1234

    Access URL: https://data-connector.geotab.com/odata/v4/svc/


    Username: other_dbname/email@domain.com

    Password: SecurePassword1234

    BI tool: Tableau

    Data Connector Server: 1


    MyGeotab Database Name: geotab_dbname

    MyGeotab Username: email@domain.com

    MyGeotab Password: SecurePassword1234

    Access URL: https://odata-connector-1.geotab.com/odata/v4/svc/


    Username: geotab_dbname/email@domain.com

    Password: SecurePassword1234

    Specify Table and Date Range Filter on Access URL

    For operational reporting, we recommend applying date range filters to specific tables/resources. Using date range filters on historical/date-based tables results in faster data returns due to the results being pre-filtered before being imported to the BI tool.

    The date range filter applies to the local date/datetime (based on the time zone setting of each telematics device in MyGeotab).

    We currently support two forms of date range filters: absolute and relative date ranges. We recommend using relative date ranges for typical dashboarding, and absolute date ranges for ad-hoc analyses.

    When using date range filters, you must explicitly name the table name or resource.

    Access URL

  4. Simplified URL for Power BI and Excel: https://data-connector.geotab.com/odata/v4/svc/[tableName]?$search=<dateRange>
  5. For Tableau-specific URLs - for databases on Data Connector server <serverNumber>: https://odata-connector-<serverNumber>.geotab.com/odata/v4/svc/[tableName]?$search=<dateRange>

Supported tables

Only historical/date-based tables can be used with date range filters. The following table/resource names support date-range filters:

  1. VehicleKpi_Hourly
  2. VehicleKpi_Daily
  3. VehicleKpi_Monthly
  4. FleetSafety_Daily
  5. VehicleSafety_Daily

Absolute date range

We recommend using an absolute date range for ad-hoc or static analyses. When using an absolute date range, data is pre-filtered on the server for the specified date range when returning results into the BI.

Method 1: specifying a from and to date

  1. This filters for data records that have a local date between the specified from and to date.
  2. Syntax for dateRange: from_yyyy-MM-dd_to_yyyy-MM-dd
  3. Method 2: specifying only a from date

  4. This filters for data records that have a local date between the specified from date until the newest available date.
  5. Syntax for dateRange: from_yyyy-MM-dd

Some examples are listed below. For Power BI and Excel users, the access URL’s subdomain should be simplified to https://data-connector.geotab.com.

Scenario

Access URL

Data Connector Server: 1

Desired table: VehicleKpi_Daily

From date: 2022-01-05

To date: 2022-01-19

https://odata-connector-1.geotab.com/odata/v4/svc/VehicleKpi_Daily?$search=from_2022-01-05_to_2022-01-19

Data Connector Server: 2

Desired table: VehicleKpi_Hourly

From date: 2022-01-23

To date: most recent

https://odata-connector-2.geotab.com/odata/v4/svc/VehicleKpi_Hourly?$search=from_2022-01-23

Relative date range

We recommend using relative date ranges for a better experience with regularly-refreshed dashboards. When using a relative date range, data is pre-filtered on the server for the specified rolling window of time, meaning your dashboards will always display the most recent information. Relative date ranges allow you to get, for example, daily vehicle level data for the most recent 30 days, hourly level data for the most recent 14 days, and so on.

Supported position keywords: Controls whether to include an impartial date part/interval.

  1. this (these)
  2. last
  3. Supported date parts: The date part or interval that controls the filtering of data.

  4. day
  5. week
  6. month
  7. year

Syntax for dateRange: <positionKeyword>_<number>_<datePart>

  1. Note that when the <number> argument is 1, it can be completely omitted and changed to <positionKeyword>_<datePart> for convenience

Listed below are some sample generic, commonly-used relative date range filters. For Power BI and Excel users, the access URL’s subdomain should be simplified to https://data-connector.geotab.com.

Scenario

Access URL

Data Connector Server: 1

Desired table: VehicleKpi_Monthly


Today’s date: 2022-01-23

Desired date range: “year-to-date”

https://odata-connector-1.geotab.com/odata/v4/svc/VehicleKpi_Daily?$search=this_1_year

Data Connector Server: 1

Desired table: VehicleKpi_Monthly


Today’s date: 2022-01-23

Desired date range:

“last complete year”

(calendar year 2021)

https://odata-connector-1.geotab.com/odata/v4/svc/VehicleKpi_Daily?$search=last_1_year

Data Connector Server: 1

Desired table: VehicleKpi_Daily5


Today’s date: 2022-01-23

Desired date range: “month-to-date”

https://odata-connector-1.geotab.com/odata/v4/svc/VehicleKpi_Daily?$search=this_1_month

Data Connector Server: 1

Desired table: VehicleKpi_Daily


Today’s date: 2022-01-23

Desired date range:

“most-recent 3 months”

(Nov 2021, Dec 2021, Jan 2022)

https://odata-connector-1.geotab.com/odata/v4/svc/VehicleKpi_Hourly?$search=these_3_month

this_3_month will also work

Data Connector Server: 2

Desired table: VehicleKpi_Daily


Today’s date: 2022-01-23

Desired date range:

“most-recent 3 complete months”

(Oct 2021, Nov 2021, Dec 2021)

https://odata-connector-2.geotab.com/odata/v4/svc/VehicleKpi_Hourly?$search=last_3_month

Data Connector Server: 2

Desired table: VehicleKpi_Hourly


Today’s date: 2022-01-23

Desired date range: “last 14 days”

https://odata-connector-2.geotab.com/odata/v4/svc/VehicleKpi_Hourly?$search=last_14_day

Using Power BI

When using the Data Connector with Power BI, data obtained from the connector is stored and cached in the local machine within the Power BI file. We encourage you to refresh your page on a daily basis to access the latest data.

  1. Under the Data ribbon tab, select Get data.
  2. Select OData Feed. If you are unable to find OData feed from this selection, select More… under the data sources, select Other from the left navigation menu, and then select OData Feed.
  3. Select the Basic radio button.
  4. Under URL, paste in the Access URL from General access via OData protocol that corresponds to your database’s Data Connector Server and then press OK.
  5. When prompted for authentication, select Basic from the left navigation menu.
  6. For the credentials, use the following:
    1. User name: <MyGeotab Database Name>/<MyGeotab Username>
    2. Password: <MyGeotab Password>
    3. Select which level to apply these settings to: do not modify this
  7. Press Connect to view the available data resources.
  8. From the Data Navigator, select all the relevant tables and resources that you would like to import.
  9. Press Load to import the data as-is, or press Transform Data to transform and alter the data before loading it into the Power BI data model.

Using Excel

When using the Data Connector with Excel, data obtained from the connector is stored and cached in the local machine within the Excel workbook file. We encourage you to refresh your page on a daily basis to fetch updated data.

  1. Under the Data ribbon tab, select Get Data.
  2. Under From Other Sources, select OData Feed.
  3. Select the Basic radio button.
  4. Under URL, paste the Access URL from General access via OData protocol that corresponds to your database’s Data Connector Server and then press OK.
  5. When prompted for authentication, select Basic from the left navigation menu.
  6. For the credentials, use the following:
    1. User name: <MyGeotab Database Name>/<MyGeotab Username>
    2. Password: <MyGeotab Password>
    3. Select which level to apply these settings to: do not modify this
  7. Press Connect to view the available data resources.
  8. From the Data Navigator, select all the relevant tables and resources that you would like to import
  9. Press Load to import the data as-is, or press Transform Data to transform and alter the data before loading it into the Excel data model.

Using Tableau

When using the Data Connector with Tableau, data obtained from the connector is stored and cached in the local machine as a Tableau extract file. We encourage you to refresh the underlying data sources on a daily basis to fetch updated data.

For new Tableau Workbooks

  1. Under the left navigation pane Connect, locate To a Server and select More…
  2. Select OData from the expanded list of server sources.
  3. Under Server, paste in the Access URL from General access via OData protocol that corresponds to your database’s Data Connector Server.
  4. Under Authentication, select Username and Password.
  5. For the credentials, use the following:
    1. Username: <MyGeotab Database Name>/<MyGeotab Username>
    2. Password: <MyGeotab Password>
  6. Press Sign In to view the available data resources.
  7. From the left navigation menu under Table, drag the relevant table or resource that you would like to import into the “Drag tables here” icon.
  8. Now that the workbook is created, follow the steps from For existing Tableau Workbooks to import any other tables.

For existing Tableau Workbooks

  1. Under the Menu bar at the top, select Data.
  2. Select New Data Source.
  3. Under the Connect screen, locate To a Server and then select More…
  4. Select OData from the expanded list of server sources.
  5. Under Server, paste in the Access URL from General access via OData protocol that corresponds to your database’s Data Connector Server.
  6. Under Authentication, select Username and Password.
  7. For the credentials, use the following:
    1. Username: <MyGeotab Database Name>/<MyGeotab Username>
    2. Password: <MyGeotab Password>
  8. Press Sign In to view the available data resources.
  9. From the left navigation menu under Table, drag the relevant table or resource that you would like to import into the “Drag tables here” icon.

Troubleshooting

General

I am able to connect to the Data Connector using correct credentials. Why is it that I can see the data schema, but not any data (or data for a specific vehicle or device)?

Ensure that you have the appropriate view permission for the vehicle/device on your MyGeotab account. If you do have the appropriate permissions enabled on your MyGeotab account and the data is still blank, it could mean that no activity for the vehicle/device was recorded for the specified time period.

What is Rate limiting?

This is a feature to protect the stability of the Data Connector. There is a limit to the number of requests sent to the Data Connector in a short period of time. Wait for a bit before refreshing the data sources to your dashboard.

Different tables may have different rate limiting, but the general rate limiting is set to 100 requests per user, per minute.

I am on an older version of Power BI/Tableau/Excel. Can I still use the Data Connector?

As long as Power BI/Tableau/Excel supports getting data through the OData feed, you should be able to use the Data Connector. However, the Data Connector is only tested on the latest versions of Power BI, Tableau, and Excel.

I am using other unlisted BI tools. Can I still use the Data Connector?

Currently, the Data Connector only officially supports Power BI, Excel, and Tableau. We do not offer official support to other BI tools as we cannot guarantee the resulting behavior. We are looking to expand our support to other BI tools in the future. It is possible to connect via the OData protocol to many other BI tools, but Geotab can not guarantee compatibility at this time.

I am not seeing VIN data populated for some vehicles. Why is this happening?

This can happen when the vehicle does not communicate VIN data to the telematics device, either due to a disconnected telematics device, or because the vehicle is not sharing (or cannot share) the VIN data with the telematics device. Also, the Data Connector does not currently capture manually-entered VINs in MyGeotab. For this matter on the Vehicle Latest Metadata table, refer to the note in the Vehicle Latest Metadata section. If you have certain vehicles that do not report VIN through the diagnostic port in the vehicle, work with your reseller and Geotab resources to open a Vehicle Data Request (VDR).

I am not seeing Odometer readings for some vehicles. Why is this happening?

Similarly to VIN, this can happen when the vehicle does not communicate odometer data to the telematics device through the diagnostic port connection. The Data Connector product does not currently capture manually-entered/edited odometer entries in MyGeotab. If you have certain vehicles that do not report odometer through the diagnostic port in the vehicle, work with your reseller and Geotab resources to open a Vehicle Data Request (VDR).

I am using SAML/SSO to log in to MyGeotab. Can I authenticate using the same credentials in Data Connector?

No. Currently, Data Connector only supports basic authentication. You must either switch your authentication type from SAML to basic authentication, or create a new account with basic authentication in MyGeotab to connect to Data Connector.

Power BI

“Unable to connect: We encountered an error while trying to connect. Details: OData: We were unable to detect required payload information”

This is a known Power BI issue caused by an empty response payload due to lack of viewing permission to the database’s data. Ensure that you have the appropriate view permission for the vehicle/device setup on your MyGeotab account.

Changing authentication/user

To change credentials used for the Data Connector server, under the ribbon menu select File, then on the left navigation pane select Options and settings. Once the Options and settings menu appears, click on Data source settings. Select the relevant Data Connector access URL and click on the Edit Permissions button near the bottom. You should now be presented with an Edit button under Credentials. Users have the ability to edit credentials for a single PBIX file using ‘Data sources in current file’ or multiple pbix files saved on the same local machine using ‘Global permissions’. Note that the credentials used are saved locally, and are not saved in each PBIX file.

The remote server returned an error: (400) Bad Request

Double-check the credentials used to access the Data Connector. Ensure the Username is in the form <MyGeotab Database Name>/<MyGeotab Username>

In addition, please ensure that the Access URL is entered correctly. There could be a misconfigured query option after the $search part of the URL, such as the date format or the date range in the query option arguments.

The remote server returned an error: (401) Not Authorized

Double-check the credentials used to access the Data Connector. Ensure the Username is in the form <MyGeotab Database Name>/<MyGeotab Username>

The remote server returned an error: (404) Not Found

Double-check the Access URL used to access the Data Connector. The error indicates that the requested resource or table does not exist.

The remote server returned an error: (406)

Double-check the Access URL used to access the Data Connector. The error indicates that the provided Data Connector Server does not match your MyGeotab database

OData: Request failed: The remote server returned an error: (412)

This error indicates that the user is not subscribed to the Geotab Data Connector solution. Refer to Requirements for instructions on how to subscribe to the Add-In.

OData: Request failed: The remote server returned an error: (416)

This error indicates that the request for data is too large, and exceeds the limits for the Data Connector. Consider reducing the amount of data returned by using narrower date range filters.

OData: Request failed: The remote server returned an error: (429)

This error indicates that you have sent too many requests to the Data Connector in a short period of time. This is a feature to protect the stability of the Data Connector. Wait a few minutes before refreshing the data sources to your dashboard.

Different tables may have different rate limits, but the general limit is 100 requests per user per minute.

OData: Request failed: The remote server returned an error: (500)

The Data Connector service is temporarily unavailable. Try again later, and if the error persists, contact customer support.

OData: Request failed: The remote server returned an error: (503)

MyGeotab authentication service is temporarily unavailable. Try again later, and if the error persists, contact customer support.

OData: The Property … does not exist on type …. Make sure to only use a property name that are defined by the type or mark the type as open type.

There is a change in the Data Connector’s metadata. Please clear the data cache from within the Get Data prompt. Navigate to the File Ribbon tab, click Options and settings, then click Options. In the new Options dialog, under the left navigation pane and right below Global, select Data Load. In the right main pane, scroll down to the section that says Data Cache Management Options and click Clear Cache. You can now restart Power BI before refreshing the data connections.

Excel

[DataFormat.Error] We cannot return the data produced by this query because of its complexity.

This error indicates that you have sent too many requests to the Data Connector in a short period of time. This is a feature to protect the stability of the Data Connector. Wait a few minutes before refreshing the data sources to your dashboard.

The remote server returned an error: (400) Bad Request

Double-check the credentials used to access the Data Connector. Ensure the Username is in the form <MyGeotab Database Name>/<MyGeotab Username>

In addition, please ensure that the Access URL is entered correctly. There could be a misconfigured query option after the $search part of the URL, such as the date format or the date range in the query option arguments.

The remote server returned an error: (401) Not Authorized

Double-check the credentials used to access the Data Connector. Ensure the Username is in the form <MyGeotab Database Name>/<MyGeotab Username>

The remote server returned an error: (404) Not Found

Double-check the Access URL used to access the Data Connector. This error indicates that the requested resource or table does not exist.

The remote server returned an error: (406)

Double-check the Access URL used to access the Data Connector. The error indicates that the provided Data Connector Server does not match your MyGeotab database.

OData: Request failed: The remote server returned an error: (412)

This error indicates that the user has not subscribed to the Geotab Data Connector solution yet. Subscribe to the solution as mentioned in Requirements before requesting the data.

OData: Request failed: The remote server returned an error (416)

This error indicates that the request for data is too large, and exceeds the limits for the Data Connector. Consider reducing the amount of data returned by using narrower date range filters.

OData: Request failed: The remote server returned an error: (429)

This error indicates that you have sent too many requests to the Data Connector in a short period of time. This is a feature to protect the stability of the Data Connector. Wait a few minutes before refreshing the data sources to your dashboard.

Different tables may have different rate limits, but the general limit is 100 requests per user per minute.

OData: Request failed: The remote server returned an error: (500)

The Data Connector service is temporarily unavailable. Try again later, and if the error persists, contact customer support.

OData: Request failed: The remote server returned an error: (503)

MyGeotab authentication service is temporarily unavailable. Try again later, and if the error persists, contact customer support.

OData: The Property … does not exist on type …. Make sure to only use a property name that is defined by the type or mark the type as an open type.

There is a change in the Data Connector’s metadata. Please clear the data cache from within the Get Data prompt. Navigate to the Data Ribbon tab, then click Get Data. Under Get Data, select Query Options. In the new Query Options dialog, under the left navigation pane select Data Load. Scroll down to the section that says Data Cache Management Options and click Clear Cache. You can now restart Excel before refreshing the data connections.

Tableau

Unable to access OData URL … with server response 400

Double-check the credentials used to access the Data Connector. Ensure the Username is in the form <MyGeotab Database Name>/<MyGeotab Username>

In addition, please ensure that the Access URL is entered correctly. There could be a misconfigured query option after the $search part of the URL, such as the date format or the date range in the query option arguments.

Unable to access OData URL … with server response 401

Double-check the credentials used to access the Data Connector. Ensure the Username is in the form <MyGeotab Database Name>/<MyGeotab Username>

Unable to access OData URL … with server response 404

Double-check the Access URL used to access the Data Connector. The error indicates that the requested resource or table does not exist.

Unable to access OData URL … with server response 406

Double-check the Access URL used to access the Data Connector. The error indicates that the provided Data Connector Server does not match your MyGeotab database.

Unable to access OData URL … with server response 412

This error indicates that the user has not subscribed to the Geotab Data Connector solution yet. Subscribe to the solution as mentioned in Requirements before requesting the data.

Unable to access OData URL … with server response 416

This error indicates that the request for data is too large, and exceeds the limits of the Data Connector. Consider reducing the amount of data returned by using narrower date range filters.

Unable to access OData URL … with server response 429

This error indicates that you have sent too many requests to the Data Connector in a short period of time. This is a feature to protect the stability of the Data Connector. Wait a few minutes before refreshing the data sources to your dashboard.

Unable to access OData URL … with server response 500

The Data Connector service is temporarily unavailable. Try again later, and if the error persists, contact customer support.

OData: Request failed: The remote server returned an error: (503)

MyGeotab authentication service is temporarily unavailable. Try again later, and if the error persists, contact customer support.

Unable to connect to the server: Deadline Exceeded

The error indicates that your connection has timed out. Slice your request by querying with a smaller date range, as the request might be too big.

scroll-up