Today, we would like to present a mechanism to consume BEx query data in an ABAP environment.
In the recent months, we were contacted by a partner company looking for a solution to a peculiar problem:
"The client needs to expose BW data to a third-party tool via a push mechanism. The client wants to consume data from already existing BEx queries in order to utilize the logic already built in them."
For this purpose, we have developed an ABAP program which can dynamically get data from any BW Query and pass it for transfer via a REST API call.
As a first step, we had to choose the best format for presenting BEx query data. The most popular lightweight text format for data interchange is JSON (JavaScript Object Notation). It presents a human-readable collection of data which can be consumed by web services and APIs.
The second step was to find best way for reading BEx query data and transforming it in JSON format. As an example, we will use a simple query with one characteristic "Calendar Year/Month" in columns and Key Figure "Total Profit" in rows for the sake of walking through the implementation (see Picture 1).
Picture 1 Simple Query
Exploring data from SAP BEx queries can be done using standard functionality from SAP. No additional changes to existing queries are required, one can fetch the data and use it. The function module (FM) RRW3_GET_QUERY_VIEW_DATA returns the structure and content of the query data, which makes it easier to display it via a web service. This function allows advanced query control via run-time defined parameters. These parameters represent the defined query variables using BEx query designer or BW modeling tools.
The FM RRW3_GET_QUERY_VIEW_DATA returns four parameters – e_axis_info (Information on Axes), e_axis_data (Axis Data), e_cell_data (Data Cells) and e_txt_symbols (Text Symbols) . The parameter "e_axis_data" keeps the information about the rows and columns. In debug mode, you can see that field AXIS = 0 is the column-axis and AXIS = 1 is the row-axis. Also, the field “SET” as an internal table contains the concrete data (see Picture 2). The structure within these data tables is organized by so called tuples. Each tuple represents one row, which needs to be filled up with the cell-data.
Picture 2 Parameter I_AXIS_DATA
Our sample query is called via FM RRW3_GET_QUERY_VIEW_DATA in our program. The program is started in debug mode. Let us see the populated returned parameters of the function module.
Firstly, we will look at the field “SET” as an internal table for AXIS=0, which shows the data in the columns - all 12 values for characteristic Calendar Year/Month from “01.2019” to “12.2019”. Each value for Calendar Year/Month is a new record in the table (see Picture 3). While the field “SET” as an internal table for AXIS=1 contains the data for our Key Figure “Total Profit”, in our case it is only row (see Picture 4).
Picture 3 "E_AXIS_DATA" - "SET" table for Columns
Picture 4 "E_AXIS_DATA" - "SET" table for Rows
The internal table "e_cell_data" represents cell data. In our example it contains values for the Key Figure “Total Profit” for each Calendar Year/Month. The first row keeps value “11,00 EUR” for “01.2019” (see Picture 5). The most important fields in this table are "VALUE" and “FORMATTED_VALUE”. Both fields keep values for Key Figures, the field "VALUE" keeps the raw value, while "FORMATTED_VALUE" keeps the value with its formatting. The formatting contains objects like currency symbols and numeric conventions. If the query data is displayed in tables, the field "FORMATTED_VALUE" is preferred, because it contains all the formatting, which is needed. When it is required to perform further calculations like summationor display in chars, it is better to use the "Value" field.
Picture 5 "E_CELL_DATA"
In our program, we combine results from these three tables in a single internal table (see Picture 6). It is done via several steps. The first step makes a list with all characteristics from the columns ("E_AXIS_DATA" - "SET", AXIS=0). The second step creates dynamic structure type from the list with all characteristics and two additional fields "KEYFIGURE" and "VALUE". In doing so, our result table has a dynamic structure depending on the BEx query. The fields in result table are based on Characteristics used in the query. The next steps populate the result table with query data through several loops.
Picture 6 Query data in single table
After the result table is ready, it is straightforward to create a JSON file with query data. SAP provides a standard functionality for converting internal tables to xml, see code below.
Picture 7 Query data in JSON format
If the receiving Web service requires the query data in a specific JSON format, it could be done through a loop on the result table. In the loop, we can manipulate the query data in required format (see Picture 8).
Picture 8 Query data in specific JSON format
Finally, we have our query data in JSON format. Web services and APIs can consume it in this format. Additional information about REST Service Integration in SAP BW could be found here.