Microsoft Excel allows you to use formulas and other functionalities for further data manipulation. As a result, people who analyze large amounts of data prefer to use Excel. Sometimes Excel documents used for data analysis are developed to a very sophisticated level with many complicated formulas, references, and macros.

However, when you develop reports on this data, it can be extremely complicated to reuse the existing Excel documents. If you have several Excel documents that are the destination for data from SAP NetWeaver BW, it is impossible to develop a report using SAP NetWeaver BW functionality.

The first time I was asked to transfer an SAP NetWeaver BW report to an Excel document, I found it to be a risky and inefficient process. The Excel documents had complicated data structures (e.g., multiple sheets and formulas) and contained charts. The destination Excel document had to be updated frequently with data, so an SAP NetWeaver BW report was used to retrieve the data. Somehow, the data from the report had to be transferred to the Excel document — this operation was repeated daily.

To transfer the data to the Excel document, I had to convert, copy, and paste the data each time the SAP NetWeaver BW report was generated. I called the report manually and copied selected ranges of data from the report into appropriate cells of the destination Excel document. This process took hours and ran the risk of human error.

I needed to find a way to automatically transfer the data from the SAP NetWeaver BW query to the Excel documents. At first, creating SAP NetWeaver BW workbooks out of the Excel documents seemed like the best solution. To do this, I ported each Excel document into a new SAP NetWeaver BW workbook and created a new BEx query for each workbook. Because of the complicated data structure of the Excel documents, in many cases I had to write Excel macros (VBA) for updating and converting data. Additionally, about 80 of the Excel documents had the same data structure. The only difference was the customer number for which the new SAP NetWeaver BW data had to be filtered before putting them into Excel. Creating 80 SAP NetWeaver BW workbooks was not the easy solution I wanted.

Instead, I decided to find a solution to fill the existing Excel documents with the appropriate data without having to change the documents or create SAP NetWeaver BW workbooks. To do this, I needed to obtain data from SAP NetWeaver BW and fill each Excel document with the data. Here are the steps of the data flow:

1. Java application calls (using SAP Java Connector [SAP JCo]) ABAP function module (Z_BW_EXEC_MDX)

2. ABAP function module executes SAP NetWeaver BW query (using BAPI and multi-dimensional expressions [MDX])

3. ABAP function module obtains data from SAP NetWeaver BW query

4. Java application obtains data from ABAP function module

5. Java application modifies (using Apache POI library) desired Excel files

I needed the following technology components for my solution:

• Use an existing SAP NetWeaver BW query (the same as for generating an SAP NetWeaver BW report). This saves the effort of accessing BW data and creating or modifying BW queries.
• Use a remote function module for getting access to the data from outside the SAP system
• Use the SAP JCo library for accessing the SAP system (and the function module) from an external application. Using the SAP JCo library implies using Java for developing the external application.

Implement a Java application and use a Java library to fill Excel documents with data. I decided to use the Apache POI library. The Apache POI library allows you to access (to fill) Excel documents in the Microsoft Office 2007 (XLSX) format and in the previous (XLS) format. Apache POI library is free of charge. It is available under: https://poi.apache.org/

The crucial points of the data flow of one iteration (requesting data from the BW system and punting it into Excel document) follow:

• Define an SAP NetWeaver BW query as a basis for retrieving the data from a BW system. With the query you select the data you transfer in the next steps. For example, the query returns the number of cars sold for each month.
• The Java standalone application calls a remote ABAP function module (Z_BW_EXEC_MDX) using the SAP JCo library. To select the desired data, the function module receives an MDX statement. In the MDX statement, you specify the name of the query you want to call and enter parameters specified in the WHERE block of the MDX statement.
• The function module calls standard BAPI modules that execute the MDX
• The function module returns values produced by the call of the MDX. The Java application gets the results.

• The Java standalone application updates the appropriate Excel documents with the new data

MDX

I used MDX because I wanted to reuse existing SAP NetWeaver BW reports. The existing reports generated the desired data that needed to be transferred. Also, it is reassuring to be able to design a BW query with Query Designer and then let a report generate to see the results of the query. If the data presented on the report is correct you can be sure that the selection of data works properly. Using MDX allows you to define a statement that retrieves simple data from a complicated SAP NetWeaver BW query. For example, you can ask for a billed quantity as a key figure and company code as a characteristic from a much more complicated SAP NetWeaver BW query (Figure 1). Using MDX, you can ask for partial information retrieved by an SAP NetWeaver BW query.

Figure 1

Default screen of the MDXTEST transaction with a simple MDX statement

If you have several Excel documents, it is sufficient to have only one SAP NetWeaver BW query as the information source. You can specify for each information destination a separate MDX to retrieve the desired data. For example, you can define a common query and in one case select the number of sold cars for all customers. In another case you can use the customer number as a parameter and select only the number of sold cars relevant to the specified customer number.

