Хранилище данных для BI системы. Онлайн ETL с помощью FoxyLink

Petro Bazeliuk —  Февраль 11, 2018 — Оставьте комментарий

Многим компаниям, в наших реалиях, необходимы перемены.  Нужно не просто поддерживать их на плаву, но и обеспечить им будущее. Тренд прочно сместился на системы бизнес-аналитики, анализа больших данных и добычи данных. Собственно, это техническая статья о том как живет несколько конфигураций «1С:Предприятие 8»,  датчики посещений, Data Warehouse, PowerBI и взаимодействуют между собой в режиме реального времени.
Перед тем, как начать приведу расшифровку некоторых терминов:

Хранилище данных (Data Warehouse) — предметно-ориентированная информационная база данных, специально разработанная и предназначенная для подготовки отчётов и бизнес-анализа с целью поддержки принятия решений в организации.

Microsoft Power BI  — замечательная и быстро развивающаяся платформа самостоятельного бизнес-анализа.

Сразу отвечу на вопрос:

Зачем необходим Data Warehouse для анализа данных?

Бывает, что данных не много до 5 GB тогда, например, можно использовать прямое подключение к «1С:Предприятие 8» через интерфейс OData (7 причин, почему интеграция стала приятной. Не упускайте ряд потрясающих возможностей). Статья предназначена для баз данных ~1 TB и больше, с такими размерами OData плохо справляется и онлайн интеграция под вопросом, да и еще несчетное количество проблем, некоторые из них описаны в этой статье: HighLoad++ для начинающих.

Прежде чем начать

Для успешного использования необходимо проработать процесс взаимодействия различных подразделений чтобы на выходе получить желаемый результат. Список шагов примерно выглядит так:

  1. Сформировать скетч дашборда или целевого показателя;
  2. Отдел аналитики формирует описание структуры таблиц понятный Data Warehouse;
  3. Под структуру таблиц формируется объект в «1С:Предприятие 8» в подсистеме FoxyLink;
  4. Выполняется первоначальное заполнение Data Warehouse с помощью CSV файла, который формирует объект обмена FoxyLink;
  5. Включаются подписки на события в «1С:Предприятие 8» в подсистеме FoxyLink и данные начинают обновляться в Data Warehouse в онлайн режиме при изменении или добавлении;
  6. Отдел аналитики подключает Power BI к необходимым данным в Data Warehouse. Из скетча дашборда или целевого показателя формирует необходимое отображение данных конечному пользователю.

Формируем структуру таблиц

В моем случае, как Data Warehouse, используется база данных на MS SQL. Аналитики формируют структуру таблиц в таком виде:

USE DataBank;
GO

CREATE TABLE DimManufacturers
(
-- Идентификатор производителя
Guid UNIQUEIDENTIFIER NOT NULL,

-- Наименование производителя
Name NVARCHAR(150) NOT NULL,

-- Дата обновления в формате ISO
UpdateDate DATETIME NOT NULL,
DataVersion ROWVERSION

CONSTRAINT PK_DimManufacturers PRIMARY KEY(Guid)
);

CREATE TABLE DimNomenclature
(
-- Идентификатор номенклатуры
Guid UNIQUEIDENTIFIER NOT NULL,

-- Наименование номенклатуры
Name NVARCHAR(100) NOT NULL,

-- Код номенклатуры
Sku NVARCHAR(11) NOT NULL,

--  Артикул номенклатуры
PartNumber NVARCHAR(35),

-- Идентификатор производителя
ManufacturersRef UNIQUEIDENTIFIER,

-- Дата обновления в формате ISO
UpdateDate DATETIME NOT NULL,
DataVersion ROWVERSION

CONSTRAINT PK_DimNomenclature PRIMARY KEY(Guid),
CONSTRAINT FK_DimNomenclature_ManufacturersRef FOREIGN KEY(ManufacturersRef) REFERENCES DimManufacturers(Guid)
);

Когда таблиц достаточно большое количество, используются системы формирования схемы базы данных и отображения связей между таблицами.

Формируем объекты в подсистеме FoxyLink

FoxyLink предоставляет унифицированную модель программирования для надежного управления задачами интеграции и запуска их на кластере серверов «1С:Предприятие 8». Обратите внимание на лицензию: GNU Affero General Public License, для снятия многих ограничений и получения дополнительных каналов необходимо получить платную лицензию.

