Получение данных из 1С для последующей аналитики в Qlik Sense или QlikView – работа относительно несложная, но трудоемкая. Чтобы снизить время на интеграцию данных из 1С и последующую ее поддержку, мы в текущей компании используем коннектор АТК BIView. Сегодня расскажу о нем и дам инструкцию по его использованию.
Contents
- 1 Варианты получения данных из 1С в Qlik (или любой другой BI-системе)
- 2 Коннектор АТК BIView для Qlik: принцип работы, плюсы и минусы использования
- 3 Принцип работы коннектора АТК BIView — Видео обзор
- 4 Ограничения коннектора АТК BIView
- 5 Плюсы коннектора АТК BIView
- 6 Инструкция по работе с коннектором АТК BIView для 1С и Qlik
- 7 Установка и настройка соединения с 1С
- 8 Создание SQL-представлений
- 9 Создание SQL-представлений в тестовой версии АТК BIView
- 10 Подключение к SQL-представлениям 1С в Qlik Sense
- 11 Подключение к SQL-представлениям 1С в QlikView
- 12 Полезные ссылки по коннектору АТК BIView
Варианты получения данных из 1С в Qlik (или любой другой BI-системе)
Глобально есть следующие способы извлечения данных из 1С для последующего использования:
- выгрузка Excel-файлов на FTP папку. Подходит только для совсем маленьких проектов, не промышленное решение, крайне не рекомендую его использовать.
- создание промежуточной аналитической базы SQL (буферная база), куда выгружаются из 1С нужные данные. Делается на основе обработок 1С. Минусы — может не хватить мощности железа «выталкивать» данные 1С с помощью обработки + на это нужны ресурсы 1С программиста, которые обычно в дефиците
- подключение через протокол OData. Этот вариант рекомендован 1С, но его минус – в специфике OData. Если у вас достаточно большой объем данных, этот протокол сильно грузит 1С
- SQL-запросы на 1С базу. Этот и OData, наверное, самый популярный вариант, но он требует много рутинных операций: поиск в конфигураторе 1С названий таблиц хранения, переименование полей и таблиц, чтобы в BI с ними было удобно работать, перечисления извлечь – вообще особая история. Это все не так уж сложно, но отнимает много времени и сил. Т
- Коннекторы. Есть коннекторы для двух основных форматов – OData и SQL. Они ускоряют работу по извлечению данных из 1С. Коннектор к OData работу по интеграции данных из 1С ускоряет, но не избавляет от минусов использования этого протокола. Лично я работал с коннектором АТК BIView, который создает SQL-представления на основе 1С, к которым мы потому без проблем подключаемся при помощи Qlik Sense или QlikView (ну или других BI-систем, включая тот же Power BI).
Коннектор АТК BIView для Qlik: принцип работы, плюсы и минусы использования
Коннектор АТК BIView в автоматическом режиме создает SQL-представления базы 1С, с нужными для BI-систем трансформациями (с переименованными полями, разрешением ссылочных полей, преобразованием дат со смещением 2000 и т.д.). При этом для коннектора не важно 1С типовая или доработанная, потому что он подключается к метаданным для создания SQL-представлений. По сути получается вот так:
Принцип работы коннектора АТК BIView — Видео обзор
Вот видео на 3,5 минуты, объясняющее, как он работает:
Ограничения коннектора АТК BIView
- Подходит для 1С, начиная с версии 8. 1С 7 не поддерживается
- Подходит, если Ваша 1С развернута на SQL или PostgreSQL. Файловая 1С не поддерживается.
- Коннектор платный – можно купить постоянную версию, но совсем скоро обещают подписку. Учитывая, что за месяц подписки можно сгенерировать все нужные SQL-представления до следующего обновления или бэкапа 1С, подписка должна быть интересна. Пока цены на постоянную версию можно посмотреть на сайте.
Плюсы коннектора АТК BIView
- Снимает большую часть монотонной работы по поиску соответствий 1С и SQL, переименованиям и пр. Если у вас несколько баз 1С, из которых нужно забирать данные в хранилище или BI-систему, экономия времени выходит значительная.
- Шансов что-то испортить в 1С нет – у коннектора права только на чтение 1С, записывает вьюхи он в соседнюю БД, которую мы для него специально создаем.
- Работает из коробки с любой конфигурацией 1С (проверяли на трех разных), но при этом коннектор – это не черный ящик. В папке проекта (та, что по умолчанию в ProgramData) создаются скрипты, которые можно открыть и посмотреть. ScriptLev2.txt – это полный скрипт того, как создавались SQL-представления и какие трансформации с данными производили. Например, в куске кода ниже видны такие преобразования (пометил красным):
- Технические наименования полей загружаются с теми названиями, что есть в пользовательском интерфейсе 1С
- Делается смещение дат 2000
- Подтягиваются справочники при помощи left join-ов
- Поддержка интеграции данных из 1С сильно упрощается. Если структура данных 1С изменилась, нужно открыть коннектор, запустить «расчет» и все. Все связи перестроятся самостоятельно и ваша BI-система или ХД будет работать с актуальными SQL-представлениями из 1С, а вам ничего не нужно будет вручную разбирать, сверять и поправлять.
Инструкция по работе с коннектором АТК BIView для 1С и Qlik
Здесь я дам инструкцию по работе с тестовой версией коннектора АТК BIView. Она бесплатна и дает возможность протестировать, что коннектор может работать с вашей 1С, а также позволяет создать мини-пилотный проект на основе данных 1С в QlikView или Qlik Sense (максимум 12 таблиц). У бесплатной версии коннектора есть два возможных сценария использования:
- Тестирование: Автоматически создать до 25 SQL-представлений для таблиц каждого из следующих типов: регистры, документы и справочники
Результат: АТК BIView» генерирует SQL-представления в алфавитном порядке, поэтому получившиеся вьюхи для пилотного проекта использовать не получится. Вы проверите, что автоматическая генерация SQL-представлений на основе вашей 1С работает - Мини-проект: Вручную в визуальном интерфейсе выбрать 12 связанных таблиц из полного списка 1С конфигурации и создать SQL-представления только для них
Результат: на основе полученных SQL-представлений можно будет создать небольшой работоспособный проект, например, по анализу продаж или оборотно-сальдовой ведомости
В тестовой версии коннектора АТК BIView есть еще одно ограничение
- не создаются SQL-представления для:
- табличных частей Документов/Справочников
- виртуальных таблиц
- сводных (pivot) таблиц
Скачать тестовую версию коннектора АТК BIView можно тут.
Установка и настройка соединения с 1С
1. Про установку подробно писать нет смысла – посмотрите видео:
2. Настройка соединения. Когда коннектор установлен, нужно настроить соединение с 1С. Тут все очевидно, кроме пункта «БД Проекта» (отметил на скриншоте ниже).
БД Проекта – это база данных, куда коннектор записывает созданные SQL-представления и служебные таблицы. Дело в том, что АТК BIView обращается к 1С только на чтение и ничего не записывает в базу данных 1С. Поэтому на том же SQL сервере, где лежат данные 1С, нужно создать отдельную базу, куда будут записываться все SQL-представления и служебные SQL-таблицы, которые создаются в процессе работы АТК BIView и впоследствии будут использоваться QlikView, Qlik Sense или другой BI-системой или хранилищем данных. В АТК BIView эта база называется «База данных проекта».
Вот БД Проекта, которую я создал на MS SQL сервере для коннектора, папка Views пока пуста:
3. Еще на экране подключения есть поле «Проект» со ссылкой на папку проекта, где хранятся все данные по настройкам ATK BIView, логи, которые пишутся во время подключения и расчета, скрипты, создающие SQL-представления. По умолчанию папка проекта находится строго в ProgramData на диске С, изменить ее местоположение можно только с Корпоративной лицензией.
4. Нажимаем «Проверка подключения» (коннектор подключается как клиент 1С) и ждем надписи, что подключение успешно. Готово:
Создание SQL-представлений
У коннектора 2 типа проекта, в которых можно создавать SQL-представления:
1-Базовый
2-Расширенный
В базовом типе проекта при генерации SQL-представлений происходит меньшее количество трансформаций данных, поэтому рекомендую всегда:
5. Выбирать Тип проекта: 2 – Расширенный.
6. Нажимаем кнопку «расчет»:
Дальше нужно подождать. Сколько подождать – зависит от объема вашей 1С. Коннектор считывает метаданные, готовит технические SQL-таблицы и на основе них уже создает SQL-представления. Информацию о процессе показывает в строке рядом с кнопкой «Расчет»:
7. Готовые SQL-представления. Когда расчет завершится, база данных проекта, которую мы создавали будет содержать все SQL-представления по вашей 1С. Например, вот так:
Их мы можем посмотреть, зайдя в MS SQL Server Management Studio. Видим, что:
- Названия таблиц соответствуют названиям таблиц 1С (вместо, к примеру, dbo._AccumRG7265 привычное человекочитаемое название – «РегистрыНакопления.Продажи»)
- Есть дополнительные SQL-представления, которые создаются для дальнейшего удобства работы с BI. Например, для хозрасчетного регистра бухгалтерии строится представление с полупроводками (движение отдельно по счету дебета, отдельно – по счету кредита). Это удобно для анализа оборотно-сальдовой ведомости в BI-системе.
8. Теперь собственно, можно подключаться к этим самым SQL-представлениям в QlikView или Qlik Sense. Инструкция – ниже.
Создание SQL-представлений в тестовой версии АТК BIView
Если Вы используете бесплатную тестовую версию коннектора АТК BIView, создать полноценный пилотный проект не получится – автоматически генерируются только 25 SQL-представлений в алфавитном порядке, поэтому можно сгенерировать вьюхи вручную. Здесь нужно вручную выбрать до 12 связанных между собой таблиц из 1С-конфигуратора и сгенерировать для них SQL-представления. В этот список таблиц можно включить любые таблицы Регистров, Документов и Справочников (кроме табличных частей Документов и Справочников, виртуальных таблиц, сводных таблиц). При этом, значения Перечислений подключаются автоматически, в полном объеме.
Сейчас будет инструкция, как это сделать, и какие таблицы 1С можно выбрать для небольшого приложения по анализу продаж и контрагентов.
Важный момент именно для тестовой версии коннектора АТК BIView: проверка подключения и выбор таблиц для создания SQL-представлений должны выполняться за один сеанс запуска коннектора — без промежуточного выхода из программы и повторного запуска. Приступим:
1. Создаем проект, указываем местонахождение базы данных проекта, вводим параметры подключения и выполняем Проверку подключения (см. раздел выше – Установка и настройка соединения с 1С).
2. Выбираем «Тип Проекта: 2 – Расширенный»
3. Теперь переходим на вкладку «Информация» и в списке слева выбираем «Tref-Список Таблиц». Это наш 1С-конфигуратор, откуда нужно выбрать 12 связанных таблиц:
4. Выбираем 12 связанных таблиц 1С из списка конфигуратора вручную, удерживая Ctrl, а затем нажимаем кнопку слева «Применить».
Например, для создания небольшого аналитического приложения по продажам и контрагентам можно выбрать таблицы:
- РегистрыНакопления.Продажи
- Документы.ЗаказПокупателя
- Справочники.Валюты
- Справочники.ДоговорыКонтрагентов
- Справочники.Номенклатура
- Справочники.Контрагенты
- Документы.ОтчетОРозничныхПродажах
- Справочники.Организации
- Документы.РеализацияТоваровУслуг
- Справочники.БанковскиеСчета
- Документы.СчетНаОплатуПокупателю
- Справочники.Склады
5. Теперь нажимаем кнопку «Расчет», и АТК BIView генерирует SQL-представления для заданных таблиц.
После выполнения Расчета сформированы SQL-представления для заданных таблиц.
6. Когда появилась надпись «Расчет завершен», можно зайти в Microsoft Management Studio и посмотреть, какие SQL-представления АТК BIView создал. К ним мы будем подключаться в следующем шаге инструкции – есть для QlikView и для Qlik Sense.
Подключение к SQL-представлениям 1С в Qlik Sense
1. Открываем Qlik Sense, создаем новое приложение, даем имя приложению и заходим в редактор скрипта.
2. Прежде всего, нам надо подключиться к базе данных Microsoft SQL, куда коннектор АТК BIView записал SQL-представления данных в таблицах 1С. Для этого выбираем «Create new connection»:
3. Подключение – OLEDB:
4. В окне выбираем провайдер Microsoft OLE DB Provider for SQL Server, вводим имя подключаемой базы данных, выбираем вариант безопасности соединения – у меня это Windows Integrated Security (для Вашей сети он может быть другой) и нажимаем «Test connection» (проверка соединения).
5. Когда проверка прошла успешно, выбираем базу данных, к которой мы подключаемся. Нужно выбрать ту, что мы указывали в интерфейсе коннектора АТК BIView в поле «БД Проекта» — туда он записывал все создаваемые SQL-представления.
6. Нажимаем на кнопку «Create» и создаем подключение.
7. Когда подключение к нужной БД Microsoft SQL установлено, соответствующий значок появляется в правом сайдбаре. Теперь нажимаем на кнопку «insert connection string» и видим, что в редакторе скрипта появилась автоматически созданная строка подключения к источнику данных:
8. Дальше нажимаем кнопку выбора данных «Select data»:
9. В появившемся окне мастера загрузки данных нужно указать владельца базы данных в поле «Owner»:
10. Теперь у нас появился список таблиц-представлений выбранной базы данных. Таблиц много (или всего 12, если у вас тестовая версия коннектора), поэтому можно воспользоваться полем поиска:
11. Ну а дальше – берем нужные таблицы, собираем из них модель данных и визуализируем в Qlik Sense.
12. Для примера можно посмотреть видео-инструкцию, где на основе SQL-представлений коннектора АТК BIView собирают простой отчет по продажам, себестоимости и марже в Qlik Sense:
Подключение к SQL-представлениям 1С в QlikView
1. Открываем QlikView, создаем новое приложение, даем имя приложению и заходим в редактор скрипта.
2. Прежде всего, нам надо подключиться к базе данных Microsoft SQL, куда коннектор АТК BIView записал SQL-представления данных в таблицах 1С. Для этого выбираем «OLEDB-Соединение»:
3. Выбираем «Microsoft OLEDB Provider SQL Server», нажимаем клавишу «Next»:
4. В окне «Data Link Properties» выбираем:
- Сервер или вводим Имя Сервера самостоятельно.
- Вариант безопасности соединения
- Нужную нам базу данных, к которой мы подключаемся. Нужно выбрать ту, что мы указывали в интерфейсе коннектора АТК BIView в поле «БД Проекта» — туда он записывал все создаваемые SQL-представления
- Нажимаем «test connection»:
5. Тест прошел успешно. Нажимаем «ОК». В редакторе скрипта появилась автоматически созданная строка подключения к источнику данных:
6. Теперь займемся загрузкой данных. Нажимаем кнопку «Выбрать»:
7. В появившемся окне мастера загрузки проверяем правильность заполнения данных в поле база данных и владелец, а также наличие галочек «Таблицы» и «просмотры» в сайдбаре слева:
8. Ну а дальше – берем нужные таблицы и поля из них, собираем модель данных и визуализируем в QlikView:
9. Для примера можно посмотреть видео-инструкцию, где на основе SQL-представлений коннектора АТК BIView собирают простой отчет по продажам, себестоимости и марже в QlikView:
Полезные ссылки по коннектору АТК BIView
- Сайт коннектора: http://biview.atkcg.ru/
- База знаний: http://biview.atkcg.ru/knowledge-base/
- Бесплатная триальная версия: http://biview.atkcg.ru/download/
- Короткое объясняющее видео о том, как работает коннектор: https://youtu.be/RHici4sy_Wo
- Видео пример: 1C + Qlik Sense, анализ продаж: https://youtu.be/67U4SeUi-jQ
- Видео пример: 1С + QlikView, анализ продаж: https://youtu.be/uy8uHWopObo
- Инструкция по работе с триальной версией АТК BIView: http://biview.atkcg.ru/knowledge-base/besplatnaya-probnaya-trialnaya-licenziya/
Leave a Reply