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

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

На Аналитическом портале Modus BI имеется функционал, позволяющий создавать Модели данных (начиная с версии 3.6.36).

По умолчанию при установке Аналитического портала этот функционал не доступен пользователям. Чтобы появилась возможность использовать Модели данных, пользователю с ролью «Администратор» необходимо настроить соответствующий функционал.

Последовательность шагов:

1. Развернуть СУБД ClickHouse;
2. Развернуть Modus Агент ETL;
3. На Аналитическом портале добавить Источник данных (СУБД ClickHouse);
4. Настроить Аналитический портал для работы с функционалом Моделей данных;
5. Перезагрузить Аналитический портал.

Скрипт настройки функционала модели данных

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

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

	Agent_Port		= 0;			-- Число	- Порт Агента ETL

	DWH_Port			= 0;	-- Число	- Порт для подключения к СУБД, где размещено хранилище

	DWH_DatasourceID	= 0;	-- Число	
-- Идентификатор Источника данных на Аналитическом портале

Подробное описание шагов

Развернуть СУБД ClickHouse:

• Для чего: создание Моделей данных выполняется на основе СУБД ClickHouse. Данные из различных источников, таких как Excel-файлы, Google Sheets, 1С, СУБД (PostgreSQL, MS SQL, Vertica, ClickHouse) будут автоматически консолидироваться в рамках развернутой СУБД ClickHouse.
• Как это сделать: подробное описание доступно по ссылке.

Развернуть Агент ETL:

• Для чего: Агент ETL обеспечивает выгрузку данных из различных источников,таких как Excel-файлы, Google Sheets, 1С, СУБД (PostgreSQL, MS SQL, Vertica, ClickHouse) и загружает эти данные в развернутую ранее СУБД ClickHouse.
• Как это сделать: подробное описание доступно в разделе «Агент ETL».

На Аналитическом портале добавить Источник данных (СУБД ClickHouse):

• Для чего:Чтобы ранее развернутую СУБД ClickHouse можно было использовать в качестве источника данных для создания Наборов данных и построения визуализаций на Аналитическом портале.
• Как это сделать: подробное описание доступно в разделе «Источники данных».

Настроить Аналитический портал для работы с Моделями данных:

• Вариант 1: чтобы настроить Аналитический портал для работы с Моделями данных можно воспользоваться специально подготовленным SQL-запросом, заполнив в нем недостающие значения параметров (такие как адрес развернутой базы данных ClickHouse, адрес развернутого Агента ETL и т.п.).
• Вариант 2: выполнить аналогичные настройки в ручном режиме в соответствии с описанными ниже пунктами.

Настройка взаимодействия Аналитического портала с Агентом ETL

• Для чего: чтобы Аналитический портал мог использовать Агента ETL для получения данных из различных источников.
• Как это сделать: в базе данных, в которой содержатся метаданные Аналитического портала (PostgreSQL) необходимо в таблице 'public.setting' найти записи, указанные в шаблоне и заполнить в них значения в столбце "value".

Шаблон:

Пример заполнения:

Настройка использования СУБД ClickHouse в качестве хранилища данных для функциональности Моделей данных

• Для чего: чтобы Аналитический портал мог использовать ранее развернутую СУБД ClickHouse в качестве хранилища данных для построения Моделей
• Как это сделать: в базе данных, в которой содержатся метаданные Аналитического портала (PostgreSQL) необходимо в таблице 'public.etl_dwh' создать одну запись, указав в ней значения в соответствии с  шаблоном (см. ниже).

Шаблон:

• "id"– укажите числовое значение 1;
• "driver"– укажите числовое значение 50;
• "name"– укажите строковое значение Clickhouse;
• "host"– укажите адрес для подключения к ранее развернутой базе данных ClickHouse;
• "port"– укажите порт для подключения к ранее развернутой базе данных ClickHouse;
• "base"– укажите имя ранее развернутой базы данных ClickHouse;
• "login"– укажите имя учетной записи с правом создания/удаления таблиц в ранее развернутой базе данных ClickHouse;
• "password"– укажите пароль от учетной записи с правом создания/удаления таблиц в ранее развернутой базе данных ClickHouse;
• "datasource_id"– укажите числовое значение идентификатора ранее созданного Источника данных на Аналитическом портале.

Пример заполнения:

Настройка файлового хранилища для Аналитического портала

• Для чего: чтобы Аналитический портал смог сохранять файлы(Excel, Google Sheets)в директории, выбранной в качестве файлового хранилища, а затем использовать данные из этих файлов для переноса в базу данных ClickHouse.
• Как это сделать: в базе данных, в которой содержатся метаданные Аналитического портала (PostgreSQL) необходимо в таблице 'public.etl_lake_storages' создать одну запись, указав в ней значения в соответствии с  таблицей ниже.

Шаблон:

• "id"– укажите числовое значение 1;
• "name"– укажите строковое значение «Основное хранилище файлов»;
• "type_name"– укажите строковое значение «LocalFileSystem»;

• "settings"– это поле имеет тип данных – jsonb. Здесь укажите путь к каталогу в рамках локальной файловой системы, в который будут записываться загружаемые файлы). Шаблон значения: {"Path": "_____________"} :

  • Пример для Windows: {"Path": "F:\\User\\Хранилище_файлов_портала"}(примечание: для Windowsсимвол "\" нужно экранировать "\\");
  • Пример для Linux: {"Path": "/opt/modusbi/imports"}
• "create_user"– укажите числовое значение 1;
• "create_date"– укажите текущую дату и время;
• "deletion_mark"– укажите булево значение false.

Пример заполнения:

Включение функциональности Моделей данных для Аналитического портала

• Для чего: чтобы на Аналитическом портале отобразился инструментарий, позволяющий работать с Моделями данных
• Как это сделать: в базе данных, в которой содержатся метаданные Аналитического портала (PostgreSQL) необходимо в таблице 'public.setting' найти записи, указанные в таблице ниже и заполнить в них значения в столбце "value".

Шаблон:

Настройка функционала модели данных через Modus ETL

Это альтернативный путь настройке через СУБД Clickhouse.

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

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

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

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

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

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

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

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

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

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

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

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

Перезагрузка Аналитического портала

• Для чего: чтобы на Аналитическом портале применились все настройки, выполненные в части метаданных.
• Как это сделать: в настройках Аналитического портала нажать на кнопку «Перезагрузка» (важно убедиться, что выбрана версия frontend портала не ниже, чем 3.6.36).

• Ожидаемый результат: на Аналитическом портале в разделе «Настройки» должны появиться дополнительные вкладки, которые позволяют использовать функционал Моделей данных:
  • Подключения
  • Правила загрузки данных
  • Наборы данных