Сквозная аналитика в HR

Привет! Меня зовут Григорий Митраков, я BI-аналитик в рекламном агентстве.
В статье расскажу вам как создать приложение в Power BI по пожеланиям заказчиков.

Предыстория

После того, как я устроился работать в агентство ко мне обратилась руководитель HR отдела для разработки небольшого автоматизированного отчета в BI. До этого основные показатели (текучесть, вовлеченность и др.) рассчитывались в табличках Excel полу ручным способом.

Так выглядели отчеты в Excel файлах:

Причину автоматизации мне объяснили тем, что около 4-х часов в неделю сотрудник HR отдела тратил на расчет показателей в таблицах Excel. (Что хорошо – видно, что какими-то графиками и диаграммами уже пользовались).

Сбор требований к отчету

После первой встречи с HR отделом в лице руководителя, специалиста, занимавшегося сбором данных и оформлением отчетов в таблицах Excel, выяснилось, что источники с необходимыми данными находятся в регулярных выгрузках из 1С Предприятие, Google Sheets, Битрикс24, PostgreSQL, iSpring. По визуальному оформлению как таковых «жестких» требований не было, была лишь просьба, учесть наглядность представляемых данных и «желательно как в таблицах Excel».

Работа над дашбордом

Задача показалась мне интересной ввиду достаточно большого количества разнообразных источников данных и свободы в выборе визуализации данных.

Для прозрачности работы, контроля текущего статуса над задачей и внесение своевременных правок в оформление и расчет метрик решили собираться раз в неделю по четвергам (этот день назвали «День HR»).

Для всех источников данных решил использовать БД PostgreSQL (была развернута в компании). Для разработки ETL-процессов — Apache Airflow.

Ниже представлена схема ETL процесса:

Данные раз в сутки (по расписанию) загружаю:

• с помощью API REST с опросников (google sheets),

• с помощью API REST с платформы для обучения сотрудников iSpring,

• с Битрикс24 веб-скрейпингом (у Битрикс24 есть за дополнительную плату расширение BI-Аналитика, но в компании бесплатная версия, поэтому получение данных организовано таким образом),

• с сетевых папок, в которых хранятся регулярные выгрузки в формате .xlsx из 1С.  

Пример DAG (таблица с курсами iSpring):

import json 
import time 
import datetime 
import requests 
import pandas as pd 
from airflow.decorators import dag, task
from airflow.models import Variable 
from sqlalchemy import create_engine 
from airflow.providers.telegram.hooks.telegram import TelegramHook 


# Параметры для аутентификации и входа

user = 'grigoriy'
host = '101.100.9.43'
db = 'internal_data'
pwd = Variable.get('planning_datas_password') 

postgresql_url = f'postgresql+psycopg2://{user}:{pwd}@{host}/{db}' 
engine = create_engine(postgresql_url) 

# запрос к БД для получения id различных курсов

query_db = '''SELECT t.module_id, t.content_item_id, t.course_id 
            FROM ispring_course_modules_table AS t 
            GROUP BY t.module_id, t.content_item_id, t.course_id''' 

URL_TOKEN = 'https://api-learn.ispringlearn.ru/api/v3/token' 
URL_CONTENT = 'https://api-learn.ispringlearn.ru/courses/modules' 

HEADER = { 
        'Content-Type': 'application/x-www-form-urlencoded', 
        'Accept': 'application/json'
        } 

HEADERS_R = { 
            'X-Target-Locale': 'en-US', 
            'Accept': 'application/json'
            } 

CLIENT_SECRET = Variable.get('ispring_api_client_secret') 

DATA_URLENCODE = { 
                'client_id': 'xxbbaaxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx', 
                'client_secret': CLIENT_SECRET, 
                'grant_type': 'client_credentials'
                } 

default_args = {'owner': 'xxxxxxx' } 

TELEGRAM_TOKEN = Variable.get('tg_bot_token') 
CHAT_ID = '0000000000' 

# Необходимые функции: 

def error_to_telegram(token: str, chat_id: str, e, name_dag: str): 
    '''
    Функция отправляет сообщения об ошибках в чат телеграмм 
    :param: token: "xxx:xxx" API токен для telegram telegram_token
    :param: chat_id куда отправлять сообщение
    :param: e - ошибка 
    :param: name_dag - название DAG
    ''' 
    telegram_conn_id = 'telegram_default' 
    
    telegram_hook = TelegramHook(telegram_conn_id, 
                                token, 
                                chat_id) 
    
    t = datetime.datetime.now() 
    
    date_and_time = str(t.date()) + ' ' + str(t.time()) 
    
    message = f'