📢 🚀Annoucing Generators for SAPI and CDS Extractors in legacy SAP systems
The tools in this repo allow you generate required objects and boilerplate code for establishing a data replication pipeline from SAP into BigQuery by utilizing BigQuery Toolkit for SAP
BigQuery Toolkit for SAP is a set of opinionated utilities, which can be used to programmatically access BigQuery resources from SAP without having to manually write code for common operations such as Table definition sync, SAP to BigQuery Data type mapping, Data chunking, Data Transfer and error handling. BigQuery Toolkit can be used within custom SAP applications such as BADIs, Enhancements, User Exists and standalone programs.
By calling the BigQuery Toolkit Data Load Class from within a Data extraction mechanism of SAP ERP, you can establish a real-time data replication from SAP to BigQuery
This repo includes tools designed to simplify and accelerate the process of extracting data from SAP systems and loading it into Google BigQuery by utilizing Datasources. These tools offer the following benefits -
- Reduced Development Effort: Automates the creation of CDS views / entities and extraction objects, saving developers significant time and effort.
- Simplified Data Export: Provides a streamlined mechanism for exporting data from SAP to BigQuery.
- Increased Efficiency: Automated processes and scheduling capabilities improve the efficiency of data transfer.
- Accelerated Time-to-Production: Reduces the overall time required to set up and deploy data pipelines from SAP to BigQuery.
- Open-Source and Extensible: The open-source nature of the tools allows for community contributions and future extensions.
You can import objects of this repo into your system by using abapGit. Alternatively, you can import the Transport Request attached to the release into your system by using STMS.
- CDS Replication Object Generator is compatible with S/4HANA 1909 and higher.
- Legacy (SAPI) Replication Objects Generator is compatible with all ERP systems
The following steps should be completed before using B/W Generator
- ABAP SDK for Google Cloud On-prem or Any-cloud edition, V1.9 or higher is installed in your SAP system
- BQ Data Transfer Configuration is completed by using Tcode /GOOG/BQTR_SETTINGS
- The ERP system is set up as both BW Source System and ODP - ABAP CDS Views or ODP - SAP (Extractors) in Tcode RSA1. Both must be active
The typical user flow for utilizing the tools in this repository is as follows:
CDS View Generator program ZGOOG_R_BQTR_GEN_MASS_CDS_VIEW can be used to create delta enabled CDS View or CDS entity for transparent tables in SAP.
Input Parameters: - Select .csv file - Enter a file that is stored in your computer. The file should have 2 columns with values separated by commas
- SAP Tablename
- CDS Entity / View Name
- SQL View Name (Optional, only required if you want to generate CDS Views, which are obsolete)
- Enter the appropriate Package to which Generated CDS object should be saved
- Enter Request/Task to be used for saving the CDS Object
Optionally, enable the following check-boxes as needed
- Create CDS View(Obsolete). If not enabled, CDS Root View Entity will be created.
- Include mandt in CDS view. If enabled, mandt field will be included in CDS View.
Upon running, the program will generate CDS Object specified in the file. In case of errors, they will be displayed in the output.
The program ZGOOG_R_BQTR_GEN_REPL_OBJECTS will generate the following data replication objects for each CDS Object specified in the input.
- BW (CDS) Datasource
- Advanced Data Store Object (ADSO)
- Data Transfer Process (DTP)
- Data Transformation
- BW Start Routine
- BW End Routine
- Process Chain to auto-trigger DTP when there is a change in source data
Within Start Routine, the generated code will call the BigQuery Toolkit Data Load Class /GOOG/CL_BQTR_DATA_LOAD to load the data into BigQuery. Within End Routine, the RESULT_PACKAGE[] is cleared, ensuring no data is written to ADSO.
You can run Replication Objects Generator with following input sources -
- Selection screen: With this option, you pass the names of the parameters including the objects to be generated as selection screen parameters
- Mass Generation using file: With this optuon, you provide a file containing one or more data sources for which B/W Objects should be generated.
- You can download a template by selecting the Radio-button 'Download Template'. This template can then be populated with Data sources and used as the input file when executing with radio-button 'Upload File'
The Replication Objects Generator supports the following actions -
- When 'Create Artifcats' radio-button is selected, B/W objects will generated. Before running this action, you must ensure that there are no existing objects with the same name
- When 'Cleanup Artifacts' radio-button is selected, existing B/W objects will be deleted from the system.
- Within this option, you also have flexibility to only delete certain B/W Objects or all the B/W objects by selecting the appropriate radio-button within box 'Select Artifacts for action'
As CDS Views typically do not include the Client (MANDT) field, you can generate code to include the client of the current system as the first column of the BigQuery table by Selecting 'Transfer with MANDT field' check box and entering the name of MANDT field.
When you run the B/W Generator program, it will perform the corresponding action entered in the selection screen and display an action log along with success or error messages. In case of errors, please review your input and perform necessary corrective actions.
For a demonstration of data replication from SAP to BigQuery using the BigQuery Toolkit, please see this video:
You can use the program ZGOOG_R_BQTR_GEN_REPL_OBJ_LEG to generate replication objects for ODP SAPI and CDS Extractors in legacy systems such as SAP ECC.
.
The program ZGOOG_R_BQTR_DEMO_DYN_LOAD demonstrates how you can directly load data from a SAP table or CDS View into BigQuery by using BigQuery Toolkit
You can utilize SAP BW Parallel Processes to increase the performance of Data Transfer to BigQuery
Follow the general instructions of SAP BW to transport BW objects into subsequent environments
As Data Transfer Objects are generated within the context of Embedded BW, you can use standard BW monitoring tools to monitor data transfer and errors. If an error occurs during data transmission to BigQuery, the errors will be logged into SAP BW and the corresponding records will be retained by SAP BW for re-processing. Below are some key links of standard SAP BW tools that can be used for monitoring and handling errors
- Setting up a separate DTP for Error records
- Controlling and monitoring Data warehouse processes
- Tools for troubleshooting
To modify the structure of data replicated from an existing source (e.g., adding, removing, or updating fields), follow these steps.
If your change does not result in data loss (i.e adding a new field or changing the definition of an existing field that increases its lenght), use the below steps -
- Stop the Process Chain and DTP for your datasource
- Edit the CDS View / Entity corresponding to your datasource using ABAP Development Tools (ADT) (i.e Add , remove or edit fields)
- Go to Transaction RSA1, navigate to the Datasource node and select the Datasource from the list
- Right click on the datasource and click on Replicate Metadata. This will bring the updated CDS View definition into the datasource
- Activate the datasource
- Restart the DTP and Process Chain
If your change results in data loss (i.e removing an existing field or changing the definition of existing field), then you would need to delete the BigQuery table, corresponding data transfer objects and generate new data transfer objects using the Replication Objects Generator.
If the initial load fails with an error or if the extractor remains in status "Extraction Running", apply the note https://me.sap.com/notes/0003038236 in your SAP system
If you want to run initialization without data transfer and only do delta transfer - follow the steps in this community blog