SAP OData In Dev

OData (Open Data Protocol) is a standardized protocol developed by Microsoft and widely adopted by SAP for secure, scalable, and efficient data exchange across SAP systems and various other platforms, applications, or devices through HTTP. The OData protocol enables seamless integration, allowing you to use SAP data within broader ecosystems.

The Fivetran SAP OData connector integrates your SAP system as a data source, with a focus on read operations. The connector extracts data from the SAP OData API and loads it into your destination, providing streamlined access to SAP ERP data, such as Core Data Services (CDS) views exposed on the SAP OData API, which reflect key business processes. The connector leverages the SAP standardized OData services for efficient data retrieval, making it easier to analyze and manipulate ERP data across diverse applications and systems.

Features

Setup guide

Follow our step-by-step SAP OData setup guide to connect SAP OData to your destination using the Fivetran connector.

Sync overview

Fivetran use the SAP OData API to extract data from your SAP OData instance. The SAP OData API is a RESTful interface that provides access to SAP OData resources, e.g.: CDS views. These views must be published on the SAP OData API and must include the following SAP annotations in their definition:

• @Analytics.dataCategory: #FACT: Identifies the CDS view as containing factual data (as opposed to dimensions or master data). The annotation is typically used in analytical scenarios where fact data is processed differently from dimensions. Fivetran supports only CDS views with the FACT annotation.

• @Analytics.dataExtraction.enabled: true: Enables data extraction from the CDS view, allowing you to load data into analytical tools, reports, or data warehouses.

• @Analytics.dataExtraction.delta.changeDataCapture.automatic: true: Enables automatic delta data extraction, which tracks changes (additions, deletions, updates) for incremental data loads. This reduces the need for full data extractions in reporting.

Fivetran leverages FactsOf and DeltaLinks entities for efficient data synchronization:

• FactsOf gathers detailed information about linked entities within a service, enabling comprehensive data extraction from CDS views.
• DeltaLinks tracks incremental changes since the last sync, optimizing resource usage and ensuring only updated records are captured in each sync cycle.

Fivetran replicates the two entities into a single table in your destination, combining the FactsOf and DeltaLinks entities to provide a comprehensive view of your SAP data. We also retrieve metadata fields to track the data extraction process (ODQ prefixed columns). These fields are essential for incremental data extraction, but are not added to the destination.

NOTE: For more information, see the SAP Analytics Annotations documentation.

Fivetran supports loading only change data from the SAP Odata API minimizing the load on your SAP system while providing efficient access to your business data.

SAP OData metadata mapping

The SAP OData API, based on the OData protocol, uses a set of conventions for building and consuming RESTful APIs. The API is structured around the concept of services, entities, properties, and keys, which map to the following elements in your destination:

• Service corresponds to a schema
• Entity corresponds to a table
• Property corresponds to a column
• Key corresponds to the primary key (PK)

The properties prefixed with ODQ are metadata fields that Fivetran uses to track data extraction. These fields are essential for incremental data extraction, but they are not added to the destination.

Sync limitation

Each service can only be assigned to a single SAP user. This is because SAP's delta management supports only one subscriber per service at a time. If you need to sync data from the same service to multiple destinations, you must create a separate service for each destination.

Type transformation mapping

Fivetran maps SAP OData data types (EDM) to Fivetran-supported types. Our system attempts to infer the types of any columns with data types that are not recognize.

The following table illustrates how Fivetran transform your SAP data types (based on the OData primitive types) into Fivetran-supported types:

NOTE: *The LOCALDATETIME type is used to represent date and time values using the Gregorian calendar. In case of dates before 1582-10-15, there may be discrepancies in the representation of dates due to the switch from the Julian to the Gregorian calendar. Warning: When you have invalid values in SAP for the chosen Edm type in OData, Fivetran will not be able to load the data due to an error about SAP Note 1797736. NOTE: For additional details about data types, see the OData documentation.

Schema information

Fivetran replicates the selected services and their underlying entities from your SAP source to your destination. Each service corresponds to a schema, each entity maps to a table, and each property represents a column. We retrieve and combine the FactsOf and DeltaLinksOf entities from your SAP OData source into a single table in your destination.

Fivetran retrieves all the available entities from your service that respect the SAP naming conventions, such as FactsOf and DeltaLinksOf. We also retrieve metadata fields to track the data extraction process (ODQ prefixed columns). These fields are essential for incremental data extraction, but are not added to the destination.

Fivetran generated columns

Fivetran adds one extra system column, _fivetran_synced, to every destination table to give you insight into the progress and changes of your connector:

Deleting data

• Fivetran tracks deleted records for all tables.
• Tables have a _fivetran_deleted column in the destination. After each re-import, we mark any row that no longer exists in the source table as deleted in the destination.