В странах СНГ принято создавать отчеты о деятельности компании в сводных таблицах Excel. Сказать, что это не юзабельно и не юзерфрендли для сложных отчётов – ничего не сказать.
Можно, конечно, попытаться как-то спасти положение различными графиками диаграмм, поэкспериментировать с VBA, попробовать с его помощью создать дашборд на стероидах, но надо быть готовым к тому, что графика будет начала нулевых, а сам отчёт скорее всего не будет интерактивным. Помимо этого, такие гибриды смотрятся некрасиво на мобильных устройствах и чаще всего оказываются совсем нечитабельными. Проблемой посерьёзнее является частота заполнения данных – раз в месяц, в лучшем случае. Это приводит к тому, что руководитель не может смотреть на положение бизнеса в режиме реального времени и предотвращать проблемы. Ему приходится мириться с положением дел в конце каждого месяца и решать всплывшие проблемы. Столько негатива, а ведь мы ещё даже не перешли на стадию автоматизации выгрузки данных в Excel, где и таятся все «черти» этого тихого омута.
«Шагая в ногу со временем, приходится менять не только обувь, но и походку»
Каким бы ни был привычным Excel, многие компании приходят к осознанию того, что для эффективного управления нужны интерактивные дашборды с наиболее частым обновлением данных в них, к тому же с обязательной поддержкой мобильных версий, без ухудшения качества и лишения интерактивности. Сегодня многие предприниматели действительно понимают, что нужно идти в ногу со временем, но нужно согласиться с тем, что довольно сложно найти специалиста, который сможет настроить всю среду автоматизации отчётов в компании.
В связи с этим мы решили поделиться нашим опытом, чтобы помочь наибольшему количеству начинающих специалистов справиться с задачей подключения 1С к Power BI.
Финансовые данные клиента, для которого мы строили аналитику, находятся в нескольких БД 1С 8.3. Сквозную интеграцию производили с использованием облачных сервисов Azure, там уже собраны все необходимые для этого инструменты, в том числе и разнообразные хранилища данных. Конечно, можно обойтись и без Azure (см. как), но исходный код, в том числе и DAX дашбордов в Power BI, мы показать не можем.
Автоматизация выполнения кода
В Azure существует ресурс, который позволяет выполнять код в облаке: Automation Account. Сам код должен находиться в специальных модулях, которые называются Runbook. При запуске модуля Runbook выделяется виртуальная машина, которая и будет выполнять исходный код.
Главный плюс: эта служба в связке с Log Analytics позволяет включить гибридную рабочую группу. С её помощью можно привязать локальное устройство к облачному сервису и выполнять код на этих устройствах. Необходимо подметить, что в нашем случае в гибридной группе должно находиться только одно устройство, т.к. нет возможности контролировать на каком гибридном воркере (устройстве клиента) будет выполняться код. Если есть несколько устройств с сервером 1С = создаём столько гибридных групп, сколько и устройств, при этом в каждую группу включаем только одно устройство. Чтобы привязать устройство к гибридной рабочей группе, необходимо установить на него Windows Monitoring Agent.
Ещё одно преимущество – запуск скриптов по расписанию. Можно поставить интервал хоть через каждый час, но достаточно и раза в сутки.
Подготовка данных
Для начала вам понадобится скрипт на языке 1С, который выгрузит все данные на локальную машину пользователя. В нашем случае запускать его мы будем через батник. Но для беспрепятственной выгрузки данных из базы 1C следует выключить «Безопасный режим»: в таком случае сервер 1С будет разрешать выполнять от имени пользователя наш скрипт.
Итого получаем текстовые файлы в Log-формате, в которых хранится интересующая информация для анализа.
Разберёмся в промежуточных и основных хранилищах данных. Azure SQL мы выбрали как удобный источник данных для Power BI. Для перекачки данных из локального устройства в Azure SQL необходимо промежуточное хранилище, к которому можно подключиться как к внешнему источнику. Им прекрасно может выступать Storage account, который обменивается данными по протоколам HTTP или HTTPS. В него удобно вливать файлы с локального устройства и удобно выгружать их в облачную БД, оперируя командой BULK.
Структуры базы данных
Так как выгрузка из 1С имеет одинаковый формат для любой компании (различия наблюдаются только с расчётом некоторых показателей в разных странах), можно заранее подготовить общую структуру таблиц. Но встречаются случаи, когда у клиентов в названии документов столько символов, что такую строку нельзя положить в БД, а это означает потерю целостности данных.
Создавать поля таблицы, используя ключевое слово MAX тоже не самый лучший выход, поэтому мы решаем эту проблему путём параллельного вычисления максимальной длины строк в столбцах каждой таблицы. Затем эта длина попадает в скрипт для автогенерации структуры SQL-таблицы. На больших массивах данных эта операция может сожрать до 30 минут выполнения, но зато сохраняется целостность данных. К слову, название самой таблицы и её столбцов тоже базируется на выгруженных файлах, что может спасти от кастомной структуры документов.
$encoding = New-Object System.Text.UTF8Encoding
$delimiter = "`t"
$reader = $CloudBlob.OpenRead()
$reader = New-Object System.IO.StreamReader($reader, $encoding)
$headers = $reader.ReadLine() -split $delimiter
$result = [ordered]@{}
foreach($header in $headers)
{
$result[$header] = 0
}
while(-not $reader.EndOfStream)
{
# Get Header in first line
$csv = $reader.ReadLine() | ConvertFrom-Csv -Header $headers -Delimiter $delimiter
# Get the largest row in a column
foreach($header in $headers)
{
$item = $csv | Select-Object -ExpandProperty $header
if($result[$header] -lt $item.Length)
{
# Put or Replace largest row info
$result[$header] = $item.Length
}
}
}Решение проблемы выше также позволят автогенерировать условия процедуры BULK. Плюс, в таком случае структура таблицы всегда будет совпадать с файлом, а значит, можно подкидывать обработчику вообще левые файлы в формате Log, и процедура BULK не будет сыпать ошибками о несоответствии структуры таблицы и файла.
В скрипт остаётся добавлять только статические переменные, которые хранят условия команды MERGE. Эта процедура, как многим известно, позволяет консолидировать данные не вписывая дубликаты строк по их полям-идентификаторам. А значит:
Можно выгружать данные только за какой-то короткий срок
Быть уверенным в отсутствии аномалий в отчёте
Для реализации команды BULK понадобятся промежуточные хранилища данных. Таковым выступает вышеупомянутая учётная запись автоматизации. В Azure SQL можно создать внешний источник данных, который будет иметь доступ к учётной записи хранения через SAS-токен. Токен также можно создавать автоматизированно, а для безопасности установить срок действия на пару часов.
Теперь у нас имеется подготовленный DWH для Power BI отчёта. Остаётся только развернуть новый workspace, развернуть в нём приложение-шаблон и вписать данные для входа в Azure SQL. К сожалению, сделать это через api невозможно. Ждём, пока такая возможность появится. Для наглядности описанного выше, приложим схему ежедневной выгрузки:
Как это выглядит в конечном результате?
Когда речь идет о дашбордах, многие не представляют себе как это выглядит и какой функционал можно реализовать. В связи с этим, мы решили добавить к технической инструкции, обзор одного из получившихся дашбордов. Специалисту важно понимать всю ценность дашбордов для будущих работодателей или клиентов.
Отчет "Товарооборот Дистрибьюторской компании"
Цель отчёта - показать руководителю эффективность работы отдела продаж, выявить проблемные категории товаров, узкие горлышки в процессе закупа и хранения товаров. И на основе информации вовремя принимать управленческие решения. Отчёт состоит из соответствующих дашбордов, между которыми можно переключаться.: Анализ продаж, Сравнение продаж, KPI, Анализ склада.
По данной ссылке, вы можете познакомиться с интерактивными дашбордами и проверить все в действии. Кликайте, фильтруйте, переключайтесь между страницами - функционал для этого и создан :)
Для примера разберём первый дашборд отчёта: Анализ продаж
Данный дашборд можно поделить на три основных блока:
Верхняя панель: навигация по дашбордам и фильтры
Левая верхняя: топ продаж
Остальное: детали по категориям
3. Выведены продажи по различным категориям.
Продажи по группам товаров и бренду KZT: по pie- chart можно «провалиться» к каждой группе и посмотреть данные о брендах, следует отметить что один бренд может относится к разным группам. Этот график удобен чтобы отслеживать именно продажи по группам и из каких брендов состоит определенная группа, нежели чем по брендам. Все графики имеют связь между собой. Нажав на одну из категорий, дашборд отфильтруется по ней и можно будет увидеть информацию по конкретной категории в разрезе городов и менеджеров.
Продажи по городам: представлены данные о продажах по городам, от большего к малому. Нажав на определённый город, вся информация отфильтруется по нему.
Продажи по менеджерам и клиентам в KZT: можно не только сравнить объём продаж менеджеров, но и «провалиться» и увидеть данные о клиентах каждого менеджера
Продажи в KZT: на данном графике представлены продажи по группам по месяцам: с января по июль.
Первый дашборд является аналитическим и показывает только общую картину. Позволяет быстро найти проблемные зоны и моменты, на которые стоит обратить внимание. Для более детальной информации и выявления причин пользователь может перейти на следующие страницы отчёта, где будет дана более детальная информация, в т.ч. в виде таблиц с подсвеченными аномалиями.
Отчёт обновляется ежедневно, поэтому каждый день пользователь будет видеть реальный ход дел и эффективность принятия решений.