To begin working with MDX, use transaction MDXTEST. In the transaction, you can browse through the characteristics and key figures of an SAP NetWeaver BW query. You can form your own MDX statements, execute them, and show results. Within the results, you obtain columns with data depending on the specified statement.

When you have at least one working MDX, you can use it as the input data for the Z_BW_EXEC_MDX function module. You can download the code for this through this link (you need to be running ABAP to view the code). You have to put your MDX code into the IT_MDX_TEXT table. The function module takes the text of the MDX statement from the IT_MDX_TEXT table. The length of a row of the IT_MDX_TEXT table is 75 characters so you have to split the text into parts, each having the maximum length of 75 characters.

Test the function module in transaction SE37. The text of the MDX is case sensitive, so you need to pay attention when testing the module in transaction SE37. By default all data is converted to uppercase – select an appropriate checkbox when calling the function module.

After you successfully call the function module (in transaction SE37), you can look at the results:

• T_OPTION_PRPTYS_VALS, which contains the values of characteristics (Figure 2)
• T_CELL_DATA STRUCTURE, which  contains the values of key figures (Figure 3)

Figure 2

Content of the T_OPTION_PRPTYS_VALS table

Figure 3

Content of the T_CELL_DATA STRUCTURE table

Note that both tables have a fixed number of columns. The structure of these tables stays the same regardless of the number of characteristics and key figures required by the MDX statement.

• T_OPTION_PRPTYS_VALS: The values of different characteristics are stored in different rows. The column TUPLE_ contains the number of the original row from the result set (equal to the number of rows listed in the MDXTEST transaction after firing the MDX expression).
• T_CELL_DATA STRUCTURE: The values of different key figures are stored in different rows one after another. For example, if you select three key figures in your MDX, you obtain three rows for each row from the result set in the result table.

In the Java application, I developed a central class that is responsible for the connection and the data transfer to and from the SAP NetWeaver BW system. This class converts the received data to a more intuitive representation—the content of the tables T_OPTION_PRPTYS_VALS and T_CELL_DATA STRUCTURE is converted to arrays. In the array corresponding to the T_OPTION_PRPTYS_VALS table the number of elements in each row is equal to the number of characteristics from the MDX statement.

In the array corresponding to the T_CELL_DATA STRUCTURE table, the number of elements in each row is equal to the number of key figures from the MDX statement. The length of both arrays is equal to the number of rows from the result set. With such arrays it is much easier to iterate over the results — repeat a processing of the data from each row. For example, you need to process the data to obtain a customer number from each row. Depending on your needs, you could, for example, count the total amount of cars sold for each customer.

For each Excel destination, I developed a dedicated class that takes two arrays as the input. The class contains the necessary logic to transfer the data to the destination document. This approach makes extending the application easier. For a new Excel document, you have to implement only a class with the logic for transferring the data to the destination. You do not have to bother executing MDX statements and the connection to the BW system — all this is made in the main class, which is the central point for executing MDX statements. Once you have the main class, you can easily extend the application. You could also use other destinations besides an Excel document. For example, you could implement a Java class that generates a PDF file.

Security Aspect

In my solution, I use the JCo Library for the connection to SAP NetWeaver BW. The login data is in the configuration file. The password is written with plain text so only administrators can access the configuration file. The user that obtains the data must be able to access my remote function module and all queries used in MDX statements. A read-only access to queries is sufficient to obtain the data. Note that my method with a configuration file is secure as long as only administrators have access to the directory with the configuration file. There is no need for other users to access the directories.

Start the Application

To provide frequent and automatic updates of the Excel files, you can schedule the data transfer from SAP NetWeaver BW. To schedule the Java standalone application, you can create a job in your SAP system or you can schedule the start of your application in the operating system.

To ensure the application was running properly, I used the free Apache log4j logging library. The system automatically records all problems with writing the data to destination Excel documents into the log file. The information in the log file helps you identify problems and the time at which the problem occurred.

I tested this solution both on Linux and Windows operating systems. The Java standalone application may work on a different operating system than the one used by the BW system. By using the SAP JCo library for the connection to the SAP NetWeaver BW system, the described solution is independent from the SAP NetWeaver portal and does not require you to have either the portal or the Java stack server.

Witold Drozdzynski

Witold Drozdzynski studied computer science and has worked for several years as a Java Web application architect and developer at the German Research Center for Artificial Intelligence and Capgemini. Since 2007, Witold has worked on SAP projects. His main areas of expertise are SAP NetWeaver Portal, J2EE applications, SAP NetWeaver BW, and integration of SAP with external systems (Web and standalone applications).

You may contact the author at editor@bi-expertonline.com.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.