Skip to Content

Large Excel report based on BWoH data

Hi BW-experts!

I would like to share with you our successful case of solving common reporting problem.

Initial situation can be described in following manner. Customer uses SAP BW on HANA, Bex Analyzer for Excel to get reporting workbook based on 3 bex-queries and huge formatting VBA-macro . One of these bex-queries should return large amount of rows, so large that Analyzer produces memory errors each time.

Business users are forced to execute workbook several times with different input parameters in order to get several result sets and manually merge them afterwards into one big Excel-file. Merging is executed monthly, after all check procedures over workbook data have been executed. Final well-formatted Excel-report contains about 120K rows and ~50 columns.

Please don’t tell me that such big reports should not be used ever, that customer should be trained to use alternative approaches to solve the same business task and so on. I know it all. In that situation we have to solve the particular task: provide real solution to get large amount of BW-data in Excel well-formatted report.

How did we solve the problem? We decided that HANA, to be more concrete – BW on HANA, can help us even is this situation. On the other hand we have to refuse using Bex because of it slow performance getting large result rest to Excel.

Another restriction I didn’t mentioned earlier was minimization of labor. If we decided to completely change the way user get their 120Kx50 data, for example by using Crystal Enterprise, we had to refine and implement huge formatting VBA-macro (more than 2000 rows of code) in another tool. So we decided to leave VBA-macro as is and just to put BW-data to excel to the same cells and order which bex-queries did.

Overall architecture of the solution is in the picture 1.

Picture 1.

Business user performs logon in SAP GUI and run ABAP-report (1), enter parameters at selection screen and execute it.

Report gets (2) result sets from 3 calculation views using parameter’s values.

Each result set is saved (3) as csv-file in %TEMP%-directory at user workstation.

Next ABAP-report opens Excel application (4) with workbook template (5) and executes 3 VBA macros in series.

Each of these macros  import (6) corresponding csv-file into worksheets at the same place which bex-analyzer do previously.

Finally ABAP-report executes formatting VBA-macro and business user gets well-formatted Excel-report.

Using direct execution of SAP HANA calculation views instead of Bex-queries we could considerably improve performance of getting large datasets. We were able to push into HANA all calculation logic implemented previously in calculation and restrict Bex-key figures. Only some formatting steps before exporting result set in csv were implemented in ABAP report. These should be done in order to better import csv-file from Excel side.

On comparable result sets, which can be executed both in new and old (Bex) solution, we got new solution about 20-25 times faster (metrics gathered without execution of formatting VBA-macro). Moreover previous Bex-solution can’t handle required large dataset at all but new solution have no limits except MS Excel maximum row count.

Finally I’d like to put here some code examples from ABAP report


  METHOD call_workbook.

    DATA: excel     TYPE ole2_object,

          workbooks TYPE ole2_object,

          workbook  TYPE ole2_object.

    CREATE OBJECT excel 'excel.application'.

    SET PROPERTY OF excel 'VISIBLE' = 1.

    CALL METHOD OF excel 'WORKBOOKS' = workbooks.

    CALL METHOD OF workbooks 'OPEN' EXPORTING #1 = i_excel_file.

    CALL METHOD OF excel 'RUN'

      EXPORTING

        #1 = 'Macro1'.

    CALL METHOD OF excel 'RUN'

      EXPORTING

        #1 = 'Macro2'.

    CALL METHOD OF excel 'RUN'

      EXPORTING

        #1 = 'Macro3'.

  ENDMETHOD.

VBA macros ‘Macro1’ – ‘Macro3’ are to import csv-files into worksheet. These macros were initially generated by Excel record macro tool.

  METHOD upload_files.

    DATA: l_filename    TYPE string,

          it_file_table TYPE tt_file.

    l_filename = i_dir && 'table1.csv'.

    me->table_to_csv( IMPORTING e_csv_table = it_file_table

                      CHANGING  c_table = c_table1 ).

    cl_gui_frontend_services=>gui_download( EXPORTING filename = l_filename

CHANGING  data_tab = it_file_table ).

    l_filename = i_dir && 'table2.csv'.

    me->table_to_csv( IMPORTING e_csv_table = it_file_table

                      CHANGING  c_table = c_table2 ).

    cl_gui_frontend_services=>gui_download( EXPORTING filename = l_filename

CHANGING  data_tab = it_file_table ).

    l_filename = i_dir && 'table3.csv'.

    me->table_to_csv( IMPORTING e_csv_table = it_file_table

                      CHANGING  c_table = c_table3 ).

    cl_gui_frontend_services=>gui_download( EXPORTING filename = l_filename

CHANGING  data_tab = it_file_table ).

  ENDMETHOD.

Hope, the document comes useful.

Sergey.

Tags:
Former Member