Практические подходы к выбору средств автоматизации сбора и анализа данных

Статья предназначена для широкого круга читателей – и для тех, кто только определяется с выбором направлений своего профессионального развития, и для практикующих аналитиков – возможно, использованные в представленных ниже кейсах решения и идеи будут применимы для решения и ваших задач.

1. Как выбрать инструмент для сбора и анализа данных?

При анализе данных важно помнить, что нет универсального инструмента, а для каждой задачи следует использовать наиболее подходящий.

В нашей практике мы используем следующий алгоритм принятия решения, какой инструмент анализа данных использовать в каждом конкретном случае:
 

Особенности задачи	Инструмент
все необходимые для анализа данные содержатся в стандартных отчетах объем данных небольшой расчет несложный и разовый	Стандартные отчеты из информационных систем (ИС), обработка данных в Excel
необходимые поля, присутствующие в анализируемых документах, не выгружаются в стандартные отчеты есть необходимость проверить корректность формирования готового отчета	Выгрузка из баз данных (БД) с помощью SQL-запросов
требуется сложный расчет данных много (как самих источников данных, так и внутри одного источника много строк/ столбцов) выгрузки из ИС в «кривом» формате (xml, 1С ОСВ) расчет регулярный, многократный	Анализ и визуализация данных в Power BI/ Python

В рамках этой статьи мы не будем останавливаться на таком базовом инструменте как обработка стандартных отчетов в Excel. Остальные инструменты имеют особенности, рассмотрим их ниже.

При помощи SQL⁴-запроса зачастую выгрузить данные⁵ получается быстрее, чем в виде стандартного отчета. Например, причина может быть в том, что в стандартном отчете в силу его универсальности могут содержаться избыточные данные.

С Power BI (PBI) проще будет работать активным пользователям Excel, поскольку в нем:

• похожее представление данных в таблицах, которые можно пролистывать;

• визуализации на разных листах;

• обработка данных начинается с Power Query, который изначально являлся надстройкой Excel;

• большую часть расчетов в Power Query можно сделать «кнопками», без использования языка программирования.

Python, на наш взгляд, обладает рядом преимуществ в анализе и обработке данных по сравнению с PBI, поскольку:

• с помощью Python проще рисовать сложные визуализации с множеством настроек (PBI обладает ограниченным набором визуализаций и настроек к ним);

• производительность Python выше, чем у PBI: одни и те же расчеты Python выполняет быстрее и с меньшей загрузкой ресурсов ПК;

• существует большое количество бесплатных библиотек для Python под разные нужды анализа данных.

Кроме того, важное преимущество Power BI/ Python перед Excel состоит в воспроизводимости расчетов: все шаги записываются, и другому коллеге будет несложно повторить весь процесс расчета, получив тот же результат на выходе.

2. Рекомендации при самостоятельной выгрузке данных из БД с помощью SQL

При самостоятельной выгрузке данных из БД с помощью SQL рекомендуется соблюдение следующих правил:

1. Проверять актуальность выгружаемых данных

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

2. Проверять полноту выгружаемых данных

Для этого надо выгрузить из информационной системы готовый отчет или список документов за определенный период, сравнить с аналогичной SQL-выгрузкой по ID документа и удостовериться, что все документы попали в выгрузку.

3. Сохранять неизменный вид выгрузки SQL

Если отсутствует прямое подключение PBI/Python к БД, выгрузки SQL сохраняются в excel/csv/txt, и далее данные обрабатываются в Power Query/PBI/Python, то они должны сохраняться в неизменном виде. Не рекомендуется перед обработкой данных другими инструментами заходить в выгруженный файл и менять там формат данных, переименовывать столбцы, удалять или добавлять информацию. Если возникнет необходимость обновить расчет, шаги, проделанные в самом файле с выгрузкой, могут быть не зафиксированы и забыты, поэтому при обновлении расчета могут появиться ошибки явные (когда в PBI/Python будет выдаваться ошибка «не найден столбец») или скрытые (когда расчет будет произведен с некорректной логикой, и будет выведен результат, который может быть неверным).

