Генератор View (Коннектор 1С для Power BI) к таблицам базы данных MS-SQL для любых конфигураций 1С 8.3
В этой статье расскажем о том как быстро и удобно получить данные из 1С для анализа в Power BI.
Предыстория
Почти в каждой российской компании есть 1С 8 (в 99% случаев это одно из типовых решений на 1С 8.3). Какие есть способы доступа к данным 1С из внешних систем аналитики (Power BI, Qlik Sense, Tableue и т.д.)?
Обычно это:
- Odata;
- Выгрузка данных из 1С в текст, Excel;
- Внешние платные сервисы по подписке.
На форумах часто вижу такие запросы:
- Как получить данные из 1С для Power BI?
- Пользуюсь OData для доступа к 1С к Power BI, но подключение постоянно падает, что делать?
- Пользуюсь OData для доступа к 1С к Power BI, но получаю ошибки. Что делать?
- У меня база 1С на MS SQL Server, но там совершенно нечитаемый формат данных. Как прочитать данные из базы для Power BI?
- и т.д.
Что делать?
Есть возможность получать данные из 1С напрямую из базы данных (для клиент-серверного варианта, конечно же), но для этого необходимо перевести таблицы 1С в человекочитаемый формат.
Вот так выглядит стандартная таблица в базе данных 1С 8:
Согласитесь, не слишком дружелюбный формат, а если таких полей типа: _Fld1707RRef, _Fld31012 и т.д. - много?
Как же выглядит подобная таблица в варианте, которые генерирует наша обработка?
Та же самая таблица, но все поля приведены к названиям полей в конфигураторе 1С, равно как и понятны имена таблиц.
Такое превращение выполняет наша обработка - "Генератор View". Если вам нужно автоматически генерировать view (представления к таблицам базы данных 1С) к вашей базе данных MS SQL по структуре метаданных 1С, то вы точно оцените данную обработку.
В нашей компании есть направление бизнес-аналитики. Мы делаем витрины данных, различные интеграции, а также внедряем у клиентов OLAP (MS Analysis Services) и PowerBI. Почти в каждом проекте у нас возникает необходимость в получении данных из баз данных 1С. Мы собираем данные из 1С напрямую, читая данные БД 1С в MS SQL Server.
Кто-то скажет, что это не очень правильно. Но зато это очень эффективно. Из опыта западных решений - очень многие предоставляют удобный доступ к своей реляционной базе данных, если и не к таблицам, то к View точно (посмотрите хотя бы на MS CRM).
Состав решения
В результате изысканий мы разработали весьма удобный конструктор view (конструктор представлений) почти ко всем таблицам 1С (на сегодня тестировались конфигурации под 1С 8.3 как внутри компании, так и у клиентов). Обработка умеет генерировать View к:
- справочникам и их табличным частям;
- документам и их табличным частям;
- планам видов характеристик;
- регистрам сведений;
- регистрам накопления;
- перечислениям (тут особенность :). Мы заполняем view перечисления всеми данными, которые в них есть, включая наименование (понимающие это оценят);
- константам;
- задачам;
- бизнес-процессам;
- регистрам расчета;
- планам видов расчета;
- регистрам бухгалтерии;
- планам счетов.
Как работает
- Запускаем обработку в режиме 1С Предприятия;
- Выполняем настройку (подключение к MS SQL Server и созданной заранее БД для хранения View (Представлений);
- Выбираем необходимые сущности для генерации View;
- Создаем их;
- Переходим в Power BI;
- Подключаемся к БД для хранения View.
Краткое видео по настройке и примеру создания View:
В итоге, получаем вот такой результат (потрачено примерно 8 минут на разработку данной панели отчетности):
Опциональные вещи:
- Приведение ключевых полей таблиц к типу bigint или varchar(32) (как вы знаете, ключевые поля в 1С хранятся в binary(16)).
- Создание view с директивой WITH (NOLOCK).
- Для регистров накопления, помимо view к таблицам с данными, можно создавать еще и view к таблицам оборотов.
- Для регистров накопления, помимо view к таблицам с данными, можно создавать еще и view к таблицам итогов.
- Для регистров накопления можно создавать дополнительные поля (Движение, Приход, Расход).
- Из полей с датами опционально можно вырезать время. Т.е. приводить поля с типом datetime к date.
- Для именования полей и представлений (view) можно использовать как названия из метаданных, так и "синонимы".
- Добавлять префиксы к view в названии (или не добавлять).
- Пересоздавать представления (drop... create...).
- . Восстанавливать выбор ранее созданных view (представлений), чтобы не вспоминать, какие из представлений надо переделать.
- . Добавлять строку в представление (view) с кодом 0 (требуется для корректной работы LOOKUPVALUE и PATH в Power BI).
- Добавлен флаг выгружать "только неудаленные элементы справочников".
- Добавлен флаг выгружать "только неудаленные документы".
- Добавлен флаг выгружать "Только проведенные документы".
Особенной фишкой нашего "Генератора View" является поиск и правильное именование ключевых полей в представлениях, что в 70% случаев позволяет использовать автоматические связи. Это актуально для Qlik Sense и Power BI. Если ключевое поле справочника и поле документа/регистра совпадают по наименованию, то Qlik Sense/Power BI создаст связь между этими полями автоматически.
Обработка реализована под MS SQL Server и PostgreSQL.
Требования, условия, ограничения
- Работает под конфигурациями на 1С 8.3 (тестировалось с 1С 8.3.13.ххх и выше), управляемые формы;
- При небольшой модификации, обработка сможет генерировать View (представления и для версий 1С 8.2/8.1;
- Необходимо прямое подключение к БД 1С (клиент-серверный режим с использованием MS SQL Server. Тестировалось не ниже MS SQL Server 2012);
- Тестировалось и корректно работает на УТ 11.4 и 1С ERP, а также на 1С "Комплексная автоматизация", Управление торговлей 10.3, 1С: УНФ, 1С: ЗУП и т.д.. Аналогично будет работать и на любых других конфигурациях под 1С 8.3 (1С Розница, 2, КА 2 и т.д.), так как используются общие для 1С 8.3 платформенные механизмы чтения и обработки метаданных;
- На обычных формах работает (просто надо зайти в пользовательский режим 1С в тонком клиенте и просто открыть обработку);
- Обработка претендует на универсальность.
Особенности:
- в MS нет ограничения на длину (или его хватает чтобы не обрезались наименования), и PostgreSQL 63 байта (31 буква кириллицы);
- в MS имя базы может отсутствовать (текущая база), в PG подключение всегда к базе, поэтому этого параметра нет;
- в MS для не блокирующего чтения используется with (nolock), в PostgreSQL чтение никогда не блокирует таблицы (если не указать FOR UPDATE);
- в MS можно создавать представления к таблицам в пределах одного инстанса, в PostgreSQL только в пределах базы (не рассматриваются варианты типа dblink, которые приводят к чтению всей таблицы).
- Значительное ускорение доступа к таблицам 1С в MS SQL Server для бизнес-аналитика;
- Высокая скорость выборки данных из таблиц 1С (скорость определяется только скоростью чтения данных БД MS SQL Server);
- Все представления (View) создаются в отдельной базе MS SQL Server, не затрагивая никоим образом основную базу 1С;
- Удобное создание и пересоздание View;
- Простой и интуитивно понятный интерфейс.
- Сделан вариант конвертации идентификаторов 1С (_IdRef) из Binary в Varchar(32);
- Добавлена выгрузка "Задачи";
- Добавлена выгрузка "Бизнес-процессы";
- Мелкая оптимизация кода.
- Теперь обработка работает не только с MS SQL Server, но и с СУБД 1С - PostgreSQL;
- Добавлены План счетов и журнал проводок 1С, Регистры бухгалтерского учета, (актуально для 1С:Бухгалтерия, 1С:Комплексная автоматизация, 1С:ERP, 1С:Управление нашей фирмой);
- Добавлены Планы видов расчета, Регистры расчетов, Зарплатные регистры (актуально для 1С:Зарплата и управление персоналом).
Достоинства обработки
Сравнение версий
Версия 2.3.2
Добавлен новый функционал:
Версия 2.4.5
Добавлен новый функционал:Стоимость решения
Генератор представлений (View) в MS SQL по структуре базы данных 1С 8.3 - 60 000 руб. (НДС нет)
Программа проверена и полностью готова к работе. Мы гарантируем 100% возврат оплаты, если программа не соответствует заявленному функционалу из описания. Мы хотим, чтобы все наши покупатели оставались довольны.
Задать вопрос / Заказать обработку
Фотогалерея
Внедрения
Товары
Услуги
Получите доступ к своим базам данных 1С и создавайте удобные интерактивные дашборды в Yandex Datalens за 10 минут
Получите доступ к своим базам данных 1С и создавайте удобные интерактивные дашборды в Power BI за 10 минут
Анализируйте показатели своего бизнеса и стройте отчеты любого уровня сложности с помощью low-code платформы Логином.