Отслеживание изменений данных в ERP. История возвращения журнала базы данных

[imir]

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

В прежних версиях Microsoft Dynamics AX с этой задачей относительно приемлемо справлялся стандартный Журнал базы данных. Хотя его интерфейс никогда не был user-friendly. Но на это есть определенные причины. Его основная задача – скорость регистрации изменений, т.е. минимальное влияние на производительность плюс компактность хранения информации.

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

Так почему такой важный для пользователей инструмент на данный момент пребывает в таком, достаточно зачаточном и плачевном состоянии и данный вопрос до сих пор не решается просто небольшим обновлением системы?

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

Краткая предыстория развития системы

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

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

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

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

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

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

Проблему же возросшей сложности запросов к базе данных было решено побороть путем плавного перехода от «натуральных», понятных человеку, текстовых ключей для связи между таблицами на системные ключи записей (Recid). Серверу баз данных гораздо проще при объединении таблиц в запросе сортировать целочисленные значения, нежели текстовые ключи.

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

Конечно, такие преобразования не могли не сказаться на удобстве работы с данными - просмотр стандартного Журнала базы данных стал еще больше напоминать заставку из фильма «Матрица» без графической оболочки. И если видишь в наборе системных кодов записей блондинку, скорее всего – ты избранный, либо просто опытный разработчик или архитектор.

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

Было принято твердое решение – журнал изменений надо делать заново

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

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

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


А помимо этого необходимо добавить недоступные сейчас возможности:

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

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

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

Не должен влиять на производительность системы

Это требование не случайно стоит первым в списке, раньше функционала, поскольку стало одновременно самым простым и неочевидно сложным. Самое главный вызов – не делать ничего синхронно с обновлением, вставкой и удалением данных.

Тем временем, в очередном обновлении Microsoft перенес само отслеживание изменений из бизнес-логики и поместил его на триггеры в базе данных MS SQL, что существенно ускорило массовые операции обновления данных. С небольшими ошибками правда, о чем мы сообщили Microsoft и ее оперативно исправили в очередном обновлении DAX 365 10.0.4, так что имейте это в виду, если версия вашей системы ниже.

Итак, первоначальная идея была очевидна – пишем периодическую процедуру, которая в фоновом режиме обрабатывает записи стандартного журнала базы данных и превращает их в записи «Журнала изменений». Звучит просто только на первый взгляд, а по факту - задача похожа на известную в «научных» кругах задачу по прокручиванию фарша назад. Помните, что мы должны расшифровать ссылочные поля и найти родительские записи? Но к моменту запуска обработки данные могли серьезно поменяться, родительские и ссылочные записи – удалиться, переименоваться. На первый взгляд, задача выглядела нерешаемой, однако это не поколебало нашу решимость, и все сложные кейсы были в итоге успешно пройдены.

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

Не должен занимать много места в базе данных

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

Однако теперь у нас все равно появилось два журнала в системе? И да, и нет. За счет тонкой настройки полей по конкретным заявкам пользователей достигается существенное снижение нагрузки на базу и ее размер. А если этого недостаточно, обработанные записи стандартного журнала можно в принципе удалять, оставив только записи расширенного. Для этого были написаны соответствующие периодические процедуры с гибкими параметрами. Можно даже определить разный срок хранения истории для разных таблиц, например, для справочников и документов.

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

Должен уметь расшифровывать значения в ссылочных полях в понятные пользователю описания

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

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

А вот переименование записи решили не игнорировать, несмотря на то, что это теперь достаточно экзотическая операция. Однако обнаружился нюанс – включение отслеживания переименования записи на уровне sql-триггера равносильно включению отслеживание вставки + удаления записи, и именно вставка – довольно накладное событие. Поэтому автоматическое отслеживание переименования записи, по умолчанию, выключили в параметрах. Его можно включать руками на конкретных таблицах там, где оно практикуется.

Так же попутно реализовали расшифровку значений складских и финансовых аналитик, что является частой задачей для отслеживания.

Расшифровка ссылочных полей, например, адресов или аналитик


Должен уметь собирать на один экран всю информацию из всех связанных таблиц

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

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

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

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

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

Позволять отслеживать события удаления связанных данных

Наиболее частый пример такой потребности – при просмотре изменений в шапке заказа на продажу, закупки или журнала – отслеживать события удаления строк и полей в них. А также всех связанных с этими строками записей.

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

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

Возможность поиска, сортировки и фильтрации по таблице, названию поля, описанию записи

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

Появилось дополнительное преимущество – теперь мы можем модифицировать стандартные названия для целей журнализации. Например – назвать все таблицы SalesTable, SalesTable_W, MCRSalesTable и т.д. одинаково, чтобы пользователь, фильтруясь по описанию «Заказ на продажу» получал сразу все изменения заголовка заказа, включая связанные таблицы, и не вникал с структуру данных.

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

Настраиваемые описания записей

Описание записи таблицы составляется из двух полей, которые сейчас жестко заданы в структуре метаданных и не редактируются без привлечения разработчика. Была добавлена отдельная настройка полей, которые используются для формирования описания записи. Например, для строк заказов это может быть: код товара, название, единица измерения, количество.

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

Пользователи должны иметь возможность видеть какие поля в данный момент отслеживаются и являться инициаторами добавления новых настроек

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

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

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

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

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

Возможность анализа размера журнала базы данных с точностью до конкретных полей

Но заявки пользователей – это не просто средство экономии времени. Это важный инструмент оптимизации настроек: теперь мы точно знаем, кто конкретно запросил данную настройку, и можем управлять настройками журнала.

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

А чтобы предотвратить их повторную активацию добавлен механизм настройки запрета от включения конкретных настроек в разрезе таблиц и полей.

Регистрация решения в AppSource

Было решено оформить данное решение в виде легко устанавливаемого дополнения системы и зарегистрировать на сайте приложений для продуктов семейства Microsoft Dynamics 365 – AppSource, который к тому времени уже начал свою жизнь и активное развитие.

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

Надеюсь, что проделанный нами путь по возвращению журнала изменений в систему сможет облегчить жизнь многих пользователей, партнеров, заказчиков, их команд внедрения, сэкономит немало времени, улучшит качество данных. И, в конечном счете, повысит качество самих внедрений и удовлетворенность заказчиков новой версией системы Dynamics 365 for Finance and Operations.

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

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

Ссылки на решение и форму обратной связи:

https://appsource.microsoft.com/en-u...d_database_log

Канал YouTube с видео, использованными в данной статье:

https://www.youtube.com/channel/UCti...RKtQ/playlists

Иван Миронов, руководитель отела разработки Microsoft Dynamics AX компании Navicon.

imironov@navicons.ru