Approach to implementing large formatted reports in SAP BW

On projects implementing reporting using the SAP BW data warehouse, many architects and consultants have to solve the problems of preparing large formatted reports: various statements, statements, etc. Such reports are usually characterized by:

• Non-standard formatting requirements for SAP tools;
• Fixed number of columns;
• A significant number of columns and rows (tens and tens of thousands or more, respectively);
• The requirement for an Excel presentation;
• Required run time no more than a few minutes

Unfortunately, it is often necessary to observe the situation when BW project architects choose the standard BW approach for implementing such reports. The essence of this approach is summarized below.

The consultant creates a BW-BEx workbook that contains one or more BW-BEx reports. Reports are uploaded to separate sheets of this book, which are usually hidden from users. Only one sheet of the book containing the target report form with the necessary formatting is visible.
')
User work with such a report is as follows:

• Depending on the SAP BW Excel tool used, the user starts BW-BEx Analyzer or SBOP Analysis for Office, connects to the SAP BW server, selects a workbook from the role and launches it for execution.
  After a few seconds (sometimes a dozen seconds), the selection screen appears.
  On the screen, the user selects the parameter values. For example, year-month, company code, material group, etc. Then presses the "execute" button.
• Now it’s time to “work” for SAP BW: all BW-BEx reports of the workbook are executed sequentially, report by report, transferring your data to Excel worksheets.
• After receiving each report data in Excel, a VBA macro is launched. The logic of the macro is such that it does nothing until the data of all the reports are received on the Excel sheets.
• When the data of the last report arrived on the Excel sheet, the VBA macro performs the main work of preparing the report formatting.
• When the VBA macro has finished, the user can see the result of the report in his Excel.

The standard approach has a number of advantages: it is simple to implement and most of the specialists in the market are fluent in it. But certain restrictions do not allow to effectively implement large reports. An inefficient implementation turns out (if it turns out at all) to be very inconvenient in work, which negatively affects the attitude of users to the implementation project in general and to SAP BW in particular. The main limitation is the maximum number of cells (the number of rows multiplied by the number of columns) in the report. If their number approaches an empirical 750,000, then the probability of failure due to lack of memory is almost 100%. Those. The report from only 18 columns and a little more than 40,000 lines already fall under this restriction. Excel limits are much higher.

What the consultants do not invent, so that, while remaining within the framework of the standard approach, it is possible to qualitatively make a big report. But almost always nothing happens. “Almost” means compromises, relaxation in requirements. Business users either agree to use more restrictive filters and the report returns less data, either wait a little longer, or manually reduce several report fragments to one.

In order not to tell the client “no, we cannot realize this with such requirements,” it is necessary to first draw the right conclusions from the obvious: each tool is designed for its own task.

The BW BEx Analyzer and SBOP Analysis for Office tools are generally not designed to implement effective reports with a large number of cells, with a number of about 750,000 or more (see SAP note 1040454). Therefore, using the SAP BW data model, you need to choose another tool, another approach to implementation. Then the solution will not only succeed, but will also be effective.

Recent versions of SAP Netweaver, SAP BW and HANA have introduced a greater variety of approaches for publishing BW data to Excel, without using BW BEx. We can mention the following:

• Connect Excel via OData services directly to SAP Netweaver or even to SAP HANA
• Connect Excel to SAP HANA, as a database, directly, through MDX

However, these approaches require either BW on HANA, or the latest versions of Excel, or deviations from the usual concepts of authority, in which users do not work with applications that directly access the database.

I want to talk about an approach that is much less demanding of the new versions of the products used, and somewhat less complex. It is about publishing these reports to an Excel document template via an OLE interface. The Excel template is also stored in the BDS repository on the SAP BW side.

The advantages of the approach with OLE are obvious:

• Works on any modern versions of SAP and Microsoft Excel products.
• There are no limitations on the data volumes in the report, except for those in Excel
• Provides maximum data transfer performance from a BW server to Excel via OLE. Example: a sample of 525,000 cells (70 columns for 7500 rows) is transmitted in 7 seconds.
• Data preparation on the “BW server” is performed in the ABAP report, which, having collected the sample into an internal table, passes it through OLE to an Excel template obtained from BDS.
• Centralized (in one BW-system) maintenance of all objects relevant to the report: Excel template, BW data model, ABAP program for filling in the template.
• Compliance with the usual SAP-standards for access control, development, transport settings, etc.

“Reverse” of the medal - this approach requires programming on ABAP. But, according to the author, this aspect should not cause significant difficulties. A “wrapper” from calling an Excel file from BDS, filling it with data and saving it, for example, to a file on disk or back to BDS is a less standard code that is used with minimal variations from report to report.

Difficulties in ABAP can arise when retrieving data from the BW model. Possible options: call the BEx report in ABAP, call FM RSDRI_INFOPROV_READ, SQL-SELECT on the data model tables. But this is usually in the arsenal of skills of an experienced BW consultant. Deep knowledge of ABAP programming will be needed if there is a need to further speed up the work of the code for preparing data by tuning ABAP-program or even parallelizing calculations. The latter, by the way, is impossible architecturally in the approach with BW BEx workbooks.

