В странах СНГ принято создавать отчеты о деятельности компании в сводных таблицах 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. Эта процедура, как многим известно, позволяет консолидировать данные не вписывая дубликаты строк по их полям-идентификаторам. А значит: 

1. Можно выгружать данные только за какой-то короткий срок 

2. Быть уверенным в отсутствии аномалий в отчёте 

Для реализации команды BULK понадобятся промежуточные хранилища данных. Таковым выступает вышеупомянутая учётная запись автоматизации. В Azure SQL можно создать внешний источник данных, который будет иметь доступ к учётной записи хранения через SAS-токен. Токен также можно создавать автоматизированно, а для безопасности установить срок действия на пару часов.  

Теперь у нас имеется подготовленный DWH для Power BI отчёта. Остаётся только развернуть новый workspace, развернуть в нём приложение-шаблон и вписать данные для входа в Azure SQL. К сожалению, сделать это через api невозможно. Ждём, пока такая возможность появится. Для наглядности описанного выше, приложим схему ежедневной выгрузки: 

Как это выглядит в конечном результате?

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

Отчет "Товарооборот Дистрибьюторской компании"

Цель отчёта - показать руководителю эффективность работы отдела продаж, выявить проблемные категории товаров, узкие горлышки в процессе закупа и хранения товаров. И на основе информации вовремя принимать управленческие решения. Отчёт состоит из соответствующих дашбордов, между которыми можно переключаться.: Анализ продаж, Сравнение продаж, KPI, Анализ склада.

По данной ссылке, вы можете познакомиться с интерактивными дашбордами и проверить все в действии. Кликайте, фильтруйте, переключайтесь между страницами - функционал для этого и создан :) 

Для примера разберём первый дашборд отчёта: Анализ продаж 

Данный дашборд можно поделить на три основных блока:

1. Верхняя панель: навигация по дашбордам  и фильтры

2. Левая верхняя: топ продаж

3. Остальное: детали по категориям

3. Выведены продажи по различным категориям.

• Продажи по группам товаров и бренду KZT: по  pie- chart   можно «провалиться» к каждой группе и      посмотреть  данные о брендах, следует отметить что один бренд  может относится к  разным группам. Этот график удобен чтобы отслеживать именно продажи по группам и из каких брендов     состоит определенная      группа, нежели чем по брендам.  Все графики имеют связь между собой. Нажав на одну из категорий, дашборд отфильтруется по ней и можно будет увидеть информацию по конкретной категории в разрезе городов и менеджеров. 

• Продажи по городам: представлены данные о    продажах по городам, от большего к малому. Нажав на определённый город, вся информация отфильтруется по нему. 

• Продажи по менеджерам и клиентам в KZT:  можно не только сравнить объём продаж менеджеров, но и «провалиться» и увидеть         данные о    клиентах каждого менеджера 

• Продажи в KZT: на данном графике представлены продажи по группам по месяцам: с января по июль. 

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

Отчёт обновляется ежедневно, поэтому каждый день пользователь будет видеть реальный ход дел и эффективность принятия решений.