Визуализация данных Staffcop в отчетах через PowerBI

Подготовка сервера Staffcop

По умолчанию удалённое подключение к базе на сервере Staffcop отключено. Чтобы его включить:

1. Создайте пользователя, который будет работать с 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;
  

2. В конфигурационном файле базы данных /etc/postgresql/15/main/pg_hba.conf добавьте строку:

host staffcop powerbi 00.00.00.000/32 md5

IP-адрес и маска подсети указывают, что доступ разрешен только с указанного IP-адреса. Замените 00.00.00.000/32 на реальный IP.

Если используете PostgreSQL 11, файл находится в /etc/postgresql/11/main/pg_hba.conf.

3. В файле /etc/postgresql/15/main/postgresql.conf укажите директиву:

'localhost, 00.00.00.000'

Замените 00.00.00.000 на IP-интерфейс, который будет прослушиваться.

4. Перезапустите PostgreSQL:

sudo systemctl stop postgresql && systemctl start postgresql

Готово! PostgreSQL прослушивает все доступные сетевые интерфейсы и ждёт подключения от PowerBI.

Настройка компьютера с установленным PowerBI

Чтобы получить данные в PowerBI:

1. Перейдите в раздел Получить данные → Другие → База данных PostgreSQL.

2. В открывшемся окне укажите IP-адрес сервера Staffcop и имя базы данных.

3. В открывшемся окне выберите таблицу, из которой хотите загрузить данные, и нажмите Загрузить.

4. После получения таблицы откройте Расширенный редактор и введите в нём 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

4. В открывшемся окне с предупреждением нажмите Изменить разрешение.

5. После этого в окне Собственный запрос к базе данных нажмите Выполнить.

Готово! Данные из 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;

Этого должно быть достаточно для устранения проблем с чтением данных из базы.