Комментарии в последней публикации «Насколько open-source экосистема R хороша для решения бизнес-задач?» насчет выгрузок в Excel привели к мысли, что имеет смысл потратить время и описать один из апробированных возможных подходов, который можно реализовать не выходя из R.

Ситуация достаточно типична. В компании всегда есть N методик по которым менеджеры вручную стараются строить в Excel отчеты. Даже если их и втоматизировать всегда остается ситуация, когда нужно срочно сделать какой-то новый произвольный срез или сделать представление для какого-либо руководителя в специфическом виде.

А еще есть ряд вручную поддерживаемых словарей в формате excel, чтобы преобразовывать представление данных в отчетах и выборках в правильной терминологии.

В силу того, что никакого подходящего инструмента (масса доп. нюансов будет ниже) так и не удалось найти, пришлось сваять «универсальный конструктор» на Shiny+R. В силу универсальности и параметризуемости настроек, такой конструктор можно легко сажать почти на любую систему в любой предметной области.

Является продолжением предыдущих публикаций.

Краткая постановка задачи

1. В качестве источника технических данных есть основное хранилище OLAP-типа (ориентируемся на Clickhouse), несколько дополнительных (Postgre, MS SQL, REST API) и ручные xml, json, xlsx справочника. В силу того, что требуется ad-hoc аналитика, включающая подсчет уникальных значений, работать необходимо только с исходными данными, а не с агрегатами.
2. Записей в БД — сотни милиардов строк на несколько сотен столбцов (временнЫе события), анализ желательно делать в режиме, измеряемом не более нескольких десятков секунд, запросы могут быть совершенно непредсказуемые, данные хранятся в техническом виде (английские сокращения, номера словарных статей и пр.). В целевом состоянии ожидается ~200Tb сырых данных.
3. Накопленные события имеют версионную специфику, т.е. по мере функционирования Системы в ней накапливается информация от разных версий и релизов источников, рапортующих о себе различным образом.
4. Менеджеры хорошо работают в excel, но не должны знать (да и физически не могут) техническую составляющую Системы.

Как решили задачу

Общий сценарий работы получается достаточно простой. Менеджеру поступила срочная задача на аналитический срез — менеджер открывает приложение, в терминах предметной области формирует произвольные выборки — смотрит и крутит табличный результат — выгружает устроивший результат в excel — рисует картинку для руководства. В качестве нулевой точки выбрали удобство и простоту интерфейса пользователя.

1. Все оформлено в виде одноэкранного Shiny приложения с меню навигации и закладками.
2. Все управляющие элементы разделены на 3 части:
   • фильтры (глобальные и частные). Ограничивают область выборки, бывают 4-х типов: выпадающих список-словарь, даты, текстовые фрагменты, цифровой диапазон.
   • 3 уровня вложенности группировок запроса
   • список агрегатных величин (именно величин).
3. В силу того, что полей в исходном источнике много (около 2.5 сотен), а отобразить надо все, управляющие элементы сгруппированы в тематические блоки.

Пример интерфейса

Пример файла с метаинформацией

Полезные «фишки» за кадром:

