SAP ERP on HANA

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.

Features

Known limitations

• We currently support only column-store transparent tables, row-store transparent tables, and views from the SAP data dictionaries. We do not support compatibility views or projection views. For more information, see Excluded objects.
• For row-store transparent tables and views, we do not support change data capture (CDC). For more information, see Replication of views and row-store tables.
• 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.
• No support for the following DDL (Data Definition Language) statements: CREATE TABLE and most ALTER TABLE operations. In case of such an event, the respective table must be re-synced. However, ADD COLUMN and DROP COLUMN are supported ALTER TABLE statements.
• 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.
• Authorization via API available only for SSH tunneling.
• 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, or lOwErCaSeScHeMa are all unsupported names.

* Special characters include (, &, @, *, $, |, %, ~, -, and ). Underscores are not considered special characters.

Setup guide

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 overview

The SAP ERP on HANA connector interfaces with the application layer of an SAP system. Within this application layer, you need to install the Fivetran Netweaver API, which then pulls a full dump of all selected data from your SAP system. We then use our proprietary tracking mechanism to retrieve modified data at regular intervals.

Syncing empty tables and columns

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.

Table search

Given the large number of tables available in SAP, displaying the entire list directly on your dashboard is impractical. Instead, you may search for the specific tables or views you wish to add to sync manually using the search feature under the Schema tab of your connector's dashboard. You can use the following options:

• Comma-separated values: for example, T000, T001, T002 (spaces are optional and ignored internally)
• Patterns: for example T00*, DD0*
• Combinations of the above: for example, VBAK, T00*, DD0*

It is also possible to adjust the list of tables selected for sync under the Schema tab.

The search supports the following:

• row-store transparent tables
• column-store transparent tables
• views

For more information, see Replication of views and row-store tables.

Additionally, the scope of tables available for search can be restricted within your Fivetran's SAP configuration screen. For more information, see Table list in SAP.

NOTE: Searching for a table or view is only possible if the object exists in both the HANA database and the SAP application layer.

Schema information

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 columns

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 mapping

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:

*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 workflows

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 changes

New tables are not automatically added. Use the Schema tab to add tables as needed.

Initial sync

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 data

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 mode

• 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 mode

• 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 columns

We do not delete columns from your destination.

Capturing deletes

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 table T000 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 scenarios

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 via ALTER 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 partitions

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 data

We support unpacking for two tables, STXL and MDTC.

STXL unpacking

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:

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.

MDTC unpacking

NOTE: This feature is available for the Fivetran NetWeaver API version 1900241 and higher.

We unpack table MDTC table into a readable and transparent format. We have modified the table structure to account for this. The following is the replicated MDTC version:

We have replaced the CLUSTR and CLUSTD columns by adding 59 new columns:

• We expanded the primary key with two columns, DTNUM (PK) and DTPOS (PK).
• We added 57 non-primary key columns containing unpacked data.

Unpacking MDTC is a many-to-many type of unpacking - one or more records from the source could be unpacked into one or more records in your destination, in human-readable format.

IMPORTANT: The MDTC table only replicates data in Live mode. Therefore, deletes that occur in your source become hard deletes in your destination.

IMPORTANT: The SAP archiving process feature is currently not available for this table.

Handling deleted data during SAP archiving process

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 becomes NULL.
• The _fivetran_sap_archived column value is set to FALSE for the records synced after the feature was enabled.
• The _fivetran_sap_archived column value is set to TRUE 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 remains FALSE.
• 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 documentation for more information about configuring SAP archiving deletes.

NOTE: The handling SAP archiving process does not work for the unpacked MDTC table. See the MDTC unpacking section for more details.

Monthly Active Rows calculation

The records archived by SAP archiving processes are seen in Fivetran as either updates (if this feature is switched on) or deletes (if the feature is switched off) and count towards paid MAR. The standard rules for Monthly Active Rows (MAR) still apply for these operations.

Replication of views and row-store transparent tables

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 calculation

The standard rules for Monthly Active Rows (MAR) still apply to views and row-store transparent tables.

MANDT filtering

NOTE: This feature is available for the Fivetran NetWeaver API version 1900230 and higher.

When logging into an SAP system, the login is client-specific, and data is automatically filtered by client (MANDT). Our connector has a broader data access and can replicate data for all clients, regardless of the logon client (client-independent data selection). You can enable MANDT filtering in the Fivetran's SAP configuration screen to limit the replicated data to only data relevant to the connected client.

For more information about enabling the MANDT filtering, see our Configure Fivetran in SAP documentation.

Table list in SAP

NOTE: This feature is available for the Fivetran NetWeaver API version 1900230 and higher.

In the Fivetran's SAP configuration screen, you can restrict the tables allowed for replication by listing them in the Source tables list. By default, this list is empty, allowing replication of all supported tables and views. Adding specific tables to this list limits the tables available for search in the Schema tab of your connector's dashboard. Only tables included in this list can be searched for and added for sync.

Filtering out existing tables

You may want to exclude a table that was previously added to the connector and potentially already has synced data. To achieve this, simply do not include or remove the table from in Source tables list in the Fivetran's SAP configuration screen. This will prevent further replication of the table, and it will be automatically disabled during the next sync.

Example scenarios

Here are some examples to illustrate how filtering works:

Scenario 1: No Filtering

If no tables are added to the Source tables list in the Fivetran's SAP configuration screen, all supported tables and views will be available for replication.

Scenario 2: Adding new tables

The current Schema tab contains tables T000 and T001. If a user adds tables T000, T001, T002, and BSEG to the Source tables list, the existing tables (T000 and T001) can still be replicated. However, only the newly added tables (T002 and BSEG) will be available for searching and adding for sync in the Schema tab.

Scenario 3: Replacing existing tables

The current Schema tab contains tables T000 and T001. If a user adds only table BSEG to the Source tables list, the existing tables (T000 and T001) will be automatically removed from the connector's Schema tab. Only table BSEG will be available for searching and adding for sync in the Schema tab.

Scenario 4: Removing and adding tables

If the current Schema tab contains tables T000 and T001, and these tables are no longer permitted because they are not specified in the Source tables table list, Fivetran will gray them out in the Schema tab and will not sync them. If you re-add tables T000 and T001, Fivetran will sync them and gray them out during the next sync cycle.

Excluded objects

Fivetran does not sync the following objects:

• Database-level system tables
• Tables and views not present in the SAP data dictionaries
• Compatibility views
• Projection views
• Temporary tables
• SAP functions