Извлечение данных из 1С в QlikView или Qlik Sense с помощью коннектора ATK BIView: пошаговая инструкция

Получение данных из 1С для последующей аналитики в Qlik Sense или QlikView – работа относительно несложная, но трудоемкая. Чтобы снизить время на интеграцию данных из 1С и последующую ее поддержку, мы в текущей компании используем коннектор АТК 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. Подходит для 1С, начиная с версии 8. 1С 7 не поддерживается
  2. Подходит, если Ваша 1С развернута на SQL или PostgreSQL. Файловая 1С не поддерживается.
  3. Коннектор платный – можно купить постоянную версию, но совсем скоро обещают подписку. Учитывая, что за месяц подписки можно сгенерировать все нужные SQL-представления до следующего обновления или бэкапа 1С, подписка должна быть интересна. Пока цены на постоянную версию можно посмотреть на сайте.

Плюсы коннектора АТК BIView

  1. Снимает большую часть монотонной работы по поиску соответствий 1С и SQL, переименованиям и пр. Если у вас несколько баз 1С, из которых нужно забирать данные в хранилище или BI-систему, экономия времени выходит значительная.
  2. Шансов что-то испортить в 1С нет – у коннектора права только на чтение 1С, записывает вьюхи он в соседнюю БД, которую мы для него специально создаем.
  3. Работает из коробки с любой конфигурацией 1С (проверяли на трех разных), но при этом коннектор – это не черный ящик. В папке проекта (та, что по умолчанию в ProgramData) создаются скрипты, которые можно открыть и посмотреть. ScriptLev2.txt – это полный скрипт того, как создавались SQL-представления и какие трансформации с данными производили. Например, в куске кода ниже видны такие преобразования (пометил красным):
    • Технические наименования полей загружаются с теми названиями, что есть в пользовательском интерфейсе 1С
    • Делается смещение дат 2000
    • Подтягиваются справочники при помощи left join-ов
  4. Поддержка интеграции данных из 1С сильно упрощается. Если структура данных 1С изменилась, нужно открыть коннектор, запустить «расчет» и все. Все связи перестроятся самостоятельно и ваша BI-система или ХД будет работать с актуальными SQL-представлениями из 1С, а вам ничего не нужно будет вручную разбирать, сверять и поправлять.

Инструкция по работе с коннектором АТК BIView для 1С и Qlik

Здесь я дам инструкцию по работе с тестовой версией коннектора АТК BIView. Она бесплатна и дает возможность протестировать, что коннектор может работать с вашей 1С, а также позволяет создать мини-пилотный проект на основе данных 1С в QlikView или Qlik Sense (максимум 12 таблиц). У бесплатной версии коннектора есть два возможных сценария использования:

  1. Тестирование: Автоматически создать до 25 SQL-представлений для таблиц каждого из следующих типов: регистры, документы и справочники
    Результат: АТК BIView» генерирует SQL-представления в алфавитном порядке, поэтому получившиеся вьюхи для пилотного проекта использовать не получится. Вы проверите, что автоматическая генерация SQL-представлений на основе вашей 1С работает
  2. Мини-проект: Вручную в визуальном интерфейсе выбрать 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

  1. Сайт коннектора: http://biview.atkcg.ru/
  2. База знаний: http://biview.atkcg.ru/knowledge-base/
  3. Бесплатная триальная версия: http://biview.atkcg.ru/download/
  4. Короткое объясняющее видео о том, как работает коннектор: https://youtu.be/RHici4sy_Wo
  5. Видео пример: 1C + Qlik Sense, анализ продаж: https://youtu.be/67U4SeUi-jQ
  6. Видео пример: 1С + QlikView, анализ продаж: https://youtu.be/uy8uHWopObo
  7. Инструкция по работе с триальной версией АТК BIView: http://biview.atkcg.ru/knowledge-base/besplatnaya-probnaya-trialnaya-licenziya/
0
Оставьте комментарий! Напишите, что думаете по поводу статьи.x