Replicating INDX-like tables with BigQuery Connector for SAP

Introduction

BigQuery Connector for SAP installs into SAP Landscape Transformation Replication Server (SLT) and enables near real time replication of SAP data directly into BigQuery.

With your data in BigQuery, you can perform real-time analytics with artificial intelligence or machine learning on live SAP application data. In BigQuery, you can also integrate your SAP data with data from other sources.

BigQuery Connector for SAP uses the standard interfaces and capabilities of SAP Landscape Transformation Replication Server and supports all of the data sources that SAP LT Replication Server supports.

BigQuery Connector for SAP is provided by Google Cloud in the form of transport files that you install into SAP LT Replication Server. The latest version of the connector as of writing this blog is V2.7

BigQuery Connector for SAP is a Google Cloud implementation of an SAP Business Add In (BAdI) for SAP LT Replication Server. BigQuery Connector for SAP is developed by Google Cloud by following SAP LT Replication Server SDK process.

BigQuery Connector for SAP leverages the change data capture functionality of SAP LT Replication Server.

In this specific blog, let’s understand how to replicate INDX-like tables using the connector

What are INDX-like tables in SAP

INDX-like tables in SAP are cluster tables specifically designed for storing temporary or persistent data in a packed binary format, often compressed.

Key Characteristics of INDX-like tables:

Binary Storage: Data is stored in a binary format, which is not directly readable but can be efficient for storage and retrieval.

Compression: Often, data in INDX-like tables is compressed to save space.

Temporary or Persistent: You can use them to store data temporarily within a session or persistently across sessions.

Flexibility: You can create custom INDX-like tables to tailor them to your specific needs.

Some examples of standard INDX-like tables in SAP are STXL, PCL1, PCL2, PCL3, PCL4, PCL5, RFDT etc. This however depends on the type and version of SAP system you are using.

Limitation for replicating INDX-like tables

As the content of INDX-like tables are stored in binary format it is not humanly readable. If such tables are replicated to BigQuery using the BigQuery connector for SAP, database values of BLOB / CLOB fields are replicated as is without any transformation, resulting in BigQuery having data in unreadable format.

SLT Offers a mechanism for replicating INDX-like tables in a readable format. SAP Note 2430480 outlines the steps for replicating such tables. However, the INDX-Like setting cannot be used for SLT configurations that use an SDK, such as BigQuery Connector for SAP

Overcoming Limitations for INDX-like tables

Let us now consider an example to illustrate how to overcome the limitations of INDX-like tables. For this purpose, we shall examine the table STXL.

You can utilize the following option, which entails implementing the following approach:

1. Establish a designated target structure comprised of the fields TDFORMAT and TDLINE (this is relevant for STXL table)
2. Implement an assignment for an end of block event related rule.
3. Replicate the table containing readable data to BigQuery utilizing the BigQuery Streaming/Change Data Capture Connector.

Now let’s examine each of these steps in detail:

Before we begin, following assumptions are made:

You are aware of replicating data using BigQuery Connector for SAP, relevant required configurations in transaction /GOOG/SLT_SETTINGS are made. Client Key data is maintained in table /GOOG/CLIENT_KEY . A mass transfer id is LTRC transaction using the SLT SDK option

Step 1: Create a target structure in transaction LTRS

In transaction LTRS select the mass transfer id using which you want to perform replication, Under Advanced Replication Settings > Table Settings > Add Object STXL . Open the tab Table Structure and perform the following steps:

1. Add a new field ITEMNO with data type: INT4 length: 10 at position 8
2. Add a new field TDFORMAT with data type: CHAR length: 2 at position: 9
3. Add a new field TDLINE with data type: CHAR length: 132 at position 10
4. Remove field CLUSTD

Save the settings, the screen will look similar to the screenshot shown below:

LTRS — Modified Target Structure for STXL

Target Structure for STXL table

Step 2: Create an Include and add the declustering logic

In transaction SE38 create an include program with name ZI_DECLUSTER_STXL (feel free to chose any other name as well). Save it in the required package and assign a transport request as required.

Add the following code in the include program, save and activate it.

Step 3: Maintain Rule Assignment in LTRS transaction

In the transaction LTRS choose your configuration (mass transfer id). The under Advanced Replication Settings > Rule Assignments > Add object STXL

1. Click on new button and add create a Event-Related rule

Create Event-Related Rule

2. Select the Target Event as End of Block (EOT)

Target Event — End of Block (EOT)

3. Provide the include name as ZI_DECLUSTER_STXL and set the status to RELEASED. Save the setting using save button in the application menu bar

Details of Rule for End of Block

Step 4: Perform Initial Load

In the transaction LTRC add the STXL table for initial load

Start Load for STXL

Wait for the load to complete and validate in the BigQuery :

Status of initial load on SLT

Record count on BigQuery

Verify that you can view the de-clustered data:

De-clustered data in BigQuery

Conclusion

Using the above mentioned approach one should be able to successfully load and replicate INDX like tables. In the example considered the de-clustered data in STXL table for RELID = TX was made up of structure containing two fields TDFORMAT and TDLINE, so we used that. However, if you are replicating other INDX-like tables you would need to know before hand what the target structure would look like and accordingly change the code provided above. Please note that for a single INDX-like table based on RELID the target structure can vary.

It is important to set the Dynamic Chunking flag in the BigQuery Connector for SAP’s settings ( using transaction code /GOOG/SLT_SETTINGS) as shown below:

Enable Dynamic Chunking

Also, note that it is possible that corrupted texts exist within the SAP system. In order to validate and delete corrupted texts prior to replicating STXL data to BigQuery, please refer to SAP Note: 125035

Thank you for reading!

Do share your feedbacks and comments, it will be very helpful