Подсистема первоначально поставляется с двумя форматами в комплекте CSV и JSON и двумя каналами Self (Export) и Self (Files). Каналы это обработки которые доставляют данные к другим системам или подсистемам внутри конфигурации.

  • Канал Self (Export) — предназначен для создания переносимых настроек или переносимого интеграционного решения (например, описание настроек для QuickBooks).
  • Канал Self (Files) — предназначен для записи результата обработки события подсистемой FoxyLink. При добавлении канала в обмен будет предложено указать файловый путь куда сохранить результат.

Как сформировать первоначальный образ для заполнения DataBank с помощью FoxyLink смотрите видео:

Первоначальное заполнение Data Warehouse

Первоначальные образы будут помещаться с помощью BULK INSERT. Привожу примеры готовых запросов. Для справочника производителей:

USE [DataBank]
GO

IF (OBJECT_ID('tempdb..#csv_temp') IS NOT NULL) DROP TABLE #csv_temp;

CREATE TABLE #csv_temp
(
Guid uniqueidentifier NOT NULL,
Name NVARCHAR(150) NOT NULL
)

BULK INSERT #csv_temp
FROM 'C:\Users\PC\Documents\_Job\_DataBank\DimManufacturers.csv'
WITH
(
CODEPAGE = '1251'
--, FIRSTROW = 2
, FIELDTERMINATOR = ';'
, ROWTERMINATOR = '\n'
, KEEPNULLS
);
GO

INSERT INTO [dbo].[DimManufacturers]
(
[Guid]
, [Name]
, [UpdateDate]
)
SELECT
[Guid]
, [Name]
, GETDATE() AS UpdateDate
FROM #csv_temp csv;

DROP TABLE #csv_temp;

Для справочника номенклатуры:

USE [DataBank]
GO

IF (OBJECT_ID('tempdb..#csv_temp') IS NOT NULL) DROP TABLE #csv_temp;

CREATE TABLE #csv_temp
(
Guid uniqueidentifier NOT NULL,
Name NVARCHAR(100) NOT NULL,
Sku NVARCHAR(11) NOT NULL,
PartNumber NVARCHAR(35),
ManufacturersRef uniqueidentifier
)

BULK INSERT #csv_temp
FROM 'C:\Users\PC\Documents\_Job\_DataBank\DimNomeclature.csv'
WITH
(
CODEPAGE = '1251'
--, FIRSTROW = 2
, FIELDTERMINATOR = ';'
, ROWTERMINATOR = '\n'
, KEEPNULLS );
GO

INSERT INTO [dbo].[DimNomenclature]
(
[Guid]
, [Name]
, [Sku]
, [PartNumber]
, [ManufacturersRef]
, [UpdateDate]
)
SELECT
[Guid]
, [Name]
, [Sku]
, [PartNumber]
, [ManufacturersRef]
, GETDATE() AS UpdateDate
FROM #csv_temp csv;

DROP TABLE #csv_temp;

Онлайн обновление Data Warehouse

Для онлайн обновления данных в DataBank необходим канал, который не идет в первоначальной поставке: External data source channel. Как настраивается канал и онлайн обновление смотрите в видео:

Заключение. Дашборды Power BI

После завершения процессов ETL, аналитикам остается наполнить скетч данными из DataBank. Функционирование можно сделать в режиме почти реального времени, примерный вид готовых решений выглядит вот так:

Дашборд Power BI из DataBank (из конфигурации «1С:Предприятие 8»)

Дашборд Power BI из DataBank («1С:Предприятие 8»)

В приведенном дашборде используются данные из датчиков (посетителей), которые так же собирает подсистема FoxyLink. Дашборды доступны на смартфоне, через web и в локальной сети предприятия. Можно прикручивать сигнализацию о негативных событиях чтобы реагировать на них мгновенно.

Petro Bazeliuk

Записи

Опыт работы с «1С:Предприятие 8» — более 10 лет, за это время реализовано 30 успешных проектов по итеративным методологиям Scrum и Kanban. Оптимальные решения для высоконагруженных ИБ с онлайном от 400 человек. Занимаюсь продвижением в массы системы контроля версий — git и методики git-flow, TDD, BDD, а также проработкой паттерна минимальной модификации конфигурации и внесением изменений без обновления базы данных. Время от времени участвую в проекте xUnitFor1C.

Комментариев нет

Be the first to start the conversation!

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход /  Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход /  Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход /  Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход /  Изменить )

Connecting to %s