SAP ERP on HANA link
SAP Enterprise Resource Planning (ERP) applications like SAP ECC and SAP S/4 HANA manage customer finance, operations, supply chain and HR processes. With the SAP ERP on HANA connector, Fivetran offers the possibility to replicate data from SAP ECC and S/4 HANA systems running on a HANA platform.
SAP HANA is an in-memory, column-oriented, relational database management system. Fivetran replicates data from your SAP HANA source database and loads it into your destination.
NOTE: You must have an Enterprise or Business Critical plan to use the SAP ERP on HANA connector.
Featureslink
Feature Name | Supported | Notes |
---|---|---|
Capture deletes | check | All tables and fields |
Custom data | check | All tables and fields |
Data blocking | check | Column level, table level, and schema level |
Column hashing | check | Column level |
Re-sync | check | Table level |
History | check | Table level. Supports history and soft delete modes. |
API configurable | ||
Priority-first sync | ||
Fivetran data models | check | Get the models: source / transform; Supports Quickstart data models |
Private networking |
Known limitationslink
- We only support column-store transparent tables, row-store transparent tables, and views from the SAP data dictionaries. For more details, see Excluded Objects.
- There are two limitations when selecting tables:
- Filters: The following search criteria is allowed. A search term must include a minimum of three alphanumeric characters and may contain any of the following special characters:
*
,?
,_
,-
,.
, or/
. - Returned Results: Currently, a maximum of 1000 tables are returned in one search execution, ordered alphabetically.
- Filters: The following search criteria is allowed. A search term must include a minimum of three alphanumeric characters and may contain any of the following special characters:
- No support for DDL(Data Definition Language) statements such as
CREATE TABLE
,DROP TABLE
,ALTER TABLE
,ADD COLUMN
orDROP COLUMN
. In case of such event, the respective table must be re-synced. - No support for updating a PrimaryKey value. This will be reflected as a new INSERT row.
- It is necessary to have an existing SAP ABAP server of version 7.5 and above that supports Unicode format. For more information, see SAP ERP on HANA setup guide.
- Limited support for special* or lowercase characters in column and table names.
- No support for special* or lowercase characters in schema names. For example,
lowercase_schema
,lowercase_SCHEMA
, orlOwErCaSeScHeMa
are all unsupported names.
* Special characters include (
, &
, @
, *
, $
, |
, %
, ~
, -
, and )
. Underscores are not considered special characters.
Setup guidelink
Follow our step-by-step SAP ERP on HANA Setup Guide for specific instructions on how to set up your SAP ERP on HANA using the Fivetran connector.
Sync overviewlink
The SAP ERP on HANA connector operates differently from typical database connectors. Instead of connecting directly to the database, it interfaces with the Application layer of an SAP system. Within this application layer, you need to install the Fivetran Netweaver API, which will pull a full dump of all the selected data from your SAP system. We then use our in-house tracking mechanism to pull the data at regular intervals.
Syncing empty tables and columnslink
Fivetran can sync empty tables for your SAP ERP on HANA connector.
We can also sync empty columns. However, we require at least one row of data that includes this new column to be added. This is because we detect changes to a table's column cardinality when we see a row with a new or removed column during an update.
For more information, see our Features documentation.
Schema informationlink
Fivetran replicates the exact schema and tables from your HANA source database to your destination according to our standard database update strategies. This ensures that the data in your destination is in a familiar format to work with.
When you connect to Fivetran and specify a source database, you also select a schema prefix. We map the SAP schema in your source database to your destination and prepend the destination schema name with the prefix you selected.
Fivetran generated columnslink
Fivetran adds the following columns to every table in your destination, depending on which sync mode you use:
- Either sync mode:
_fivetran_synced
(UTC TIMESTAMP) which indicates the time when Fivetran last successfully synced the row. - Soft delete mode:
_fivetran_deleted
(BOOLEAN) marks rows that were deleted in the source database. - History mode:
_fivetran_active
(BOOLEAN),_fivetran_start
(UTC TIMESTAMP), and_fivetran_end
(UTC TIMESTAMP) . These are used to mark which rows are currently active. They keep a record of the previous record and their lifetime.
We add these columns to give you insight into the state of your data and the progress of your data syncs. For more information about these columns, see our System Columns and Tables documentation.
Type transformation and mappinglink
As we extract your data, we match SAP data types to the types that Fivetran supports. Our system attempts to infer the types of any columns with data types that we do not recognize.
The following table illustrates how we transform your SAP data types (based on the SAP Data Dictionaries) into Fivetran supported types:
SAP Type | Fivetran Type | Fivetran Supported |
---|---|---|
ACCP | String | True |
CHAR | String | True |
CLNT | String | True |
CUKY | String | True |
CURR | BigDecimal | True |
DATS | String/LocalDate * | True |
DEC | BigDecimal | True |
D16D | BigDecimal | True |
D16N | BigDecimal | True |
D16R | Binary | True |
D16S | Binary | True |
D34D | BigDecimal | True |
D34N | BigDecimal | True |
D34R | Binary | True |
D34S | Binary | True |
FLTP | Float | True |
INT1 | Integer | True |
INT2 | Integer | True |
INT4 | Integer | True |
INT8 | Long | True |
LANG | String | True |
LCHR | String | True |
LRAW | Binary | True |
NUMC | String | True |
PREC | BigDecimal | True |
RAW | Binary | True |
RSTR | Binary | True |
QUAN | BigDecimal | True |
SSTR | String | True |
STRG | String | True |
TIMS | String | True |
UNIT | String | True |
VARC | - | False |
*NOTE The String or LocalDate type conversion depends on the Enable SAP date to LocalDate conversion toggle’s state in the connector setup form.
If we are missing an important data type that you need, reach out to support.
In some cases, when loading data into your destination, we may need to convert Fivetran data types into data types that are supported by the destination. For more information, see the individual data destination pages.
Data mapping workflowslink
Depending on the values, the data mapping for the connector can be in one of the following situations:
- The value can be parsed to a Fivetran data type - Fivetran takes the value and sends it to the destination in the appropriate data type.
- The value cannot be parsed to a Fivetran data type, and the column is not a primary key - Fivetran coerces the value a
null
and displays a warning. The sync continues. - The value cannot be parsed to a Fivetran data type, and the column is a primary key - The sync for the respective table ends with an error.
Schema changeslink
New tables are not automatically added. Use the Schema tab to add tables as needed.
Initial synclink
The initial sync can be performed once the connection to the SAP ERP on HANA database is established. The NetWeaver Fivetran API manages all data transport during this process.
Updating datalink
Fivetran performs incremental updates of any new or modified data from your source database. New changes are fetched and then synchronized into the destination. The update process varies depending on your sync mode:
Soft delete modelink
- An INSERT in the source table generates a new row in the destination with
_fivetran_deleted = FALSE
. - A DELETE in the source table updates the corresponding row in the destination with
_fivetran_deleted = TRUE
. - An UPDATE in the source table updates the corresponding row in the destination with
_fivetran_deleted = FALSE
. - An UPDATE of the primary key in the source table generates is treated like a new row being added. We therefore add a new row in the destination with
_fivetran_deleted = FALSE
. The old row remains in the destination with_fivetran_deleted = FALSE
until the corresponding table is re-synced.
History modelink
- An INSERT in the source table generates a new row in the destination with
_fivetran_active = TRUE
,_fivetran_start = <CURRENT_TIMESTAMP>
, and_fivetran_end = 9999-12-31 23:59:59.999+00
. - A DELETE in the source table marks the corresponding row in the destination inactive with
_fivetran_ACTIVE = FALSE
and_fivetran_end = <CURRENT_TIMESTAMP>
. - An UPDATE in the source table first marks the corresponding row in the destination inactive with
_fivetran_ACTIVE = FALSE
and_fivetran_end = <CURRENT_TIMESTAMP>
. We then add a new row in the destination with the same data and_fivetran_active = TRUE
,_fivetran_start = <CURRENT_TIMESTAMP>
, and_fivetran_end = 9999-12-31 23:59:59.999+00
. - An UPDATE of the primary key in the source table is treated like a new row being added. We therefore add a new row in the destination with
_fivetran_active = TRUE
,_fivetran_start = <CURRENT_TIMESTAMP>
, and_fivetran_end = 9999-12-31 23:59:59.999+00
. The old active row remains in the destination with_fivetran_active = TRUE
,_fivetran_start = <CURRENT_TIMESTAMP>
, and_fivetran_end = 9999-12-31 23:59:59.999+00
until the corresponding table is re-synced.
Deleted columnslink
We do not delete columns from your destination.
Capturing deleteslink
Capturing deletes requires additional consideration as they can go unnoticed if they occur in sections of data that have already been imported during previous cycles.
The current mechanism for detecting deletes is by means of triggers. The installed Fivetran NetWeaver API will thus create 2 objects for each table imported:
- an associated
Deletes
table, having the prefix/FIVETRAN/DELETES_
followed by the table name. For example, for tableT000
it would be/FIVETRAN/DELETES_T000
. - a trigger to the table that detects deletes and populates the associated
Deletes
table.
During each import cycle we check the contents of the Deletes
table and update the destination accordingly.
IMPORTANT: Under no circumstance should users manually alter the trigger or the
Deletes
table. In case of such event, Fivetran can not guarantee replication consistency. This would necessitate a full re-sync of all data.
Re-sync partition and re-sync table scenarioslink
The DROP
and TRUNCATE
operations have certain side effects that can impact the reliability of your connector. Fivetran can detect and automate specific processes without disrupting the data replication:
- In the case of a
TRUNCATE
(partition or table) operation: Fivetran automatically detects truncations for each partition and initiates a re-sync during the next sync cycle. - In the case of a
DROP
partition operation (typically done viaALTER TABLE
): Automatic detection from Fivetran side is currently not supported. This capability is still under development. Therefore, you must manually re-sync your table in case one partition is dropped. - In the case of a
DROP TABLE
operation: No further action is taken for that table, and it is treated as if there are no updates. The data remains intact on the destination.
Create partitionslink
Creating a new partitions for any table is automatically detected by Fivetran. This will be synced normally during the next sync cycle.
Fivetran also detects modified/restructured partitions, triggering a re-sync for those partitions.
Unpack datalink
NOTE: This feature is available for the Fivetran NetWeaver API version 1000202 and higher.
We unpack long text STXL
table into a readable and transparent format. We have modified the table structure to account for this. The following is the replicated STXL
version:
Original STXL | Replicated STXL |
---|---|
MANDT (PK) | MANDT (PK) |
RELID (PK) | RELID (PK) |
TDOBJECT (PK) | TDOBJECT (PK) |
TDNAME (PK) | TDNAME (PK) |
TDID (PK) | TDID (PK) |
TDSPRAS (PK) | TDSPRAS (PK) |
SRTF2 (PK) CLUSTR CLUSTD | /FIVETRAN/STXL_TEXT |
We replaced the SRTF2
, CLUSTR
, and CLUSTD
columns by the new /FIVETRAN/STXL_TEXT
column. The aggregation of these three columns contain the necessary information to decode the human-readable text for the new column.
Handling deleted data during SAP archiving processlink
NOTE: This feature is available for the Fivetran NetWeaver API version 1900216 and higher.
You can mark records archived by a SAP archiving process as non-deleted in your destination by checking the Start tracking archive deletes box under SAP Archiving job users in the Fivetran's SAP configuration screen to enable this feature.
IMPORTANT: Once you checked this box, you cannot un-check it. This change is applied at a given SAP source. This means that all connectors replicating data from the same SAP source will have this feature enabled.
When the box is switched on, an input list labeled SAP Archiving job users appears under SAP Archiving job users. Here, you specify users related to the SAP archiving process users (or any user for whom you would like to enable this feature).
When you have enabled marking archived records as non-deleted for the SAP source, Fivetran does the following:
- A new column is added to all tables synced from the relevant source in your destination,
_fivetran_sap_archived
(BOOLEAN). The column value for the previously synced data becomesNULL
. - The
_fivetran_sap_archived
column value is set toFALSE
for the records synced after the feature was enabled. - The
_fivetran_sap_archived
column value is set toTRUE
for the records archived on the SAP side if the user committing the delete is included in the input list of users. The specified username and the username of the SAP user related to the SAP archiving process must be an exact match. In all other cases, (for example, if the box was checked, but the input user list remains empty), the_fivetran_sap_archived
column value remainsFALSE
. - If this column is
TRUE
, then the corresponding record will not be marked as deleted. For example, depending on the sync mode, such record continues to have_fivetran_deleted=FALSE
in case of soft deletes or_fivetran_active=TRUE
in case of history mode.
See our Configure Fivetran in SAP for more information about configuring SAP archiving deletes.
Replication of views and row-store transparent tableslink
NOTE: This feature is available from Fivetran NetWeaver API version 1900216.
You can replicate data from row-store transparent tables, column-store transparent tables, and views.
There are some differences between row-store transparent tables and views, and column-store transparent tables.
IMPORTANT: Due to limitations in the structure of views and row-store transparent tables, we cannot incrementally sync them. At each sync, we re-sync them.
During a re-sync of views or row-store transparent tables, upcoming records are processed in one of the following ways:
- If the upcoming record is not present in the destination, then it is new record, thus it is inserted into the destination.
- If the upcoming record is already present in the destination, then the existing record is updated with the values of the upcoming record.
IMPORTANT: Records still present in the destination, but which were not present in the current re-sync cycle are permanently deleted in the destination.
Additionally, the delete-capturing mechanism is not needed anymore. Hence, we don't create any trigger or shadow deletes table for views and row-store transparent tables.
Views don't have primary keys*. As a result, we introduce a new column, _fivetran_id
(LONG), which is naturally incremented for each record.
*NOTE: Some views may appear as if they have a primary key in some rare cases. For example, when they are based on a single underlying table. In that case, we have noticed that the view takes the primary key of that table. We automatically discard the primary key constraints for views.
TIP: You can easily distinguish views and row-store tables from standard column-store tables:
- Views have the 'View' prefix attached to their description
- Row-store tables have the "ROW-STORE TABLE" prefix attached to their description
Monthly Active Rows calculationlink
The standard rules for Monthly Active Rows (MAR) still apply to views and row-store transparent tables.
Excluded objectslink
Fivetran does not sync the following objects:
- Database-level system tables
- Tables and views not present in the SAP data dictionaries.
- Temporary tables
- SAP functions