Подключение power bi к 1с

Обновлено: 02.07.2024

Анализ работоспособности в организации – это в первую очередь способ достижения положительных результатов, нежели работа ради работы. Информация, представленная в визуальном виде, позволит оперативно дать оценку коммерческой ситуации предприятия. Что в свою очередь даст толчок к развитию. Для визуализации аналитических данных используют различные программные продукты в список, которых входит Microsoft Power BI. Эта программа отличный помощник в построении динамических графиков или диаграмм, а также в формировании альтернативных отчетов. Power BI позволяет получать аналитику из сторонних программ по средствам подключения через интерфейс OData . Перед переходом к практической части статьи, закрепим понимание важности аналитики. Понятие «Аналитика» представляет собой искусство рассуждения логических учений об анализе проводимых операций. Проще говоря, аналитика – это, то, что нужно постоянно применять каждому руководителю подразделения или целого предприятия, чтобы развиваться в лучшую сторону. Вообще любая информация, представляемая визуально, воспринимается куда легче, поэтому практическое использование вспомогательных средств для построения диаграмм просто необходима. Часто встречаются случаи, когда в компании информацию ведется и хранится разрознено, то есть данные по сотрудникам ведутся в 1С:ЗУП 3.1, номенклатура учитывается в Excel и так далее. В таком случае использование Power BI является решением этих проблем.

Выгрузка информации из 1С в Power BI.

Так как в конфигурации «Управление IT-отделом 8» поддерживается функционал работы с интерфейсом OData, а также в программе ведется учет заявок Service Desk или номенклатуры предприятия, рассмотрим пошаговую выгрузку данных и подключение к Power BI. Сперва нужно предоставить доступ к базе данных, а также к интерфейсу OData путем публикации конфигурации. Для этого откроем программу в режиме конфигуратора, перейдем в раздел «Администрирование» -> «Публикация на веб-сервере» и установим флаг «Публиковать стандартный интерфейс OData». Если публикация базы осуществляется на веб-сервере IIS (Internet Information Service), то тогда дополнительным шагом необходимо установит флаг «Использовать аутентификацию операционной системы», что позволит использовать доменную авторизацию, при использовании интерфейса OData. После установки всех настроек, нажимаем опубликовать и при успешной публикации высветится диалоговое окно, которое сообщит об успешной публикации.

После успешного открытия доступа к интерфейсу OData через WEB, перейдем к дальнейшей настройке. Откроем конфигурацию в режиме 1С:Предприятие, перейдем в меню «Все функции» и откроем обработку «Настройка стандартного интерфейса OData».

Примечание: «Для того, чтобы включить команду «Все функции», необходимо перейти в «Настройки» -> «Параметры» и активировать чек-бокс «Отображать команду «Все функции»».

Во время карантина появилось немного свободного времени и я решил рассказать, как я на прошлом месте работы смог быстро интегрировать несколько баз 1С с BI-системой (в нашем случае это был Qlik Sense). Сразу скажу, мы не стали использовать OData, потому что потребовалось бы слишком много ресурсов от отдела 1С и на интеграцию, и на дальнейшую поддержку.

Для интеграции данных 1С в BI-систему мы взяли коннектор

Теперь мое мнение о плюсах и минусах коннектора и небольшая инструкция для заинтересованных.

Мнение о коннекторе

Плюсы Коннектора АТК BIView:

Снимает большую часть монотонной работы по поиску соответствий 1С и SQL, переименованиям и пр. Если у вас несколько баз 1С, из которых нужно забирать данные в хранилище или BI-систему, экономия времени выходит значительная.
Шансов что-то испортить в 1С нет – у коннектора права только на чтение 1С, записывает вьюхи он в соседнюю БД, которую мы для него специально создаем.
Работает из коробки с любой конфигурацией 1С (проверяли на трех разных), но при этом коннектор – это не черный ящик. В папке проекта (та, что по умолчанию в ProgramData) создаются скрипты, которые можно открыть и посмотреть. ScriptLev2.txt – это полный скрипт того, как создавались SQL-представления и какие трансформации с данными производили. Например, в куске кода ниже видны такие преобразования (пометил красным):
- Технические наименования полей загружаются с теми названиями, что есть в пользовательском интерфейсе 1С
- Делается смещение дат 2000
- Подтягиваются справочники при помощи left join-ов

