Прежде чем перейти к статье, хочу вам представить, экономическую онлайн игру Brave Knights, в которой вы можете играть и зарабатывать. Регистируйтесь, играйте и зарабатывайте!
Добрый день, уважаемые читатели! Материал носит теоретический характер и адресован исключительно начинающим аналитикам, которые впервые столкнулись с BI-аналитикой.
Что традиционно понимается под этим понятием? Если говорить простым языком, то это комплексная система (как и, например, бюджетирование) по сбору, обработке и анализу данных, представляющая конечные результаты в виде графиков, диаграмм, таблиц.
Это требует слаженной работы сразу нескольких специалистов. Дата-инженер отвечает за хранилища и ETL/ELT-процессы, аналитик данных помогает в заполнении базы данных, аналитик BI разрабатывает управленческие панели, бизнес-аналитик упрощает коммуникации с заказчиками отчетов. Но такой вариант возможен, только если фирма готова оплачивать работу команды. В большинстве случаев небольшие компании для минимизации затрат делают ставку на одного человека, который зачастую вообще не обладает широким кругозором в области BI, а имеет лишь шапочное знакомство с платформой для отчетов.
В таком случае происходит следующее: сбор, обработка и анализ данных происходит силами единственного инструмента – самой BI-платформой. При этом данные предварительно никак не очищаются, не проходят компоновки. Забор информации идет из первичных источников без участия промежуточного хранилища. Результаты такого подхода можно легко лицезреть на тематических форумах. Если постараться обобщить все вопросы касательно BI-инструментов, то в топ-3 попадут, наверное, следующие: как загрузить в систему плохо структурированные данные, как по ним рассчитать требуемые метрики, что делать, если отчет работает очень медленно. Что удивительно, на этих форумах вы практически не найдете обсуждений ETL-инструментов, описания опыта применения хранилищ данных, лучших практик программирования и запросов SQL. Более того, я неоднократно сталкивался с тем, что опытные BI-аналитики не очень лестно отзывались о применении R/Python/Scala, мотивируя это тем, что все проблемы можно решить только силами BI-платформы. Вместе с тем всем понятно, что грамотный дата инжиниринг позволяет закрывать массу проблем при построении BI-отчетности.
Дальнейший разговор предлагаю построить в форме разбора упрощенных блок-схем. Я сознательно не буду называть конкретные программы, а лишь укажу их класс. Во-первых, это не имеет принципиального значения для раскрытия темы, а, во-вторых, упоминание инструментов сразу приводит к ненужным спорам в комментариях.
«Data – BI» Самый простой вариант. Именно с него начинается прототипирование управленческих панелей. В роли источника данных часто выступает отдельный (-ые) статичный файл (csv, txt, xlsx и т. д.).
Плюсы. Самый быстрый способ построения отчетности. Идеально подходит, для ситуационной аналитики или когда результат нужен был еще вчера. Не требует применения вспомогательных инструментов, следовательно, не нужно тратить ресурсы на их поддержание. Аналитик BI не обязан иметь компетенции в области дата инжиниринга или программирования.
Минусы. Далеко не изо всех источников можно забрать информацию напрямую (пример, прикладные решения на платформе 1С). Если массивы плохо структурированы, то это потребует много дополнительных шагов по их обработке. Качество данных никак не проверяется (проблема дубликатов, пустых строк, некорректного написания значений и т. д.). При большом количестве строк заметно замедляется работа самой BI-платформы, вплоть до полной невозможности перестраивать графики и диаграммы. Нет возможности составить расписание на обновление исходников.
«Data – DB – BI» Вариант похож на предыдущий за тем исключением, что первоначальный массив напрямую заливается в базу в неизмененным виде, а уже к ней идет подключение. База данных может быть как развернута локальна, запущена в контейнере, так и представлена облачным хранилищем.
Плюсы. Есть возможность агрегировать разрозненные, однотипные файлы. Нагрузку по хранению информации теперь несет хранилище. Есть возможность задействовать всю мощь языка запросов SQL (или его диалекта), чтобы отфильтровать или агрегировать сырые строки перед их передачей в BI-инструмент. Уменьшается размер файла с управленческими панелями.
Минусы. Нет контроля над первичными данными, поэтому в хранилище заливается большое количество ненужной информации. Качество загружаемых датасетов никак не контролируется. Добавление данных в базу осуществляется в ручном режиме. Аналитик должен на базовом уровне знать SQL.
«Data – ETL – DB – BI» Частичная автоматизация. В качестве ETL-инструмента может выступать как программный продукт с графическим интерфейсом, так и код написанный на R/Python/Scala и т. д. Все данные проходят предварительный предпроцессинг. Структура наполняемых таблиц прописывается заранее.
Плюсы. Возможность загружать только хорошо структурированную информацию, которая прошла предварительную верификацию. Экономия места в базе данных. Снижается количество доработок на BI-платформе.
Минусы. Аналитик должен уверенно владеть ETL-инструментом и языком запросов SQL. Процесс разработки и тестирования скриптов требует времени. Если источников информации много, то затрудняется синхронизация получения информации.
Для иллюстрации этого варианта я решил написать простейшие скрипты. В рамках «игрушечного» примера я использую SQLite. Это позволит прикрепить базу данных к публикации, чтобы каждый желающий мог попрактиковаться в написании скриптов (архив). Датасет для разбора это E-Commerce Data с сайта Kaggle.
# импорт библиотек
import pandas as pd
# опции отображения
pd.set_option('display.max_columns', 10)
pd.set_option('display.expand_frame_repr', False)
path_dataset = 'dataset/ecommerce_data.csv'
# Предварительная обработка датасета
def func_main(path_dataset: str):
# Считываем датасет
df = pd.read_csv(path_dataset, sep=',')
# Приводим названия столбцов датасета к нижнему регистру
list_col = list(map(str.lower, df.columns))
df.columns = list_col
# Избавляемся от времени и трансформируем строку-дату в правильный формат
df['invoicedate'] = df['invoicedate'].apply(lambda x: x.split(' ')[0])
df['invoicedate'] = pd.to_datetime(df['invoicedate'], format='%m/%d/%Y')
# Рассчитываем сумму покупки по каждому товару
df['amount'] = df['quantity'] * df['unitprice']
# Удаляем ненужные для дальнейшего анализа столбцы
df_result = df.drop(['invoiceno', 'quantity', 'unitprice', 'customerid'], axis=1)
# Задаем порядок вывода столбцов для визуального контроля результата
df_result = df_result[['invoicedate', 'country', 'stockcode', 'description', 'amount']]
return df_result
# Таблица Продажи
def func_sale():
tbl = func_main(path_dataset)
df_sale = tbl.groupby(['invoicedate', 'country', 'stockcode'])['amount'].sum().reset_index()
return df_sale
# Таблица Страны
def func_country():
tbl = func_main(path_dataset)
df_country = pd.DataFrame(sorted(pd.unique(tbl['country'])), columns=['country'])
return df_country
# Таблица Товары
def func_product():
tbl = func_main(path_dataset)
df_product = tbl[['stockcode','description']].\
drop_duplicates(subset=['stockcode'], keep='first').reset_index(drop=True)
return df_product
В коде сочетается Extract и Transform. Считываем датасет, парсим столбец с датами. Рассчитываем сумму покупки по каждой строке и удаляем ненужные для дальнейшего анализа колонки. Так как датафрейм записывается в базу данных не монолитом, а разбивается на таблицы, то готовим три вспомогательные функции.
# импорт библиотек
import pandas as pd
import sqlite3 as sq
from etl1 import func_country,func_product,func_sale
con = sq.connect('sale.db')
cur = con.cursor()
## Таблица Страны
# cur.executescript('''DROP TABLE IF EXISTS country;
# CREATE TABLE IF NOT EXISTS country (
# country_id INTEGER PRIMARY KEY AUTOINCREMENT,
# country TEXT NOT NULL UNIQUE);''')
# func_country().to_sql('country',con,index=False,if_exists='append')
## Таблица Товары
# cur.executescript('''DROP TABLE IF EXISTS product;
# CREATE TABLE IF NOT EXISTS product (
# product_id INTEGER PRIMARY KEY AUTOINCREMENT,
# stockcode TEXT NOT NULL UNIQUE,
# description TEXT);''')
# func_product().to_sql('product',con,index=False,if_exists='append')
## Таблица Продажи (основная)
# cur.executescript('''DROP TABLE IF EXISTS sale;
# CREATE TABLE IF NOT EXISTS sale (
# sale_id INTEGER PRIMARY KEY AUTOINCREMENT,
# invoicedate TEXT NOT NULL,
# country_id INTEGER NOT NULL,
# product_id INTEGER NOT NULL,
# amount REAL NOT NULL,
# FOREIGN KEY(country_id) REFERENCES country(country_id),
# FOREIGN KEY(product_id) REFERENCES product(product_id));''')
## Таблица Продажи (временная)
# cur.executescript('''DROP TABLE IF EXISTS sale_data_lake;
# CREATE TABLE IF NOT EXISTS sale_data_lake (
# sale_id INTEGER PRIMARY KEY AUTOINCREMENT,
# invoicedate TEXT NOT NULL,
# country TEXT NOT NULL,
# stockcode TEXT NOT NULL,
# amount REAL NOT NULL);''')
# func_sale().to_sql('sale_data_lake',con,index=False,if_exists='append')
## Перегружаем данные из вспомогательной таблицы (sale_data_lake) в основную (sale)
# cur.executescript('''INSERT INTO sale (invoicedate, country_id, product_id, amount)
# SELECT sdl.invoicedate, c.country_id, pr.product_id, sdl.amount
# FROM sale_data_lake as sdl LEFT JOIN country as c ON sdl.country = c.country
# LEFT JOIN product as pr ON sdl.stockcode = pr.stockcode
# ''')
## Очищаем вспомогательную таблицу
# cur.executescript('''DELETE FROM sale_data_lake''')
def select(sql):
return pd.read_sql(sql,con)
sql = '''select *
from (select s.invoicedate,
c.country,
pr.description,
round(s.amount,1) as amount
from sale as s left join country as c on s.country_id = c.country_id
left join product as pr on s.product_id = pr.product_id)'''
print(select(sql))
cur.close()
con.close()На следующем этапе (Load) мы создаем четыре таблицы. Две из них будут справочниками. Одна содержать сгруппированную информацию по продажам. Нам также потребуется вспомогательная таблица, в которую мы запишем строки с продажами до момента замены текстовых значений на числовые ид. На последнем шаге очистим ее от всех значений.
В заключении нам остается лишь выполнить тестовый запрос SQL, чтобы проверить корректность всех операций. Если все сделано правильно, запускаем BI-платформу.
Так как BI-инструмент не может из коробки напрямую подключиться к SQLite напишем простейший скрипт на Python.
import pandas as pd
import sqlite3 as sq
con = sq.connect('C:/Users/Pavel/PycharmProjects/test/sale.db')
cur = con.cursor()
def select(sql):
return pd.read_sql(sql,con)
sql = '''select *
from (select s.invoicedate,
c.country,
pr.description,
replace(round(s.amount,1),'.',',') as amount
from sale as s left join country as c on s.country_id = c.country_id
left join product as pr on s.product_id = pr.product_id)'''
tbl = select(sql)
print(tbl)После загрузки данных в систему и проверки корректности распознанных форматов можно приступать к непосредственному построению дашборда.
«Data – Workflow management platform + ETL – DB – BI» Полная автоматизация. Оркестратор скриптов берет на себя контроль за своевременным выполнением всех вспомогательных процессов в системе.
Плюсы. Возможность оптимально настроить время сбора данных из разрозненных источников. Можно мониторить ошибки и перезапускать упавшие задачи.
Минусы. Усложнение инфраструктуры. Рост требований к квалификации аналитика BI. Необходимо осваивать дополнительные инструменты или языки программирования.
«Data – Workflow management platform + ELT – Data Lake – Workflow management platform + ETL – DB – BI» Самый сложный вариант, где информация проходит двухступенчатый конвейер: сначала это неструктурированные данные (Data Lake), а затем уже хранилище (DB), где информация отсортирована и преобразована к требуемому виду.
Плюсы. Возможность разнести во времени сбор информации и ее обработку. Если на этапе проектирования таблиц учтены не все требования, возможно обращение за дополнительными данными в Data Lake.
Минусы. Аналогичны предыдущему варианту. Если выбранная платформа Data Lake – платная, как следствие рост расходов на аналитику компании.
Краткие выводы.
Построение BI-аналитики без даты инжиниринга возможно лишь на старте.
Если аналитик BI работает в единственном числе и система постоянно усложняется, то он обязан подменять собой сразу несколько специалистов.
Понимание базовых принципов построения хранилищ данных, уверенное владение SQL, программирование на каком-либо языке и, конечно, дизайнерские навыки вот далеко не полный перечень требований к сотруднику, которому делегируется проектировать управленческие панели.
На этом все. Всем здоровья, удачи и профессиональных успехов!
