Olap-кубы

Достаточно часто требуется оперативная аналитическая обработка данных. Зачастую пользователи делают выгрузку данных в Excel и затем создают Сводную таблицу, т.к. весь процесс построения не требует навыков программирования, а строится в мастере несколькими нажатиями кнопки мыши. Но в этом есть один существенный минус: перед формированием отчета требуется каждый раз делать экспорт данных, а это занимает определенное время и назвать данный анализ оперативным уже сложно.

Если же ваша база данных хранится на MS SQL или может быть задана через связные серверы и нет инструмента для построения OLAP отчета, то можно использовать платформу Клиент Коммуникатор, через использование представлений (однопользовательская версия бесплатная). Для этого необходимо составить SQL запрос и записать его в конфигураторе в разделе Представления.

В данной статье я буду рассматривать построение отчета на основании данных, хранящихся в базе данных КлиКа. На основе представления отчет строится аналогично, только выбрать надо будет не таблицу, а представление.

Рассмотрим на примере следующей задачи: провести анализ наполненности реквизитов контрагентов:

• ИНН,
• основной вид деятельности,
• основное юридическое лицо,
• телефон,
• e-mail,
• контактное лицо.

Анализ должен быть в разрезе по куратору, состоянию, типу, сальдо, региону.

Все необходимые данные по задаче хранятся в одной таблице. Но возможно построение отчета по нескольким таблицам, связными с основной по прямым и обратным ссылкам. В клиентской части имеется Мастер построения OLAP отчетов, любой пользователь может самостоятельно создавать и сохранять свои отчеты. Я буду рассматривать построение отчета в дизайнере, которое отличается от пользовательского только тем, что может быть выведено в основное меню программы.

Шаг 1. В модуле Дизайнер создать шаблон формы. Задать название формы и выбрать основную таблицу.



Шаг 2. Добавить на форму компонент Сводная таблица. Установить компоненту выравнивание Весь клиент (или любой другой на выбор).



Шаг 3. Задать параметры таблицы.

Существует 4 типа области размещения атрибутов:

• строки расположение слева списком, может быть несколько раскрывающихся списков;
• столбцы размещение горизонтально сверху списком, возможно несколько уровней;
• фильтры слева сверху горизонтально;
• данные основная часть с агрегированными данными, обязательно надо задать, как рассчитывать общий итог: количество, минимальное, максимальное, сумма, среднее. Как правило для расчета данных используются поля числовые, которые можно сложить. Для строковых переменных можно использовать только агрегатную функцию Количество. В примере я демонстрирую, как использовать вычисляемое выражение с применением функции SQL CASE.

Шаг 4. Задать параметры просмотра и поведения.



Шаг 5. Вывести форму в меню программы (в модуле Администратор).

Результат:

Между областями строк, столбцов и фильтров можно перемещать атрибуты при помощи мыши, тем самым получаем различные срезы. Имеется возможность экспортировать результат в файл. Как вывести расшифровку по строке отчета, я опубликую позже на своем youtube канале.

Более подробно смотрите в видео.

Какой у Вас выбор?

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

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

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

Приведенные особенности BI-систем заставляют задуматься о подборе альтернативы. Далее я предлагаю сравнить решение стандартного набора задач при подготовке отчетности с помощью Power BI и Excel.

Power BI или Excel?

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

А как решается эта задача с помощью Power BI?

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

Какие преимущества применения Power BI по сравнению с традиционным подходом можно заметить в приведенном примере?

1 Автоматизация процедуры получения данных и подготовка их к анализу.
2 Построение бизнес-модели.
3 Невероятная визуализация.
4 Разграниченный доступ к отчетам.

А теперь давайте разберем каждый пункт по отдельности.

1 Для подготовки данных к построению отчета, нужно единожды определить процедуру, выполняющую подключение к данным и их обработку и каждый раз, когда понадобится получить отчет за другой период, Power BI будет пропускать данные через созданную процедуру. Таким образом автоматизируется большая часть работы по подготовки данных к анализу. Но дело в том, что Power BI осуществляет процедуру подготовки данных с помощью инструмента, который доступен в классической версии Excel, и называется он Power Query. Он позволяет выполнить поставленную задачу в Excel абсолютно тем же способом.

2 Здесь та же ситуация. Инструмент Power BI для построения бизнес-модели имеется и в Excel это Power Pivot.

3 Как Вы, наверное, уже догадались, с визуализацией дело обстоит подобным образом: расширение Excel Power View справляется с этой задачей на ура.

4 Остается разобраться с доступом к отчетам. Тут не так все радужно. Дело в том, что Power BI это облачный сервис, доступ к которому осуществляется через персональную учетную запись. Администратор сервиса распределяет пользователей по группам и задает для этих групп различный уровень доступа к отчетам. Этим достигается разграничение прав доступа между сотрудниками компании. Таким образом, аналитики, менеджеры и директора заходя на одну и туже страницу видят отчет в доступном для них представлении. Может быть ограничен доступ к определенному набору данных, либо к отчету целиком. Однако, если отчет находится в файле формата Excel, то усилиями системного администратора можно попытаться решить задачу с доступом, но это будет уже не то. Я еще вернусь к рассмотрению этой задачи, когда буду описывать особенности корпоративного портала.

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

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

Предлагаю Вашему вниманию более профессиональный подход, воспользовавшись которым, Вы получите свою собственную, полностью управляемую, автоматизированную систему построения бизнес-аналитических отчетов с разграниченным доступом к ним.

ETL и DWH

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

