3517434 - Inconsistencies when importing Data into SAP Analytics Cloud (SAC) using Odata

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.

Environment

SAP Analytics Cloud (Enterprise) 2024.1

Cause

When the data is imported into SAC, it can potentially require more than one batch call to import the data. In that case, if there is no consistent ordering of the data then the ordering of the data can change in between batches which will lead to inconsistent data being imported.

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 resultset.

This ordering can either be applied on SAC or on the Datasphere side (If using datasphere as the source).

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 the data. The Freehand Query always starts with the Entity to be imported, for example: ProductView. This corresponds to the name of the object 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 the import query does not determine the final ordering in SAC.

Datasphere

It is also possible to apply ordering on 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

Datasphere OData Parameters: Link

Datasphere OData integration: Link

Keywords

Planning, datasphere, sort, order, inconsistent, error , KBA , LOD-ANA-DES , Story Design & Visualizations , LOD-ANA-AQU , Import Data Connections (Acquiring Data) , Problem

Product

SAP Analytics Cloud 1.0