Визуализация данных Staffcop в отчетах через PowerBI¶
Подготовка сервера Staffcop¶
По умолчанию удалённое подключение к базе на сервере Staffcop отключено. Чтобы его включить:
Создайте пользователя, который будет работать с PowerBI:
запустите консоль PostgreSQL:
sudo -u postgres psql -d staffcopв консоли создайте нового пользователя и выдайте ему права на работу с таблицами:
CREATE USER powerbi WITH PASSWORD 'password'; GRANT CONNECT ON DATABASE staffcop TO powerbi; GRANT USAGE ON SCHEMA public TO powerbi; GRANT SELECT ON ALL TABLES IN SCHEMA public TO powerbi;
В конфигурационном файле базы данных /etc/postgresql/15/main/pg_hba.conf добавьте строку:
host staffcop powerbi 00.00.00.000/32 md5IP-адрес и маска подсети указывают, что доступ разрешен только с указанного IP-адреса. Замените
00.00.00.000/32
на реальный IP.Если используете PostgreSQL 11, файл находится в /etc/postgresql/11/main/pg_hba.conf.
В файле /etc/postgresql/15/main/postgresql.conf укажите директиву:
'localhost, 00.00.00.000'
Замените 00.00.00.000 на IP-интерфейс, который будет прослушиваться.
Перезапустите PostgreSQL:
sudo systemctl stop postgresql && systemctl start postgresql
Готово! PostgreSQL прослушивает все доступные сетевые интерфейсы и ждёт подключения от PowerBI.
Настройка компьютера с установленным PowerBI¶
Чтобы получить данные в PowerBI:
Перейдите в раздел Получить данные → Другие → База данных PostgreSQL.
В открывшемся окне укажите IP-адрес сервера Staffcop и имя базы данных.
В открывшемся окне выберите таблицу, из которой хотите загрузить данные, и нажмите Загрузить.
После получения таблицы откройте Расширенный редактор и введите в нём SQL-запрос:
let Source = Value.NativeQuery(PostgreSQL.Database("192.168.0.56", "staffcop", [CreateNavigationProperties=false]), " SELECT agent_time.year AS year, agent_time.month AS month, agent_time.day AS day, agent_agent.id AS id, agent_agent.guid AS guid, agent_agent.computer_name AS computer_name, analytics_filter.category AS category, analytics_filter.name_ru AS NAME, agent_web.site AS site, agent_application.app_name AS app_name, agent_time.time_zone AS time_zone, agent_time.year AS year, agent_time.month AS month, agent_time.day AS day, agent_time.hour AS hour, agent_time.quarter AS quarter, analytics_filter.type AS type, Min(analytics_session_extended.local_time) AS local_time_min, Max(analytics_session_extended.local_time) AS local_time_max, Max(analytics_session_extended.time_finish) AS time_finish_max, Min(analytics_session_extended.come) AS come_min, Max(analytics_session_extended.leave) AS leave_max, Min(analytics_session_extended.accounting_start) AS accounting_start_min, Max(analytics_session_extended.accounting_end) AS accounting_end_max, Min(analytics_session_extended.start_work_time) AS start_work_time_min, Max(analytics_session_extended.end_work_time) AS end_work_time_max, Min(analytics_session_extended.start_break_time) AS start_break_time_min, Max(analytics_session_extended.end_break_time) AS end_break_time_max, Max(analytics_session_extended.work_time) AS work_time_max, Every(analytics_session_extended.selected) AS selected_every, Array_agg(analytics_session_extended.source_table) AS source_table_array_agg, Array_agg(analytics_session_extended.source_id) AS source_id_array_agg, Sum(COALESCE(analytics_session_extended.activity_time, 0)) AS activity_time_sum, Sum(COALESCE(analytics_session_extended.idle_time, 0)) AS idle_time_sum, Count(*) AS record_count FROM analytics_session_extended LEFT JOIN agent_agent ON analytics_session_extended.agent_id = agent_agent.id LEFT JOIN agent_application ON analytics_session_extended.application_id = agent_application.id LEFT JOIN agent_time ON analytics_session_extended.time_id = agent_time.id LEFT JOIN agent_web ON analytics_session_extended.web_data_id = agent_web.id LEFT JOIN analytics_filter_events ON analytics_filter_events.event_id = analytics_session_extended.id LEFT JOIN analytics_filter ON analytics_filter.id = analytics_filter_events.filter_id WHERE ( analytics_session_extended.local_time <='2023-06-14 23:59:59.999000+07:00' AND analytics_session_extended.local_time >='2023-06-14 00:00:00+07:00' ) GROUP BY agent_time.year, agent_time.month, agent_time.day, agent_agent.id, agent_agent.guid, agent_agent.computer_name, analytics_filter.category, analytics_filter.name_ru, agent_web.site, agent_application.app_name, agent_time.time_zone, agent_time.year, month, day, hour, quarter, analytics_filter.type ORDER BY agent_time.year, agent_time.month, agent_time.day, agent_agent.computer_name, start_work_time_min ASC " , null, [EnableFolding=false]) in Source
В открывшемся окне с предупреждением нажмите Изменить разрешение.
После этого в окне Собственный запрос к базе данных нажмите Выполнить.
Готово! Данные из Staffcop загружены в редактор PowerBI. Можно приступать к настройке визуализации!
В случае возникновения ошибок¶
Проверьте, что созданный пользователь базы данных имеет доступ к просмотру таблиц. Для этого зайдите в консоль ыPostgreSQL от имени созданного пользователя:
sudo -u postgres psql -d staffcop -U powerbi
и проверьте доступы пользователя к таблицам базы:
\dp
Если вывод команды будет пустым, выйдите из консоли PostgreSQL:
\q
и заново зайдите в неё, но уже от имени администратора:
sudo -u postgres psql -d staffcop
После этого выполните команды:
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO powerbi;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO powerbi;
Этого должно быть достаточно для устранения проблем с чтением данных из базы.