Авторы: Анна Бобылева, внутренний аудитор ПАО «ФосАгро», член Ассоциации «Институт внутренних аудиторов»1

Павел Нагорнов, CIA2, заместитель директора по внутреннему аудиту ПАО «ФосАгро», член Ассоциации «Институт внутренних аудиторов»3

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

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

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

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

Особенности задачи

Инструмент

  • все необходимые для анализа данные содержатся в стандартных отчетах

  • объем данных небольшой

  • расчет несложный и разовый

Стандартные отчеты из информационных систем (ИС), обработка данных в Excel

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

  • есть необходимость проверить корректность формирования готового отчета

Выгрузка из баз данных (БД) с помощью SQL-запросов

  • требуется сложный расчет

  • данных много (как самих источников данных, так и внутри одного источника много строк/ столбцов)

  • выгрузки из ИС в «кривом» формате (xml, 1С ОСВ)

  • расчет регулярный, многократный

Анализ и визуализация данных в Power BI/ Python

 

 

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

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

С 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, сохраненные в pdf6). При этом искомые таблицы в этих файлах находились вперемешку с текстом, сами файлы были разного формата и разной длины (от 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 секунд.

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

Имеется доступ к созданным представлениям документов СЭД7 (т.е. виртуальные таблицы, содержащие полный набор полей из документов в понятном виде, а не разрозненный перечень «сырых» таблиц БД). Информация по блокам согласований содержится в формате 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) позволяет раздвинуть границы аудитов: анализировать весь массив данных, а не выборки, проводить непрерывные аудиты бизнес-процессов.


1 Ассоциация «Институт внутренних аудиторов» (Ассоциация «ИВА»), зарегистрированная в 2000 г., является профессиональным объединением более чем 4000 внутренних аудиторов, внутренних контролеров и работников других контрольных подразделений российских компаний и организаций. Подробности на сайте www.iia-ru.ru

2 CIA – международная сертификация Certified Internal Auditor («Дипломированный внутренний аудитор»).

3 Ассоциация «Институт внутренних аудиторов» (Ассоциация «ИВА»), зарегистрированная в 2000 г., является профессиональным объединением более чем 4000 внутренних аудиторов, внутренних контролеров и работников других контрольных подразделений российских компаний и организаций. Подробности на сайте www.iia-ru.ru

4 SQL (Structured Query Language, язык структурированных запросов) – декларативный язык программирования, при котором описывается ожидаемый результат, а не способ его получения.

5 Например, из ERP-систем, CRM

6 Описанные обработки возможны только с pdf-файлами, сохраненными из Word, Excel. С pdf-файлами в виде картинок указанные обработки не работают.

7 Система электронного документооборота.