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

Note
  • SAP Analytics Cloud supports OData Version 2.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 lists the minimum requirements for generic OData:

    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"

  • Embedded Complex types are not supported.

Procedure

  1. Go to the Models list: select Start of the navigation path (Main Menu) Next navigation step  Browse Next navigation step  ModelsEnd of the navigation path.
  2. Select the appropriate option:
    • If creating a new model: Select Start of the navigation path Next navigation step Get data from an app Next navigation step OData ServicesEnd of the navigation path.
    • If importing into an existing model: In the Import Data menu, select Import Data From OData Services.
  3. Existing model only: specify the target model.
  4. 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 Hybris 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:

  5. Choose whether you want to copy an existing query, or create a new query.
  6. If you're copying an existing query, select it from the list, and then click OK.
    1. Make changes to the query, and then click Create.
    2. Skip to step 8.
  7. If you're creating a new query, enter a name for your query.
    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, and then select Create. For more information see, Building a Query.

    3. If you selected the Freehand Query option, type a query in the box and select OK.

      For example, the following query lets you get the product by rating:

      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
      
      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.
  8. Select the appropriate option:
    • If creating a new model: In the Draft Data dialog, select the dataset that you just uploaded.
    • If importing to an existing model: In the Draft Sources list, select the dataset that you just uploaded.

Next Steps

After the initial import of raw data, continue with the data preparation task before completing your model: Preparing Data.