Визуализация данных 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/11/main/pg_hba.conf добавьте строку

host staffcop powerbi 0.0.0.0/0 password

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

listen_addresses = '*'

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;

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