Importing OData Services Data to a New or Existing Model
You can create a connection that allows you to import data from both on-premise and cloud data sources using generic OData services. It is possible to request a customized OData data source solution. You can also save your OData connection details, and schedule model updates and data imports from OData sources.
Context
-
SAP Analytics Cloud supports OData Version 4.0. Logical Operators (such as Equal, Not Equal, Greater than, Greater than or equal, Less than, Less than or equal, Logical and, Logical or) are supported. Not logical negation, arithmetic operators, or functions are not supported.
The following table shows which operators need to be supported for each data type, for a generic OData service to integrate with SAP Analytics Cloud:
Data Type
Operator
Format
String (Edm.String) "eq", "ne", "startswith", "toLower"
"eq", "ne", "startswith", "toLower";
Number (Edm.Decimal) "gt", "ge", "lt", "le", "eq", "ne", "M"
[value] m
Number (Edm.Double) "gt", "ge", "lt", "le", "eq", "ne", "d"
[value] d
Number (Edm.Single) "gt", "ge", "lt", "le", "eq", "ne", "f"
[value] f
Number (Edm.Int16) "gt", "ge", "lt", "le", "eq", "ne"
Number (Edm.Int32) "gt", "ge", "lt", "le", "eq", "ne"
Number (Edm.Int64) "gt", "ge", "lt", "le", "eq", "ne", "L"
[value] L
Datetime (Edm.DataTime) "gt", "ge", "lt", "le", "eq", "ne"
Datetime (Edm. DataTimeOffset) "gt", "ge", "lt", "le", "eq", "ne"
Boolean (Edm.Boolean) "eq", "ne"
True|False
Edm.Guid "eq", "ne"
guid'[value]'
Logical Operator "and", "or"
- To integrate with SAP Analytics Cloud,
your OData service must support these query parameters and paging
capabilities:
OData Service Support
Requirements
Query parameters - $select: Filters properties (columns). Lets you request a limited set of properties for each entity.
- $filter: Filters results (rows). Lets you filter a collection of resources that are addressed by a request URL.
- $expand: Retrieves related resources. Specifies the related resources to be included with retrieved resources.
- $skip: Specifies the number of items in the queried collection that are to be skipped and not included in the result.
- $top: Sets the page size of the results. Specifies the number of items in the queried collection to be included in the result.
- $orderby: Orders the results. Lets you request resources in either ascending or descending order using asc and desc.
- $inlinecount: OData V2 only. Specifies that the response to the request includes a count of the number of Entries in the Collection of Entries identified by the Resource Path section of the URI.
- $count: OData V4 only. Lets you request a count of the matching resources included with the resources in the response.
Paging capabilities - Support paging based on $top and $skip.
- Should have the ability to return 1000 data rows per request, but if it doesn't, it must implement the “nextLink” function.
- The order of the data rows in the data source needs to be fixed during data acquisition (for example, by sorting on a column). Otherwise, when paging is done, there could be data correctness issues. For example, the same data row could appear more than once in different pages.
- The order of data rows should be guaranteed on data source during paging by $skip and $top.
- If you want to use the query builder in the step below when
you create a new query, the data service must support the select system
query option. Example:
https://services.odata.org/OData/OData.svc/Products?$select=Price,Name
Key-as-Segment isn't supported by the query builder, and should only be used with freehand queries.
Also, the $skip parameter must be supported by the data service.
- Embedded Complex types are not supported.
Procedure
Next Steps
After the initial import of raw data, continue with the data preparation task before completing your model: Preparing Data.