В странах СНГ принято создавать отчеты о деятельности компании в сводных таблицах Excel. Сказать, что это не юзабельно и не юзерфрендли для сложных отчётов – ничего не сказать.
Для наглядности всего ужаса в Excel
Можно, конечно, попытаться как-то спасти положение различными графиками диаграмм, поэкспериментировать с VBA, попробовать с его помощью создать дашборд на стероидах, но надо быть готовым к тому, что графика будет начала нулевых, а сам отчёт скорее всего не будет интерактивным. Помимо этого, такие гибриды смотрятся некрасиво на мобильных устройствах и чаще всего оказываются совсем нечитабельными. Проблемой посерьёзнее является частота заполнения данных – раз в месяц, в лучшем случае. Это приводит к тому, что руководитель не может смотреть на положение бизнеса в режиме реального времени и предотвращать проблемы. Ему приходится мириться с положением дел в конце каждого месяца и решать всплывшие проблемы. Столько негатива, а ведь мы ещё даже не перешли на стадию автоматизации выгрузки данных в Excel, где и таятся все «черти» этого тихого омута.
В связи с этим мы решили поделиться нашим опытом, чтобы помочь наибольшему количеству начинающих специалистов справиться с задачей подключения 1С к Power BI.
Финансовые данные клиента, для которого мы строили аналитику, находятся в нескольких БД 1С 8.3. Сквозную интеграцию производили с использованием облачных сервисов Azure, там уже собраны все необходимые для этого инструменты, в том числе и разнообразные хранилища данных. Конечно, можно обойтись и без Azure (см. как), но исходный код, в том числе и DAX дашбордов в Power BI, мы показать не можем.
Главный плюс: эта служба в связке с Log Analytics позволяет включить гибридную рабочую группу. С её помощью можно привязать локальное устройство к облачному сервису и выполнять код на этих устройствах. Необходимо подметить, что в нашем случае в гибридной группе должно находиться только одно устройство, т.к. нет возможности контролировать на каком гибридном воркере (устройстве клиента) будет выполняться код. Если есть несколько устройств с сервером 1С = создаём столько гибридных групп, сколько и устройств, при этом в каждую группу включаем только одно устройство. Чтобы привязать устройство к гибридной рабочей группе, необходимо установить на него Windows Monitoring Agent.
Схема работы компьютера в гибридной группе
Ещё одно преимущество – запуск скриптов по расписанию. Можно поставить интервал хоть через каждый час, но достаточно и раза в сутки.
Расписание запусков скриптов
Итого получаем текстовые файлы в Log-формате, в которых хранится интересующая информация для анализа.
Структура выгрузки из 1с
Разберёмся в промежуточных и основных хранилищах данных. Azure SQL мы выбрали как удобный источник данных для Power BI. Для перекачки данных из локального устройства в Azure SQL необходимо промежуточное хранилище, к которому можно подключиться как к внешнему источнику. Им прекрасно может выступать Storage account, который обменивается данными по протоколам HTTP или HTTPS. В него удобно вливать файлы с локального устройства и удобно выгружать их в облачную БД, оперируя командой BULK.
Выгрузка из 1с в Storage account
Создавать поля таблицы, используя ключевое слово 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. Эта процедура, как многим известно, позволяет консолидировать данные не вписывая дубликаты строк по их полям-идентификаторам. А значит:
Выгрузка из 1с в Azure SQL
Теперь у нас имеется подготовленный DWH для Power BI отчёта. Остаётся только развернуть новый workspace, развернуть в нём приложение-шаблон и вписать данные для входа в Azure SQL. К сожалению, сделать это через api невозможно. Ждём, пока такая возможность появится. Для наглядности описанного выше, приложим схему ежедневной выгрузки:
Схема ежедневной выгрузки данных
Верхняя панель
По данной ссылке, вы можете познакомиться с интерактивными дашбордами и проверить все в действии. Кликайте, фильтруйте, переключайтесь между страницами - функционал для этого и создан
Для примера разберём первый дашборд отчёта: Анализ продаж
Данный дашборд можно поделить на три основных блока:
Дашборд "Анализ продаж"
3. Выведены продажи по различным категориям.
Первый дашборд является аналитическим и показывает только общую картину. Позволяет быстро найти проблемные зоны и моменты, на которые стоит обратить внимание. Для более детальной информации и выявления причин пользователь может перейти на следующие страницы отчёта, где будет дана более детальная информация, в т.ч. в виде таблиц с подсвеченными аномалиями.
Отчёт обновляется ежедневно, поэтому каждый день пользователь будет видеть реальный ход дел и эффективность принятия решений.
habr.com
![Для наглядности всего ужаса в Excel Для наглядности всего ужаса в Excel](https://habrastorage.org/r/w1560/getpro/habr/upload_files/70b/977/13d/70b97713d56954712f3ab65f142bbf12.png)
Можно, конечно, попытаться как-то спасти положение различными графиками диаграмм, поэкспериментировать с VBA, попробовать с его помощью создать дашборд на стероидах, но надо быть готовым к тому, что графика будет начала нулевых, а сам отчёт скорее всего не будет интерактивным. Помимо этого, такие гибриды смотрятся некрасиво на мобильных устройствах и чаще всего оказываются совсем нечитабельными. Проблемой посерьёзнее является частота заполнения данных – раз в месяц, в лучшем случае. Это приводит к тому, что руководитель не может смотреть на положение бизнеса в режиме реального времени и предотвращать проблемы. Ему приходится мириться с положением дел в конце каждого месяца и решать всплывшие проблемы. Столько негатива, а ведь мы ещё даже не перешли на стадию автоматизации выгрузки данных в Excel, где и таятся все «черти» этого тихого омута.
![38bc967261488c86a240582076a209aa.jpg](https://habrastorage.org/r/w1560/getpro/habr/upload_files/38b/c96/726/38bc967261488c86a240582076a209aa.jpg)
Каким бы ни был привычным Excel, многие компании приходят к осознанию того, что для эффективного управления нужны интерактивные дашборды с наиболее частым обновлением данных в них, к тому же с обязательной поддержкой мобильных версий, без ухудшения качества и лишения интерактивности. Сегодня многие предприниматели действительно понимают, что нужно идти в ногу со временем, но нужно согласиться с тем, что довольно сложно найти специалиста, который сможет настроить всю среду автоматизации отчётов в компании.«Шагая в ногу со временем, приходится менять не только обувь, но и походку»
В связи с этим мы решили поделиться нашим опытом, чтобы помочь наибольшему количеству начинающих специалистов справиться с задачей подключения 1С к Power BI.
Финансовые данные клиента, для которого мы строили аналитику, находятся в нескольких БД 1С 8.3. Сквозную интеграцию производили с использованием облачных сервисов Azure, там уже собраны все необходимые для этого инструменты, в том числе и разнообразные хранилища данных. Конечно, можно обойтись и без Azure (см. как), но исходный код, в том числе и DAX дашбордов в Power BI, мы показать не можем.
Автоматизация выполнения кода
В Azure существует ресурс, который позволяет выполнять код в облаке: Automation Account. Сам код должен находиться в специальных модулях, которые называются Runbook. При запуске модуля Runbook выделяется виртуальная машина, которая и будет выполнять исходный код.Главный плюс: эта служба в связке с Log Analytics позволяет включить гибридную рабочую группу. С её помощью можно привязать локальное устройство к облачному сервису и выполнять код на этих устройствах. Необходимо подметить, что в нашем случае в гибридной группе должно находиться только одно устройство, т.к. нет возможности контролировать на каком гибридном воркере (устройстве клиента) будет выполняться код. Если есть несколько устройств с сервером 1С = создаём столько гибридных групп, сколько и устройств, при этом в каждую группу включаем только одно устройство. Чтобы привязать устройство к гибридной рабочей группе, необходимо установить на него Windows Monitoring Agent.
![Схема работы компьютера в гибридной группе Схема работы компьютера в гибридной группе](https://habrastorage.org/r/w1560/getpro/habr/upload_files/e46/e16/cdc/e46e16cdced4f69a7f2e76ba507d5ab0.jpg)
Ещё одно преимущество – запуск скриптов по расписанию. Можно поставить интервал хоть через каждый час, но достаточно и раза в сутки.
![Расписание запусков скриптов Расписание запусков скриптов](https://habrastorage.org/r/w1560/getpro/habr/upload_files/6be/291/824/6be2918241d8bc6ab9473a1b6c85257f.png)
Подготовка данных
Для начала вам понадобится скрипт на языке 1С, который выгрузит все данные на локальную машину пользователя. В нашем случае запускать его мы будем через батник. Но для беспрепятственной выгрузки данных из базы 1C следует выключить «Безопасный режим»: в таком случае сервер 1С будет разрешать выполнять от имени пользователя наш скрипт.Итого получаем текстовые файлы в Log-формате, в которых хранится интересующая информация для анализа.
![Структура выгрузки из 1с Структура выгрузки из 1с](https://habrastorage.org/r/w1560/getpro/habr/upload_files/8b3/0ed/e30/8b30ede30d4fd9bf35b96ff791a11946.png)
Разберёмся в промежуточных и основных хранилищах данных. Azure SQL мы выбрали как удобный источник данных для Power BI. Для перекачки данных из локального устройства в Azure SQL необходимо промежуточное хранилище, к которому можно подключиться как к внешнему источнику. Им прекрасно может выступать Storage account, который обменивается данными по протоколам HTTP или HTTPS. В него удобно вливать файлы с локального устройства и удобно выгружать их в облачную БД, оперируя командой BULK.
![Выгрузка из 1с в Storage account Выгрузка из 1с в Storage account](https://habrastorage.org/r/w1560/getpro/habr/upload_files/774/e26/6e8/774e266e81a0cef2398603a1212a4678.png)
Структуры базы данных
Так как выгрузка из 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. Эта процедура, как многим известно, позволяет консолидировать данные не вписывая дубликаты строк по их полям-идентификаторам. А значит:
- Можно выгружать данные только за какой-то короткий срок
- Быть уверенным в отсутствии аномалий в отчёте
![Выгрузка из 1с в Azure SQL Выгрузка из 1с в Azure SQL](https://habrastorage.org/r/w1560/getpro/habr/upload_files/d37/492/a2d/d37492a2d11fb837d643386b8b836fec.png)
Теперь у нас имеется подготовленный DWH для Power BI отчёта. Остаётся только развернуть новый workspace, развернуть в нём приложение-шаблон и вписать данные для входа в Azure SQL. К сожалению, сделать это через api невозможно. Ждём, пока такая возможность появится. Для наглядности описанного выше, приложим схему ежедневной выгрузки:
![Схема ежедневной выгрузки данных Схема ежедневной выгрузки данных](https://habrastorage.org/r/w1560/getpro/habr/upload_files/813/717/95f/81371795fbf69854b439788a6725ea37.jpg)
Как это выглядит в конечном результате?
Когда речь идет о дашбордах, многие не представляют себе как это выглядит и какой функционал можно реализовать. В связи с этим, мы решили добавить к технической инструкции, обзор одного из получившихся дашбордов. Специалисту важно понимать всю ценность дашбордов для будущих работодателей или клиентов.Отчет "Товарооборот Дистрибьюторской компании"
Цель отчёта - показать руководителю эффективность работы отдела продаж, выявить проблемные категории товаров, узкие горлышки в процессе закупа и хранения товаров. И на основе информации вовремя принимать управленческие решения. Отчёт состоит из соответствующих дашбордов, между которыми можно переключаться.: Анализ продаж, Сравнение продаж, KPI, Анализ склада.![Верхняя панель Верхняя панель](https://habrastorage.org/r/w1560/getpro/habr/upload_files/584/3cc/e88/5843cce889d67b87af4dc64b82df3848.png)
По данной ссылке, вы можете познакомиться с интерактивными дашбордами и проверить все в действии. Кликайте, фильтруйте, переключайтесь между страницами - функционал для этого и создан
Для примера разберём первый дашборд отчёта: Анализ продаж
Данный дашборд можно поделить на три основных блока:
- Верхняя панель: навигация по дашбордам и фильтры
- Левая верхняя: топ продаж
- Остальное: детали по категориям
![Дашборд Анализ продаж Дашборд Анализ продаж](https://habrastorage.org/r/w1560/getpro/habr/upload_files/45d/45d/b62/45d45db62e878293cd32e5f431eb0163.jpg)
3. Выведены продажи по различным категориям.
![a5f4fbf3353dc248611be468b2d26adc.png](https://habrastorage.org/r/w1560/getpro/habr/upload_files/a5f/4fb/f33/a5f4fbf3353dc248611be468b2d26adc.png)
- Продажи по группам товаров и бренду KZT: по pie- chart можно «провалиться» к каждой группе и посмотреть данные о брендах, следует отметить что один бренд может относится к разным группам. Этот график удобен чтобы отслеживать именно продажи по группам и из каких брендов состоит определенная группа, нежели чем по брендам. Все графики имеют связь между собой. Нажав на одну из категорий, дашборд отфильтруется по ней и можно будет увидеть информацию по конкретной категории в разрезе городов и менеджеров.
- Продажи по городам: представлены данные о продажах по городам, от большего к малому. Нажав на определённый город, вся информация отфильтруется по нему.
- Продажи по менеджерам и клиентам в KZT: можно не только сравнить объём продаж менеджеров, но и «провалиться» и увидеть данные о клиентах каждого менеджера
- Продажи в KZT: на данном графике представлены продажи по группам по месяцам: с января по июль.
![e1160e1041bb89f8ed6a3cfff51486eb.png](https://habrastorage.org/r/w1560/getpro/habr/upload_files/e11/60e/104/e1160e1041bb89f8ed6a3cfff51486eb.png)
Первый дашборд является аналитическим и показывает только общую картину. Позволяет быстро найти проблемные зоны и моменты, на которые стоит обратить внимание. Для более детальной информации и выявления причин пользователь может перейти на следующие страницы отчёта, где будет дана более детальная информация, в т.ч. в виде таблиц с подсвеченными аномалиями.
Отчёт обновляется ежедневно, поэтому каждый день пользователь будет видеть реальный ход дел и эффективность принятия решений.
Как мы делали сквозную аналитику из 1C в Power BI
В странах СНГ принято создавать отчеты о деятельности компании в сводных таблицах Excel. Сказать, что это не юзабельно и не юзерфрендли для сложных отчётов – ничего не сказать. Для наглядности...
![habr.com](https://assets.habr.com/habr-web/img/favicons/favicon-16.png)