Поддержка интеграции данных из 1С сильно упрощается. Если структура данных 1С изменилась, нужно открыть коннектор, запустить «расчет» и все. Все связи перестроятся самостоятельно и ваша BI-система или ХД будет работать с актуальными SQL-представлениями из 1С, а вам ничего не нужно будет вручную разбирать, сверять и поправлять.

К сожалению, коннектор АТК BIView – не open source, нужно купить лицензию. Цены на постоянную лицензию начинаются примерно от 60 тыс. рублей, в зависимости от количества баз 1С. У нас в рамках общего бюджета проекта по внедрению BI-системы, стоимость коннектора была незначительной.

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

Если у вас файловая 1С (не SQL, не PostgreSQL) или более старая версия 1С, чем 8.1, коннектор не подойдет.

Настройка соединения

Теперь небольшая инструкция о том, как работать с коннектором АТК BIView.

Сначала коннектору нужно настроить соединение с 1С (тут все понятно) и базой данных, куда коннектор запишет созданные SQL-представления и служебные таблицы. Она называется «БД Проекта» (отметил на скриншоте) и обязательно создается на SQL-сервере 1С. Вот экран настройки соединения:

Дальше жмем «Проверка подключения» (коннектор подключается как клиент 1С) и ждем надписи, что подключение успешно.

Вот БД Проекта, которую я создал на MS SQL сервере для коннектора, папка Views пока пуста:

Если вы собираетесь потом использовать SQL-представления в Power BI, на стартовом экране нужно поставить галочку «Режим проекта Power BI». Поскольку у нас был Qlik Sense, не могу прокомментировать, мы не ставили.

Еще на экране подключения есть поле «Проект» со ссылкой на папку проекта, где хранятся все данные по настройкам ATK BIView, логи, которые пишутся во время подключения и расчета, скрипты, создающие SQL-представления. Неудобство может быть только с минимальной лицензией на коннектор (на одну базу) – там местоположение папки изменить нельзя (она находится строго в ProgramData на диске С).

Создание SQL-представлений

У коннектора 2 типа проекта, в которых можно создавать SQL-представления:

Не очень понял, в чем смысл базового типа проекта, потому что он создает те же вьюхи, только количество полезных трансформаций для BI-щиков в них гораздо меньше. Похоже, можно всегда просто выбирать второй тип проекта (на скриншоте), а затем – кнопка «расчет»:

Дальше нужно подождать. Сколько подождать – зависит от объема вашей 1С. Коннектор считывает метаданные, готовит технические SQL-таблицы и на основе них уже создает вьюхи. Информацию о процессе показывает в строке рядом с кнопкой «Расчет»:

Готовые SQL-представления: особенности

Когда расчет завершится, база данных проекта, которую мы создавали будет содержать все SQL-представления по вашей 1С. Например, вот так:

Какие способы подключения Power BI к 1С существуют?

Проблема. Нет единственного и правильного коннектора или способа подключения! Нельзя открыть Power BI, выбрать источник данных 1С, вбить логин, пароль и подключиться. Из-за этого у руководителей, начинающих аналитиков появляется много вопросов.

Мой опыт программиста позволяет использовать 4 способа подключения Power BI к 1С.
Разберу по порядку:
-выгрузка данных 1С в Excel файлы
-публикация базы на веб-сервере, подключение к ней через OData
-подключение к SQL базе 1С напрямую
-построение собственного аналитического хранилища.

Выгрузка данных 1С в Excel файлы

Люблю этот способ, часто практикую, когда у клиентов нет денег или нужно быстро запуститься.

Аналитик изучает файлы Excel и использует в работе, пока разрабатываются продвинутые способы подключения Power BI к 1С.

Не все знают, но можно не только вручную выгружать информацию в Excel файлы, но также наладить выгрузку файлов в автоматическом режиме. Выбираете какие документы, справочники, регистры требуется выгрузить, настраиваете расписание, папку на сервере. Ночью, данные выгрузятся и отчет Power BI заберет их в автоматическом режиме.

У этого способа свои нюансы. с такими выгрузками тяжело поддерживать поэтапное добавление данных. Например каждый день выгружать новый файл за прошлый день. А вдруг бухгалтер провел документы задним числом? А вдруг переименовали контрагента, номенклатуру и нужно заново сделать выгрузку? Эти вопросы сложно решаются и практически единственный путь — ночью перезаписывать файлы Excel целиком.

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

