Реальный кейс анализа и технического описания базы данных клиента для осуществления корректной миграции на отечественное ERP-решение. Важным аспектом проекта было сохранение работоспособности всех систем, зависящих от исходной ERP-системы. В статье — опыт аудита OLAP-системы клиента.
Привет, Хабр! Меня зовут Анастасия Цацкина, я старший разработчик Отдела разработки баз данных IBS. В продолжение нашей серии статей о переезде с иностранных решений на отечественные, сегодня я хочу поделиться проектным опытом проведения полноценного аудита базы данных аналитической отчетности перед сменой ERP-системы. Расскажу, какая задача стояла, как выглядела архитектура баз данных заказчика, из каких этапов состоял аудит и что в результате всего этого получилось.
С чем пришел заказчик
Российская «дочка» иностранной промышленной компании находилась в процессе преобразования в независимое от Запада юрлицо и осуществляла миграцию экосистемы цифровых продуктов на отечественные аналоги. Срочный вынужденный переезд с иностранной ERP-системы на «1С:ERP» осложнялся тем, что на базе данных ERP у заказчика строится аналитическая отчетность, которую нужно было сохранить с минимальными доработками, а в идеале — вообще без них. Идея заключалась в том, чтобы отключить базу данных ERP от самой ERP-системы и путем ETL (Extract, Transform, Load) регулярно загружать в эту структуру всю необходимую информацию из «1С:ERP».
Справедливости ради, в большинстве крупных компаний центральная ERP-система тесно связана с десятком других учетных систем. Но главная проблема в данном проекте заключалась в том, что у заказчика не было детальной информации об имеющейся у него системе (спасибо родительскому холдингу, который отключил российское подразделение от сетевых дисков с документацией), а без понимания бизнес-сущностей полей в базе данных ERP и их связи с аналитической отчетностью невозможно было составить техническое задание для организации обмена.
Таким образом, в рамках аудита мне нужно было описать взаимосвязь базы данных ERP с тремя OLAP-кубами и двумя отчетами Power BI, а также бизнес-сущности объектов базы данных. И всё это в максимально сжатые сроки: на аудит и оформление документации было выделено всего 20 рабочих дней. Честно говоря, изначально задача вообще казалась неподъемной — обычно на такой аудит уходит 2–3 месяца. Спасло то, что командой сопровождения клиента уже была проведена некоторая подготовительная работа по выгрузке моделей кубов и дашбордов.
Структура баз данных
Все базы данных заказчика управляются в СУБД Microsoft SQL Server, OLAP-кубы — в Analysis Services, а дашборды — в Microsoft Power BI.
Как видно по схеме выше, информация из базы данных ERP перекладывается в хранилище данных (Data Warehouse, DWH). Там данные трансформируются, агрегируются и обогащаются информацией из внешних источников. Только после этого информация поставляется в кубы и дашборды Power BI.
Всего у заказчика 13 кубов и 20 дашбордов. Изначально в техническом задании на аудит шла речь об описании связей с ключевыми отчетами — это 3 куба и 2 дашборда. Но, забегая вперед, скажу, что в итоге пришлось анализировать всё, потому что связей оказалось гораздо больше. Также у заказчика есть другие базы данных, которые тоже являются источниками информации для кубов и дашбордов, но они не входили в скоуп аудита. Поскольку на всех остальных этапах много «лишней» информации, которая мешает анализу, было решено начать именно с базы данных ERP.
Сами базы данных у заказчика относительно небольшие. В базе данных ERP 18 таблиц, которые раскладываются на 100 таблиц в хранилище данных.
Формирование базы данных с моделями кубов и дашбордов
В отличие, например, от PostgreSQL, в Microsoft SQL кубы представляют собой самостоятельную сущность, которая живет в отдельном компоненте — Analysis Services. Аналитическая отчетность у заказчика визуализируется в Excel, но ни Microsoft Power BI, ни Analysis Services не предоставляют инструментов для быстрого анализа модели OLAP-куба. Чтобы упростить этот этап, нужно было провести подготовительную работу. Модели кубов и дашбордов были выгружены штатными средствами в файлы: из Analysis Services — в файл XMLA (XML for Analysis), который является стандартным протоколом обмена клиентскими приложениями с Analysis Services, а из Microsoft Power BI — в файлы BIM. На самом деле, и то и другое является JSON-ом, у этих файлов практически идентичный формат.
Далее в отдельной базе данных команда сопровождения написала хранимые процедуры, которые распарсили эти JSON-ы в плоскую структуру. Каждой модели соответствуют четыре таблицы:
таблица с перечнем таблиц фактов и измерений с указанием источника;
таблица, описывающая колонки фактов и измерений;
таблица с взаимосвязями, которые есть и внутри кубов;
таблица с описанием мер, где указаны уже сами формулы.
Функции загрузки модели в базу данных для анализа:
Для удобного поиска информации сразу по всем моделям были сгенерированы представления. На скриншоте ниже, например, виден куб “Open_Orders_Register” и соответствующая ему таблица “Open_Orders”, которая строится на основании представления “Fact_Open_Orders”.
Анализ кода хранилища данных
Следующий этап — проанализировать базу данных DWH на предмет наличия связей между базой данных ERP и кубами. К этой задаче можно подойти по-разному. Например, с помощью простого скрипта, которым по вхождению можно поискать нужное название в коде хранимых процедур, функций и представлений.
SELECT OBJECT_SCHEMA_NAME(o.object_id) as SchemaName,
o.name as Object_name,
o.[type],
sm.[definition] as Procedure_script
FROM sys.objects as o
INNER JOIN sys.sql_modules sm on o.object_id = sm.object_id
WHERE o.[type] in ('V', 'P', 'Fn')
AND sm.[definition] LIKE '%desired expression%'
ORDER BY o.name;Скрипт основан на системных представлениях SQL sys.objects и sys.sql_modules. Но это оказалось не очень удобно, потому что недостаточно было просто указать, что «вот эта таблица из базы данных ERP используется в таком-то дашборде с помощью такого-то представления». Поскольку предстояло описывать бизнес-сущности, здесь нужно было сопоставление полей.
В общем, пришлось анализировать весь код вручную. Я выгрузила ddl-скрипт базы данных DWH и уже локально у себя на компьютере искала объекты с помощью текстового редактора Notepad++. Как уже было сказано, база данных у заказчика небольшая (для бизнеса такого масштаба) — весь ее скрипт уместился в 33 000 строк.
В результате анализа я увидела, что связи существуют не только с пятью заявленными отчетами, но и с другими тоже. Заказчик попросил это тоже всё подсветить.
Еще одна «находка» — большая вложенность объектов в хранилище данных. Таблица из базы данных ERP перекачивается как есть в хранилище, там она раскладывается на другие таблицы, эти таблицы используются в хранимых процедурах и обновляют третьи таблицы, которые используются в представлениях и других хранимых процедурах и т. д. Словом, каждый найденный объект нужно было также читать, искать и описывать. Кроме того, есть поля, которые не используются в кубе напрямую, но используются в условии или в JOIN — и это тоже нужно было описать.
Отчет для аналитиков
В результате аудита я подготовила отчет для аналитиков, где подробно расписала путь каждой таблицы от базы данных ERP до аналитического отчета, на основании чего она строится, какова логика переименования полей, когда одно поле с каким-то преобразованием раскладывается на несколько полей, либо поля рассчитаны на основании полей нескольких таблиц, описала JOIN-ы, условия, подсветила, что где-то в условиях указаны конкретные значения и т. д.
В отчете Excel, построенном на основании OLAP-куба, пользователь видит русскоязычные псевдонимы полей. Только получив понимание, что «лежит» за каждым полем, стало возможным описать, какую информацию в него необходимо закачивать.
Потом на основании этого отчета наши аналитики встречались с ключевыми пользователями заказчика и описывали бизнес-сущности.
Итоги
С чем столкнулась при анализе:
Полное отсутствие документации.
В некоторых местах отсутствуют псевдонимы таблиц в запросах. Это осложняло описание, так как приходилось сопоставлять поля таблиц.
Большая вложенность хранимых процедур и представлений.
В коде указаны конкретные идентификаторы, значения полей таблиц, фамилии менеджеров, названия стран, товаров и прочего. При смене системы важно это всё соблюсти и не растерять по дороге.
В названии полей встречаются ключевые слова (type, name, order и т. п.) и спецсимволы (<, >, = и другие). С технической точки зрения проблем нет, всё корректно описано, но при миграции базы данных на другую СУБД это может привести к определенным сложностям и необходимости обработки таких ситуаций вручную.
Результаты анализа:
Подготовлен отчет заказчику, в котором описаны взаимосвязи между объектами баз данных ERP, DWH и отчетами, а также указаны неиспользуемые поля.
Описаны бизнес-сущности объектов базы данных ERP.
Совместно с разработчиками «1С:ERP» начата разработка ETL-процессов для загрузки данных в базу данных ERP.
Отчет получился максимально наглядным и детальным, со множеством пояснительной информации — вот колонка, вот номер заказа, вот наименование поставщика, а вот как все эти данные взаимосвязаны. И да, я-таки уложилась в 20 дней! История закончилась хэппи-эндом: клиент принял отчет, основная часть ETL-процессов уже организована, и сейчас идет этап выверки данных.
Надеюсь, эта статья поможет кому-то, кто столкнется с аналогичной задачей. Если у вас есть вопросы, пишите в комментариях — постараюсь на всё ответить.