Многим компаниям, в наших реалиях, необходимы перемены. Нужно не просто поддерживать их на плаву, но и обеспечить им будущее. Тренд прочно сместился на системы бизнес-аналитики, анализа больших данных и добычи данных. Собственно, это техническая статья о том как живет несколько конфигураций «1С:Предприятие 8», датчики посещений, Data Warehouse, PowerBI и взаимодействуют между собой в режиме реального времени.
Перед тем, как начать приведу расшифровку некоторых терминов:
Хранилище данных (Data Warehouse) — предметно-ориентированная информационная база данных, специально разработанная и предназначенная для подготовки отчётов и бизнес-анализа с целью поддержки принятия решений в организации.
Microsoft Power BI — замечательная и быстро развивающаяся платформа самостоятельного бизнес-анализа.
Сразу отвечу на вопрос:
Зачем необходим Data Warehouse для анализа данных?
Бывает, что данных не много до 5 GB тогда, например, можно использовать прямое подключение к «1С:Предприятие 8» через интерфейс OData (7 причин, почему интеграция стала приятной. Не упускайте ряд потрясающих возможностей). Статья предназначена для баз данных ~1 TB и больше, с такими размерами OData плохо справляется и онлайн интеграция под вопросом, да и еще несчетное количество проблем, некоторые из них описаны в этой статье: HighLoad++ для начинающих.
Прежде чем начать
Для успешного использования необходимо проработать процесс взаимодействия различных подразделений чтобы на выходе получить желаемый результат. Список шагов примерно выглядит так:
- Сформировать скетч дашборда или целевого показателя;
- Отдел аналитики формирует описание структуры таблиц понятный Data Warehouse;
- Под структуру таблиц формируется объект в «1С:Предприятие 8» в подсистеме FoxyLink;
- Выполняется первоначальное заполнение Data Warehouse с помощью CSV файла, который формирует объект обмена FoxyLink;
- Включаются подписки на события в «1С:Предприятие 8» в подсистеме FoxyLink и данные начинают обновляться в Data Warehouse в онлайн режиме при изменении или добавлении;
- Отдел аналитики подключает Power BI к необходимым данным в Data Warehouse. Из скетча дашборда или целевого показателя формирует необходимое отображение данных конечному пользователю.
Формируем структуру таблиц
В моем случае, как Data Warehouse, используется база данных на MS SQL. Аналитики формируют структуру таблиц в таком виде:
[code lang=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)
);
[/code]
Когда таблиц достаточно большое количество, используются системы формирования схемы базы данных и отображения связей между таблицами.
Формируем объекты в подсистеме 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. Привожу примеры готовых запросов. Для справочника производителей:
[code lang=sql]
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;
[/code]
Для справочника номенклатуры:
[code lang=sql]
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;
[/code]
Онлайн обновление Data Warehouse
Для онлайн обновления данных в DataBank необходим канал, который не идет в первоначальной поставке: External data source channel. Как настраивается канал и онлайн обновление смотрите в видео:
Заключение. Дашборды Power BI
После завершения процессов ETL, аналитикам остается наполнить скетч данными из DataBank. Функционирование можно сделать в режиме почти реального времени, примерный вид готовых решений выглядит вот так:
В приведенном дашборде используются данные из датчиков (посетителей), которые так же собирает подсистема FoxyLink. Дашборды доступны на смартфоне, через web и в локальной сети предприятия. Можно прикручивать сигнализацию о негативных событиях чтобы реагировать на них мгновенно.