С точки зрения бюджета, затрачиваемых усилий, результата — способ рекомендую использовать. Подойдет на первое время если у вас небольшой бизнес. Если ежеминутно не бьются чеки как в сетевых магазинах. Ведь там миллионы строк данных о продажах набираются за несколько дней.

Кто может сделать? Штатный бухгалтер или программист. Подробную техническую инструкцию найдете в статье Настройка выгрузок в Excel из 1С и Автоматизация выгрузок в Excel из 1С с помощью Рассылки отчетов.

Подключение к веб-базе 1С через OData

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

Существует много облачных сервисов, большинство компаний уже используют облачные технологии. Поэтому технически такая возможность либо уже есть, либо реализуема. Тут главное понять принцип — публикуете базу в интернете, присваиваете доменное имя (как сайту) и зная это имя, логин, пароль работаете в 1С. Для безопасности прописываете список IP адресов, кому разрешено подключаться к базе. Способ неплохой, но есть плюсы и минусы.

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

Во вторых, подключение через браузер это тоже обмен файлами. Power BI делает запрос к 1С, получает ответ в виде набора данных - файла формата JSON. Если данных много, файл большой, скорость интернета низкая — произойдет обрыв соединения. Поэтому этот способ техничнее чем Excel выгрузки, но по производительности такой же. Для небольших компаний и небольших объемов. Ритейлу, банкам, крупному бизнесу он не подойдет

По стоимости — оплатить 1 день работы программиста 1С и 1 день работы аналитика — забрать, обработать данные.

Подключение к базе SQL 1C напрямую

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

Тут 2 проблемы. Информация в SQL базе хранится в зашифрованном виде в виде метаданных. Регистр бухгалтерии это не одна таблица с русскими названиями, а десяток таблиц с ключами, кодами, непонятными значениями.

Вторая проблема — лицензионная политика 1С запрещает подключение напрямую к этой базе.

Решаем эти проблемы следующим образом.
Делаем копию SQL базы, программисты настраивают синхронизацию боевой базы 1С и этой копии (по расписанию), подключаемся к этой копии и разбираем эти метаданные. Существуют специальные обработки, которые позволяют из этого хаоса найти нужные таблицы и вместо метаданных получить таблицу как в Excel. Но нужно время, трудозатраты уже составляют неделю работы специалиста. Поэтому рекомендую 100 000 рублей заложить в бюджет проекта.

Такой способ хорош, когда данных много, их нельзя получить через Excel или веб-файлы. Через SQL десятки миллионов строчек прогрузятся в Power BI за пару минут. Скорость обновления отчетов Power BI будет быстрой.

Готовых статей — инструкций по этому способу у меня нет. Мой опыт программиста 1С позволяет в этом разобраться, а вот клиенты уже не могут. Используем этот способ в больших проектах.

Построение собственной аналитической базы

Построить собственную аналитическую базу SQL, где хранятся все данные компании для анализа.

Это 1С, информация рекламных кабинетов, выгрузки специфических промышленных сервисов. Эти данные можно объединить, почистить, обработать, сделать сложные расчеты.

Технически это выглядит так. Программистам 1С ставится задание — по расписанию выполнять запросы, выгружать информацию, например регистра бухгалтерии в определенные таблицы SQL.

Аналогичная задача ставится программистам других сервисов. Информация загружается на первый уровень, это сырые данные. Система проверяет, что данные загружены, запускает механизм обновления — на лету чистит, обрезает, модифицирует, обновляет главные таблицы. Обеспечивается поэтапное добавление и контроль целостности данных.

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

Стоимость. Сильно зависит от трудозатрат. Это не 1 месяц работы программиста-аналитика. Сразу нельзя предусмотреть все варианты, начнете с базовых таблиц 1С, потом постепенно добавите другие сервисы. Потом поймете, какие изменения нужно сделать в этой базе, потому что Power BI не справляется с текущей архитектурой. Это сотни часов работы аналитиков и программистов. В крупном холдинге или компании это занимает не один год.

Тоже скажу — знаю, умею, но не берусь. Мы делаем проекты по аналитике от 100 000 руб. до 300 000 руб., иногда дороже, если растянуты по времени. А тут смета на хранилище начнется только от 300 000 руб. за 1-2 месяца работы, а этого может не хватить для большой компании.

