Connect to the OData API
The Gimmal Records OData API allows you to access reporting data in your Gimmal Records cloud tenant from reporting tools such as Excel, Tableau, and Power BI. Any application that can consume an OData connection can be used. This article walks you through connecting to the API using Power BI Desktop.
The OData API is only available for production tenants in the Gimmal Cloud, i.e. those accessed via https://records.gimmal.cloud or https://records-ca.gimmal.cloud.
Prerequisites
Access to the Gimmal Records cloud service.
Access to the Gimmal Records OData connector.
The username and password to an internal Gimmal Records Service Account. These accounts are easily identified by their orange tile on the Secure page of the Gimmal Records Manager web.
Loading Data into Power BI
From the Home ribbon select Get Data->OData feed.
OData feed selected from the Get data drop-down
The OData feed window appears
Enter the URL for the Gimmal Records OData API
https://records.gimmal.cloud/odata for customers in the US cloud OR
https://records-ca.gimmal.cloud/odata for customers in the Canadian cloud
Click OK.
The authentication window appears
Select Basic authentication and ensure https://records.gimmal.cloud/odata (or https://records-ca.gimmal.cloud/odata) is selected in the drop-down.
Enter the username and password for a Gimmal Records internal service account, then click Connect.
The Navigator window appears
Select the tables needed and click Load. If this is your first time connecting, it may be a better option to use the Power BI Template to get a basic understanding of the data.
Additional tables
The OData API provides a core set of tables for Gimmal Records. However, there are additional tables you may need. Accessing these requires the URLs and additional steps described below. Make sure to replace <ODataAPI> with the base OData URL (i.e., https://records.gimmal.cloud/odata for US cloud tenants, or https://records-ca.gimmal.cloud/odata for Canadian cloud tenants).
URL | Description |
|---|---|
<ODataAPI>/destructioncertificates?$select=*&$expand=approvals($select=*) | Destruction Certificate approvals |
<ODataAPI>/destructioncertificates?$select=*&$expand=content($select=*) | Destruction Certificate content |
<ODataAPI>/legalcases?$select=*&$expand=holdrules($select=*) | Legal hold rules |
<ODataAPI>/lifecycles?$select=*&$expand=phases($select=*) | Lifecycle phases |
<ODataAPI>/recordclasses?$select=*&$expand=approvalgroups($select=*;$expand=users($select=*)) | Record Class approval groups and users |
<ODataAPI>/recordclasses?$select=*&$expand=classificationrules($select=*) | Record Class classification rules |
<ODataAPI>/triggers?$select=*&$expand=triggerrules($select=*) | Trigger rules |
Loading Additional Tables
Follow these steps to load these additional tables in Power BI Desktop:
From the Home ribbon select Get Data->OData feed.
Enter the URL of the table, e.g. Record Class classification rules (remember to replace <ODataAPI> with the correct base OData URL for your tenant) and click OK.
If prompted for authentication, specify Basic authentication, enter the username and password for a Gimmal Records internal service account, and ensure the URL in the drop-down matches the additional table URL. Then click Connect.
Authentication to load the Record Class Classification Rules table
A data preview window will open, with the full URL at the top:
Select Transform Data to open the Power Query Editor.
For these tables, you will need to expand the column listed in the URL, in this case, ClassificationRules.
Left click the button with the two sideways arrows in the ClassificationRules column header. A window will open allowing you to select which columns of the Classification Rules you want to keep, as well as specify if you want to use the original column name (ClassificationRules) as a prefix of the columns you do keep. If this is your first time accessing this data, it may be useful to keep all of them until you are more familiar with the data. Make your selections then click OK.
When you chose to transform the data, a new query was created, likely Query1 (or similar). We recommend changing it to something more user-friendly, such as ClassificationRules.
Click Close and Apply from the Home Ribbon in the Power Query Editor.
Your new table will be loaded into Power BI Desktop.
Repeat these steps for the other tables as necessary.