С их помощью осуществляется выгрузка данных из источников (Extract), их преобразование (Transform), что подразумевает очистку и сопоставление, и загрузка в хранилище данных (Load). Хранилище данных (DWH Data Warehouse) это, как правило, реляционная база данных, расположенная на сервере. Эта база содержит данные, пригодные для анализа. По расписанию запускается ETL-процесс, который обновляет данные хранилища до актуальных. Кстати говоря, всю эту кухню прекрасно обслуживает Integration Services, входящие в состав MS SQL Server.

Далее, как и раньше для построения бизнес-модели данных и визуализации можно воспользоваться Excel, Power BI, либо другими аналитическими инструментами, такими как Tableau или Qlik Sense. Но прежде, мне бы хотелось обратить Ваше внимание еще на одну возможность, о которой Вы могли не знать, несмотря на то, что она Вам давно доступна. Речь идет о построении бизнес-моделей с помощью аналитических служб MS SQL Server, а именно Analysis Services.

Модели данных в MS Analysis Services

Этот раздел статьи будет более интересен тем, кто уже использует MS SQL Server в своей компании.

На данный момент службы Analysis Services предоставляют два вида моделей данных это многомерная и табличная модели. Кроме того, что данные в этих моделях связаны, значения показателей модели предварительно агрегируются и хранятся в ячейках OLAP кубов, доступ к которым осуществляется MDX, либо DAX запросами. За счет такой архитектуры хранения данных, запрос, который охватывает миллионы записей, возвращается за секунды. Такой способ доступа к данным необходим компаниям, таблицы транзакций которых содержат от миллиона записей (верхний придел не ограничен).

Excel, Power BI и многие другие солидные инструменты умеют подключаться к таким моделям и визуализировать данные их структур.

Если Вы пошли продвинутым путем: автоматизировали процесс ETL и построили бизнес-модели при помощи служб MS SQL Server, то Вы достойны иметь свой собственный корпоративный портал.

Корпоративный портал

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

Однако, пока не понятно, как будет организовано отображение отчетов на странице портала. Чтобы ответить на этот вопрос, сначала нужно определиться с технологией, на основе которой будет строиться портал. Я предлагаю взять за основу один из фреймворков: ASP.NET MVC/Web Forms/Core, либо Microsoft SharePoint. Если в Вашей компании имеется хотя бы один .NET разработчик, то выбор не составит труда. Теперь можно подбирать встраиваемый в приложение OLAP-клиент, способный подключаться к многомерным или табличным моделям служб Analysis Services.

Выбор OLAP-клиента для визуализации

Сравним несколько инструментов по уровню сложности встраивания, функциональности и цене: Power BI, компоненты Telerik UI for ASP.NET MVC и компоненты RadarCube ASP.NET MVC.

Power BI

Чтобы организовать доступ сотрудников компании к отчетам Power BI на странице своего портала, нужно воспользоваться функцией Power BI Embedded.

Сразу скажу, что потребуется лицензия Power BI Premium и дополнительная выделенная емкость. Наличие выделенной емкости позволяет публиковать дашборды и отчеты для пользователей в организации без необходимости приобретать лицензии для них.

Сначала отчет, сформированный в Power BI Desktop, публикуется на портале Power BI и потом, с помощью не простой настройки, встраивается в страницу web-приложения.

С процедурой формирования простого отчета и его публикацией вполне может справится аналитик, а вот со встраиванием могут возникнуть серьезные проблемы. Так же очень непросто разобраться с механизмом работы этого инструмента: большое количество настроек облачного сервиса, множество подписок, лицензий, емкостей сильно повышают требование к уровню подготовки специалиста. Так что эту задачу лучше поручить IT-специалисту.

Компоненты Telerik и RadarCube

Для встраивания компонентов Telerik и RadarCube достаточно владеть программными технологиями на базовом уровне. Поэтому профессиональных навыков одного программиста из IT-отдела будет вполне достаточно. Все что нужно, это разместить компонент на web-странице и настроить их под свои нужды.

Компонент PivotGrid из набора Telerik UI for ASP.NET MVC встраивается на страницу в изящной манере Razor и предоставляет самые необходимые OLAP-функции. Однако, если требуется более гибкие настройки интерфейса и развитый функционал, то лучше использовать компоненты RadarCube ASP.NET MVC. Большое количество настроек, богатый функционал с возможностями его переопределения и расширения, позволят создать OLAP-отчет любой сложности.

Ниже приведу таблицу сравнения характеристик рассматриваемых инструментов по шкале Низкий-Средний-Высокий.


Теперь можно перейти к определению критериев для выбор аналитического инструмента.

Условия выбора Power BI

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

Условия выбора компонентов Telerik

• Нужен простой OLAP-клиент для Ad hock анализа.
• В штате компании имеется .NET разработчик начального уровня.
• Небольшой бюджет на разовую покупку лицензии и дальнейшее ее продление со скидкой менее 20%.

Условия выбора компонентов RadarCube

• Необходим многофункциональный OLAP-клиент с возможностью кастомизации интерфейса, а также поддерживающий встраивание собственных функций.
• В штате компании имеется .NET разработчик среднего уровня. Если такого нет, то разработчики компонента любезно предоставят свои услуги, но за дополнительную плату, не превышающую уровня оплаты труда штатного программиста.
• Небольшой бюджет на разовую покупку лицензии и дальнейшее ее продление со скидкой 60%.

Заключение

Правильный выбор инструмента для бизнес-аналитики позволит полностью отказаться от формирования отчетности в Excel. Ваша компания сможет постепенно и безболезненно перейти к использованию передовых технологий в области BI и автоматизировать работу аналитиков всех отделов.