Cortex Framework: integration with SAP

This page describes the integrating steps for SAP operational workloads (SAP ECC or SAP S/4HANA) in the Cortex Data Foundation. Cortex Framework integrates data from SAP with Dataflow pipelines through to BigQuery, while Cloud Composer schedules and monitors these Dataflow pipelines for getting insights from your SAP operational data.

The config.json file in the Cortex Data Foundation repository configures the settings required to transfer data from any data source, including SAP. This file contains the following parameters for operational SAP workloads:

  "SAP": {
        "deployCDC": true,
        "datasets": {
            "cdc": "",
            "raw": "",
            "ml": "ML_MODELS",
            "reporting": "REPORTING"
        },
        "SQLFlavor": "ecc",
        "mandt": "100"
    }

The following table describes the value for each SAP operational parameter:

While there is not a minimum version of SAP that is required, the ECC models have been developed on the current earliest supported version of SAP ECC. Differences in fields between our system and other systems are expected, regardless of the version.

Prerequisites for SAP replication

• Cortex Data Foundation expects SAP tables to be replicated with the same field names and types as they are created in SAP.
• As long as the tables are replicated with the same format, names of fields and granularity as in the source, there is no requirement to use a specific replication tool.
• Table names need to be created in BigQuery in lowercase.
• The list of tables used by SAP models are available and configurable in the CDC setting.yaml. If a table is not listed during deployment, the models depending on it would fail. Other models would deploy successfully.
• Consider the following if you are using BigQuery Connector for SAP:
  • About the conversion option, follow the default table mapping documentation.
  • Disabling record compression is recommended, because compression might change original SAP data in a way that impacts Cortex CDC layer as well as Cortex reporting dataset.
• If you are not planning on deploying test data, and if you are planning on generating CDC DAG scripts during deployment, make sure table DD03L for SAP metadata is replicated from SAP in the source project. This table contains metadata about tables, like the list of keys, and is needed for the CDC generator and dependency resolver to work. This table also lets you to add tables not covered by the model to generate CDC scripts, like custom or Z tables.

• If there is a minor differences in a table name some views might fail not finding a field, because SAP systems might have minor variations due to versions or add-on, and append structures into tables, or because some replication tools may have slightly different handling of special characters. Executing the deployment with turboMode : false is recommended to spot most failures in one attempt. For example:

  • Fields starting with _ (for example, _DATAAGING) have their _ removed.
  • Fields can't start with / in BigQuery.

  In this situation, you can adapt the failing view to select the field as it is landed by your replication tool of choice.

Replicating raw data from SAP

The purpose of the Data Foundation is to expose data and analytics models for reporting and applications. The models consume the data replicated from an SAP system using a preferred replication tool, like those listed in the Data Integration Guides for SAP.

Data from SAP system (either ECC or S/4HANA) are replicated in raw form. The data is copied directly from SAP to BigQuery without any changes to its structure. It's essentially a mirror image of the tables in your SAP system. BigQuery uses lowercase table names for its data model. So, even though your SAP tables might have uppercase names (like MANDT), they are converted to lowercase (like mandt) in BigQuery.

Change Data Capture (CDC) processing

Choose a CDC processing methods. Cortex Framework offers two ways for replication tools to load records from SAP:

• Append-always: Insert every change in a record with a timestamp and an operation flag (Insert, Update, Delete), so the last version can be identified.
• Update when landing (merge or upsert): This creates an updated version of a record on landing in the change data capture processed. It performs the CDC operation in BigQuery.

Cortex Data Foundation supports both modes, although for append-always, it provides CDC processing templates. Some capabilities need to be commented out for update on landing. For example, OneTouchOrder.sql and all its dependent queries. The capability can be replaced with tables like CDPOS.

Configure CDC templates for tools replicating in append-always mode

