Microsoft Power BI + 1С. Зачем и как?

Продолжаем нашу эпопею с мобильным миром. И сегодня я хочу поговорить о вот таком вот продукте Microsoft Power BI.

Что есть такое Microsoft Power BI?

А это служба бизнес-аналитики для совместного доступа, управления и использования запросов данных и книг Excel, которые содержат запросы данных, модели данных и отчеты.

Тут можно почитать подробней. Однако в последнее время я заметил за собой одну такую интересную штуку – читая официальные описания продуктов, я вообще ничего не понимаю. Т.е., как говорил один мой преподаватель в универе – «Вы понимаете каждое слово в отдельности, но вот предложение целиком – вы не в состоянии осмыслить» (преподаватель по электродинамике).

Вот так и с этими описаниями, посему – давайте я попробую описать все это своими словами.

Если выражаться словами 1Сника, то:

Microsoft Power BI – это СКД. Вот и все J

Но вот только СКД в более глобальном плане. Т.е. если в СКД мы можем взять данные из БД или, например, из неких внешних источников данных. То в BI – мы берем все данные из внешних источников и рисуем из них красивые и живые графики. Причем не такие живые в 1С, а реально – интерактивные графики со взаимосвязями и т.д. Хотя ради справедливости стоит заметить, что то же самое можно сделать и в 1С, но не так удобно.

Немного теории

BI– это облачный сервис, где хранятся ваши данные и ваши отчеты. Да, да. Как я и говорил ранее – BI очень похоже на отчеты на СКД.

Как вы понимаете – отчеты надо где-то писать. Посему существует конфигуратор Power BI Designer, где вы этим и занимаетесь. Скачивайте его отсюда.

Естественно, что раз есть место, где мы отчеты пишем, значит, должно быть место, где мы их смотрим. И таких мест несколько:

• 1.       Сама среда, где мы их пишем. Ну это логично, но полезно только нам;
• 2.       Веб-интерфейс;
• 3.       iOS устройства;
• 4.       Windows устройства, могут установить в стиле метро. Те, у кого Win 8/8.1. Смотрится реально футуристично, особенно на больших мониторах.
• 5.       Android устройства. «Androidappisontheway». Ждем.

Логика работы

Давайте сначала объясню логику работы, чтобы потом было понятней – почему делаем так, а не иначе. Всю разработку можно разбить на несколько этапов:

• 1.       Регистрацию аккаунта. Тут можно посмотреть цены, но есть и бесплатная версия, так что тестировать можно. Конечно, для запада цена в 10$ с пользователя в месяц – это мелочь, а вот для наших стран – это-таки немало. Хотя опять же, даже если взять фирму в 500 человек, скольким сотрудникам понадобится такой функционал?
• При регистрации учтите один момент – она выполняется слева внизу, где указываете почту. Если зайти в Sign in – там только авторизация.
• 2.       Необходимо запустить конфигуратор Power BI Designer, тот, который вы скачали ранее.
• 3.       В конфигураторе мы делаем все, что мы хотим, формируем внешний вид и т.д., потом сохраняем файл проекта.
• 4.       Теперь файл проекта нам надо подтянуть в нашу учетку, чтобы изменения сохранились там.
• 5.       Авторизуясь под своим аккаунтом на мобильном устройстве или в клиенте для Windows – подхватываются ваши настройки.
• 6.       Получить удовольствие.

Работа с конфигуратором

Теперь давайте разберемся с тем, как все-таки строить отчеты и как получать данные?

Получение данных

При первом открытии вас встретит помощник. Закрываем его. И удаляем все лишнее. У нас должно остаться только вот такое вот окошко:

Пока не будем вдаваться в подробности о назначении всех кнопочек. А попробуем ответить на вопрос – как нам получить данные?

В погоне за ответом мы должны нажать кнопку «GetData», - та, что слева вверху, нас встретит вот такой диалог:

Как видите – список потенциальных источников просто огромен. Однако, мы возьмем вариант получения данных при помощи OData, видь он есть у нас в 1С, а мы его обычно не используем. Давайте вот его и применим. Более детально про него можно почитать тут:

• 1.       ИТС
• 2.       //infostart.ru/public/305854/

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

Есть несколько вариантов:

• 1.       Поднять свой веб-сервер, и данные тащить оттуда. Но такой вариант неудобен для обучения, так как я свой сервер не дам в публичный доступ.
• 2.       Пойти на сайт 1С, где есть демо-конфигурации, и попытать счастья там, в надежде, что хоть в одной из них открыт OData. Убедиться, что это не так, и закрыть сайт.
• 3.       Пойти на англоязычный сайт 1С, присмотреть там какую-то базу, у которой открыт доступ через OData. И найти такую.

