Model Data API
The Model Data API is intended to provide users with the ability to query the model warehouse via an ODATA API. This is achieved by using HTTP GET requests.
The path of the URL specifies the target warehouse to query. Query operators, such as filter and sort can be specified through query options (discussed later in this article).
Prerequisites
In order for the API to work, the following is required:
An authentication method: should be chosen and configured (if applicable).
Basic Authentication - Use the Data Hub generated API username and password. (Most commonly used method)
Azure Active directory Authentication ( must be configured as the Data Hub Authentication method). More information on how to Configure Azure Active Directory
Windows Authentication (On-premise instances only)
All other Authentication methods (except the default B2C method) supported by Data Hub can be used, provided the client service supports these methods. Insomnia will work with all authentication methods supported by Data Hub.
An Existing model or deployed solution, that has been processed and exists in the public folder only.
Note
When the Enable Odata Endpoint option is checked for the first time, the model has to be processed before endpoint requests will work.
Configure API Authentication
In Data Hub add a new API user from API Integration in Settings.
Add a new API User.
Important
Make sure to copy the password before you shift focus away from this window. The password will not be visible after that.
Copy the username and password.
Add the new API user to the model you will be using. In the model role Application Users, go to the MEMBERS tab, and click the refresh button in the Users with access section.
Publish the Role.
How to use the API
In Data Hub, navigate to the model who's warehouse will be queried. On the Model design panel, in the Reporting section, check Enable Odata endpoint. If this is enabled for the first time you will get a notification to remind you that the model needs to be processed before it will be usable.
Click on the Copy Odata URL button to copy the Endpoint URL.
Important
Verify that the API user is added to the model.
In any client service like Postman, Insomnia or excel for example you can use the Endpoint URL you copied.
Set your request method to GET. Paste your copied Endpoint URL here.
Choose your Authentication type. If using the API username and password the type is Basic. Provide the details here.
For Windows Authentication the type will be Microsoft NTLM, and for AAD the type will be OAuth2.0.
To use AAD in excel, choose Organizational for AAD authentication
Click Send.
A successful request will return a list of available tables from the warehouse.
Querying Options
System Query Options are query string parameters (referred to as Parameters from here) that can be used to control the amount and order of the data that is returned by the endpoint URL. The names of all parameters are prefixed with a “$” character.
Detailed documentation is available at Odata Documentation
System Query Options (Parameters)
ZAP supports the following parameters: $filter, $orderBy, $top, $skip, $count, and $select options. We do not support the $expand or $search options, or accessing an individual record by key.
Operators
The $filter system query option specifies conditions that MUST be met by a resource for it to be returned in the set of matching resources.
Most of the operators to be used with the $filter parameter, are supported. Below are some examples of Logical and Arithmetic operators. When an operator is not supported it will return a "not supported" status.
Examples of Logical Operators | Example Endpoint Query | |
eq | equal | https://services.odata.org/OData/OData.svc/Products?$filter=Name eq 'Milk' (Requests all products with a Name equal to 'Milk'). |
ne | not equal | https://services.odata.org/OData/OData.svc/Products?$filter=Name ne 'Milk' (Requests all products with a Name not equal to 'Milk'). |
gt | greater than | https://services.odata.org/OData/OData.svc/Products?$filter=Name gt 'Milk' (Requests all products with a Name greater than 'Milk'). |
ge | greater than or equal to | https://services.odata.org/OData/OData.svc/Products?$filter=Name ge 'Milk' (Requests all products with a Name greater than or equal to 'Milk'). |
lt | less than | https://services.odata.org/OData/OData.svc/Products?$filter=Name lt 'Milk' (Requests all products with a Name less than 'Milk'). |
le | less than or equal to | https://services.odata.org/OData/OData.svc/Products?$filter=Name le 'Milk' (Requests all products with a Name less than or equal to 'Milk'). |
and | and | https://services.odata.org/OData/OData.svc/Products?$filter=Name eq 'Milk' and Price lt '2.55M' (Requests all products with the Name 'Milk' that also have a Price less than 2.55). |
or | or | https://services.odata.org/OData/OData.svc/Products?$filter=Name eq 'Milk' or Price lt '2.55M' (Requests all products that either have the Name 'Milk' or have a Price less than 2.55). |
not | not | https://services.odata.org/OData/OData.svc/Products?$filter=not endswith(Name, 'ilk') (Requests all products that do not have a Name that ends with 'ilk'). |
Examples of Arithmetic Operators | Example Endpoint Query | |
add | addition | The Addition Operator (or ‘add’) adds the left and right numeric operands together. https://services.odata.org/OData/OData.svc/Products?$filter=Price add 2.45M eq '5.00M' (Requests all products with a Price of 2.55M). |
mul | multiply | The Multiplication Operator (or ‘mul’) multiples the left and right numeric operands together. https://services.odata.org/OData/OData.svc/Products?$filter=Price mul 2.0M eq '5.10M' (Requests all products with a Price of 2.55M). |
Examples
Select only a specific table. Add the table name to the endpoint to filter the results.
https://DataHubInstance/Default/Warehouse/Public/BITrainingPublic/odata + /Customer?
Order results by a column. Add the $orderby parameter and the column to order by (optional: add order)
https://DataHubInstance/Default/Warehouse/Public/BITrainingPublic/odata/Customer? + $orderby=Type, name desc
Filter results where a column is greater than or equal to a value. Add the table name, filter parameter and column name to filter, lastly add the operator greater than or equal to (ge) and the value. https://DataHubInstance/Default/Warehouse/Public/BITrainingTest/BITrainingNew/odata/ + Product?$filter=ProductID ge 965
Troubleshooting
Data Hub or configuration problems
Cannot add the API user to the model: If you are trying to add the API user to your model role, and its not working, this will likely due to the model being in a personal folder.
Solution: Move the model to the public folder.
HTTP Error Codes
Note
Standard explanations of HTTP response codes can be found at Mozilla HTTP response codes
HTTP 200 OK success status response code indicates that the request has succeeded. A 200 response is cacheable by default.
HTTP 202 Accepted response status code indicates that the request has been accepted for processing, but the processing has not been completed; 202 is non-committal, meaning that there is no way for the HTTP to later send an asynchronous response indicating the outcome of processing the request
HTTP 403 Forbidden response will most likely be returned due to incorrect credentials
HTTP 404 Not found response status code indicates that the server cannot find the requested resource. These are some troubleshooting suggestions:
The URL could be incorrect or incorrectly copied
The model is in a personal folder
Incorrect Query option- or object names will also result in a 404
HTTP 505 Internal Server Error response can be due to various different reasons. These are some troubleshooting suggestions:
The API User hasn't been added to the model, or it has been added but the role hasn't been published.