SAP Knowledge Base Article - Public

2816755 - Inconsistent Results While Importing Data Using OData Service in SAP Analytics Cloud (SAC)

Symptom

  • Inconsistent results are imported while running same query based on Import Data connection to OData-based service in SAP Analytics Cloud (SAC)
    • During data import, there maybe be duplicate or missing rows/values
    • During dimension import, some dimensions may have different unique members (less then expected) in each run

Environment

  • SAP Analytics Cloud
  • Import data connection to OData Service (SAP S/4HANA, SAP IBP, SAP BPC NW, SAP SuccessFactors, etc.)

Reproducing the Issue

  1. Log on to SAC tenant.
  2. Try to create a new model on Import Data connection to OData-based service.
    => When the same query runs several times, the result differs after each query run if the result set is big.
    => Some dimensions may have different unique members (less then expected) in each run.

Cause

  • The issue occurs due to inconsistent pagination for larger datasets.
  • This happens because datasource/service fails to keep track of the order of the data when processing batches.
    • By default, the batch size is set to 50,000, and you can now configure a batch size from 1,000 to 50,000 (see detail from help guide).
    • Note: custom batch size feature isn't designed for freehand query based on an OData connection which will retrieve data in batches of 1000 . At the moment, it is only supported for query builder. 
  • By fetching the $metadata of the OData service, you will notice that no <Key> elements are defined under the entity. The absence of keys causes the backend to return untracked data, leading to inconsistent results during pagination.

Resolution

Backend Fixes (Recommended):

  1. Define Keys in the Backend:
    • In most OData services (e.g., SAP S/4HANA or SAP Datasphere), this issue can be resolved by defining proper keys for the entity in the backend.
    • Keys ensure that the data is uniquely identified and consistently ordered.
    • Example of metadata with Keys:

      <EntityType Name="EntityName">  
        <Key>  
          <PropertyRef Name="KeyField"/>  
        </Key>  
        <Property Name="KeyField" Type="Edm.String" Nullable="false"/>  
      </EntityType>

  2. Configure nextLink for Server-Side Pagination:

    • If defining keys does not fully resolve the issue, configure nextLink for proper server-side pagination.
    • This ensures that the server handles pagination consistently, eliminating duplicates or missing records.

Workaround (If Backend Fixes Are Not Applied):

  1. Use Freehand Query in SAC: