Как мало пройдено дорог — как много сделано отчетов ©

Введение

— Василий, мы установили новый BI продукт, наш САМЫЙ ГЛАВНЫЙ от него просто в восторге!
— Да, но я не знаю, как выгрузить данные для анализа из этой системы?! Он, похоже, только в html может что-то показывать.
— Ничего, я думаю ты справишься, сам понимаешь, чем шире улыбка шефа, тем выше премия.
— Но, Иван Васильевич, этот продукт в качестве источника данных использует только PDF файлы.
— Зато он показывает шикарные разноцветные графики, у него анимация как в “Звездных войнах”, а руководство просто в восторге от его интерактивных возможностей. Там ещё и пасхалочка есть. Если три раза кликнуть в правом нижнем углу, появится Дарт Вейдер и споёт Марсельезу. Да и в целом, Вася, будь оптимистом! Хочешь анекдот в тему?

— Что у вас запланировано на 1 января?
— Катание на санках
— А если снег не выпадет?
— Это нас огорчит, но не остановит.©

— Не грусти Вася, принимайся за работу, а мне пора спешить – утренняя планерка, эээ… Daily Standup Meeting точнее, всё никак не могу запомнить.

Вася садится за свой рабочий стол и с грустью смотрит в монитор. Да уж, красивые графики, только толку от них? В Excel не выгрузить, с формулами не сверить, хоть бери тетрадку с ручкой и делай всё на бумаге. Плюс ещё как-то KPI на основе этого надо посчитать. Зато в ИТ отдел, говорят, художника взяли, чтобы он красивые отчеты для руководства оформлял. Глядя на новый продукт, Вася загрустил. В голове у него крутились пару строк из стихотворения C.А. Есенина “Мне грустно на тебя смотреть”:

Так мало пройдено дорог,
Так много сделано ошибок.

Ну что ж, оставим Васю наедине со своей болью и посмотрим на проблему шире. Видя переделку строк C.А. Есенина, которая вынесена в цитату к этой статье, мне кажется, что он не одинок в своих мыслях. Сложно понять, как работают современные BI системы и для кого их пишут – то ли для аналитиков, то ли для руководителей. Очень много теории и информации, причём, в зависимости от источника, эта информация может противоречить самой себе. К этому стоит добавить обилие научных терминов и трудный для понимания язык описания. Сложно угадать с выбором, а цена ошибки велика, так как системы дорогие и работа с ними часто требует определенной квалификации. Понимая всё это, я решил поделиться своим опытом в BI сфере. Попытаюсь написать об этом простым языком и не вдаваться глубоко в теорию. Речь пойдет о Microsoft Analysis Services и о том, как он может решить часть проблем связанных с аналитической отчетностью. Другую часть этих проблем, я решил, написав специальную программу, которая позволяла формировать отчеты непосредственно в Excel, минуя HTML формы и минимизируя нагрузку на Web сервер, но о ней я уже писал тут https://habr.com/ru/post/281703/, а тут даже видео снял: https://youtu.be/_csGSw-xyzQ.

Приятного вам чтения.

Если лень читать, то есть кортокое видео (11 минут)

Создание OLAP-куба в Microsoft Analysis Services: https://youtu.be/f5DgG51KMf8
Но в этом видео далеко не всё то, о чём пойдёт речь далее!!!

Отчетность и её проблемы

Все началось с задачи, поставленной финансовым отделом крупного банка. Надо было создать систему отчетности, которая бы позволяла быстро и оперативно оценивать текущую ситуацию в организации. Для решения этой задачи мы взяли базу данных. Организовали в ней Хранилище (Data Warehouse), настроили процессы загрузки данных и установили систему отчетности. В качестве которой мы взяли SQL Server Reporting Services, так как этот продукт входил в MS Sharepoint, использовавшийся в тот момент в банке. В принципе всё работало, но у заказчика были претензии:

Претензия 1. HTML -> MS Excel: отчеты изначально формируются в HTML, а аналитики работают с MS Excel. Надо постоянно делать экспорт из одного формата в другой. При этом часто сбивается разметка и в Excel часто подгружается множество дополнительной информации, большой объём которой, в некоторых случаях, существенно влияет на производительность.
Претензия 2. Параметры для отчета: данные в отчетах зависят от параметров, причём при их изменении формируется новый отчет, который надо опять выгружать в Excel, что не всегда удобно.
Претензия 3. Добавление изменений в отчет: для того, чтобы что-то изменить в отчете, добавить новую колонку или создать группировку, надо обращаться к специалисту, который может поправить этот отчет на сервере.
Претензия 4. Анализ данных: отчеты получаются “статическими” и когда нужно посмотреть различные разрезы, поменять строки с колонками, отфильтровать или добавить, либо удалить какие-то значения, надо делать все эти манипуляции в Excel, что не всегда удобно, а порой и сложно, из-за проблем с производительностью компьютеров, на которых работают аналитики.

Стоит отметить, что сотрудники банка не рассматривали для себя никакого другого инструмента в качестве замены MS Excel. И на то были веские основания. Весь его функционал сложно чем-то заменить. К примеру, аналитики очень часто:

сверяли данные из базы по формулам, которые можно добавить в Excel,
хранили одни и те же срезы данных, сделанные в разные дни, с целью обнаружить ошибки,
добавляли туда свои данные,
писали различные скрипты

В общем использовали его на все 100%. Хотя были те, кто предлагал им что-то другое, точнее не столько предлагал, сколько заставлял. Как итог таких предложений, у нас в системе появились SAP BO, Oracle Reports Services и ряд других BI инструментов. Возможно, они в чем-то превосходили SQL Server Reporting Services, но суть работы с ними кардинально не изменилась:

формируем отчет в HTML,
экспортируем его в Excel,
начинаем заниматься бесконечными танцами вокруг данных.

Требовалось что-то более кардинальное, не просто отчет, а некий набор данных, с которым удобно было бы работать.

Выход из ситуации

К найденному решению подтолкнули PivotTable в Excel

