Интеграция 1С 8.3 с Power BI (Генератор представлений (View) в MS SQL/PostgreSQL по структуре базы данных 1С 8.3)

Публикация размещена исключительно в образовательных целях и подходит только для платформы версии [8.3.10 и выше]
Обработка 1С предоставляет недокументированные средства доступа к базе данных 1С.

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

Генератор View к таблицам базы данных MS-SQL/PostgreSQL для любых конфигураций 1С 8.3 (Коннектор 1С для Power BI)

Почти в каждой российской компании есть 1С (в 99% случаев это одно из типовых решений на 1С 8.3)

На форумах по BI-аналитике часто вижу такие запросы:

1. Как получить данные из 1С для Power BI?
2. пользуюсь OData для доступа к 1С к Power BI, но подключение постоянно падает, Что делать?
3. пользуюсь OData для доступа к 1С к Power BI, но получаю ошибки. Что делать?
4. У меня база 1С на MS SQL Server, но там совершенно нечитаемый формат данных. Как прочитать данные из базы для Power BI?
5. Как сделать коннектор к 1С из Power BI
6. и т.д.

Что делать?

Есть возможность получать данные из 1С (для клиент-серверного варианта конечно же) напрямую - из базы данных, но для этого необходимо "перевести" таблицы 1С в человекочитаемый формат.

И для этого есть в арсенале наших разработок незаменимый помощник для бизнес-аналитиков -  "Генератор View", он же "Коннектор 1С для BI". Если вам нужно автоматически получать данные из 1С напрямую в Power BI или другую систему через view (представления к таблицам базы данных 1С) к вашей базе данных MS SQL по структуре метаданных 1С, то вы точно оцените данную обработку.

Состав решения

Мы разработали простой и удобный конструктор view (представлений) почти ко всем таблицам 1С (нет доступа к части виртуальных таблиц 1С). Обработка умеет генерировать View к:

1. Справочники и их табличные части
2. Документы и их табличные части
3. Планы видов характеристик
4. Регистры сведений
5. Регистры накопления
6. Перечисления (тут особенность :). Мы заполняем view Перечисления всеми данными, которые в них есть, включая наименование, понимающие в этом - оценят)
7. Константы
8. Задачи
9. Бизнес-процессы
10. Регистры расчета
11. Планы видов расчета
12. Регистры бухгалтерии
13. Планы счетов

Есть еще опциональные вещи:

1. Приведение ключевых полей таблиц к типу bigint или varchar(32) (как вы знаете, ключевые поля в 1С хранятся в binary(16))
2. создание view  с директивой  WITH (NOLOCK)
3. для регистров накопления, помимо view к таблицам с данными, можно создавать еще и view к таблицам оборотов
4. для регистров накопления, помимо view к таблицам с данными, можно создавать еще и view к таблицам итогов
5. для регистров накопления, можно создавать дополнительные поля (Движение, Приход, Расход)
6. из полей с датами, опционально можно вырезать время. Т.е. приводить поля с типом datetime к date
7. для именования полей и представлений (view) использовать как названия из метаданных, так и "синонимы"
8. Добавлять префиксы к view в названии )или не добавлять)
9. Пересоздавать представления (drop... create...)
10. Восстанавливать выбор ранее созданных view (представлений), чтобы не вспоминать какие из представлений надо переделать
11. Добавлять строку в представление (view) с кодом 0 (требуется для корректной работы LOOKUPVALUE и PATH в Power BI
12. Также, можно встроить отбор на "НЕПОМЕЧЕННЫЕ" на удаление документы и/или Справочники, а также вывод только ПРОВЕДЕННЫХ документов

Особенной фишкой нашего "Генератора View" является поиск и правильное именование ключевых полей в представлениях, что в 70% случаев позволяет использовать автоматические связи (актуально для Qlik Sense и Power BI, где если ключевое поле справочника и поле документа/регистра совпадают по наименованию, то Qlisk Sense/Power BI создаст связь между этими полями автоматически)

Существует две версии обработки:

1. для MS SQL Server (реализовано для управляемых и обычных форм)

2. для PostgreSQL

Как это работает?

Особые требования:

1. Необходимо подключение к БД 1С (клиент-серверный режим с использованием MS SQL Server/PostgreSQL, тестировалось не ниже MS SQL Server 2012)
2. тестировалось и корректно работает на УТ 11.4 и 1С ERP 2.1. Аналогично будет работать и на любых других конфигурациях под 1С 8.3 (1С Розница, 2, КА 2 и т.д.), так как используются общие для 1С 8.3 платформенные механизмы чтения и обработки метаданных
3. Версия для MS SQL Server реализована уже и для управляемых и для обычных форм
4. Версия PostgreSQL пока сделана только для управляемых форм
5. Обработка претендует на некоторую универсальность

Особенности:

Для версии MS SQL Server

- в MS нет ограничения на длину (или его хватает чтобы не обрезались наименования), и PG 63 байта (31 буква кириллицы)
- в MS имя базы может отсутствовать (текущая база), в PG подключение всегда к базе, поэтому этого параметра нет
- в MS для не блокируещего чтения используется with (nolock), в PG чтение никогда не блокирует таблицы (если не указать FOR UPDATE)
- в MS можно создавать представления к таблицам в пределах одного инстанса, в PG только в пределах базы (не рассматриваются варианты типа dblink, которые приводят к чтению всей таблицы)

Настройка для версии PostgreSQL

1. Установить драйвер - psqlodbc (psqlodbc - PostgreSQL ODBC driver)

Далее заполняем строка подключения к ODBC PostgreSQL

• ODBC - драйвер PostgreSQL Unicode
• 
• после настройки, получается строка(вроде этой)
• DRIVER={PostgreSQL Unicode};DATABASE=БАЗА;SERVER=СЕРВЕР;PORT=5432(ПОРТ);UID=ЮЗЕР;PWD=ПАРОЛЬ;SSLmode=disable

Важное дополнение

Данная обработка 1С не создает Хранилище данных, не выгружает данные 1С - он просто дает возможность прочитать физические таблицы 1С в "человекочитаемом" формате

Данный подход накладывает определенные ограничения на получение данных из 1С:

1. Данные читаются "без блокировки" из SQL-базы 1С
2. Нет никакой защиты данных (нет RLS, ограничений по доступам). Т.е. тот кто получает доступ к View - получает абсолютно все данные из вашей базы
3. Данный способ не рекомендуется для тех BI-систем, где нет своей базы данных (т.е. наш "Генератор View" можно рекомендовать для таких систем, как Power BI или Visiology, но НЕ стоит его использовать для Yandex Datalens или Apache Superset)
4. Виртуальные таблицы (остатки, срезы последних, обороты, Движения по субконто, регистрация изменений и т.д.) - будут недоступны
5. Вы должны понимать, что соединение таблиц в схему, все join вы выполняете на стороне уже DWH (BI-системы) и это тоже дает нагрузку на вашу базу 1С

Если же Вы хотите избежать всех этих ограничений и получить дополнительную функциональность, то посмотрите другое наше решение: "Экстрактор данных 1С в BI"

Причины купить

1. Прямой доступ к базе данных 1С для бизнес-аналитиков из Power BI
2. Быстрая загрузка данных в в Power BI из 1С
3. Высокая надежность
4. Нет абонентских платежей: один раз купил и пользуешься ("навсегда")

Достоинства

Важным преимуществом обработки является 

1. значительное ускорение подготовки View к таблицам 1С в MS SQL Server для бизнес-аналитика
2. удобное создание и пересоздание View
3. Быстрый и надежный доступ к данным 1С напрямую из Power BI
4. Простой и интуитивно понятный интерфейс

Сравнение версий

Версия 2.4.7

Добавлены

1. Задачи
2. Бизнес-процессы
3. Регистры расчета
4. Планы видов расчета
5. Регистры бухгалтерии
6. Планы счетов

Добавлена поддержка PostgreSQL

Версия 2.4.1

1. Добавлена возможность указать схему данных (по умолчанию dbo)
2. Добавлена кнопка "Восстановить" (перебирает ранее созданные View и при совпадении имени восстанавливает флаг отметки объекта

Версия 2.3.2

Добавлен новый функционал:
1. Сделан вариант конвертации идентификаторов 1С (_IdRef) из Binary в Varchar(32)

2. Добавлена выгрузка "Задачи"
3. Добавлена выгрузка "Бизнес-процессы"
4. Мелкая оптимизация кода