Разработка системы KPI для оценки эффективности закупочной деятельности компании на базе ERP 1С и базы данных Microsoft SQL Server с последующей визуализацией в Power BI.
Цель проекта — создать прозрачную, управляемую и устойчивую систему аналитической отчетности для руководства по направлениям:
- Закупки
- Тендеры
- Склад и хранение
Проект включал анализ структуры базы данных, создание аналитических представлений (VIEW), построение модели данных в Power BI и разработку дашбордов с управленческими показателями.
Для демонстрации проекта записан видео-обзор дашбордов (в целях корпоративной безопасности все реальные данные намеренно замазаны).
ERP: 1С
База данных: Microsoft SQL Server
BI-инструмент: Power BI
Работа строилась по следующей логике:
- Анализ структуры БД
- Очистка и подготовка данных через SQL
- Создание отдельной схемы для BI
- Построение управляемой модели в Power BI
- Реализация KPI через DAX
Разработаны дашборды по следующим показателям:
- Точность плана закупок
- Соблюдение бюджета
- Среднее время обработки заявки
- Среднее время проведения тендера
- Среднее время заключения контракта
- Доля срочных закупок
- Количество активных поставщиков
- Качество поставщиков
- Доля поставок с браком
- Процент своевременных поставок
- Динамика закупочной активности
- и т.д.
База данных была унаследована от крупной компании. В основной схеме находилось около 14 000 таблиц, при этом реально использовалось примерно 2 000. Большинство таблиц были пустыми или техническими.
Это сильно усложняло поиск нужных источников данных.
Решение:
- через
sys.tablesбыла сформирована вспомогательная таблица с количеством строк по каждой таблице - таблицы сортировались по числу записей
- анализировались только таблицы, содержащие реальные данные
Таким образом я сузил область поиска и работал только с релевантными источниками.
При подключении к SQL Server Power BI отображал только первые 10 000 таблиц в алфавитном порядке. Часть нужных таблиц не отображалась даже через поиск.
Решение:
- создана отдельная схема
pbi - в ней создавались представления (VIEW) с очищенными и объединёнными данными
- Power BI подключался только к этой схеме
Это позволило полностью контролировать источники данных и избежать хаотичной структуры.
В ряде таблиц часть строк не содержала полезной информации.
Для оценки использовались:
COUNT(*)- расчет доли строк через оконные функции
В результате были исключены шумовые таблицы и оставлены только аналитически значимые данные.
Руководство требовало большое количество показателей, разделённых по направлениям (Закупки, Тендеры, Склад).
В большинстве таблиц было по 3–4 связи, что приводило к:
- конфликтам фильтрации
- циклическим зависимостям
- искажению агрегированных показателей
- нарушению динамики отчётов
Решение:
- минимизировано количество физических связей
- оставлены только ключевые отношения
- большая часть логики реализована через DAX
Связи в ряде случаев создавались логически через DAX (CALCULATE, FILTER, USERELATIONSHIP, итерационные функции).
Это позволило управлять контекстом фильтрации вручную и стабилизировать модель.
Из-за большого количества KPI возникала проблема читаемости отчетов.
Решение:
- разделение показателей по страницам
- подбор визуализации под бизнес-логику
- использование условного форматирования
- выделение ключевых отклонений
Основной акцент делался на управленческое восприятие, а не на декоративность.
В процессе анализа были выявлены ошибки учета.
Пример:
Склад оприходовал товар в килограммах, но стоимость была указана за тонну. Это увеличило итоговую стоимость поставки в 1000 раз.
Аномалия стала заметна через:
- выбросы на графиках
- несоразмерные значения KPI
- резкие отклонения от динамики
Таким образом BI стал инструментом контроля качества данных, а не только отчетности.
Руководство требовало отображать не только итоговый показатель, но и:
- дополнительные расшифровки
- таблицы-источники
- данные, из которых формируется KPI
Решение:
- добавлены детализированные страницы
- созданы вспомогательные (подсказочные) таблицы
- добавлены таблицы с расшифровкой источников
Это повысило прозрачность отчётности и упростило проверку показателей.