и PivotGrid от фирмы DevExpress ( https://demos.devexpress.com/blazor/PivotGrid).

Детально изучив эти решения вышли на MS Analysis Services и решили попробовать. Его можно использовать в Excel, и он может работать с Oracle, как с источником данных, что нас на тот момент устраивало. С точки зрения архитектуры, источником данных для него может служить что угодно, был бы нужный провайдер. Суть его в том, что он способен хранить в себе большие объемы данных со всеми их агрегациями и выдавать их клиенту максимально быстро. К Excel его можно легко подключить и манипулировать данными в Pivot Table.

В MS Analysis Services есть возможность партиционирования данных (хранение их в виде множества отдельных частей) и так же инкрементальное обновление данных. Это даёт ему возможность загружать данные из внешних систем небольшими кусочками и хранить их во множестве партиций. С точки зрения максимальных объемов, у него есть ограничения, но они довольно большие https://docs.microsoft.com/en-us/analysis-services/multidimensional-models/olap-physical/maximum-capacity-specifications-analysis-services?view=asallproducts-allversions.

MS Analysis Services является OLAP системой, которая использует отдельный сервер для хранения данных, либо части данных. Его плюсом является то, что он способен довольно быстро работать с миллионами записей, будучи установленным на обычный, современный компьютер. Так же он позволяет анализировать данные непосредственно в Excel и может заменить собой десятки отчетов на MS Reporting Services или ему подобных. Причем при работе с ним не надо писать и править различные запросы типа SQL, хотя при желании можно, только вместо SQL он использует MDX.

Правда есть тут и ложка дегтя. В Excel можно запросить разом очень большой объём данных и OLAP их вернет, но отобразить такой объем Excel не сможет, либо сможет, но работать при этом будет очень медленно. На первых порах это раздражало аналитиков, но поняв причину и настроив фильтры в Pivot Table эту проблему решили.

Секрет быстродействия MS Analysis Services, как и любой другой OLAP системы, кроется в архитектуре хранения данных. В нем все храниться в максимально подготовленном и оптимизированном для запросов виде. Такая подготовка требует времени и запись вновь пришедших данных в OLAP происходит не быстро, но, с другой стороны, чтение данных получается очень быстрым. Выходит — долго пишем быстро читаем.

Немного теории

Чаще всего, когда анализируют данные их объединяют в группы, а сами группы так же объединяют в иерархии. Для примера возьмём торговую точку. С точки зрения бизнеса, интерес представляют продажи. То есть сколько товара было продано за день (1-группа), за месяц (2-ая) и за год (3-я). Где день месяц и год — это разные уровни одной иерархии. Получается, что продажи за месяц — это сумма всех продаж за все дни в месяце, а продажи за год – это сумма продаж за все месяцы в этом году. Отсюда получается, что для получения максимального быстродействия, можно заранее собрать данные в группы и рассчитать агрегаты (в нашем примере суммы продаж) для каждого уровня иерархи. Вот на этом принципе и работают MS Analysis Services. Им достаточно сказать — что надо считать, по какой формуле и на какие группы это можно разбить. Остальную работу они сделают сами. Тут немного о том как они это делают: http://citforum.ru/consulting/BI/molap_overview/node7.shtml. Стоит отметить, что в современных OLAP системах все агрегаты, чаще всего, не рассчитываются заранее. Это всё делается «на лету», в момент запроса.

Теперь о терминах:

MS Analysis Services – это одна из OLAP систем, где OLAP это аббревиатура — online analytical processing. Дословно это означает – интерактивная (online) аналитическая обработка данных. Со временем данная формулировка утратила свой первоначальный смысл, так как появились системы, способные обрабатывать данные с большой скоростью и передавать их пользователю без использования подходов, декларируемых в OLAP. Поэтому, сейчас есть более полное описание требований к системам, которые могут называться OLAP, это:

Тест FASMI — Fast Analysis of Shared Multidimensional Information. Более детально об этом можно прочитать здесь: http://www.olap.ru/basic/fasmi.asp.
И описание “правильного куба”, которое дал в 1993 году Е.Ф. Кодд: http://www.calligraph.ru/kodda.htm .

По своему опыту, могу сказать, что чем больше ваш OLAP куб удовлетворяет описанию Е.Ф. Кодда, тем лучше, как с точки зрения работы с ним, так и с точки зрения его создания.

Вкратце, OLAP – это система хранения, организованная таким образом, чтобы данные в ней:

были взаимосвязаны,
собирались в иерархии,
имели функции агрегации по всему множеству иерархий

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

При построении OLAP выделяют Факты и Измерения. Факты – это цифровые значения измеряемых величин. Измерения – это сами измеряемые величины. Совокупность всех связанных между собой измерений, фактов и функций для их агрегации называют OLAP-кубом. Факты и Измерения связанны между собой. По типу связи выделяют 2 схемы организации хранения данных – Звезда и Снежинка. Звезда – это когда все измерения напрямую связаны с фактом, снежинка – это когда есть измерения, которые связанны с фактом через другие измерения. Эти схемы можно создавать и просматривать в разделе Data Source Views в SSAS.

Создание OLAP-куба в Microsoft Analysis Services

Построение OLAP кубов делается через проект в Visual Studio. По большей части там реализована технология визуального программирования – перетащить, кликнуть мышкой, настроить. Отсюда это проще показать, чем описать. Что я и сделал в моем видео: https://youtu.be/f5DgG51KMf8. Так же стоит отметить то, что Microsoft, в ознакомительных целях, предоставляет свои продукты бесплатно. Отсюда, посмотреть, как он работает можно на любом компьютере с ОС Windows 10, удовлетворяющем следующим требованиям: https://docs.microsoft.com/en-us/sql/sql-server/install/hardware-and-software-requirements-for-installing-sql-server-ver15?view=sql-server-ver15. Требования по ссылке к MS SQL Server, так как MS Analysis Services являются его частью.

Заключение

OLAP — это относительно простой способ повысить скорость и удобство работы с данными. В данный момент существует множество решений, основанных на этой технологии. Я работал с MS Analysis Services (SSAS) и вот что мне в нём понравилось:

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

В заключение, хочу сказать, что мне MS Analysis Services, в свое время, очень сильно помог и я надеюсь, что знания, которыми я поделился в этой статье, так же кому-то помогут.

Как Microsoft Analysis Services финансовым аналитикам жизнь упростил