Переход с 1С:УПП на 1C:ERP. Переделываем интеграции, использующие SQL запросы к СУБД (на примере УПП -> QlikView -> ERP)

Для тех, кто не читал предыдущую статью, расскажу о сути проекта. В 2020-2021 году я участвовал в роли руководителя команды разработчиков Внедренческого центра "Раздолье" в проекте Управление продажами в международной компании на базе "1С:ERP" (ссылка на сайт 1c.ru). Проект был выбран победителем международного конкурса «1С:Проекта года» в номинации «Лучший проект с использованием технологии "Дистанционное внедрение"».

Суть проекта заключалась в переводе Заказчика с 1С:УПП на 1С:ERP. На его примере кратко опишу, какой была организационная структура и какие программы мы использовали при взаимодействии в команде и с пользователями.

Практически весь проект выполнялся удалённо. Многие сотрудники Заказчика, участвующие в проекте, в условиях карантинов и локдаунов были переведены на удалённую работу. Многие сотрудники нашей компании тоже работали удалённо, с командировками в этот период были большие проблемы. Сам Заказчик работает в режиме 24х7 и является одним из крупнейших предприятий в России по производству кофе. На начало проекта в качестве основы корпоративной системы у Заказчика была программа 1С:УПП редакции 1.2 (даже не 1.3). По завершению проекта в 2021-м перешли на ERP 2.5. К слову, когда начинали работу, в 2020-м году, когда 2.5. была ещё в бета-версии, но мы решили прислушаться к рекомендациям "Фирмы 1С" запускать новые проекты на ней, а не на 1С:ERP 2.4.

Рис 1.1 Схема ИТ-архитектуры проекта

По плану проекта компания отказывалась от комплекса программ (1С:УПП + 1С:ДО + множественные интеграции с внешними решениями) и меняла его на связку 1С:ERP + ЗУП + ДО + поддержка тех же интеграций. Основные работы мы начали в августе 2020 года, а закончили - в апреле 2021 г.

Одной из задач перехода с УПП на ERP (ЕРП) был перевод интеграции между УПП и системой аналитики QlikView.

Справка

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

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

ВНИМАНИЕ:

! Инструменты перевода с языка SQL на язык 1С и обратно, а также организация процесса подойдут для перевода большинства прямых интеграций с СУБД старого продукта 1С на новый продукт 1С, т.е. технология касается не только частного случая для QlikView, представленного в этой статье, а подойдёт на других проектов с переводом других ПО.

Рис. 1 Примерный вид QlikView (к данному проекту картинка отношения не имеет, взята из сети для визуализации)

Вводная по задаче с QlikView

Интеграция QlikView была реализована ранее с УПП на уровне прямых sql-запросов к СУБД.

Данные передавались в одном направлении из УПП в QlikView:

• Контрагенты

• Договоры

• Заказы

• Продажи

• Планы

• и другие

Требовалось перейти в 1C:ERP (ЕРП):

• переделать сбор данных, т.е. запросы к источникам, т.к. структура хранения данных между УПП и ERP значительно поменялась;

• а также доработать ERP (ЕРП) под особенности, затребованные со стороны QlikView и доработанные ранее в УПП.

Интеграция QlikView с УПП проводилась давно (несколько лет назад) и успешно работала, не требуя вмешательства, поэтому всё, что касалось её создания и процесса настройки было успешно стёрто из памяти исполнителей:

• Интеграционные запросы к УПП были описаны на языке SQL и не имели описания на языке 1С

• Со стороны Клиента исполнители не помнили технологию реализации задачи (столько лет прошло)

• Был контакт компании-интегратора со стороны QlikView

• Срок реализации задачи установлен до конца 1 квартала с момента перехода с УПП на ERP (ЕРП) в начале года (для расчёта KPI и зарплаты менеджеров)

Решение задачи по переводу в ERP (ЕРП)

Шаг 1: Наладка взаимодействия

Со своего опыта: вовлечение 3-го, а именно - сторонней компании, в текущий проект 1С, всегда сопровождается перекладыванием ответственности на друг друга и риском получить проблемы с урегулированием сроков. Поэтому крайне важно сработаться.

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

Работы были поделены на два контура:

1. Мы - со стороны 1С (понимаем SQL запросы к УПП и трансформируем их в аналоги для ERP)

2. Они - со стороны QlikView (делают модель связи QlikView с ERP и организуют в QlikView переключение с модели источников УПП на ERP)

Шаг 2: Трансформация SQL-запросов

Составили сводный документ, содержащий список источников данных (около 20 блоков), подтягивающихся из УПП в QlikView:

• Номенклатурные группы

• Номенклатура

• Менеджеры

• Контрагенты

• …

• Логистические услуги

• Заказы

• Неотгруженные заказы

• Сетевые скидки

• ….

• Продажи

• Планы

Примечание: QlikView сам забирает данные из Источника, согласно разработанным запросам.

Каждый блок разбили части:

1. Таблица УПП-SQL-1C – старый текст sql-команд, промаркированный номерами (присвоены краткие уникальные обозначения полям и таблицам) и дополненный описанием полей и таблиц 1С. Примечание: Запросов на языке 1С для сбора данных из УПП для QlikView у Клиента не было или не сохранилось.

2. Таблица ERP-1C-SQL – текст запроса на языке 1С для ERP с промаркированными данными. Дополнена описанием соответствий и различий по сравнению с Таблицей УПП-SQL-1C (тексты запроса на языке SQL для ERP сопоставленные с полями 1С).

Список запросов содержал сложные запросы, состоящие из большого числа соединений различных таблиц, например, по планам продаж или неотгруженным заказам. Не буду их приводить, лучше разберем саму технологию на простом запросе. Далее на примере упрощенного блока "Контрагенты" разберем, как трансформировать SQL-запрос из старой системы (УПП) в новую (ERP):

Таблица УПП-SQL-1C

На входе дан запрос: что это - непонятно.

SELECT
   T1._IDRRef,
   T1._Description,
   T1._Fld1261,
   T1._Fld1265,
   T1._Fld1276RRef,
   T1._Fld1272,
   T1._Fld1273,
   T1._Fld1279RRef
FROM dbo._Reference78 T1

Чтобы разобраться, надо транслировать названия таблиц и полей SQL на язык 1С. Для этого воспользуемся замечательной обработкой Крючкова Владимира по конвертации текстов SQL в представление языка 1С (ссылка ниже в приложении). Обработка имеет управляемую форму, поэтому, чтобы её запустить в старом УПП, необходимо изменить режим запуска на "Управляемый". Запустим рабочую базу УПП в режиме управляемого приложения. Для этого у администратора изменим режим запуска на «Управляемое приложение».

После этого открываем обработку конвертации из SQL в 1С через Меню-Файл-Открыть, вставляем в неё текст запроса и жмём [Преобразовать].

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

Делаем описание в виде таблицы

После выполнения перевода текстов исходных SQL запросов на язык 1С все данные сохраняем в общий документ. Далее анализируем с консультантом: выделили используемые источники данных УПП, пытались понять получаемый результат и сконструировать аналогичные выборки уже на основе источников данных ERP.

Таблица ERP-1C-SQL

На стороне ERP нам нужно было смоделировать запрос 1С с той же структурой и логикой сбора по суммам, количеству и наборам атрибутов объектов, а затем получить его описание текстом SQL.

Для этих целей мы пользовались транслятором 1С в SQL от Пермитина Юрия (см. ссылку ниже в приложении). Транслятор запускаем в рабочей базе ERP, устанавливаем связь с СУБД и можем далее с помощью конструктора 1С собирать запрос и получать его SQL представление.

Как видно, запрос в ERP усложнился, из-за того, что требуемые атрибуты контрагентов разделены в ERP по двум справочникам: "Контрагенты" и "Партнеры".

Примечание: Важно, что все запросы SQL получать транслятором нужно именно в рабочей базе. Это нужно чтобы совпадали имена таблиц и реквизитов на уровне СУБД.

Пример:

База-копия, развернутая из архива рабочей базы, изначально имеет совпадающие имена таблиц и полей СУБД. Но если, например, добавляем в хранилище новый реквизит справочника "Договоры" и подтягиваем изменения в обе базы, то реквизит в рамках конфигуратора 1С имеет одинаковое 1С-имя в обоих базах (очевидно), а вот в рамках СУБД получает в соответствие поля с различными именами. В следствие чего запрос SQL, работающий на базе-копии и обращающийся к полям базы-копии в своем тексте, выполниться с ошибкой в рабочей базе, где аналогичный реквизит 1С соответствует полю СУБД с иным именем.

Делаем описание в виде таблицы

Запросы 1С и SQL, полученные для ERP, также сохраняем в общий документ, который затем пойдет на передачу интегратору QlikView.

Новый запрос SQL для QlikView фиксируем в виде:

SELECT
    T1._IDRRef,
    T1._Description,
    T1._Fld64962,
    T1._Fld64967,
    T1._Fld64971RRef,
    T2._Fld68618,
    T2._Fld68620,
    T2._Fld68615RRef
FROM dbo._Reference392 T1
LEFT OUTER JOIN dbo._Reference533 T2
    ON (T1._Fld64970RRef = T2._IDRRef)

Шаг 3: Запуск новой модели интеграции

Интегратор QlikView на основании предоставленного описания запросов разработал модель связи образа QlikView с ERP. Далее выполнили контрольные заборы данных в QlikView за одинаковый период по старой модели (из УПП) и новой (из ERP). Выполнили сверку количественных и списочных показателей. Выявленные расхождения данных были переданы нам для анализа и пояснений, в итоге:

• часть различий была устранена за счёт уточнения текстов и фильтров в запросах,

• по части – были выявлены ошибки учёта как в УПП, так и в ERP, приведшие к расхождениям,

• также важно, что по части моментов были описаны объективные различия логики систем УПП и ERP и даны пояснения, например, по различию в значениях себестоимости товаров, которые никогда не приведут к совпадению показателей в контрольных выборок данных.

После нескольких итераций, заборы данных по старой и новой модели стали приемлемо расходиться по контролируемым показателям. Новая система связи QlikView c ERP получила одобрение (клиента, наше и интегратора QlikView) и была запущена в продуктив, а связь с УПП отключена.

Приложение:

Список использованных решений

Для решения задачи использовались следующие публикации с сайта "Инфостарт":

1. Конвертер для преобразования текстов запросов и планов SQL в представления языка 1С (infostart.ru)

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

2. Транслятор запросов 1С в SQL (infostart.ru)

Инструмент для трансляции запросов платформы 1С в SQL, а также их диагностики.

Не забудьте поплюсовать, если материал оказался чем-то полезным для вас.

Автор статьи: Дмитрий Малышев (sapervodichka)