3517434 - Data import inconsistency from OData connection in SAP Analytics Cloud

Symptom

When importing data into SAC from an external datasource (such as Datasphere) using OData, data inconsistencies (such as duplicate or missing data) can occur if the data is not ordered deterministically (ie. missing ORDER BY) or if the data changes during the import run.

"Image/data in this KBA is from SAP internal systems, sample data, or demo systems. Any resemblance to real data is purely coincidental."

Environment

SAP Analytics Cloud (Enterprise) 2024.1

Cause

When data is imported into SAC, more than one batch call may be required to retrieve the full result set. If the data source does not apply a consistent ordering, the row sequence can change between batches, resulting in duplicate or missing records. This also occurs when data is modified in the source system during an active import run.

Resolution

The problem can be avoided by applying ordering to the result set. The problem cannot be prevented if data changes during the execution of the import run or if no ordering is applied to the result set.
This ordering can either be applied on SAC or on the Datasphere side (If using Datasphere as the source).
Check the video demonstrating how to create a freehand SQL query:

SAC

During model creation, select Freehand Query instead of using the Query Designer.

A Freehand Query is an OData query that SAC uses to import data. The Freehand Query always starts with the entity to be imported — for example: ProductView. This corresponds to the name of the object to be imported from Datasphere
The query is further parameterized by the following parameters:
- $select=<column1, …>: the attributes (columns) that shall be imported, eg. ProductName. Multiple attributes to be selected must be separated by a comma (,). Attributes must correspond to their technical name on Datasphere side.
- $filter=<condition>: allows to filter the resultset based on attribute values. Please refer to the Datasphere documentation for the set of supported filtering operations. Not all data types support all filtering operations.
- $orderby=<column> [<asc|desc>]: Specifies the order in which items are returned. Unless otherwise specified, the default ordering is ascending. Adding this parameter is crucial to ensure correct ordering of the resultset!
Parameters are added with ? to the Freehand Query. When using more than one parameter, & must be used for all subsequent parameters.
Special characters such as spaces, quotes must be escaped using percent-encoding like the following:

space

‘

(

)

;

[

]

%20

%21

%23

%24

%26

%27

#28

%29

%2A

%2B

%2C

%2F

%3A

%3B

%3D

%40

%5B

%5D

When filtering for string values, values must be quoted with a single quote (using %27).
For example, the following Freehand Query obtains all Products with a price of more than 100 together with their name and SKU, ascendingly ordered by the Product ID:
- ProductView?$select=Name,SKU&$filter=Price%20gt%20100&$orderby=ID

Note that the order of the import query does not determine the final ordering in SAC.

Datasphere

It is also possible to apply ordering on the Datasphere side for all operations accessing the object. This can e.g. be done when creating a SQL view using standard SQL syntax (ORDER BY), see help.

It is not possible to define a default ordering on analytical models. For analytical models, a Freehand Query must be used instead.

Resources

Please, also check this KBA, which can solve this matter as well: KBA 2816755 - Inconsistent results while importing data using an OData service in SAP Analytics Cloud

Datasphere OData Parameters: Link

Datasphere OData integration: Link

Keywords

Data, SAC, SAP Analytics Cloud, Datasphere, import data, data inconsistency, duplicate data, missing data, freehand, query, $orderby, order by, sort, inconsistent results, batch import, pagination, OData V2, OData V4, import error , KBA , LOD-ANA-DES , Story Design & Visualizations , DS-BB-ODATA , To address issues related to Odata consumption API , LOD-ANA-AQU , Import Data Connections (Acquiring Data) , Problem

Product

SAP Analytics Cloud 1.0