Но если все по-взрослому, серьезно, надолго — это лучший вариант. Мерседес, если сравнивать с машинами. Но не всем нужно.

В данной статье я хотел бы подробнее остановиться на данной теме, так как данные из 1С в том или ином виде есть почти у каждого нашего клиента. Всего мы знаем 5 разных способов подключения к данным, из которых в своей практике использовали 4:

выгрузка файлов Excel/XML в папку;
коммерческий коннектор (АТК, BI Consult, Первый Бит);
публикация базы 1С в вебе и подключение к ней по протоколу oData;
построение собственной аналитической базы SQL и наполнение ее выгрузками из 1С;
подключение напрямую к рабочей базе SQL 1С и разбор метаданных.

1. Выгрузка файлов Excel в папку или на FTP сервер

Отличный способ, когда нужно максимально быстро и просто получить обновляемые файлы из базы 1С. С помощью штатного инструмента 1С Рассылка отчетов вы можете выложить файлы в общую папку на сервере/FTP ресурс/облачный диск. Программисты 1С вместо Excel файлов могут использовать XML файлы, которые используют при обменах между базами 1С.

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

Далее вам нужно воспользоваться инструментом Рассылка отчетов . Вы указываете подходящий Вариант отчета , настраиваете расписание, формат выходного файла и его местоположение.

Вы можете задать локальную папку, сетевую или FTP ресурс.

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

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

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

2. Коммерческие коннекторы (АКТ, BI Consult, Первый Бит и другие)

Крупные игроки на рынке BI аналитики Qlik разработали собственные коммерческие коннекторы к 1С. Мы тестировали коннектор от АТК, поэтому мои дальнейшие выводы основаны на его использовании.

Реализация коннектора будет зависеть от используемой базы SQL под 1С. Если используется Microsoft SQL то коннектор работает в полном режиме, создает SQL базу, в которой будут специальные представления View, которые представляют собой уже расшифрованные в удобном для аналитике виде данные 1С.

В режиме Postgre SQL коннектор работает гораздо проще, не может создать представление View, а просто генерирует текстовые файлы запросов, которые можно использовать в BI системе. То есть запрос на рисунке выше вы сможете использовать напрямую в Power BI.

Стоит отметить, что коннектору обязательно нужны доступы как к самой базе 1С, так и SQL базе.

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

Коннектор сможет отсканировать все объекты метаданных конфигурации 1С, чтобы аналитик мог выбрать нужные ему документы и регистры.

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

Таким образом, коннектор помогает аналитику выбрать подходящие объекты конфигурации, сразу увидеть их значения и подключить BI систему к базе SQL. Это экономит десятки часов по раскодированию/расшифрове метаданных 1С. Это одно из главных преимуществ коннектора, возможность развернуть его за 1 рабочий день аналитика и почти сразу получать нужные данные из 1С.

Из минусов коннектора отметим, что в режиме PostgreSQL он может генерировать только текстовые файлы-запросы, которые мог бы нам сделать и обычный программист 1С. Поэтому рекомендуется использовать Microsoft SQl для полного функционала коннектора. Также сторонников безопасности может насторожить тот факт, что коннектору нужен доступ на запись в рабочую базу SQL 1C для создания представлений View (но в другой схеме).

Примерные трудозатраты и бюджет : от 60 000 рублей за 1 базу 1С, в рамках 1 рабочего дня программиста 1С на подключение.

3. Публикация базы 1С в вебе и подключение к ней по протоколу oData

Этот способ сейчас активно рекламируется и продвигается различными аналитиками. В первую очередь, потому что это довольно просто. Нужно просто опубликовать базу 1С на веб-сервере (у нас на сайте есть статья-инструкция на эту тему) и затем согласно справке можно делать такие запросы вида:

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

На выходе получается файл json со всеми реквизитами и значениями справочника.

Так в чем же проблема этого метода? Проблем я вижу две.

Первое, подключение к Odata довольно медленное, если файл будет содержать сотни тысяч записей, а то и миллионы (а для регистра бухгалтерии это довольно обычный объем), то файлы будут запрашиваться по 5-10-15 минут, могут возникнуть обрывы подключения. Каждый новый документ и справочник потребует генерации нового источника данных и нового файла. В итоге при большом объеме данных этот способ не сильно будет отличаться от выгрузок Excel файлов.