4. Хранить текст запроса SQL для воспроизводимости процесса – в самом файле с выгрузкой или как комментарий в коде Python перед подключением к файлу.

3. Пример различия в скорости обработки данных

На примере одного из заданий покажем разницу в скорости обработки данных разными способами.

Задание: для последующего анализа требовалось свести на один лист Excel-таблицы из 155 pdf-файлов (изначально это были текстовые файлы Word, сохраненные в pdf⁶). При этом искомые таблицы в этих файлах находились вперемешку с текстом, сами файлы были разного формата и разной длины (от 2 до 46 листов).

Было рассмотрено 3 варианта решения этой задачи. Длительность ее исполнения тестировалась на 2-х видах ПК – менее мощном и более мощном. Результаты следующие:

	Fine Reader + Power BI	Power BI	Python
Менее мощный ПК (core i5 2017 г.)	2 часа на распознавание pdf-файлов в FineReader и сохранение их в Excel + 20 минут на подключение к папке c файлами Excel и их обработка в PBI	40 минут на подключение к папке с файлами pdf и их обработка в PBI (без распознавания и преобразования в Excel)	7 минут на подключение к папке с файлами pdf и их обработка в Python (модуль fitz из библиотеки PyMuPDF)
Более мощный ПК (core i7 2021 г.)	40 минут + 3 минуты	5 минут	1 минута

Приведенный пример показывает, что Python быстрее считывает и обрабатывает данные из pdf, чем Power BI, и быстрее, чем даже распознанные Fine Reader файлы Excel, собранные также в Power BI. Разница особенно видна при использовании менее мощного ПК, а также при большем количестве файлов.

4. Кейс с использованием Python

На следующем примере мы покажем, как при помощи Python выявить отклонения на всем массиве данных.

Была поставлена задача – проанализировать длительность согласований договоров и дополнительных соглашений в системе электронного документооборота; понять, есть ли превышения в регламентных сроках согласований, какие подразделения дольше всего выполняют процесс согласования.

Несколько этапов решения этой задачи:

1. Выгрузка и обработка данных по согласованиям договоров.

2. Выгрузка и обработка данных по пользователям – в какие даты осуществлялся переход пользователя с должности на должность в разных подразделениях (необходимо для того, чтобы в согласования подтянуть название подразделения, в котором на момент согласования работал пользователь).

3. Выгрузка и обработка данных по подразделениям – начиная от нижнего уровня (отдел) и заканчивая верхним уровнем (дирекция), чтобы сгруппировать данные по дирекциям (например, дирекция по строительству, дирекция по закупкам, бухгалтерия).

4. Подтягивание производственного календаря к данным о согласованиях для корректного расчета фактической длительности процесса (исключаются выходные/ праздники/ нерабочие часы).

5. Сбор данных в один массив – согласования с пользователями, подразделениями и производственным календарем для итоговых расчетов. На этом этапе количество обрабатываемых python-строк доходило до 194 миллионов, но Python обрабатывал их достаточно быстро – примерно за 30 секунд.

Далее более подробно расскажем про первый этап.

Имеется доступ к созданным представлениям документов СЭД⁷ (т.е. виртуальные таблицы, содержащие полный набор полей из документов в понятном виде, а не разрозненный перечень «сырых» таблиц БД). Информация по блокам согласований содержится в формате xml в каждой ячейке, относящейся к договору/ДС, т.е. в таком виде («ID» – ID договора, «Решения» – вся информация, содержащаяся в блоке согласований по данному договору):

Попытки прочитать эти данные стандартными инструментами xml в Power BI (Получить данные – xml) и Python (функция pd.read_xml) оказались неуспешными, потому что внутри каждой ячейки таблицы был «залит» отдельный xml-документ и потому что формат xml-документа внутри ячейки был в «кривом» виде.

