Data & Insights datasets, including private datasets, can be accessed through a unique OData endpoint, allowing users to seamlessly connect to their data through a number of different tools.
As of 11/06/2017, Data & Insights's OData endpoints will also support OData v4, following the URL format of https://$domain/api/odata/v4/$dataset_identifier. If you are using the previous version (v2) of OData, endpoints follow this URL structure: https://$domain/OData.svc/$dataset_identifier, we recommend updating to the latest version.
Finding the Odata Endpoint
You can easily locate the OData endpoint for a dataset on both the dataset's Primer page and through the data table.
To access the endpoint through Data & Insights Primer, select the "..." button on the top right of the screen. This will open up a drop-down where you can select "Access Data via OData". This will bring up a pop-up screen which will contain the OData Endpoint for the dataset. You can click on the OData box to choose either the v2 or the v4 endpoint.
This endpoint can also be found in the table view. When viewing the dataset select the “Export” pane and choose the OData section and copy either of the two listed OData endpoints.
Analyzing OData through Microsoft Excel
OData connections are often analyzed through Microsoft Excel. Supported versions of Excel and Power BI for use with Data & Insights OData are:
- Power Query Version 2.40.4554.161 or newer - Excel 2010 / Excel 2013
- Get & Transform Tool - Excel 2016 version 16.0.6965.2117 or newer
- Power BI Desktop - Version 2.42.4611.901 or newer
For more information on using Excel to connect to your OData endpoint, please see the following article.
Note: Microsoft does not support OData connections on the Mac version of Excel
Authenticating in OData
In some cases, it is required to authenticate with your Data & Insights username and password to access an Odata feed. This can be done using the basic authentication option in most software that offers Odata connections.
The main cases for requiring authentication is:
- When the dataset or view you are connecting to is Private.
- When the dataset or view has been rejected in approvals from view in the catalog. Even though these assets are not private, since they are hidden from the catalog, connecting to these assets will require authentication. (Hint: You can tell if an asset is not approved when it can be viewed on your browser without logging in but the asset is not available in the Public Catalog).
Querying using OData
You can also directly query Data & Insights data using OData as a REST API. Data & Insights datasets follow the OData URI Conventions documented in https://www.odata.org/documentation/odata-version-2-0/uri-conventions/. For more information on OData, see http://www.odata.org/documentation/
Note: Not all querying functionality is supported in V4 endpoints, please use V2 endpoints for data filtering.
Here are some examples of how to apply these conventions to a Data & Insights catalog and dataset:
Data & Insights Datatypes exposed as OData
This table lists the OData types used to represent Data & Insights data.
|Data & Insights Datatype||ODataDatatype|
|Date & Time (floating timestamp)||Edm.String (the OData V4 protocol has removed the datetime without timezone type)|
|Date & Time (with timezone)||Edm.DateTimeOffset|
|Document||Complext type with Edm.String listing the URL to the document and Edm.String listing the name of the document|
|Location||Complex type with Edm.Decimal for latitude/longitude and Edm.String for Address, City, State and Zip|
|Phone||Complext type with Edm.String listing the phone number and Edm.String listing the phone type|
|Photo||Edm.String listing the URL to the photo|
|Text (Formatted)||Edm.String with HTML tags|
|Website URL||Complex type with Edm.String listing the URL and Edm.String listing the description|
Four data types are not exposed via OData. These are: Star, Flag, Nested Table and Dataset Link.