Creating a Dataset from OData Services Data

You can import data from both on-premise and cloud data sources into a dataset using generic OData services.

Context

Note

  • 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

  1. Create a new dataset and choose a data source:
    1. Select Start of the navigation path (Main Menu) Next navigation step  Create Next navigation step  DatasetEnd of the navigation path.
    2. Select Data from a data source.
    3. Select OData Services.
    Note
    From the acquire data panel, select the filter icon to narrow down the number of data sources in the list. You can filter by data source type or by category.
  2. Select an existing connection, or select Create New Connection to create a new connection.

    If you create a new connection, you can select the following options:

    • Connect to an On-Premise OData service
      Note

      To connect to an On-Premise OData service, ensure that the following tasks are completed:

      1. The cloud connector is installed. For more information, see Installing the SAPCP Cloud Connector.
      2. The cloud connector is configured. For more information, see Configuring the SAPCP Cloud Connector.

      The SAP Analytics Cloud agent doesn't need to be installed during the configuration process.

    • Connect to an SAP OData service When you select this option, specific SAP metadata is respected. This metadata specifies default behaviors based on SAP OData services guidelines. For more information, see SAP Annotations for OData Version 2.0Information published on SAP site.
    Note
    Advanced features of customized OData data sources, such as SAP Cloud for Customer and SAP Business ByDesign Analytics, are only available using customized data source types. These features are not available using generic OData services. It is highly recommended to use the relevant customized data source types, if available, for your data service to leverage full capability of the data connector. It is possible to request a customized OData data source solution.

    For more information, see:

  3. Type a name for your query.
  4. Choose whether you want to build a query, or type in a query manually.
    1. Select Build a Query to build a query using the query builder, or Freehand Query to manually type a query using V2 OData query syntax.
    2. If you chose to use the query builder, select a table, and then select Next.

      Build your query by moving data elements into the Selected Data and Filters areas. For more information, see Building a Query.

    3. If you selected the Freehand Query option, type a query in the box and select OK.
      Note
      When using freehand queries, the columns (including expanded entity columns) need to be specified, otherwise they won't be picked up in SAP Analytics Cloud.

      For example, the following queries let you get the product by rating, or find a nearby airport:

      OData V2 syntax:

      Sample Code
      KeyPredicate:
Categories(1)/Products?$format=json&$select=Name ,Category/Name,Supplier/Name,Supplier/Address&$expand=Category,Supplier&$filter=Supplier/ID eq 0
FunctionImport:
GetProductsByRating?rating=3&$format=json&$select=Name,Rating,Category/Name,Supplier/Name,Supplier/Address&$expand=Category,Supplier

      OData V4 syntax, using the expand parameter:

      Sample Code
      KeyPredicate:
Products?$select=ID,Name,Description,ReleaseDate,DiscontinuedDate,Rating,Price&$expand=ProductDetail($select=ProductID),ProductDetail($select=Details)
FunctionImport:
GetNearestAirport(lat=80, lon=90)

      For more information on the OData query syntax, refer to the OData documentationInformation published on non-SAP site.

      Note

      SAP Analytics Cloud has the following validation rules for freehand queries:

      • Duplicated parameters ($select, $expand, $format, $top, $skip, $inlinecount, $filter) in the query are not allowed.
      • Only entity set and function import are supported.
      • For function import, entity set is only supported as a return type.
      • If $select contains the Nav property but without $expand property, the query is invalid.

Next Steps

After importing the raw data, continue with data preparation before completing your dataset: About Adding Data to a Story.