1. Поскольку источники данных эволюционируют, то вся настройка интерфейса, включая создание контролов, подсказок, содержимого доступных группировок и агрегатов, правил выгрузок в excel и т.д. оформлены в виде метамодели в виде excel файла. Это позволяет оперативно модифицировать «конструктор» под новые поля или расчетные агрегаты без существенного изменения (или вообще без изменения) исходного кода.
2. Трудно заранее сказать, какие значения могут встретиться в том или ином поле, а уж найти то, не знаю что — еще сложнее. Вручную поддерживать все 90 динамических контролов почти невозможно. В некоторых списках словарный состав включает в себя несколько сотен значений. Поэтому словарные статьи для контролов актуализируются в фоновом режиме на основании данных, накопленных в бэкенде.
3. Менеджерам необходимо видеть все поля и содержимое на русском. А в источниках эти данные могут храниться в служебном виде. Поэтому используется комбинация словарных технологий Clickhouse и двустороннего постпроцессинга значений полей на уровне Shiny. Тут же обеспечивается обработка всякого рода исключений из правил и версионных нюансов содержимого полей.
4. Для защиты от некорректных выборок сделана кросс-связь между списками для группировки. Уровень 2 можно выбрать, только если задан Уровень 1, а Уровень 3, только если задан Уровень 2. И списки доступных значений динамически редуцируются с учетом уже выбранных.
5. Немаловажный элемент — управление отображением выборки как на экране, так и при последующей выгрузке в excel. Тут тоже есть ряд фич в постпроцессинге, направленных на удобство инструмента для менеджера:
   • организована поддержка «матрицы видимости» в виде excel файла. Эта матрица определяет отображение или сокрытие тех или иных полей в выборке в зависимости от установленных фильтров.
   • построчная динамическая модификация содержания выборки. В зависимости от содержания тех или иных полей может быть изменено содержимое другого поля (например, если в поле «объем заказа» указан 0, то в поле «тип заказа» отображать пустую строку.
   • управление отображением персональных данных. в зависимости от сконфигурированных прав ролевого доступа перс. данные могут как отображаться, так и частично маскироваться с помощью *.
   • управление точностью. просто уж для упоминания. показывать 10 знаков после запятой — моветон, но бывают ситуации, когда не хватает точности 2 знака после запятой. 80% объектов, например, имеют процентную долю 0.00% — надо увеличить значащие знаки при округлении, чтобы была видна разность между строками. Да и сумма при выгрузке в excel должна бы сходиться (сумма по всем строкам в долевой колонке резонно ожидаема в районе 100%).
   • обеспечение ролевого доступа на уровне доступного содержимого контролов. Права доступа управляются конфигурационным json файлом.
6. Динамическое управление глубиной запроса. В случае, когда никакие группировки и агрегаты не заданы (идет изучение и нужно просто выдать сырые данные, подпадающие под установленные фильтры) включается защита от перегрузки бэкенда. Пользователь может задать временной диапазон для поиска в 1 год, а реально нужно 1000 последних записей из выборки. Зная, что ежедневно поступают миллионы записей, сначала выполняется пробный запрос на сокращенную глубину (3-7 дней назад). Если полученного количества строк не достаточно (жесткие условия фильтрации), то запускается полный запрос на весь временной период.
7. Выгрузка полученных выборок в формате excel. Все отформатировано, все на русском, сопровождается отдельным листом с фиксацией всех параметров выборки, с тем, чтобы можно было легко понять, как был получен тот или иной результат.
8. В приложении ведется детальный лог, так что можно получить представление как по действиям пользователя, так и по работе подкапотной механики.

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

Естественно, что ссылку на продукт надо давать с учетом всего спектра выдвинутых требований. Ну и желательно сразу с оценкой требуемой инфраструктурой. Для указанного варианта на весь комплекс достаточно двух-трех серверов средней мощности (64-128Gb; 12-20 CPU core, диск — исходя из объема данных). ELK не подошел, поскольку основная задача — численная аналитика, а не работа с текстом.

Развернутый набор требований

Ниже, для информации, приведен развернутый список требований к аналитическому блоку в части интерфейсов машина-машина и человек-машина («конструктор отчетов» — только часть).

Импортэкспортокружение

1. Лог файлы стандартизованы и структурированы только в части временных меток, модулей и подсистем. Система должна обрабатывать лог-файлы с произвольным содержанием содержательной части сообщения (тело лог записи), поддерживая как структурированное, так и неструктурированное тело лог записи.
2. Для обогащения данных Система должна иметь адаптеры импорта, как минимум, к следующим типам источников данных:
   • плоские файлы (csv, txt)
   • структурированные файлы xml, json, xlsx
   • odbc совместимые источники, в частности, MS SQL, MySQL, PostgreSQL
   • данные, предоставляемые посредством REST API.
3. Система должна поддерживать как автоматический импорт, так и импорт по запросу пользователя. При импорте пользовательских данных Система должна обеспечивать:
   • возможность технической валидации импортируемых данных (корректность кол-ва полей, их типов, полнота, наличие значений
   • возможность логической валидации (содержание полей, проверка на допустимые значения, кросс проверка, …)
   • возможность настройки параметров валидации (в любом виде) в соответствии с логикой процедуры импорта;
   • детальный отчет об обнаруженных технических и логических ошибках, позволяющий оператору максимально быстро локализовать и устранить неисправности в импортируемых данных.
4. Система должна поддерживать экспорт результатов, как минимум, в следующие форматы:
   • экспорт данных в плоские файлы csv, txt
   • экспорт данных в структурированные файлы xml, json, xlsx
   • экспорт данных в odbc совместимые источники, в частности, MS SQL, MySQL, PostgreSQL
   • предоставление доступа к данным по протоколу REST API
5. Система должна обладать функционалом для формирования печатных представлений отчетов:
   • связное объединение текста, табличных представлений и графических представлений в единый документ по заранее сформированному шаблону (story telling);
   • формирование всех расчетных элементов (таблицы, графики) на момент генерации печатной формы;
   • использование внешних источников и справочников, требуемых при подготовке того или иного отчета, в режиме «на лету» по любым протоколам, указанным выше, без интеграции и дублирования данных
   • экспорт сформированных отчетов в форматы html, docx, pdf
   • должно поддерживаться формирование печатных представлений как по требованию, так и в фоновом режиме, по расписанию.
6. Система должна вести детальный лог совершаемых расчетов, активных действий пользователя или взаимодействия с внешними системами.
7. Система должна быть установлена на on-site.
8. Установка и последующее функционирование должно проводиться при полной изоляции системы от Интернета.

Вычисления

1. Система должна поддерживать вычисление агрегатных метрик (минимальное, максимальное, среднее, медиана, квартили) за произвольный временной интервал в режиме в режиме, близком к реальному времени.
2. Система должна поддерживать вычисление базовых метрик (кол-во значений, кол-во уникальных значений) за произвольный временной интервал в режиме, близком к реальному времени.
3. При вычислении агрегатных данных, периоды агрегации должны определяться пользователем из заранее предустановленных диапазонов: 5 мин, 10 мин, 15 мин, 20 мин, 30 мин, 1 час, 2 часа, 24 часа, 1 неделя, 1 месяц
4. Система должна включать конструктор для формирования произвольных выборок. Состав возможных операций должен определяться заранее заданной метамоделью данных. Конструктор должен поддерживать следующие минимальные настройки:
   • Поддержка фильтра для дат: [начало отчетного периода – конец отчетного периода]
   • Поддержка фильтров (выпадающие списки) с возможностью мультивыбора для перечислимых полей (например, города: Москва, Санкт-Петербург, …)
   • Автоматическое формирование содержания выпадающих списков для фильтров перечислимых полей на основании динамических внешних справочников или накопленных данных.
   • поддержка не менее трех уровней последовательной группировки данных в запрашиваемой выборке; сами параметры для группировки задаются пользователем из списка доступных, заданных на уровне метамодели данных.
   • ограничение полей, доступных для группировки на том или ином уровне, с учетом полей, выбранных на более высоких уровнях группировки (например, если на 1-м уровне выбрали «город», то это параметр не должен быть доступен на 2-м или 3-м уровнях группировки)
   • возможность группировки по аугментированным временнЫм параметрам: день недели, часовая группа (11-12; 12-13), неделя
   • поддержка базовых вычисляемых агрегатов: (минимум, максимум, среднее, медиана, количество, количество уникальных);
   • поддержка тестовых фильтров для обеспечения полнотекстового поиска в выборке;
   • поддержка на этапе отображения обогащения и трансформации полученных по запросу данных на основании данных из внешних справочников или источников.
5. Система должна иметь механизмы для расчета метрик в пространственных координатах (sp=spatial point) для поддержки геоаналитики.
6. Для временнЫх метрик (транзакции, операции, запросы, …) система должна вычислять и отображать плотности распределения времени исполнения запросов.
7. Все вычисляемые показатели должны выполняться как для всех объектов в целом, так и для подвыборок, устанавливаемых пользователем с помощью фильтров
8. Система должна проводить все вычисления в памяти.
9. Все события имеют временную метку, поэтому система должна поддерживать работу как с эквидистантными, так и с произвольными временными рядами.
10. Система должна поддерживать возможность настройки и включения механизмов для восстановления пропущенных во временных рядах данных (различные алгоритмы), определения аномалий, прогнозирования временных рядов, классификациикластеризации.

Интерфейсная часть

1. Весь пользовательский интерфейс, включая содержимое графических и табличных элементов, должен быть локализован.
2. Для управляющих элементов и столбцов табличных представлений должна поддерживаться возможность формирования всплывающих подсказок с детальным описанием (hover tip), формируемых как статическим образом, так и динамическим (например, в подсказке могут быть параметры по которым проводился расчет).
3. Интерфейс рабочих мест должен быть построен только с применением технологий HTML, CSS, JS, без использования устаревших, платформозависимых или непортируемых технологий, наподобие Adobe Flash, MS Silverlight и пр.
4. Время на графиках должно отображаться в 24-часовом формате.
5. Параметры отображения данных на осях должно поддерживать автоматическое масштабирование (периодичность меток и формат отображения) в зависимости от диапазона значений. Типичный пример – отображение часов при диапазоне измерений в рамках одного дня, отображение дней при диапазоне измерений в рамках недели.
6. Система должна, как минимум, поддерживать следующие атомарные форматы графического отображения:
   • Гистограмма (столбчатая)
   • Точечный
   • Линейный
   • Тепловая карта
   • Контуры (изолинии)
   • Круговые диаграммы
7. Система должна поддерживать возможность интеллектуального автоматического размещения маркеров (например, значений) определенного подмножества точек с обеспечением минимального перекрытия этих маркеров.
8. Система должна поддерживать возможность объединения на одном графическом представлении данных, полученных из разных источников данных. Должна поддерживаться возможность задания различных атомарных форматов графического отображения для каждого источника данных при условии совпадения координатных осей и типа системы координат.
9. Система должна поддерживать фасетное (разбиение графиков по сетке M x N) распределение атомарных графиков по заданной переменной параметризации. При фасетном отображении для каждого графика должна быть обеспечена возможность независимого масштабирования как оси X, так и для оси Y.
10. Графики должны поддерживать параметризацию следующих характеристик:
    • Цвет
    • Тип линии или точки
    • Толщина линии или контура точки
    • Размер точки
    • Прозрачность
11. Для задач геоанализа данных система должна поддерживать работу с шейп-файлами, включая импорт, отображение, площадную параметризованную раскраску и обеспечивать наложение различных графических элементов и расчетных показателей на сформированную геоподложку.
12. Управляющие элементы интерфейс пользователя (списки, поля, панели и пр.) должны поддерживать динамическое изменение своего содержимого в зависимости от состояния других элементов. Например, при выборе определенного региона содержание элемента выбора города должно быть ограничено списком городов, входящих в регион.
13. Должна поддерживаться ролевая модель доступа к отчетным приложениям:
    • поддержка метамодели данных для обеспечения ролевого доступа на уровне url (можнонельзя)
    • поддержка метамодели данных для обеспечения ролевого доступа на уровне содержания того или иного управляющего элемента (например, список доступных объектов в выпадающих списках определяется региональной ответственностью менеджера)
    • поддержка метамодели данных для обеспечения ролевого доступа на уровне визуализации персональных данных (например, маскирование «*» определенной части номеров e-mail или иных полей)

Заключение

Основная цель публикации — показать, что возможности R достаточно сильно простираются за границы классической статистики. Проверено практически, поступаться качеством или функционалом не приходится.

Предыдущая публикация — «Насколько open-source экосистема R хороша для решения бизнес-задач?».

Автор: i_shutov

Источник