На проектах внедрения отчетности с использованием хранилища данных SAP BW многим архитекторам и консультантам приходится решать задачи подготовки больших форматированных отчетов: разнообразных ведомостей, выписок и т.п. Такие отчеты обычно характеризуются:

Нестандартными относительно инструментов SAP требованиями к форматированию;
Фиксированным числом столбцов;
Значительным количеством столбцов и строк (соответственно, десятки и десятки тысяч и более);
Требованием наличия Excel-представления;
Требованием к времени выполнения не более нескольких минут

К сожалению, нередко приходится наблюдать ситуацию, когда архитекторы BW-проектов выбирают стандартный для BW подход реализации таких отчетов. Кратко суть этого подхода изложена ниже.

Консультантом создается рабочая книга BW-BEx, которая содержит один или несколько BW-BEx-отчетов. Отчеты выгружаются на отдельные листы этой книги, которые обычно скрывают от пользователей. Видимым оставляют лишь один лист книги, содержащий целевую форму отчета с необходимым форматированием.

Работа пользователя с таким отчетом выглядит следующим образом:

в зависимости от используемого Excel-инструмента SAP BW, пользователь запускает BW-BEx Analyzer или SBOP Analysis for Office, подключается к серверу SAP BW, выбирает из роли рабочую книгу и запускает ее на выполнение.
Через несколько секунд (иногда – десятка секунд) появляется селекционный экран.
На экране пользователь выбирает значения параметров. Например, год-месяц, балансовую единицу, группу материала и т.п. Затем нажимает кнопку «выполнить».
Теперь настала очередь «поработать» для SAP BW: все BW-BEx-отчеты рабочей книги выполняются последовательно, отчет за отчетом, передавая на рабочие листы Excel свои данные.
После получения в Excel данных каждого отчета запускается VBA-макрос. Логика работы макроса такова, что он ничего не делает, пока данные всех отчетов не будут получены на Excel-листы.
Когда данные последнего отчета поступили на Excel-лист, VBA-макрос выполняет основную работу по подготовке форматирования отчета.
Когда VBA-макрос завершил работу, пользователь может увидеть результат отчета в своем Excel.

У стандартного подхода есть ряд преимуществ: он прост в реализации и им хорошо владеют большинство специалистов на рынке. Но определенные ограничения не позволяют эффективно реализовывать большие отчеты. А неэффективная реализация получается (если вообще получается) очень неудобной в работе, что негативно сказывается на отношении пользователей к проекту внедрения вообще и к SAP BW в частности. Основное ограничение – максимальное количество ячеек (число строк, умноженное на число столбцов) в отчете. Если их число приближается к эмпирическим 750000, то вероятность сбоя из-за нехватки памяти практически 100%. Т.е. отчет из всего 18 колонок и чуть более 40000 строк уже попадает под это ограничение. А ведь лимиты у Excel намного больше.

Чего только не придумывают консультанты, чтобы, оставаясь в рамках стандартного подхода, качественно сделать-таки большой отчет. Но почти всегда ничего не получается. «Почти» означает компромиссы, послабления в требованиях. Бизнес-пользователи либо соглашаются применять более ограничивающие фильтры и отчет возвращает меньше данных, либо ждать выполнения подольше, либо вручную сводить несколько фрагментов отчета в один.

Чтобы все-таки не говорить клиенту «нет, мы не можем этого реализовать при таких требованиях», необходимо для начала сделать правильные выводы из очевидного: каждый инструмент предназначен для своей задачи.

Инструменты BW BEx Analyzer и SBOP Analysis for Office в общем случае не предназначены для реализации эффективных отчетов с большим количеством ячеек, с числом около 750000 и более (см SAP-ноту 1040454). Поэтому, используя модель данных SAP BW, надо выбрать другой инструмент, другой подход в реализации. Тогда решение не только обязательно получится, но и будет при этом эффективным.

Последние версии SAP Netweaver, SAP BW и HANA внесли большее разнообразие подходов публикации BW-данных в Excel, без использования BW BEx. Можно упомянуть такие:

Подключение Excel через OData-сервисы напрямую к SAP Netweaver или даже к SAP HANA
Подключение Excel к SAP HANA, как к базе данных, напрямую, через MDX

Однако, эти подходы требуют либо BW on HANA, либо новейших версий Excel, либо отклонений от привычных концепций полномочий, при которой пользователи не работают с приложениями, обращающимися напрямую к БД.

Я хочу рассказать о подходе, гораздо менее требовательном к новизне версий используемых продуктов, и в чем-то менее сложным. Речь идет о публикации данных отчетов в шаблон Excel-документа через OLE-интерфейс. Excel-шаблон при этом хранится в репозитории BDS на стороне SAP BW.

Преимущества подхода с OLE очевидны:

Работает на любых современных версиях продуктов SAP и Microsoft Excel
Никаких ограничений на объемы данных в отчете, кроме собственных в Excel
Обеспечивает максимальную производительность передачи данных от сервера BW в Excel через OLE. Пример: выборка 525000 ячеек (70 колонок на 7500 строк) передается за 7 сек.
Подготовка данных на «сервере BW» выполняется в ABAP-отчете, который, собрав выборку во внутреннюю таблицу, передает ее через OLE в Excel-шаблон, полученный из BDS.
Централизованное (в одной BW-системе) ведение всех объектов, релевантных для отчета: шаблон Excel, модель данных BW, программа ABAP для заполнения шаблона.
Соответствие обычным SAP-стандартам по разграничению доступа, разработке, транспорту настроек и пр.

«Обратная сторона» медали – этот подход требует программирования на ABAP. Но, по мнению автора, этот аспект не должен вызывать существенных трудностей. «Обертка» из вызова Excel-файла из BDS, его заполнения данными и сохранения, например, в файл на диске или обратно в BDS — более менее стандартный код, который с минимальными вариациями используется от отчета к отчету.

Сложности в ABAP могут возникнуть при получении данных из модели BW. Возможные варианты: вызов BEx-отчета в ABAP, вызов FM RSDRI_INFOPROV_READ, SQL-SELECT по таблицам модели данных. Но это обычно есть в арсенале навыков опытного BW-консультанта. Глубокие знания программировании ABAP понадобятся, если возникнет потребность еще более ускорить работу кода по подготовке данных за счет тюнинга ABAP-программы или даже распараллеливания вычислений. Последнее, кстати, невозможно архитектурно в подходе с рабочими книгами BW BEx.

Вкратце, порядок создания отчета с использованием подхода с OLE следующий.

Разработка и отладка кода ABAP, который возвращает во внутреннюю таблицу данных отчета в соответствии с входными параметрами. ABAP-код может быть в виде FM, а лучше – в виде статического метода ABAP-класса;
Подготовка Excel-шаблона отчета с базовым форматированием и vBA-макросом, который выполняется после заполнения данными. Такой макрос обычно принимает параметр «число строк», хотя и это не обязательно. Задачи макроса – применить форматирование ячеек отчета при условии неизвестного наперед количества строк;
Помещение Excel-шаблона в репозиторий BDS;
Разработка и отладка ABAP-кода, который заполняет внутреннюю таблицу результатов отчета, считывает из BDS Excel-шаблон, помещает в него данные из внутренней таблицы в соответствии с мэппингом «поле в таблице – поле в шаблоне», запускает на выполнение VBA-макрос, сохраняет заполненный файл на диске во временном каталоге и открывает его на просмотр пользователю;
Подготовка пользовательской транзакции, которая готовится на основе разработки из предыдущего пункта.

Что может понадобится, чтобы сделать первый пример на основе подхода с ABAP-OLE и успешно применять его в дальнейшем?

Транзакция работы с репозиторием BDS: OAOR
Фрагменты ABAP-кода по работе с документами из BDS через OLE (см ниже)
Сертификат для макроса VBA или разрешающая опция Excel по запуску макросов (см. 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.

Подход к реализации больших форматированных отчетов в SAP BW