In short, the procedure for creating a report using the approach with OLE is as follows.

• Development and debugging of the ABAP code, which returns to the internal data table of the report in accordance with the input parameters. An ABAP code may be in the form of FM, or better, in the form of a static method of an ABAP class;
• Preparing an Excel report template with basic formatting and a vBA macro, which is performed after filling with data. Such a macro usually takes the parameter "number of lines", although this is not necessary. Macro tasks - apply the formatting of report cells, provided that the number of rows is unknown in advance;
• Placing an Excel template in the BDS repository;
• The development and debugging of the ABAP code that fills the internal table of report results, reads an Excel template from the BDS, puts data from the internal table into it in accordance with the mapping “field in table - field in template”, runs the VBA macro for execution, saves the filled file on the disk in the temporary directory and opens it for viewing to the user;
• Preparing a user transaction, which is prepared on the basis of the development from the previous paragraph.

What may be needed to make the first example based on the approach with ABAP-OLE and successfully apply it in the future?

• BDS: OAOR Repository Transaction
• ABAP code fragments for working with documents from BDS via OLE (see below)
• Certificate for a VBA macro or allowing Excel to run macros (see support.microsoft.com/en-us/kb/206637 )

data: l_iref_template type ref to cl_bds_document_set, l_oref_container type ref to cl_gui_custom_container, l_iref_control type ref to i_oi_container_control, l_iref_error type ref to i_oi_error, l_iref_document type ref to i_oi_document_proxy, l_iref_spreadsheet type ref to i_oi_spreadsheet, l_retcode type soi_ret_string. data: lt_signature type sbdst_signature, lw_signature type bapisignat, lt_uri type sbdst_uri, lw_uri type bapiuri, lt_sheet type soi_sheets_table, lw_sheet type soi_sheets. data: lt_fields type standard table of rfc_fields, lv_last_row type i, lv_last_col type i. call method c_oi_container_control_creator=>get_container_control importing control = l_iref_control retcode = l_retcode. check l_retcode = c_oi_errors=>ret_ok. call method l_iref_control->init_control //    pv_template  BDS exporting r3_application_name = pv_template inplace_enabled = 'X' inplace_scroll_documents = 'X' parent = l_oref_container importing retcode = l_retcode. check l_retcode = c_oi_errors=>ret_ok. create object l_iref_template. lw_signature-prop_name = 'DESCRIPTION'. lw_signature-prop_value = pv_template. append lw_signature to lt_signature. refresh lt_uri. call method l_iref_template->get_with_url exporting classname = 'SOFFICEINTEGRATION' classtype = 'OT' object_key = 'SOFFICEINTEGRATION' changing uris = lt_uri signature = lt_signature exceptions nothing_found = 1 error_kpro = 2 internal_error = 3 parameter_error = 4 not_authorized = 5 not_allowed = 6. clear lw_uri. read table lt_uri into lw_uri index 1. check sy-subrc = 0. call method l_iref_control->get_document_proxy exporting document_type = 'Excel.Sheet' importing document_proxy = l_iref_document retcode = l_retcode. check l_retcode = c_oi_errors=>ret_ok. call method l_iref_document->open_document exporting document_url = lw_uri-uri open_inplace = 'X' importing retcode = l_retcode. check l_retcode = c_oi_errors=>ret_ok. free l_iref_error. call method l_iref_document->get_spreadsheet_interface importing error = l_iref_error sheet_interface = l_iref_spreadsheet. call method l_iref_spreadsheet->get_sheets importing sheets = lt_sheet error = l_iref_error. check l_iref_error->error_code = c_oi_errors=>ret_ok. clear lw_sheet. read table lt_sheet into lw_sheet index 1. check sy-subrc = 0. call method l_iref_spreadsheet->select_sheet exporting name = lw_sheet-sheet_name importing error = l_iref_error. check l_iref_error->error_code = c_oi_errors=>ret_ok. refresh lt_fields. call function 'DP_GET_FIELDS_FROM_TABLE' //    lt_fields   pt_excel tables data = pt_excel fields = lt_fields. lv_last_row = lines( pt_excel ). lv_last_col = lines( lt_fields ). call method l_iref_spreadsheet->set_selection //     exporting left = 1 top = 1 rows = lv_last_row columns = lv_last_col. call method l_iref_spreadsheet->insert_range //   exporting columns = lv_last_col rows = lv_last_row name = pv_template. call method l_iref_spreadsheet->insert_one_table // ,    Excel exporting data_table = pt_excel[] fields_table = lt_fields rangename = pv_template. … call method l_iref_document->execute_macro //   MakeFormat   Module1 exporting macro_string = 'Module1.MakeFormat' param1 = lv_last_row param_count = 1 importing error = l_iref_error retcode = l_retcode … concatenate pv_file sy-uzeit '.xls' into pv_file. call method l_iref_document->save_as exporting file_name = pv_file. call method l_iref_document->release_document importing retcode = l_retcode. free: l_iref_spreadsheet, l_iref_document. call method l_iref_control->release_all_documents. call method l_iref_control->destroy_control.