Второе, не все готовы публиковать базу 1С на веб-сервере. Иногда, например для синхронизации 1С и CRM системы клиенты уже это делают, но есть и консервативные компании, которые не работаю с облачными сервисами и доступом принципиально из-за угроз утечки данных.

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

Примерные трудозатраты и бюджет : условно бесплатно, необходимо участие программиста 1С, настраивается примерно за 1 рабочий день.

4. Построение собственной аналитической базы SQL и наполнение ее выгрузками из 1С

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

Работая с файлами Excel/Json вы столкнетесь с проблемами производительности и сложностью администрирования и технической поддержки. Пытаясь разобраться в структуре данных SQL 1C базы, вы столкнетесь с тем, что она проектировалась для оптимизации работы 1С, а не для аналитиков. Простой документ может потянуть за собой десяток справочников, а каждый справочник может использовать дочерние справочники, а дочерние справочники какие-нибудь перечисления и так далее. Можно легко получить 3-5 уровней нормализации данных, что также не всегда нужно аналитику.

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

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

Мы получали данные из различных источников:

1С УПП
веб-сервис ГИС с геоданными
база данных производственной программы
ряд Excel файлов финансовой службы.

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

Данные, которые без изменений приходят из различных источников

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

На этом уровне происходит агрегирование данных, представление данных в удобном для аналитике виде.

Таким образом внутри аналитической базы вы сможете проводить расчеты любой степени сложности. Данные расчеты могут быть выполнены быстрее, чем к примеру в Power Query от Power BI.

А как же наполнять эти данные выгрузками из 1С? Скорей всего вам придется самостоятельно написать нужные обработки и регламентные задания, которые представляют собой запросы к базе 1С и выгрузку результатов этих запросов в заранее созданные SQL таблицы. Вот пример подобного запроса:

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

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

Примерные трудозатраты и бюджет : от 1 недели до 1 года в зависимости от объема данных, количества источников, совместимости данных друг с другом и множества других факторов. Бюджет от 100 000 рублей до нескольких миллионов для крупных компаний.

5. Подключение напрямую к рабочей базе SQL 1С и разбор метаданных

Честно говоря, я никогда не рассматривал этот способ всерьез. У него есть несколько существенных минусов.

Во первых, это запрещено лицензионной политикой 1С.

Нельзя обращаться к данным информационной базы напрямую, минуя уровень объектов работы с данными «1С:Предприятия», например при помощи средств СУБД или при помощи внешних компонент, которые реализуют прямой доступ к СУБД. Это ограничение распространяется на любые действия с данными, в том числе на изменение их структуры, а так же на чтение или изменение самих данных информационной базы или служебных данных «1С:Предприятия».

Во вторых, подключившись к таблице документа мы увидим что-то подобное:

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

Примерные трудозатраты и бюджет : условно бесплатно, необходимо участие программиста 1С, настраивается примерно за 1 неделю.

Недавно мы опубликовали несколько статей, в которых рассказали об основах моделирования (первая и вторая) Power BI, а также рассмотрели кейсы по внедрению отчетности для отдела продаж на данных из amoCRM (первый и второй). У наших читателей появилось множество вопросов о том, как при помощи нашего сервиса myBI Connect можно самостоятельно создавать отчеты в Power BI Desktop. Мы решили объединить теорию с практикой и рассмотреть этот вопрос более детально.

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

Выгрузка данных

Хранилище данных

Данные, которые myBI Connect получает из различных онлайн-источников, загружаются в хранилище, представляющее собой обычную базу данных Azure SQL Database. Использование базы данных (БД) в качестве промежуточного звена между источником данных и Power BI, позволяет получить ряд преимуществ, которые невозможны при прямом подключении к источнику и извлечении данных непосредственно из него при помощи Power Query:

в первую очередь, использование БД позволяет получать из сервисов-источников только измененные и новые сведения без необходимости постоянной выгрузки всех исторических данных;
данные загруженные в БД могут быть использованы многократно для решения задач различных специалистов без необходимости постоянного извлечения их из сервисов-источников;
выгрузка данных с использованием Azure SQL Database, позволяет достаточно быстро загружать необходимые данные по защищенным каналам в онлайн-сервис Power BI без установки дополнительных шлюзов;
наконец, немаловажным преимуществом является возможность работать в Power BI как через DirectQuery, так и через импорт данных.

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