Итак, останавливаемся на УНФ, проверить, что ODataдоступен, можно вот таким путем:

http://apps.1c-dn.com/sb/odata/standard.odata/

Если вы там видите список метаданных, то все ок.

Теперь давайте проверим справочник, например, справочник товаров:

http://apps.1c-dn.com/sb/odata/standard.odata/Catalog_Nomenclature

Как видите, мы к первому адресу просто прибавили имя справочника и обозначение, что это справочник, в русскоязычных конфигураторах он бы выглядел вот так:

http://apps.1c-dn.com/sb/odata/standard.odata/Catalog_Номенклатура

Когда мы перейдем по этому URL, мы должны увидеть xml, который будет описывать каждый элемент справочника, однако, все зависит от браузера, FireFox выводит эти данные в виде RSS ленты. Но сути это не меняет.

Если же у вас, при подключении к вашей базе, появилось сообщение о том, что не найдена сущность, то вам сюда //infostart.ru/public/297325/.

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

Итак, первым делом – нам надо понять, где хранятся продажи. Они точно хранятся в регистре накоплений, а этот регистр обозначается в OData, как AccumulationRegister. Вот их и ищем. Нам в этом помогут элементарные знания инглиша. А вот и наш регистр AccumulationRegister_Sales. Проверяем:

http://apps.1c-dn.com/sb/odata/standard.odata/AccumulationRegister_Sales

Если видим xml, то все ок.

Теперь идем в наш BI конфигуратор, выбираем в нем строку с OData, ту, что я выделил желтым на скрине выше и жмем Connect, далее заполняем все, что надо:

Теперь нас встретит следующее окно:

Тут нам сообщается о том, что у нас не указана авторизация. Надо указать. Причем обратите внимание, вы авторизацию можете указать как в целом для домена, так и для конкретного адреса, как указать авторизацию для конкретной базы – я не нашел, но это нам сейчас и не важно.

Слева нам надо выбрать тип авторизации. В нашем случае – это авторизация по типу Basic:

Указываем логин без пароля и жмем connect. Должна начаться загрузка данных. После нее – у вас появятся слева поля:

Доступные поля отображаются справа вверху, сейчас их только два.

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

Слева вверху – страницы с отчетами, т.е. на одной странице (а-ля макет 1С) может быть много разных данных.

Теперь надо разобраться, почему это у нас всего два поля, видь измерений, реквизитов, ресурсов у нас явно больше. Давайте перейдем в запросы:

Это окно работы с запросами. Слева вверху будут доступны все запросы, которые вы добавите.

По центру сверху – строка подключения. По сути – это текст запроса, тут указываются и отборы, и пути и т.д.

Справа у нас идет название запроса и последовательность шагов.

Вот на последовательности мы немного поговорим. Суть ее сводится к тому, что мы описываем в ней пошагово правила, которые хотим применить к нашим данным. Т.е. если вы хотите удалить колонку, то надо указать в каком времени – это будет шаг номер 2, или номер 10. Дальше станет понятней.

Итак, у нас есть всего три колонки. С чем это связано? Давайте внимательно посмотрим на xml, который нам отдает 1С и найдем то место, где начинается список, т.е. в случае OData нам возвращается не построчное представление регистра, как мы привыкли, а выводится сначала информация о регистраторе, а потом уже вложены в него его движения.

Ну а нас интересуют именно эти строки. Давайте теперь выделим любой элемент в средней колонке и внизу увидим вот такое сообщение:

А должны были бы увидеть зачения, но не увидим. Для того чтобы увидеть – нам надо развернуть массив в строки. Для этого нажимаем кнопочку, которая справа вверху в заголовке средней колонки, я ее на скрине выделил желтым, и запомните – у нас сейчас 18 строк, нажимаем:

Теперь у нас 50 строк. Т.е. мы развернули все построчно, а ранее было сгруппировано по регистратору, если говорить словами 1С.

Но нас это не устраивает, нам мало колонок, я думаю, вы догадались, что надо сделать – жмем еще раз на ту кнопку в заголовке:

А вот теперь мы выбираем – что мы хотим добавить, т.е. по аналогии с запросом в 1С – выбираем поля, которыми мы потом хотим оперировать:

И жмем ОК:

Обратите внимание – у нас справа заполнился список действий, т.е. в нашем случае действия такие:

• 1.       Получить данные;
• 2.       Развернуть данные;
• 3.       Развернуть данные.

