Модель данных

Модель данных нужна для более простого соединения таблиц друг с другом и выстраивания связей между объектами без программирования.

Работает с Clickhouse. Сервис позволяет загружать данные из Excel, Google Sheets, 1C (через адаптер), SQL. После загрузки данных в хранилище создается таблица, в которую помещают данные.

Реализована возможность установки связи между объектами. Соединение работает через JOIN, поэтому не рекомендуется соединять большие объемы данных.

Сценарий

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

DO $$
DECLARE
-- СПИСОК ПАРАМЕТРОВ:
	Agent_Host text;
	Agent_Port integer;
	Agent_SSL text;
	Agent_User text;
	Agent_Password text;
	DWH_Host text;
	DWH_Port integer;
	DWH_Base text;
	DWH_UserName text;
	DWH_Password text;
	DWH_DatasourceID integer;	
	FileStorage_LocalPath text;
	FileStorage_Settings text;
BEGIN 	



	-- НАСТРОЙКА ПАРАМЕТРОВ
	-- !ВАЖНО: Здесь необходимо заполнить значения параметров
	-- ▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲

	-- Агент ЕТЛ
	Agent_Host		= '';			-- Строка	- Адрес Агента ETL
	Agent_Port		= 0;			-- Число	- Порт Агента ETL
	Agent_SSL		= 'disabled';	-- Строка	- Использовать SSL (disabled/enabled) при подключении к Агенту
	Agent_User		= '';			-- Строка	- Имя пользователя для Агента ETL
	Agent_Password	= '';			-- Строка	- Пароль для Агента ETL


	-- Хранилище данных (ClickHouse)
	-- !ВАЖНО: Прежде чем указывать эти настройки, нужно на Аналитическом портале ранее развернутую БД ClickHouse зарегистрировать в качестве Источника данных (т.е. создать соответствующий Источник данных), чтобы id этого Источника указать здесь
	DWH_Host			= '';	-- Строка	- Адрес для подключения к Clickhouse
	DWH_Port			= 0;	-- Число	- Порт для подключения к Clickhouse
	DWH_Base			= '';	-- Строка	- Имя базы данных, используемой в качестве хранилища
	DWH_UserName		= '';	-- Строка	- Имя пользователя базы данных, используемой в качестве хранилища, с правом созданя/удаления таблиц
	DWH_Password		= '';	-- Строка	- Пароль пользователя базы данных, используемой в качестве хранилища
	DWH_DatasourceID	= 0;	-- Число	- Идентификатор Источника данных на Аналитическом портале (нужен для того, чтобы данные из Хранилища можно было использовать для создания Наборов данных на Аналитичесокм портале)

	
	-- Файловое хранилище для получения файлов Excel и Google Sheets
	FileStorage_LocalPath = ''; -- Строка	- Путь в локальной файловой системе к каталогу, в который будут записыватся загружаемые файлы
	-- 										Пример (Windows):
	-- 											F:\\User\\Хранилище_файлов_портала (для Windows символ "\" нужно экранировать "\\")
	-- 										Пример (Linux):
	-- 											/opt/modusbi/imports

	-- ▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼
	




	-- ВНЕСЕНИЕ НАСТРОЕК В ТАБЛИЦЫ МЕТАДАННЫХ АНАЛИТИЧЕСКОГО ПОРТАЛА

	-- ---------------------------------------------------------------------------------------------------------------------
	-- ## Подключение к Агенту
	INSERT INTO setting ("setting_id", "name", "value", "visible")
	VALUES(860, 'agent.host', Agent_Host, 1) ON CONFLICT (setting_id) DO UPDATE SET "name" = 'agent.host', "value" = Agent_Host;
	INSERT INTO setting ("setting_id", "name", "value", "visible")
	VALUES(861, 'agent.port', Agent_Port, 1) ON CONFLICT (setting_id) DO UPDATE SET "name" = 'agent.port', "value" = Agent_Port;
	INSERT INTO setting ("setting_id", "name", "value", "visible")
	VALUES(864, 'agent.ssl', Agent_SSL, 1) ON CONFLICT (setting_id) DO UPDATE SET "name" = 'agent.ssl', "value" = Agent_SSL;
	INSERT INTO setting ("setting_id", "name", "value", "visible")
	VALUES(862, 'agent.user', Agent_User, 1) ON CONFLICT (setting_id) DO UPDATE SET "name" = 'agent.user', "value" = Agent_User;
	INSERT INTO setting ("setting_id", "name", "value", "visible")
	VALUES(863, 'agent.password', Agent_Password, 1) ON CONFLICT (setting_id) DO UPDATE SET "name" = 'agent.password', "value" = Agent_Password;


	-- ---------------------------------------------------------------------------------------------------------------------
	-- ## Подключение к Хранилищу ClickHouse, используемому для загрузки данных для Модели данных
	INSERT INTO etl_dwh("id", "driver", "name", "host", "port", "base", "login", "password", "datasource_id")
		VALUES(1, 50, 'Clickhouse', DWH_Host, DWH_Port, DWH_Base, DWH_UserName, DWH_Password, DWH_DatasourceID)
			ON CONFLICT (id) DO UPDATE SET
				"driver" = 50,
				"host" = DWH_Host,
				"port" = DWH_Port,
				"base" = DWH_Base,
				"login" = DWH_UserName,
				"password" = DWH_Password,
				"datasource_id" = DWH_DatasourceID;


	-- ---------------------------------------------------------------------------------------------------------------------
	-- ## Добавление файлового хранилища, для размещения загружаемых файлов (Excel, Google Sheets):
	FileStorage_Settings = '{"Path": "' || FileStorage_LocalPath || '"}';
	INSERT INTO etl_lake_storages("id", "name", "type_name", "settings", "create_user", "create_date", "deletion_mark")
		VALUES(1, 'Основное хранилище файлов', 'LocalFileSystem', FileStorage_Settings::jsonb, 1, CURRENT_TIMESTAMP, false)
			ON CONFLICT (id) DO UPDATE SET "type_name" = 'LocalFileSystem', "settings" = FileStorage_Settings::jsonb;
	

	-- ---------------------------------------------------------------------------------------------------------------------
	-- ## Функционал Моделей данных включен
	INSERT INTO setting ("setting_id", "name", "value", "visible")
		VALUES(1000, 'features.etl_tools', 'enabled', 1)
			ON CONFLICT (setting_id) DO UPDATE SET "name" = 'features.etl_tools', "value" = 'enabled';
	-- ## Облачный функционал выключен
	INSERT INTO setting ("setting_id", "name", "value", "visible")
		VALUES(1001, 'features.cloud_mode', 'disabled', 1)
			ON CONFLICT (setting_id) DO UPDATE SET "name" = 'features.cloud_mode', "value" = 'disabled';




END $$;

Данный сценарий обновляет таблицу метаданных Аналитического портала, чтобы включить и настроить аналитическую модель данных.

В данном сценарии необходимо изменить параметры:

пароль:	`Agent_Password = ''; -- Строка - Пароль для Агента ETL`
порты:
Порт агента ETL	`Agent_Port = 0; -- Число - Порт Агента ETL` Где посмотреть порт агента? В доступах у вас должна быть информация об агенте, как представлено ниже на рисунке. Либо вы можете посмотреть его в настройках. В Modus ETL перейдите в "Главное", откройте "Информация/ Основные настройки". Далее нажмите на поле "Агент для выполнения сценария". В поле "Адрес" отобразиться информация о порте, как показано на рисунке ниже.
Порт в Clickhouse	`DWH_Port = 0; -- Число - Порт для подключения к СУБД, где размещено хранилище` Где посмотреть порт в Clickhouse? В Аналитическом портале перейдите в меню Администрирования, откройте раздел "Источники" и выберите источник "ClickHouse". В поле "Порт" отобразиться необходимое значение.
id источника Clickhouse. Это id источника, в котором будут создаваться новые таблицы и который будет работать как источник-считывания	`DWH_DatasourceID = 0; -- Число -- Идентификатор Источника данных на Аналитическом портале` Где посмотреть id? В Аналитическом портале перейдите в меню Администрирования, откройте раздел "Источники" и выберите источник "Clickhouse". В поле "ID" отображается необходимое значение.

Подключение базы

Необходимо подключить базу метаданных. Можно запустить скрипт в сторонних программах, в примере будем работать в Modus ETL. Настройки подключения базы методанных находится в файле modusbi.json.

Перейдите в Modus ETL, откройте раздел «Базы данных». Нажмите на кнопку «Создать» для создания базы данных.

Заполните необходимые поля, например:

Тип базы данных - PostgreSQL;
База - postgres;
Имя схемы - public;
Сервер - 192.168.20.40;
Порт - возьмите с поля "Порт" из подключения "dwh", который был настроен до этого;
Пользователь - postgres;
Пароль - установите ваш единый пароль.

Нажмите на кнопку «Записать и закрыть». Перейдите обратно в созданную базу и нажмите на кнопку «Проверить подключение». Ваше подключение должно быть доступно как на рисунке ниже.

Сценарий обработки данных

В Modus ETL перейдите в раздел «Главное/ Сценарии обработки данных».

Нажмите на кнопку «Создать», в отобразившейся вкладке заполните поля:

Наименование — введите наименование, например, «Включение модели»;
База данных — выберите ранее созданную базу Postgres.

Нажмите на кнопку «Записать».

Перейдите на вкладку «Шаги сценария». В шаблонах создайте произвольный код SQL.

Нажмите «Редактировать шаг». Тип можете оставить "EXEC". Скопируйте текст SQL сценария, который был описан ранее, и вставьте его.

Нажмите на кнопку «Записать и закрыть». Запустите шаг.

После выполнения сценария перейдите в раздел «Настройки портала» в меню Администрирования Аналитического портала и нажмите на кнопку «Перезагрузка».

Отобразиться страница «Сервис временно недоступен». Дождитесь обновления страницы, возможно необходимо будет подождать больше 10 секунд.

После перезагрузки сервера отобразиться новый элемент в меню Администрирования Аналитического портала — раздел «Подключения».

Если новый элемент не отображается, то попробуйте нажать <Ctrl>+F5 или сбросьте кеш.

Раздел «Подключения»

Раздел «Подключения» — это структура, которая связана с источником и получением данных. Например, это может быть подключение Excel-файла.

Перейдите в раздел «Подключения» и добавьте подключение нажатием на кнопку «Добавить подключение».

Выберите значение «Excel-файл» и нажмите на кнопку «Выбрать».

В отобразившейся странице с помощью специальной кнопки выберите Excel-файл с локального компьютера.

После загрузки файла отобразиться таблица. Заполните поле «Наименование».

Для сохранения таблицы нажмите на кнопку «Сохранить».

Правила загрузки данных

Перейдите в раздел «Правила загрузки данных».

Нажмите на кнопку «Добавить правило». В отобразившейся форме нажмите на ранее добавленное подключение (в данном случае «БДДС»).

В отобразившейся странице заполните поле «Наименование» и «Таблица-приемник». В настройках полей определите наименование и тип полей.

Нажмите на кнопку «Сохранить». Отобразиться уведомление об успешном добавлении правила.

В правилах загрузки данных при обновлении нажатием на кнопку «Обновить» все данные будут перезаписаны в таблицу.

При создании правила загрузки данных автоматически в разделе «Наборы данных» создается простой набор данных.

Перейдите в раздел «Наборы данных». В разделе отобразиться ранее созданный набор с загруженными данными.

Подключение 1С

Подключение через Google Sheet

Составной набор данных

Реализована возможность использования составного набора данных. Составной набор данных реализуется при помощи простых наборов данных.

Перейдите в раздел «Наборы данных» в меню Администрирования. Нажмите на кнопку «Составной набор данных» для добавления составного набора данных.

Выберите объект с левого столбца и перетащите в правый, где область построения модели.

Добавьте дополнительно еще несколько таблиц в область построения модели.

Все добавленные таблицы будут связаны с таблицей, которая была добавлена первой в область построения модели.

Нажмите на кнопку для настройки связи объектов между таблицами.

В отобразившейся форме нажмите на кнопку и выберите необходимый тип связи. Варианты типов связи:

Inner join - объединение данных, которые присутствуют в обеих таблицах;
Left join - выбираются все данные первой (левой) таблицы, даже если они не соответствуют записям во второй (правой) таблице;
Right join - выбираются все данные второй (правой) таблицы, даже если они не соответствуют записям в первой (левой) таблице;
Full join - объединения двух таблиц с сохранением всех записей, включая те, для которых нет соответствующих записей в другой таблице.

Далее настройте связь полей двух таблиц. При необходимости добавления строки нажмите на кнопку «Добавить связь».

Нажмите на кнопку «Сохранить». Форма «Связь объектов» закроется.

Настройте аналогично связь объектов между всеми оставшимися таблицами.

Перейдите на вкладку «Настройка полей».

При необходимости измените имя полей.

Чтобы не выводить определенные поля установите им значение «Не использовать» —

. При активации значения иконка отображется голубым цветом.

Введите наименование составного набора данных, нажмите на кнопку «Сохранить», далее на кнопку «Закрыть».

Добавилась новая модель данных в разделе «Наборы данных».

Данную модель возможно использовать в отчете. Для этого перейдите в отчет, добавьте новый компонент, перейдите в его настройки.

Добавьте компонент «Таблица», в структуре данных выберите набор данных «Пользователи» (который мы загрузили ранее через 1С), перетащите поле «Наименование» в полку. Это пример использования простого набора данных.

Для использования ранее созданной нами составного набора данных выберите его наименование (в данном случае «ПланФакт02») в структуре данных и перетащите значения таблицы необходимые полки. Данные отобразились в таблице.

Например, в полученной таблице у нас данные «СуммаФакт» загружены из 1С, а данные «СуммаПлан» из Excel.

Модель данных - Продукт Modus BI