Также имелись сложности с прочтением выгрузки PBI/Python из-за имеющихся символов табуляции и переноса строки в текстовых полях внутри согласований (комментарии). Их мы заменили непосредственно в SQL-выгрузке на пробелы:

Импортируем необходимые пакеты Python, загрузим выгруженный в формате txt файл в Python:

В нашей выгрузке 32 тыс. строк – не так и много для обычного анализа.

Разделим с помощью разделителя «><» столбец «Решения», но не на новые столбцы, а на строки:

Получилось 17 млн строк – что для дальнейших обработок в PBI многовато, а Python обрабатывает довольно быстро.

Затем создадим новый столбец «Вид данных» с признаком – назовем только те поля, которые нужны нам для дальнейшего анализа, т.е. в получившемся столбце «Решения» – те строки, которые начинаются с «StartDate» (дата и время, когда согласующий получил договор для согласования), «EndDate» (дата и время, когда согласующий согласовал договор либо отправил его на доработку инициатору) и т.д.:

Выберем только те строки, где поле «Вид данных» не пустое и дважды сбросим индекс, чтобы получить, во-первых, номер по порядку каждого ID договора (поле «index»), во-вторых – номер каждой строки в таблице (поле «level_0»:

В каждом этапе согласования может быть разное количество элементов даже среди отобранных строк – если согласующий не оставлял комментария, строка «Comment» будет отсутствовать в принципе, и в каком-то этапе согласования договора будет присутствовать 7 строк в поле «Вид данных», а в каком-то – 6, что затрудняет обработку данных. Пример:

№ п/п	Договор ID 969016 первый согласующий согласовал без комментария	Договор ID 969016 второй согласующий согласовал с комментарием
1	EndDate	Comment
2	FactUserFIO	EndDate
3	FactUserID	FactUserFIO
4	SolutionResult	FactUserID
5	StartDate	SolutionResult
6	UserPosition	StartDate
7		UserPosition

Каждый этап согласования внутри каждого ID договора начинается с «Comment» (если такой элемент присутствует на данном этапе) или с «EndDate», если комментария нет. Поэтому создадим столбец «Номер», который будет подтягивать уникальный номер строки из поля «Level_0», если вид данных является «EndDate» или «Comment»:

Нам нужно, чтобы у каждого этапа согласования стояло одинаковое значение в поле «Номер», поэтому пропишем условие (создадим отдельный столбец «Номер2» для наглядности) – если значение в поле «Номер» равно значению поля «Номер» из строки выше, к которому прибавили единицу – то берем значение из строки выше, иначе – берем значение из существующей строки поля «Номер»:

И заполняем поле «Номер2» вниз (т.е. если в ячейке пусто, она возьмет значение из ячейки, находящейся выше нее):

Извлечем элементы, находящиеся между символами «>» и «<» (тегами), в новый столбец «V2»:

«Развернем» данные – то, что у нас было в столбце «Вид данных», станет названиями столбцов, выделенные данные в промежуточный столбец «V2» заполнят ячейки в этих столбцах, при этом все будет сгруппировано, во-первых, по ID договора, во-вторых – по присвоенному номеру этапа согласования (поле «Номер2»):

С полученными данными можно работать дальше: приводить их к нужному формату, удалять ненужные столбцы, подтягивать наименования структурных подразделений по ID сотрудника, считать фактическое время согласования как разницу между окончанием согласования и его началом (за вычетом выходных/ праздников/ нерабочего времени), сравнивать его с регламентным и строить итоговые графики, например, такой:

Заключение

Мастерство аналитика, в т.ч. и внутреннего аудитора-аналитика, заключается не только во владении современными инструментами, но и в их уместном выборе. Часть задач можно по-прежнему решить стандартными средствами Exсel и «не стрелять из пушки по воробьям». Расширение набора инструментов (выгрузки из баз данных при помощи SQL, использование Python) позволяет раздвинуть границы аудитов: анализировать весь массив данных, а не выборки, проводить непрерывные аудиты бизнес-процессов.

---