Configure CDC templates according to your needs, because some default frequencies might result in unnecessary costs if the business does not require such level of data freshness. If using a tool that runs in append-always mode, Cortex Data Foundation provides CDC templates to automate the updates and create a latest version of the truth or digital twin in the CDC processed dataset.

You can use the configuration in the file setting.yaml if you need to generate CDC processing scripts. See Set up CDC Processing for options. For test data, you can leave this file as a default.

Make all required changes to the DAG templates according your instance of Airflow or Cloud Composer. See more information in the Appendix - Gathering Cloud Composer settings.

Optional: If you want to add and process tables individually after deployment, you can modify the setting.yaml file to process only the tables you need and re-execute the specified module calling src/SAP_CDC/cloudbuild.cdc.yaml directly.

Set up CDC processing

During deployment, you can choose to merge changes in real time using a view in BigQuery or scheduling a merge operation in Cloud Composer (or any other instance of Apache Airflow). Cloud Composer can schedule the scripts to process the merge operations periodically. Data is updated to its latest version every time the merge operations execute, however, more frequent merge operations translate into higher costs. Customize the scheduled frequency according your business needs. For more information, see scheduling supported by Apache Airflow.

The following example script shows an extract from the configuration file:

  data_to_replicate:
    - base_table: adrc
      load_frequency: "@hourly"
    - base_table: adr6
      target_table: adr6_cdc
      load_frequency: "@daily"

This configuration sample file does the following:

1. Create a copy from source\_project\_id.REPLICATED\_DATASET.adrc into target\_project\_id.DATASET\_WITH\_LATEST\_RECORDS.adrc, if the latter does not exist.
2. Create a CDC script in the specified bucket.
3. Create a copy from source\_project\_id.REPLICATED\_DATASET.adr6 into target\_project\_id.DATASET\_WITH\_LATEST\_RECORDS.adr6\_cdc, if the latter does not exist.
4. Create a CDC script in the specified bucket.

If you want to create DAGs or runtime views to process changes for tables that exist in SAP and are not listed in the file, add them to this file before deployment. This works as long as the table DD03L is replicated in the source dataset and the schema of the custom table is present in that table. For example, the following configuration creates a CDC script for custom table _zztable\_customer_ and a runtime view to scan changes in real time for another custom table called _zzspecial\_table_:

    - base_table: zztable_customer
      load_frequency: "@daily"
    - base_table: zzspecial_table
      load_frequency: "RUNTIME"

Sample generated template

The following template generates the processing of changes. Modifications, such as the name of the timestamp field, or additional operations, can be modify at this point:

  MERGE `${target_table}` T
  USING (SELECT * FROM `${base_table}` WHERE recordstamp > (SELECT IF(MAX(recordstamp) IS NOT NULL, MAX(recordstamp),TIMESTAMP("1940-12-25 05:30:00+00")) FROM `${target_table}`)) S
  ON ${p_key}
  WHEN MATCHED AND S.operation_flag='D' AND S.is_deleted = true THEN
    DELETE
  WHEN NOT MATCHED AND S.operation_flag='I' THEN
    INSERT (${fields})
    VALUES
    (${fields})
  WHEN MATCHED AND S.operation_flag='U' THEN
  UPDATE SET
      ${update_fields}

Alternatively, if your business requires near-real time insights and the replication tool supports it, the deployment tool accepts the option RUNTIME. This means a CDC script won't be generated. Instead, a view would scan and fetch the latest available record at runtime for immediate consistency.

CDC fields required for MERGE operations

The following parameters are required for the automated generation of CDC batch processes:

• Source project + dataset: Dataset where the SAP data is streamed or replicated. For the CDC scripts to work by default, the tables need to have a timestamp field (called recordstamp) and an operation field with the following values, all set during replication:
  • I: for Insert.
  • U: for Update.
  • D: for Deletion.
• Target project + dataset for the CDC processing: The script generated by default generates the tables from a copy of the source dataset if they don't exist.
• Replicated tables: Tables for which the scripts need to be generated
• Processing frequency: Following the Cron notation, how frequently the DAGs are expected to run:
• Target Cloud Storage bucket where the CDC output files are copied.
• Connection's name: The name of the connection used by Cloud Composer.
• Optional: If the result of the CDC processing remains in the same dataset as the target, you can specify the name of the target table.

Performance optimization for CDC tables

For certain CDC datasets, you might want to take advantages of BigQuery table partitioning, table clustering or both. This choice depends on the following factors:

• Size and data of the table.
• Columns available in the table.
• Need for real-time data with views.
• Data materialized as tables.

By default, CDC settings don't apply table partitioning or table clustering. The choice is yours to configure it based on what works best for you. To create tables with partitions or clusters, update the CDC setting.yaml file with relevant configurations. For more information, see Table Partition and Cluster Settings.

1. This feature only applies when a dataset in setting.yaml is configured for replication as a table (for example, load_frequency = "@daily") and not defined as a view (load_frequency = "RUNTIME").
2. A table can be both, a partitioned table as well as a clustered table.

If you are using a replication tool that allows partitions in the raw dataset, like the BigQuery Connector for SAP, setting time-based partitions in the raw tables is recommended. The type of partition works better if it matches the frequency for CDC DAGs in the setting.yaml configuration. For more information, see Design considerations for SAP data modeling in BigQuery.

Configuring the flattener for SAP hierarchies

The deployment process can optionally flatten hierarchies represented as sets (for example, transaction GS03) in SAP. The process can also generate the DAGs for these hierarchies to be refreshed periodically and automatically. This process requires configuration prior to the deployment and should be known by a Financial or Controlling consultant or power user.

If you need to generate scripts to flatten hierarchies, you can use the configuration in the file sets.yaml. This step is only executed if the CDC generation flag is set to true.

The following are examples of configurations for Cost Centers and Profit Centers including the technical information. If unsure about these parameters, consult with a Finance or Controlling SAP consultant.

    sets_data:
    #Cost Centers:
    # table: csks, select_fields (cost center): 'kostl', where clause:Controlling Area (kokrs), Valid to (datbi)
    - setname: 'H1'
      setclass: '0101'
      orgunit: '1000'
      mandt:  '800'
      table: 'csks'
      key_field: 'kostl'
      where_clause: [ kokrs = '1000', datbi >= cast('9999-12-31' as date)]
      load_frequency: "@daily"
    #Profit Centers:
    # setclass: 0106, table: cepc, select_fields (profit center): 'cepc', where clause: Controlling Area (kokrs), Valid to (datbi)
    - setname: 'HE'
      setclass: '0106'
      orgunit: '1000'
      mandt:  '800'
      table: 'cepc'
      key_field: 'prctr'
      where_clause: [ kokrs = '1000', datbi >= cast('9999-12-31' as date) ]
      load_frequency: "@monthly"
    #G/L Accounts:
    # table: ska1, select_fields (GL Account): 'saknr', where clause: Chart of Accounts (KTOPL), set is manual. Might also need to poll Financial Statement versions.

The deployment file takes the following parameters:

• setname: Name of the set.
• setclass: Class of the set (as listed by SAP in standard table SETCLS)
• orgunit: Organizational unit controlling area or additional key for the set
• mandt: Client or Mandant.
• table: Reference table for the referenced primary data.
• key_field: Reference key field for primary data.
• where_clause: Additional filter conditions (where clause).
• load_frequency: frequency of loading data.

This configuration generates two separate DAGs. For example, if there were two configurations for Cost Center hierarchies, one for Controlling Area 1000 and one for 2000, the DAGs would be 2 different files and separate processes but the target, flattened table would be the same.

What's next?

• For more information about other data sources and workloads, see Data sources and workloads.
• For more information about the steps for deployment in production environments, see Cortex Data Foundation deployment prerequisites.