codbex

Appearance

Migrate BW Query (BEx) with ABAP to Snowflake with Tableau ​

It is common to create BEx Variables which are processed within a Customer Exit with the help of ABAP coding.

global-settings.png

Coding can be implemented within BADI or User-Exit but to avoid locking and versioning issues (a well known ABAP problem where 2 people cannot work on the same piece of coding and transport locking) many customers are implementing a wrapper class, which dynamically calls the respective class for each variable.

wrapper-class.png

Some Info how normally these is done:

  • Execute variable exit class
  • For each customer exit variable, a dedicated class needs to be created based on Specific logic, normally these classes are inheriting the name of the Variable.

Knowing this story, in this demo, we want to achieve the following:

  • Translate BW Query Graphical View into SQL

We are going to use the Flight demo data provided by IDES. The query we will use is: CP_Z1SFLIGHT_26

BEx-SQL-ABAP.png

We will look at and use the customer exit Variable: C_V_CONNID01

exit-variable.png

Our Input Variable is for the flight date – I_V_FLDATE

input-variable.png
  • This is an example of how to Process BW Customer Exit Variables within Snowflake

Class ZCL_BADI_VAR_PROCCESING – Main Class for Customer exit Processing

main-class.png

Class ZCL_VAR_PROCESSING – Abstract Class which is used for the Processing of the Specific variables

abstract-class.png

Class ZCL_VAR_C_V_CONNID01 – Inheriting from ZCL_VAR_PROCESSING

inherit-class.png

Logic implemented within the Class is to get the connection IDs for a specific date, where User Input is the date.

logic.png
  • Create Simple Report within Tableau with Input Variable (Input will be processed within the Customer Exit Variable)

Process information ​

The description above shows the general idea and the story behind the process.

SAP BW Implementation ​

The first BW and ABAP code can be seen using Eclipse IDE.

process.png

The CP_Z1SFLIGHT_26 query used allows the user to provide a date for the filter and the result is based on the filter provided. The date used for the example is 26.08.2020

query.png

As previously described the ABAP code behind the CP_Z1SFLIGHT_26 query can be reviewed in the Eclipse environment.

result.png

Moving the data to Snowflake ​

Flights data can be transferred to Snowflake by using any of the compatible for both SAP and Snowflake ETL tools such as Informatica, Talend or Iapetus by codbex.

BEX Booster tool ​

The purpose of the BEX booster tool is to transfer the SAP BW query to SQL.

bex-booster.png

The version of the tool used in the demo is built in Streamlit as a Snowflake native app. The screenshot below shows the generated query:

gen-query.png

There are cases that could not be covered by the queries generated. The discussed approach is used to address these issues.

Process architecture ​

The codbex Kronos tool is used for the execution of the ABAP code. The tool is converting the ABAP code to JavaScript by Open-ABAP. The ABAP code is queried using Snowflake UDF and a result is returned. The UDF is incorporated in the query defined by the BEX Booster tool.

Setting up Snowflake container environment for codbex ​

The Kronos tool is installed on Snowpark container services. It follows the description in Implement Snowflake UDF for leave request days calculatio

The current installation is done using the following steps:

  • Setting up the required roles, warehouses and rights in the Snowflake environment
set-env.png
  • Setting up the required infrastructure
set-infra.png
  • Running the Kronos container
run-kronos.png

ABAP Code in Kronos ​

The Kronos IDE is used for the ABAP code implementation and interface between Kronos and Snowflake.

kronos-ide.png

TIP

The ABAP code was implemented as-is, requiring only minor or no adjustments.

kronos-abap.png

It follows the same code pattern and architecture as in the SAP BW (described in the first section above).

kronos-bw.png

The ABAP process accepts 4 parameters from Snowflake and is returning a string value. In general the approach allows the process to accept a different number of parameters.

kronos-params.png

Setting up SNOWFLAKE UDF ​

The Kronos image is deployed on Snowpark Container services, and it follows the Snowflake best practices. This simplifies the definition of the snowflake UDF.

udf.png

The adapted query using the defined UDF can be seen in the screenshot below. The results match the result from the first ABAP query.

udf-result.png

Tableau Frontend Implementation ​

Tableau is a front-end reporting tool which is used for the execution of the query defined in the points defined in the points defined above.

tableau.png

The same filters are used for evaluating the query:

tableau-result.png

Conclusion ​

The documented approach outlines a systematic process for integrating existing ABAP code with Snowflake environments. When implemented alongside the BEX Migration Booster this solution provides a comprehensive framework for organizations transitioning from SAP BW queries to Snowflake. This integrated approach offers several advantages:

  • Streamlines the migration of complex SAP BW queries.
  • Preserves existing business logic in ABAP code.
  • Reduces transformation time and technical debt.
  • Ensures consistency between source and target systems.
  • Minimizes the need for extensive code rewrites.