October 28, 2015 at 11:43Approach to implementing large formatted reports in SAP BW
On projects for implementing reporting using the SAP BW data warehouse, many architects and consultants have to solve the problem of preparing large formatted reports: various statements, statements, etc. Such reports are usually characterized by:
Unfortunately, it is often necessary to observe a situation when architects of BW projects choose a standard approach for implementing such reports for BW. A brief summary of this approach is outlined 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 is left visible, containing the target report form with the necessary formatting.
The user’s work with such a report is as follows:
The standard approach has several advantages: it is easy to implement and it is well-mastered by most specialists in the market. But certain restrictions do not allow the effective implementation of large reports. And an ineffective implementation is obtained (if at all) 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 times the number of columns) in the report. If their number is close to empirical 750,000, then the probability of failure due to lack of memory is almost 100%. Those. a report of only 18 columns and just over 40,000 rows already falls under this limitation. But Excel has a lot more limits.
What do the consultants not come up with so that, remaining within the framework of the standard approach, to qualitatively make a great report. But almost always nothing happens. “Almost” means compromises, concessions in requirements. Business users either agree to use more restrictive filters and the report returns less data, or wait longer to complete, or manually combine several report fragments into one.
In order not to tell the client “no, we cannot realize this under such requirements”, it is first necessary to draw the right conclusions from the obvious: each tool is designed for its 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 a different tool, a different implementation approach. Then the solution will not only work out, but will also be effective.
Recent versions of SAP Netweaver, SAP BW, and HANA have added a wide variety of approaches for publishing BW data to Excel, without using BW BEx. You can mention these:
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 access the database directly.
I want to talk about an approach that is much less demanding on the novelty of the versions of the products used, and in something less complicated. We are talking about publishing report data in an Excel document template through the OLE interface. The Excel template is stored in the BDS repository on the SAP BW side.
The advantages of the OLE approach are obvious:
The “reverse side” of the coin - this approach requires programming in 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 more or 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: calling a BEx report in ABAP, calling FM RSDRI_INFOPROV_READ, SQL-SELECT from the data model tables. But this is usually in the arsenal of skills of an experienced BW consultant. A deep knowledge of ABAP programming will be needed if there is a need to further accelerate the work of the code for data preparation by tuning the ABAP program or even parallelizing the calculations. The latter, incidentally, is not architecturally possible with the BW BEx workbooks.
In short, the procedure for creating a report using the OLE approach is as follows.
What may be needed to make the first example based on the ABAP-OLE approach and successfully apply it in the future?
- Non-standard formatting requirements regarding SAP tools;
- Fixed number of columns;
- A significant number of columns and rows (respectively, tens and tens of thousands or more);
- The requirement for an Excel view;
- Requirement for runtime no more than a few minutes
Unfortunately, it is often necessary to observe a situation when architects of BW projects choose a standard approach for implementing such reports for BW. A brief summary of this approach is outlined 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 is left visible, containing the target report form with the necessary formatting.
The user’s work with such a report is as follows:
- depending on the used SAP BW Excel tool, the user launches the BW-BEx Analyzer or SBOP Analysis for Office, connects to the SAP BW server, selects a workbook from the role and starts it to run.
After a few seconds (sometimes a dozen seconds), a selection screen appears.
On the screen, the user selects the parameter values. For example, year-month, company code, material group, etc. Then he presses the “execute” button. - Now it’s the turn to “work” for SAP BW: all BW-BEx reports of the workbook are executed sequentially, report after report, transferring their data to Excel worksheets.
- After receiving the data of each report in Excel, the VBA macro is launched. The logic of the macro is such that it does nothing until the data of all reports is received on Excel sheets.
- When the data of the last report arrived on the Excel sheet, the VBA macro does the main job of preparing the formatting of the report.
- When the VBA macro has finished, the user can see the result of the report in his Excel.
The standard approach has several advantages: it is easy to implement and it is well-mastered by most specialists in the market. But certain restrictions do not allow the effective implementation of large reports. And an ineffective implementation is obtained (if at all) 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 times the number of columns) in the report. If their number is close to empirical 750,000, then the probability of failure due to lack of memory is almost 100%. Those. a report of only 18 columns and just over 40,000 rows already falls under this limitation. But Excel has a lot more limits.
What do the consultants not come up with so that, remaining within the framework of the standard approach, to qualitatively make a great report. But almost always nothing happens. “Almost” means compromises, concessions in requirements. Business users either agree to use more restrictive filters and the report returns less data, or wait longer to complete, or manually combine several report fragments into one.
In order not to tell the client “no, we cannot realize this under such requirements”, it is first necessary to draw the right conclusions from the obvious: each tool is designed for its 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 a different tool, a different implementation approach. Then the solution will not only work out, but will also be effective.
Recent versions of SAP Netweaver, SAP BW, and HANA have added a wide variety of approaches for publishing BW data to Excel, without using BW BEx. You can mention these:
- Connecting Excel via OData Services Directly to SAP Netweaver or even to SAP HANA
- Connecting 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 access the database directly.
I want to talk about an approach that is much less demanding on the novelty of the versions of the products used, and in something less complicated. We are talking about publishing report data in an Excel document template through the OLE interface. The Excel template is stored in the BDS repository on the SAP BW side.
The advantages of the OLE approach are obvious:
- Works on any modern versions of SAP and Microsoft Excel products
- There are no restrictions on the amount of data in the report, except for your own in Excel
- Provides maximum data transfer performance from the BW server to Excel via OLE. Example: a sample of 525,000 cells (70 columns per 7,500 rows) is transmitted in 7 seconds.
- Data preparation on the “BW server” is performed in the ABAP report, which, having collected the sample into the internal table, transfers it through OLE to the Excel template received from BDS.
- Centralized (in one BW-system) maintenance of all objects relevant for the report: Excel template, BW data model, ABAP program for filling out the template.
- Compliance with the usual SAP standards for access control, development, settings transport, etc.
The “reverse side” of the coin - this approach requires programming in 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 more or 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: calling a BEx report in ABAP, calling FM RSDRI_INFOPROV_READ, SQL-SELECT from the data model tables. But this is usually in the arsenal of skills of an experienced BW consultant. A deep knowledge of ABAP programming will be needed if there is a need to further accelerate the work of the code for data preparation by tuning the ABAP program or even parallelizing the calculations. The latter, incidentally, is not architecturally possible with the BW BEx workbooks.
In short, the procedure for creating a report using the OLE approach is as follows.
- Development and debugging of ABAP code, which returns to the internal table of report data in accordance with the input parameters. The ABAP code can be in the form of FM, or better, in the form of a static method of the ABAP class;
- Preparation of an Excel report template with basic formatting and a vBA macro, which is executed after filling with data. Such a macro usually takes a “row count” parameter, although this is not necessary. Tasks of the macro - apply the formatting of report cells provided that the number of rows unknown in advance;
- Place Excel template in the BDS repository;
- Development and debugging of the ABAP code, which fills the internal table of the report results, reads the Excel template from the BDS, puts the data from the internal table in it according to the mapping “field in the table - field in the template”, runs the VBA macro, saves the completed file on disk in the temporary directory and opens it for viewing to the user;
- Preparation of a user transaction, which is prepared on the basis of the development of the previous paragraph.
What may be needed to make the first example based on the ABAP-OLE approach and successfully apply it in the future?
- BDS repository transaction: OAOR
- Snippets of ABAP code for working with documents from BDS via OLE (see below)
- VBA macro certificate or Excel enable option for running 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.