Подключение к базе данных

Для упрощения работы, а также для дальнейшего создания шаблонов в Power BI мы рекомендуем сохранить имя базы данных и сервера в отдельные параметры Power Query и при создании запросов использовать уже их.

После подключения к базе данных Вам будет предложено выбрать таблицы с данными, которые нужно загрузить. Все сущности, относящиеся только к рассматриваемой CRM помечаются префиксом АМО.

Таблицы и представления

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

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

Структура базы данных

Сделки

AMO Сделки
AMO Параметры сделок
AMO Дополнительные параметры сделок
AMO Метки сделок
AMO Примечания сделок

Контакты

AMO Контакты
AMO Параметры контактов
AMO Дополнительные параметры контактов
AMO Метки контактов

Компании

AMO Компании
AMO Параметры компаний
AMO Дополнительные параметры компаний
AMO Метки компаний

Покупатели

AMO Покупатели
AMO Параметры покупателей
AMO Метки покупателей

Задачи

AMO Задачи
AMO Параметры задач
AMO Примечания задач

Звонки

AMO Звонки
AMO Параметры звонков

Общие таблицы измерений

SHD Параметры посетителей
SHD Параметры источников данных
SHD Параметры дат

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

Загрузка данных

После выбора необходимых для выгрузки таблиц они автоматически загружаются в Power BI. Важной особенностью является тот факт, что необходимость в дополнительной обработке данных в Power Query при использовании представлений, сведена к минимуму. В случае загрузки данных из amoCRM он нам будет полезен только в двух случаях:

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

Связи

После того как мы выбрали необходимые таблицы и загрузили их в Power BI встает другой вопрос: как сделать так чтобы данные из разных таблиц использовались как единое целое? Для этого существуют связи.

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

В PowerBI реализованы два типа связи:

Не стоит также забывать и про кросс-фильтрацию, которая поможет в дальнейшем при построении отчета. Существует два типа:

Используя Power BI, можно создать связь между двумя таблицами, перетащив идентификатор одной таблицы на столбец другой таблицы, по которому и происходит связь.

После загрузки данных в Power BI, он автоматически попытается определить связи между существующими представлениями (если только эта функция не отключена в настройках приложения). Поиск производится до момента обнаружения столбцов со схожими названиями, однородного пересекающегося набора значений и одинакового типа данных. Отметим, что связи могут быть не обнаружены между таблицами в том числе потому, что в них нет никаких данных. В таком случае необходимо вручную проставлять все возможные связи между сущностями. Также стоит заметить, что не все связи могут быть активированы. Если существует двунаправленная кросс-фильтрация между таблицами, то она может препятствовать подключению третьей таблицы к этим обеим сущностям, поскольку в данном случае они воспринимаются как единая таблица, а двойной связи к одной и той же таблице существовать не может. Автоматическое определение связей позволяет сэкономить время и не тратить ресурсы на создание связей между сущностями. С другой стороны, бывают ситуации, когда связи определяются неверно, либо же PowerBI их вообще не находит, как в случае, описанном выше.

Следует отметить одну интересную особенность, которая по сути является правилом, по которому Вы можете проверять корректность построенной модели данных. Практически все связи на концах «Снежинки» относятся к типу «один к одному», в то время как все таблицы фактов связаны между своими соседями по принципу многие к одному. Также все граничные таблицы модели данных имеют двунаправленную кросс-фильтрацию; в свою очередь в центре схемы все связи однонаправленные.

Оформление модели

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

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

Кроме этого, если Вы решите использовать Excel для работы с моделью созданной в Power BI, что иногда бывает очень удобно, то вам не обойтись без мер, так как Excel не воспринимает числовые значения как показатели, а работает только с мерами.

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

Тиражирование

Файл → Сохранить как или Файл → Экспорт → Шаблоны Power BI

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

Таким образом, в рамках данной статьи мы постарались осветить не только основные шаги по загрузке и построению модели данных для amoCRM, но и описать, почему мы используем Azure SQL, а также рассказали некоторые теоретические основы, которые необходимо сегодня знать при работе в Power BI. Конечно, данная статья не способная раскрыть все тонкости построения модели, поскольку для каждой реальной задачи они свои.

Следующая наша статья будет посвящена построению отчетности в Power BI на базе рассмотренной модели.

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

Читайте также: