Добрый день. Меня зовут Жданов Андрей, в команде #Zhdanov мы занимаемся внедрением amoCRM и аналитикой на базе Power BI и Qlik Sense. Недавно прошел вебинар наших партнеров myBI Connect, где показывали один из наших кейсов и мы затронули вопрос выгрузки данных из 1С.
В данной статье я хотел бы подробнее остановиться на данной теме, так как данные из 1С в том или ином виде есть почти у каждого нашего клиента. Всего мы знаем 5 разных способов подключения к данным, из которых в своей практике использовали 4:
- выгрузка файлов Excel/XML в папку;
- коммерческий коннектор (АТК, BI Consult, Первый Бит);
- публикация базы 1С в вебе и подключение к ней по протоколу oData;
- построение собственной аналитической базы SQL и наполнение ее выгрузками из 1С;
- подключение напрямую к рабочей базе SQL 1С и разбор метаданных.
Спойлер — нельзя сказать, что какой-то способ лучше других. В зависимости от объема данных и бюджета следует рассмотреть каждый из этих способов и подобрать оптимальный именно для вашего проекта.
1. Выгрузка файлов Excel в папку или на FTP сервер
Отличный способ, когда нужно максимально быстро и просто получить обновляемые файлы из базы 1С. С помощью штатного инструмента 1С Рассылка отчетов вы можете выложить файлы в общую папку на сервере/FTP ресурс/облачный диск. Программисты 1С вместо Excel файлов могут использовать XML файлы, которые используют при обменах между базами 1С.
В первую очередь вам нужно подготовить Универсальный отчет. В отчете вы задаете какой объект конфигурации вам нужен, какая таблица, период, указываете отбор. Далее вы сохраняете получившийся вариант отчета.
Далее вам нужно воспользоваться инструментом Рассылка отчетов. Вы указываете подходящий Вариант отчета, настраиваете расписание, формат выходного файла и его местоположение.
Вы можете задать локальную папку, сетевую или FTP ресурс.
Наверно, самым большим ограничением данного способа, является то, что сложно обеспечить добавление данных и обычно предполагается, что каждый файл перезатирает предыдущий. Таким образом поддерживается достоверность данных.
Обычно мы предлагаем такую схему- ночью происходит полная выгрузка исторических закрытых периодов, а ежечасно днем — выгрузка текущего/прошлого месяца. Обычно > 90% информации редактируется в текущем периоде и достаточно оперативно выгружать только его, объем данных составит несколько мегабайт и это позволит оперативно делать такие выгрузки на рабочей базе 1С.
Конечно, такой способ отлично работает когда файлов относительно немного. Если речь пойдет о сотнях файлов со сложной структурой и расписанием, то наверно из этого способа вы уже выросли и стоит перейти к следующим.
Примерные трудозатраты и бюджет: условно бесплатно, можно сделать самостоятельно без участия программистов, максимум 1 час на каждый файл.
2. Коммерческие коннекторы (АКТ, BI Consult, Первый Бит и другие)
Крупные игроки на рынке BI аналитики Qlik разработали собственные коммерческие коннекторы к 1С. Мы тестировали коннектор от АТК, поэтому мои дальнейшие выводы основаны на его использовании.
Реализация коннектора будет зависеть от используемой базы SQL под 1С. Если используется Microsoft SQL то коннектор работает в полном режиме, создает SQL базу, в которой будут специальные представления View, которые представляют собой уже расшифрованные в удобном для аналитике виде данные 1С.
В режиме Postgre SQL коннектор работает гораздо проще, не может создать представление View, а просто генерирует текстовые файлы запросов, которые можно использовать в BI системе. То есть запрос на рисунке выше вы сможете использовать напрямую в Power BI.
Стоит отметить, что коннектору обязательно нужны доступы как к самой базе 1С, так и SQL базе.
Именно в этой SQL базе, но в собственной схеме коннектор и будет делать представления данных View, которые будут создаваться после успешного подключения к данным.
Коннектор сможет отсканировать все объекты метаданных конфигурации 1С, чтобы аналитик мог выбрать нужные ему документы и регистры.
Стоит отметить, что в выходных таблицах View коннектора он сразу может преобразовывать многочисленные ключи справочников в их значения. То есть простой документ может уже сразу содержать нужные наименования Организации, Номенклатуры, Контрагента и так далее и нет нужды тянуть к документу еще пару десятков справочников.
Таким образом, коннектор помогает аналитику выбрать подходящие объекты конфигурации, сразу увидеть их значения и подключить BI систему к базе SQL. Это экономит десятки часов по раскодированию/расшифрове метаданных 1С. Это одно из главных преимуществ коннектора, возможность развернуть его за 1 рабочий день аналитика и почти сразу получать нужные данные из 1С.
Из минусов коннектора отметим, что в режиме PostgreSQL он может генерировать только текстовые файлы-запросы, которые мог бы нам сделать и обычный программист 1С. Поэтому рекомендуется использовать Microsoft SQl для полного функционала коннектора. Также сторонников безопасности может насторожить тот факт, что коннектору нужен доступ на запись в рабочую базу SQL 1C для создания представлений View (но в другой схеме).
Примерные трудозатраты и бюджет: от 60 000 рублей за 1 базу 1С, в рамках 1 рабочего дня программиста 1С на подключение.
3. Публикация базы 1С в вебе и подключение к ней по протоколу oData
Этот способ сейчас активно рекламируется и продвигается различными аналитиками. В первую очередь, потому что это довольно просто. Нужно просто опубликовать базу 1С на веб-сервере (у нас на сайте есть статья-инструкция на эту тему) и затем согласно справке можно делать такие запросы вида:
https://lc-web.azhdanov.ru:8443/ka/odata/standard.odata/Catalog_Номенклатура?$format=json
В ответ вы получите файл-справочник Номенклатура. Более того в справке 1С можно найти команды, которые позволят вам сделать преобразование ссылок в значения на лету, то есть вам не нужно будет выгружать десяток справочников, чтобы собрать один документ. Также поддерживается возможность задать разные фильтры, чтобы ограничить объем получаемых данных.
На выходе получается файл json со всеми реквизитами и значениями справочника.
Так в чем же проблема этого метода? Проблем я вижу две.
Первое, подключение к Odata довольно медленное, если файл будет содержать сотни тысяч записей, а то и миллионы (а для регистра бухгалтерии это довольно обычный объем), то файлы будут запрашиваться по 5-10-15 минут, могут возникнуть обрывы подключения. Каждый новый документ и справочник потребует генерации нового источника данных и нового файла. В итоге при большом объеме данных этот способ не сильно будет отличаться от выгрузок Excel файлов.
Второе, не все готовы публиковать базу 1С на веб-сервере. Иногда, например для синхронизации 1С и CRM системы клиенты уже это делают, но есть и консервативные компании, которые не работаю с облачными сервисами и доступом принципиально из-за угроз утечки данных.
Не смотря на указанные недостатки выше, все равно это очень популярный способ и многие аналитики, интеграторы публикуют инструкции и доп. сервисы, облегчающие генерации нужных веб-запросов к опубликованной базе 1С.
Примерные трудозатраты и бюджет: условно бесплатно, необходимо участие программиста 1С, настраивается примерно за 1 рабочий день.
4. Построение собственной аналитической базы SQL и наполнение ее выгрузками из 1С
Думаю, что если скажу, что для серьезной аналитики с большими данными нужна своя аналитическая база SQL, то большинство аналитиков со мной согласится.
Работая с файлами Excel/Json вы столкнетесь с проблемами производительности и сложностью администрирования и технической поддержки. Пытаясь разобраться в структуре данных SQL 1C базы, вы столкнетесь с тем, что она проектировалась для оптимизации работы 1С, а не для аналитиков. Простой документ может потянуть за собой десяток справочников, а каждый справочник может использовать дочерние справочники, а дочерние справочники какие-нибудь перечисления и так далее. Можно легко получить 3-5 уровней нормализации данных, что также не всегда нужно аналитику.
Поэтому для больших проектов мы будем предлагать этот вариант, хотя он самый трудозатратный и дорогой.
В чем преимущество данной базы? Во первых, вы сможете в ней объединить данные из разных источников. Вот пример схемы из моего реального проекта.
Мы получали данные из различных источников:
- 1С УПП
- веб-сервис ГИС с геоданными
- база данных производственной программы
- ряд Excel файлов финансовой службы.
Все эти данные аккумулировались в специальной аналитической базы, а пользователи могли подключаться к ней через Excel или BI инструменты — Power BI или любой другой.
Во вторых, внутри этой базы вы можете разложить свои данные на несколько уровней. Аналитики различают несколько уровней и преобразований данных:
- Сырые данные.
Данные, которые без изменений приходят из различных источников
- Подготовленные данные
На этом уровне происходит фильтрация ошибочных значений, обновление данных, приведение их к единому формату, обогащение данных доп. информацией
- Аналитические данные
На этом уровне происходит агрегирование данных, представление данных в удобном для аналитике виде.
Таким образом внутри аналитической базы вы сможете проводить расчеты любой степени сложности. Данные расчеты могут быть выполнены быстрее, чем к примеру в Power Query от Power BI.
А как же наполнять эти данные выгрузками из 1С? Скорей всего вам придется самостоятельно написать нужные обработки и регламентные задания, которые представляют собой запросы к базе 1С и выгрузку результатов этих запросов в заранее созданные SQL таблицы. Вот пример подобного запроса:
Для программиста 1С такой запрос не представляет особой сложности, но работа может быть довольно трудоемкой, если данные нужны из различных документов, регистров и справочников. Для каждого такого объекта нужно будет подготовить свою таблицу SQL и свои запросы.
Проектирование подобной базы стоит доверить опытному аналитику, который имеет доступ ко всем источникам данных для проектам и сможет правильно их соединить, обработать и подготовить в необходимом для анализа виде.
Примерные трудозатраты и бюджет: от 1 недели до 1 года в зависимости от объема данных, количества источников, совместимости данных друг с другом и множества других факторов. Бюджет от 100 000 рублей до нескольких миллионов для крупных компаний.
5. Подключение напрямую к рабочей базе SQL 1С и разбор метаданных
Честно говоря, я никогда не рассматривал этот способ всерьез. У него есть несколько существенных минусов.
Во первых, это запрещено лицензионной политикой 1С.
Нельзя обращаться к данным информационной базы напрямую, минуя уровень объектов работы с данными «1С:Предприятия», например при помощи средств СУБД или при помощи внешних компонент, которые реализуют прямой доступ к СУБД. Это ограничение распространяется на любые действия с данными, в том числе на изменение их структуры, а так же на чтение или изменение самих данных информационной базы или служебных данных «1С:Предприятия».
Во вторых, подключившись к таблице документа мы увидим что-то подобное:
Большинство полей будут представлять собой ссылки на другие таблицы (справочники, перечисления), плюс все названия таблиц, столбцов представляют собой так называемые Метаданные — особую структура базы данных 1С в которой разобраться может быть непросто.
С другой стороны, от коллег я с удивлением узнал, что множество аналитиков использует этот способ и один раз разобравшись со сложной структурой метаданных могут очень быстро получать любые данные из рабочей базы SQL 1C.
Примерные трудозатраты и бюджет: условно бесплатно, необходимо участие программиста 1С, настраивается примерно за 1 неделю.
Таким образом как вы видите способ получения данных из 1С довольно много. Нельзя сказать, что какой-то самый лучший, а какой-то самый плохой. Все эти способы имеют право на существование и успешно используются в разных проектах. Можно начать с самого простого — выгрузок в Excel, построения первых аналитических отчетов и постепенное развитие культуры аналитики в компании. А можно сразу начать проектирование корпоративного хранилища данных. Все зависит от целей и задач аналитики и мы с удовольствием проконсультируем и подберем оптимальное решение для ваших задач и бюджета.
С уважением Жданов Андрей и наша команда #Zhdanov
