1. Overview
OData (or Open Data Protocol) is a REST-based data access protocol which was initially defined by Microsoft.
This article shows you how to connect to OData services such as Microsoft Azure Marketplace.
For a list of available services, see OData - Live Services.
2. Connect to the OData Sample Service
The following shows example steps for connecting to a sample test service provided by odata.org.
Create a new data connector, entering a Name and setting the Data Provider dropdown to OData.
Set the Address to the service address, such as: http://services.odata.org/OData/OData.svc/
Select the Authentication method. For this example, select Anonymous.
Click Save to create the data connector and connect to the service.
To check the results, create a new dashboard or metric set, and in the Explore window expand the Data Connectors folder and locate and expand your data connector. Drag one of the OData tables to the dashboard canvas to see its data.
3. Connect to SharePoint
You can also connect to data on your SharePoint server using the OData protocol.
For example, set the Address to http://spserver/_vti_bin/listdata.svc/ and the Authentication method to Specified Windows credentials.
4. Advanced options
Click to expand the Advanced section for additional settings, including:
- By default, the OData connector will auto-detect the OData version, but you can use the OData Service Version option to force a specific version (e.g., between V1 and V4).
- The Records Per Page option specifies the number of records or rows to fetch at a time. By default, it is set to 100 but you can increase this in order to load data faster.
- When connecting to OData 4.0.1 or higher using version 26.2 of the application or higher, you can select Use POST Requests to avoid exceeding URL length limits or to keep query details out of the OData URL address for security reasons.
4.1. Connection script
In version 26.2 and higher, you can expand the Advanced section and optionally enter a Connection Script to implement custom logic for determining the values of the OData data provider settings.
As with other data scripts, the language is DundasScript, which is similar to C# and other common languages. Your script can access the following objects:
- session — the current logon session of the user when the script runs.
connection — the OData connection object, with the following properties and methods:
Property Type Description ProviderValues ParameterValueCollection Read provider settings by indexing them by ID (Guid) or call SetValue, for example. RequestHeaders IReadOnlyDictionary<string, string> A read-only collection of HTTP request headers included with each request. Method Description AddHeader(string name, string value)
Adds or updates an HTTP header with the specified name with the specified value. RemoveHeader(string name)
Removes the HTTP header with the specified name if it exists, returning a Boolean value indicating whether the header was removed.
Similar to the Generic Web data provider, you can use the following classes in addition to the ones you can access in any DundasScript:
- ConversionHelper — helper methods for converting and manipulating request and response data.
- HttpUtility — provides methods for encoding and decoding URLs when processing Web requests.
- Request — executes HTTP requests to an online service.
- Response — the response returned by a request.
For example:
// Set the Bearer Token data provider value to a custom attribute value from the current session:
Guid bearerTokenId = new Guid("d8e898bc-dc5b-4898-bac4-e8efeaec52f0");
connection.ProviderValues.SetValue(bearerTokenId, session.GetCustomAttributeValues("CompanyIDs").First());
// Add authentication headers:
connection.AddHeader("X-API-KEY", "HelloWorld");
5. Using query options
Queries sent to the data source can include various options that you can set, including $filter, $expand, $top, $skip, $select, $orderby, and (when using OData version 4) $search.
These can be set when selecting a table in a data cube, by configuring its Tabular Select transform and clicking Parameters.
Click to edit one of the predefined parameters, and set the Value using the syntax used by OData query options following the equal (=) sign.
For example, the $filter parameter could be set to IataCode eq 'LAX'.
You will also find a Programmatic Structures folder located directly under the data connector, listing the results of the $count query option for each table, which you can drag onto a data cube or metric set.
