Set up CDS-based replication: SAP S/4HANA to BigQuery through SAP Datasphere

With the Replication Flow feature of SAP Datasphere, you can replicate data from SAP S/4HANA to BigQuery.

This guide explains how to replicate data from SAP S/4HANA to BigQuery through SAP Datasphere when you're using Core Data Services (CDS)-based replication for SAP S/4HANA.

The high-level steps are as follows:

  1. Connect SAP Datasphere to the SAP S/4HANA source system.
  2. Connect SAP Datasphere to the Google Cloud project that contains the target BigQuery dataset.
  3. Create a replication flow.
  4. Run the replication flow.
  5. Validate the replicated data in BigQuery.

For information about setting up SLT-based replication, see Set up SLT-based replication: SAP S/4HANA to BigQuery through SAP Datasphere.

Before you begin

Before you begin, make sure that you or your administrators have completed the following prerequisites:

Connect SAP Datasphere to the SAP S/4HANA source system

This section provides instructions to establish a connection between SAP Datasphere and the SAP S/4HANA source system.

Install SAP Cloud Connector

To securely connect your SAP Datasphere tenant to the SAP S/4HANA source system, SAP Cloud Connector is required when your SAP S/4HANA source system is running on-premises, hosted on any cloud environment, or if you're using the SAP S/4HANA Cloud Private Edition. However, if you're using the SAP S/4HANA Cloud Public Edition, then the SAP Cloud Connector is not needed. In that case, skip the SAP Cloud Connector installation and configuration, and move to Create a connection to the SAP S/4HANA source system.

If your SAP S/4HANA source system is running on-premises or hosted on any cloud environment, then you need to install and configure the SAP Cloud Connector on your operating system (OS). For information about OS-specific requirements and instructions to install SAP Cloud Connector, see the SAP documentation Preparing Cloud Connector Connectivity.

If you're using the SAP S/4HANA Cloud Private Edition, then the SAP Cloud Connector is pre-installed as part of the SAP S/4HANA setup. In that case, skip the SAP Cloud Connector installation, and move to Configure SAP Cloud Connector.

Configure SAP Cloud Connector

You configure SAP Cloud Connector to specify the SAP Datasphere subaccount, mapping to the SAP S4/HANA source system in your network, and the accessible resources.

This section highlights the most important steps involved in configuring SAP Cloud Connector. For detailed information about configuring SAP Cloud Connector, see the SAP documentation Configure Cloud Connector.

The most important steps are as follows:

  1. In your web browser, access the SAP Cloud Connector administration UI using the host where your SAP Cloud Connector is installed and the port. For example: http://localhost:8443.

  2. Log in to SAP Cloud Connector. If you're logging in for the first time after installing SAP Cloud Connector, then use the following default credentials:

    • Username: Administrator
    • Password: manage

    Before proceeding, change the default password. For more information, see the SAP documentation Initial Configuration.

  3. Specify the following details to connect your SAP Cloud Connector to your SAP BTP subaccount:

    • Details about your SAP Datasphere subaccount, including the subaccount name, region, and subaccount user. For more information about these fields, see the SAP documentation Configure Cloud Connector.
    • For the specified subaccount, a location ID that identifies the location of your SAP Cloud Connector.

  4. To provide access to the SAP S/4HANA source system, add the system mapping information, including information about the internal host and the virtual host system.

  5. For accessing data using CDS view extraction, you must specify the following resources:

    • DHAMB_ -Prefix
    • DHAPE_ -Prefix

    • RFC_FUNCTION_SEARCH

  6. Save your configuration.

Create a connection to the SAP S/4HANA source system

In SAP Datasphere, create a local connection to use the SAP S/4HANA source system for data access. You use this connection to create replication flows.

To create a connection to the SAP S/4HANA source system, perform the following steps:

  1. In SAP Datasphere, go to Connections, and select your space.

  2. Create a local connection to the ABAP system of the connection type SAP S/4HANA On-Premise:

    1. Configure the connection properties according to your SAP Cloud Connector configuration.
    2. Enter the virtual host details that you defined during the SAP Cloud Connector configuration.

    For information about the SAP S/4HANA On-Premise connection type, see the SAP documentation SAP S/4HANA On-Premise Connections.

  3. To validate the connection between SAP Datasphere and SAP S/4HANA, select your connection, and click the Validate Connection icon.

For more information about how to create a connection between SAP Datasphere and SAP S/4HANA, see the SAP documentation Create a Connection.

Before you can use the connection for replication flows, check the SAP Notes relevant to replication flows and implement any necessary note on your SAP S/4HANA system. For more information about the required SAP Notes, see:

Connect SAP Datasphere to Google Cloud project

This section provides instructions to establish a connection between SAP Datasphere and your Google Cloud project that contains the target BigQuery dataset.

Create a service account

For the authentication and authorization of SAP Datasphere, you need an IAM service account in your Google Cloud project. You grant roles to the service account that contains permissions to interact with BigQuery.

You also need to create a JSON key for the service account. You upload the JSON key to SAP Datasphere to authenticate with Google Cloud.

To create a service account, perform the following steps:

  1. In the Google Cloud console, go to the IAM & Admin Service accounts page.

    Go to Service accounts

  2. If prompted, select your Google Cloud project.

  3. Click Create Service Account.

  4. Specify a name for the service account and, optionally, a description.

  5. Click Create and Continue.

  6. In the Grant this service account access to project panel, select the following roles:

    • BigQuery Data Owner
    • BigQuery Job User

  7. Click Continue.

  8. Click Done. The service account appears in the list of service accounts for the project.

Download JSON key for the service account

To download a JSON key for the service account, perform the following steps:

  1. Click the email address of the service account that you want to create a key for.
  2. Click the Keys tab.
  3. Click the Add key drop-down menu, then select Create new key.
  4. Select JSON as the Key type and click Create.

Clicking Create downloads a service account key file. Make sure to store the key file securely, because it can be used to authenticate as your service account. For more information, see Create and delete service account keys.

Create a BigQuery dataset

To create a BigQuery dataset, your user account must have the proper IAM permissions for BigQuery. For more information, see Required permissions.

To create a BigQuery dataset, perform the following steps:

  1. In the Google Cloud console, go to the BigQuery page:

    Go to BigQuery

  2. Next to your project ID, click the View actions icon, , and then Create dataset.

  3. In the Dataset ID field, enter a unique name. For more information, see Name datasets.

  4. In the Location type field, choose a geographic location for the dataset that one you are planning to utilize. After a dataset is created, the location can't be changed.

For more information about how to create BigQuery datasets, see Create datasets.

Upload SSL certificates to SAP Datasphere

To encrypt the data transmitted between SAP and Google Cloud, you need to upload the required Google SSL certificates to SAP Datasphere.

To upload the SSL certificates, perform the following steps:

  1. From the Google Trust Services repository, download the following certificates:

    • GTS Root R1
    • GTS CA 1C3

  2. In the SAP Datasphere, go to System > Configuration > Security.

  3. Click Add Certificate.

  4. Browse your local directory and select the certificates that you downloaded from the Google Trust Services repository.

  5. Click Upload.

For more information from SAP about uploading certificates to SAP Datasphere, see Manage Certificates for Connections.

Upload the driver for BigQuery to SAP Datasphere

The BigQuery ODBC driver acts as a bridge between SAP Datasphere and BigQuery for replication flows. To enable access to BigQuery, you need to upload the required ODBC driver files to SAP Datasphere.

For more information from SAP about uploading the required ODBC driver files to SAP Datasphere, see Upload Third-Party ODBC Drivers (Required for Data Flows).

To upload the driver files, perform the following steps:

  1. From ODBC and JDBC drivers for BigQuery, download the required BigQuery ODBC driver.

  2. In the SAP Datasphere, go to System > Configuration > Data Integration.

  3. Go to Third-Party Drivers and click Upload.

  4. Browse your local directory and select the driver file that you downloaded from ODBC and JDBC drivers for BigQuery.

  5. Click Upload.

  6. Click Sync to synchronize the driver changes. After the synchronization is finished, you can use data flows with the connection.

Create a connection to Google Cloud project

To replicate data from your SAP S/4HANA source system to the target BigQuery dataset, you need to create a replication flow in your SAP Datasphere tenant.

To create a connection to Google Cloud project, perform the following steps:

  1. In SAP Datasphere, go to Connections, and create a new connection in your space.

  2. Choose the connection type as Google BigQuery.

  3. In the Connection details sections, specify the following:

    • Project ID: enter your Google Cloud project ID in lowercase.
    • Location: enter your Google Cloud project location.

  4. In the Credential section, upload the JSON key file that is used for authentication. For more information, see Download JSON key for the service account.

  5. To validate the connection between SAP Datasphere and BigQuery, select your connection, and click the Validate Connection icon.

For more information from SAP about the connection to connect to and access data from BigQuery, see Google BigQuery Connections.

Create a replication flow

You create a replication flow to copy SAP data from your SAP S/4HANA source system to the target BigQuery dataset.

To create a replication flow through CDS, perform the following steps:

  1. In SAP Datasphere, go to Data Builder, and click New Replication Flow.

  2. Specify the source for your replication flow:

    1. Select the source connection of type SAP S/4HANA On-Premise that you have created in the section Create a connection to the SAP S/4HANA source system.

    2. Select CDS_EXTRATION - CDS Views Enabled for Data Extraction as a source container.

    3. Add source objects as required.

      For more information, see the SAP documentation Add a Source.

  3. Specify the target environment for your replication flow:

    1. Select the connection to the Google Cloud project that contains the target BigQuery dataset.

    2. Select the container, which is the dataset in BigQuery, to which you want to replicate your data.

      For more information, see the SAP documentation Add a Target.

  4. Create mappings to specify how the source data is to be changed on its way into the target. For more information, see the SAP documentation Define Mapping.

  5. Save the replication flow.

  6. Deploy the replication flow.

For more information, see the SAP documentation Creating a Replication Flow.

Run the replication flow

Once your replication flow is configured and deployed, you can run it.

  • To run a replication flow, select the replication flow, and click Run.

    Once completed, the Run Status section in the Property panel is updated. For more information, see the SAP documentation Running a flow.

Monitor replication flow status

You can view and monitor the execution details of replication flows.

To monitor replication flow status, perform the following steps:

  1. In the SAP Datasphere, go to Data Integration Monitor > Flows.

  2. Select a flow run in the left panel to view its details.

For more information, see the SAP documentation Monitoring flows.

Validate the replicated data in BigQuery

After the replication flow run is complete, validate the replicated table and data in BigQuery.

To validate the replicated data in BigQuery, perform the following steps:

  1. In the Google Cloud console, go to the BigQuery page.

    Go to BigQuery

  2. In the Explorer section, expand your project to view the dataset and its tables.

  3. Select the required table. The table information is displayed under a tab in the content pane on the right side of the page.

  4. In the table information section, click the following headings to view the SAP data:

    • Preview: shows the data replicated from the SAP S/4HANA source system.
    • Details: shows the table size, the total number of rows, and other details.