И сразу обратите внимание на формулу вверху.

Теперь нам надо решить проблему, потому что нас совсем не устраивает наименование товара в виде UID. Я сомневаюсь, что такой вид будет информативен.

Значит, нам надо теперь вывести информацию по товару, давайте мы это сделаем объединением таблиц товара и продаж.

Для этого – добавляем новый запрос, но уже к справочнику товаров http://apps.1c-dn.com/sb/odata/standard.odata/Catalog_Nomenclature, точно так же, как и продаж, и переименуем эти таблицы, чтобы не путаться:

Обратите внимание – я сразу применил несколько шагов к данным о товаре, а именно:

• 1.       Получил данные:
• = OData.Feed("http://apps.1c-dn.com/sb/odata/standard.odata/Catalog_Nomenclature")
• 2.       Удалил лишние колонки:
• = Table.RemoveColumns(Source,{"SKU", "ChangeDate", "WriteOffMethod", "DescriptionFull", "BusinessActivity_Key", "Vendor_Key", "Cell_Key", "Comment", "OrderCompletionTerm", "TimeNorm", "FixedCost", "CountryOfOrigin_Key", "PictureFile_Key", "Parent_Key", "DataVersion"})
• 3.       Отфильтровал записи, убрал все группы:
• = Table.SelectRows(#"Removed Columns", each ([IsFolder] = false))

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

Т.е. мы всегда должны указывать, к какому шагу мы применяем то или иное правило.

Идем дальше, теперь у нас есть две таблицы, и точно так же, как и в случае с 1С, мы должны их объединить. Есть два способа, как можно это сделать:

• 1.       Просто взять и сделать объединения по типу связей в 1С, когда мы объединяем две таблицы.
• 2.       Добавить в одну из таблиц нужные нам данные из другой.

Мы их разберем оба. Итак. Сначала мы просто делаем объединение. Для этого идем на вкладку Отчеты (слева внизу) и нажимаем кнопку Manage:

И настраиваем связи:

Ну и, наконец-то, давайте выведем любой отчет... а хотя нет, еще рано. Давайте переименуем поля в такие, чтобы было понятно, что за что отвечает. Для этого возвращаемся в запросы и переименовываем все, что нам надо, просто нажимая правой кнопкой на колонке и Rename или два раза по ней. Не забудьте потом перепроверить связи таблиц.

А теперь идем уже строить отчеты:

Как видите – отчеты выглядят очень даже симпатично, особенно – сама идея того, что можно просто взять и связать кучу графиков друг с другом.

Теперь давайте попробуем еще добавить сюда номенклатурные группы товара. По идее, надо было бы добавить еще один запрос с номенклатурными группами и связать их с полем NomenclatureGroup_Key в запросе номенклатур. Однако нам это не понадобится. Идем в самый конец, смотрим на последние колонки в запросе Номенклатура.

И вас встретит ошибка, и это, скорее всего, баг платформы, на которой опубликована эта база, так как на последних платформах – все ок. Поэтому тестируйте у себя. Но суть в том, что тут появятся названия номенклатурных групп.

Перенос данных на свою учетку

После всего этого – сохраняем полученный нами отчет. Он, кстати, прикреплен к статье. Так что можете его скачать из прикрепленных файлов к этой статье.

Далее идем в наш аккаунт на сайте:

Нажимаем GetData, выбираем пункт PowerBIDesignerFile и жмем Connect, далее выбираем файл и еще раз жмем Connect.

Ну, а дальше все и так интуитивно понятно. Вы прям тут можете редактировать отчеты, доски и т.д.

А теперь давайте зайдем в приложение iOS и посмотрим, как оно выглядит там:

Вот и все.

Итог

В итогах хочу сказать лишь одно – не надо ограничиваться только возможностями 1С, ведь 1С – это, во-первых – база данных. Так что красивости – это уже для других решений.

Если хорошо знать OData, то сделать такого рода отчет можно за несколько минут. И кроме этого – для простоты работы и меньшей нагрузки на БД можно просто создать отдельный регистр сведений, куда скидывать сразу всю нужную информацию, а этим решением забирать ее и выводить.

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

Все, что надо для того, чтобы приступить к работе - установить веб-сервер, обновить платформу до 8.3.6 (может и с 8.3.5 будет работать, я не тестил, главное, чтобы там не было багов в платформе), вытянуть данные и скомпоновать их.

Т.е. для программиста это займет от силы несколько часов. А результат - отличный.

P.S. Не забудьте про безопасность данных!