[Из песочницы] Подход к реализации больших форматированных отчетов в SAP BW
На проектах внедрения отчетности с использованием хранилища данных 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.
