Сегодня хочу рассказать историю проекта по запуску небольшого хранилища данных DWH одной из известных российских инвестиционных площадок по финансированию малого и среднего бизнеса.
Два года назад в России стал довольно активно развиваться рынок краудлендинга. Краудлендинг — это процесс, при котором группа инвесторов дает займы компаниям. Но изюминка заключается в том, что в роли инвесторов выступают обычные люди, такие как мы с вами. А заемщиками выступают обычные организации типа ИП или ООО, которые в основном управляют либо розничным бизнесом, либо e-commerce. Соответственно была запущена инвестиционная платформа, которая сводила вместе инвесторов и заемщиков.
Не стоит и говорить, что сразу после запуска площадки руководству потребовалась управленческая и аналитическая отчетность для управления бизнесом. Была поставлена цель быстро настроить предоставление ежедневных оперативных ключевых показателей для кампании.
Сам WEB-сайт инвестиционной площадки и все его внутренние процессы, или так называемый «кредитный конвейер», крутились на БД PostgreSQL. Кредитный конвейер — это собирательное понятие, описывающее весь процесс выдачи компаниями кредитов, начиная от подачи заемщиком заявки на займ и заканчивая погашением кредита.
Стек технологий, который был выбран для внедрения этого проекта, был БД MS SQL + SQL Server Analysis Service + сводные таблицы OLAP в Excel (BI система) для разработки ежедневных кубов OLAP.
Перед дальнейшим рассказом хотел бы расшифровать некоторые термины:
- OLAP — это online analytical processing, он же — оперативный анализ данных. Для обычного потребителя отчета это выглядит как сводная таблица в Excel. Но подключается она к SQL Server Analysis Service для отбора данных и отображения их в Excel.
- DWH – аббревиатура от Data Warehouse, т.е. хранилище данных. Некая база данных, где собирается вся информация о нашем проекте.
- ETL-процесс — Extraction-Transformation-Loading т.е. Извлечение, Обработка, Загрузка. Подразумевается процесс загрузки данных из одной или нескольких исходных систем в DWH. Этот процесс извлекает информацию из внешних систем-источников, трансформирует ее, очищают и загружают в единое хранилище.
- BI-система – инструмент аналитики и визуализации показателей. Business Intelligence (BI) позволяет компаниям собрать информацию из различных источников, проанализировать ее и представить в наиболее понятном и удобном для восприятия виде. С помощью различных программ и инструментов BI специалисты анализируют большие массивы данных, на основании которых разрабатывают и автоматизируют отчетность и дашборды. На основе полученных данных сотрудники принимают ключевые для развития компании решения
Важно понимать, что под сводными таблицами в Excel понимается не просто статичный отчет, а полноценная BI-система для аналитики данных. Набор показателей и фильтров, которыми можно «вращать» в реальном времени. Добавлять/удалять показатели, настраивать фильтры и смотреть статистику в различных разрезах — все это называется общим термином аналитика показателей.
Круг задач, которые нужно решить, был следующим:
- Настроить ежедневную выгрузку данных из БД PostgreSQL в хранилище DWH.
- Собрать структуры данных и детальные витрины показателей Data Marts.
- На основе этих витрин собрать кубы на MS Analysis Service и при этом посчитать порядка 30 показателей и измерений.
- Собрать сводную таблицу с помощью которого можно смотреть показатели и проводить аналитику. Добавлять показатели, фильтры и разрезы для получения нужной статистики.
Архитектура для данного проекта была принята следующая:
Схема движения потоков данных в DWH
Поскольку во всем проекте было вовлечено минимум сотрудников, то процесс разработки протекал максимально эффективно. Два бизнес-заказчика предоставляли требования к отчетности, а владелец источника предоставлял доступы и описания как к различным таблицам, так и к структурам данных. Я же выполнял роль и разработчика, и аналитика в одном лице. За счет того, что каждый сотрудник выполнял свою роль по максимуму, коммуникация внутри данного круга лиц была очень быстрой и эффективной. А сам процесс разворачивания хранилища протекал очень продуктивно.
Что было сделано в первую очередь?
В первую очередь был настроен обмен данными с хранилищем через ETL-процессы. В MS SQL были созданы коннекторы, которые могли подключаться источнику данных. Под источником понимается другая БД PostgreSQL. Также при создании коннектора установили драйверы для PostgreSQL и настроили системный DSN.
Пример создания подключения к базе
Создание «связного сервиса» в MS SQL
В MS SQL есть возможность прямого подключения к другим БД, так называемый «Связный сервис». В других базах она может называться по-разному (например, в oracle — это db-link), но суть одна и та же. Установив на сервере драйверы для подключения к PostgreSQL можно напрямую из MS SQL выполнять запросы в другую БД и выгружать данные. Соответственно, создав небольшую SQL-процедуру очистки и загрузки данных, как на примере ниже, можно выгружать по линкам любые таблицы из любых источников.
Пример самой простой процедуры полной загрузки таблицы из источника
Также стоит рассказать, про так называемый, сервис «Агент SQL сервер» в MS SQL позволяющий создавать планировщик заданий, запускающийся по расписанию (обычно ночью) и запускающий эти процедуры для загрузки данных в хранилище. Таким образом мы получили набор таблиц, структура которых идентична источнику, на основе которых далее можно выполнять обработку и строить витрины.
Пример создания планировщика заданий на MS SQL c запуском процедур и кубов OLAP
Витрины данных (Data Marts)
Следующим большим шагом проекта было создание витрин данных. В самой базе было создано всего 3 основные смысловые схемы:
stg — от слова STAGE. В ней лежат таблицы структура данных приближенная к источникам. Туда загружаются данные напрямую из источника.
dim — от слова dimensions. Схема, в которой хранятся справочники.
dds — от слова detail data stage т.е. детальный слой данных. В ней собираются уже обработанные данные взятые из stg.
Для общего понимания обработка dds выглядит как набор процедур и функций порядка 2000 строк чистого SQL кода. Важно заметить, что динамический SQL не использовался, что сильно упрощало жизнь.
Ну и один из последних шагов — создание кубов на MS SQL Analysis Service. Выполнялось все это с помощью инструмента Visual Studio. В них создается физическая и логическая структура сущностей на основе витрин DDS. А также настраиваются показатели и измерения с бизнес понятными переводом для потребителей отчетов.
Пример формы создания логической структуры данных OLAP кубов
Пример настройки связей для фактовых сущностей в кубах
Пример настройки вычисляемых полей в OLAP
Важно заметить, что при построении таких хранилищ и отчетности очень важным моментом является стабилизация отчетности в плане качества данных. Тестирование показателей и устранение расхождений также отнимает большое количество времени и трудозатрат при построении витрин и показателей. Несмотря на тот факт, что вся настройка OLAP выполняется в интерфейсах, сам инструмент является очень простыми и удобным в разработке.
Также очень важно понимать, что кубы OLAP очень удобно использовать на небольших хранилищах не более чем в несколько терабайт. Если речь идет о построении больших хранилищ, например для ритейла, телекоммуникаций или банковской сферы, то объемы данных начинают исчисляться десятками терабайт. Большие объемы требуют полноценные BI-решения с наличием высокопроизводительных серверов. Например, такие продукты как Oracle BI, SAS BI являются полноценными Enterprise решениями стоимость которых начинаются с цифры с нескольким количеством нулей и исчисляется в долларах.
В общем была создана система аналитики на основе OLAP со стартовым набором показателей, которая сильно помогла в принятии дальнейших управленческих решений для развития бизнеса.
Пример сводной таблицы, который подключается к Analysis Service для вращения кубов
В заключение, скажу, что это был только первый этап построения аналитической отчетности. На текущий момент хранилище очень сильно выросло и в объемах, и в количестве источников. Но базис, заложенный в начале, очень сильно помог в развитии дальнейшего бизнеса. А смысл всей истории в том, что не обязательно тратить месяцы на разработку и моделирование архитектуры для построения DWH, как обычно это бывает. При запуске бизнеса или стартапа, можно пожертвовать техническим долгом для быстрого достижения бизнес целей и делать это можно вполне успешно.
