Недавно мы опубликовали несколько статей, в которых рассказали об основах моделирования (первая и вторая) Power BI, а также рассмотрели кейсы по внедрению отчетности для отдела продаж на данных из amoCRM (первый и второй). У наших читателей появилось множество вопросов о том, как при помощи нашего сервиса myBI Connect можно самостоятельно создавать отчеты в Power BI Desktop. Мы решили объединить теорию с практикой и рассмотреть этот вопрос более детально.
С данной статьи мы начинаем цикл публикаций, посвященный построению универсальной отчетности для CRM. Вы сможете узнать про то, как настроить выгрузку данных из amoCRM, где и каким образом они хранятся в дальнейшем, а также как правильно собрать базовую модель в Power BI. В последующих статьях мы более детально остановимся непосредственно на процессе создания отчетов и разберем принципы написания мер.
Выгрузка данных
Первый шаг, который необходим для успешного построения отчета в Power BI, заключается в получении данных. Для этого мы воспользуемся нашим сервисом myBI Connect, который предназначен для автоматической выгрузки данных из различных онлайн-источников. Процесс подключения аккаунта amoCRM в сервисе детально описан в инструкции «Настройка выгрузки из amoCRM», поэтому мы не будем здесь его повторять.
После настройки источника необходимо произвести начальную загрузку исторических данных в хранилище. Для этого в подразделе «Действия», настроенного ранее источника, нажимаем на кнопку «ВЫБРАТЬ», выбираем загрузку и на следующей странице указываем необходимый период, за который будут выгружены данные из amoCRM. Статус загрузки можно посмотреть в соответствующем разделе.
На этом этапе работа по выгрузке данных завершена, в дальнейшем сервис будет автоматически подгружать новые или измененные сведения, по умолчанию раз в сутки. Более детально о работе с самим сервисом Вы можете прочитать в «Основы работы с сервисом», далее же мы будем рассматривать непосредственно работу с данными.
Хранилище данных
Данные, которые myBI Connect получает из различных онлайн-источников, загружаются в хранилище, представляющее собой обычную базу данных Azure SQL Database. Использование базы данных (БД) в качестве промежуточного звена между источником данных и Power BI, позволяет получить ряд преимуществ, которые невозможны при прямом подключении к источнику и извлечении данных непосредственно из него при помощи Power Query:
- в первую очередь, использование БД позволяет получать из сервисов-источников только измененные и новые сведения без необходимости постоянной выгрузки всех исторических данных;
- данные загруженные в БД могут быть использованы многократно для решения задач различных специалистов без необходимости постоянного извлечения их из сервисов-источников;
- выгрузка данных с использованием Azure SQL Database, позволяет достаточно быстро загружать необходимые данные по защищенным каналам в онлайн-сервис Power BI без установки дополнительных шлюзов;
- наконец, немаловажным преимуществом является возможность работать в Power BI как через DirectQuery, так и через импорт данных.
К последнему пункту необходимо добавить, что от выбора правильного метода загрузки данных зависит дальнейшая работа с Power BI. С помощью обычного импорта все данные из источника будут загружены в модель. Такая загрузка сказывается на увеличении потребления ресурсов, но в тоже время позволяет значительно снизить время необходимое для вычислений.
В свою очередь, DirectQuery — это прямое подключение к базе данных, в этом случае Power BI хранит только метаданные таблиц (имена таблиц, имена столбцов, отношения). Power BI будет уровнем визуализации, который будет запрашивать данные в БД каждый раз, когда происходит обновление каких-либо компонентов отчета. С одной стороны это позволяет работать с актуальными данными при частом их обновлении, с другой же может негативно сказаться на производительность отчета, так как значительно увеличивается количество запросов к БД и все вычисления будут выполняться в ней.
Подключение к базе данных
Далее нам необходимо подключить Power BI к БД, в которую ранее были загружены данные из amoCRM. После запуска Power BI в разделе меню «Главное» необходимо выбрать «Получить данные», где в дальнейшем происходит подключение к «Базе данных SQL Azure».
Для упрощения работы, а также для дальнейшего создания шаблонов в Power BI мы рекомендуем сохранить имя базы данных и сервера в отдельные параметры Power Query и при создании запросов использовать уже их.
Чтобы ввести данные о сервере и базе данных, необходимо вернуться в интерфейс myBI Connect в раздел «Хранилище данных», где будут указаны все необходимые сведения.
На скриншоте видно, что сервис предлагает двух разных пользователей для доступа к базе данных. Первый, «Владелец» имеет полный доступ к БД и может как получать данные, так и вносить какие-либо изменения. Второй же «Пользователь» имеет ограниченный доступ только на чтение и только к представлениям, о которых мы расскажем подробнее немного позднее, и именно его мы рекомендуем использовать в Power BI. Это реализовано для того, чтобы избежать случайных изменений в БД, а также упростить работу с данными.
После подключения к базе данных Вам будет предложено выбрать таблицы с данными, которые нужно загрузить. Все сущности, относящиеся только к рассматриваемой CRM помечаются префиксом АМО.
Таблицы и представления
Фактически, данные, загруженные в БД, находятся в таблицах, которые представляют совокупность связанных данных, хранящихся в структурированном виде виде столбцов и строк. В дополнение к ним сервис автоматически создает так называемые представления, о которых мы уже упоминали. Представление — это предварительно установленная команда запроса, которая хранится в словаре БД. В отличие от обычных таблиц в реляционной базе данных, представление не является частью физической схемы. По своей сути это виртуальная таблица, содержимое которой динамически вычисляется на основании данных, находящихся в реальных таблицах. Изменение данных в реальной таблице базы данных немедленно отражается в содержимом всех представлений, построенных на основании этой таблицы.
При создании модели в Power BI желательно импортировать представления и избегать загрузки самих таблиц. Причина заключается в том, что импорт таблиц создает сильную зависимость между физической структурой данных и отчетом. Со временем изменения в структуре базы данных могут повредить существующий отчет. Например, переименование столбца в таблице или изменение его типа — это все операции, требующие соответствующих изменений и в модели данных Power BI, что может значительно осложнить работу в случае большого количества или наличия сложных отчетов. Поэтому создание представлений для каждой физической таблицы позволяет создать промежуточный слой, который можно легко видоизменять для поддержания обратной совместимости.
Кроме этого, представления, создаваемые сервисом, имеют понятные названия на русском как самих себя так и содержащихся в них столбцов. Что в значительной степени облегчает работу с ними в Power BI.
Однако стоит отметить, что при импорте представлений вместо таблиц, 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 Примечания задач
Звонки
- AMO Звонки
- AMO Параметры звонков
Стоит отметить, что кроме таблиц измерений и фактов, характерных только для данной CRM системы, в БД есть и общие таблицы измерений. Они обозначаются префиксом SHD и очень полезны, если Вы используете несколько поставщиков данных. Например, Вы можете анализировать одновременно информацию из нескольких источников — amoCRM, Google Analytics и Яндекс.Директ. Именно общие таблицы измерений позволяют объединять и использовать данные из различных источников совместно.
Общие таблицы измерений
- SHD Параметры посетителей
- SHD Параметры источников данных
- SHD Параметры дат
Выше мы постарались рассмотреть существующую схему в контексте сущностей amoCRM, в результате чего таблицы были объединены в отдельные группы. В тоже время необходимо помнить о том, что разные сущности amoCRM могут быть связаны друг с другом, к примеру сделка с контактом. Все эти связи уже существуют в базе данных, и могут быть перенесены в Power BI, а реализуются они все через те же самые таблицы фактов.
Загрузка данных
После выбора необходимых для выгрузки таблиц они автоматически загружаются в Power BI. Важной особенностью является тот факт, что необходимость в дополнительной обработке данных в Power Query при использовании представлений, сведена к минимуму. В случае загрузки данных из amoCRM он нам будет полезен только в двух случаях:
- Отбор актуальных записей. Некоторые из таблиц хранят в себе как актуальную информацию, так и устаревшую, что иногда может быть полезно для анализа изменений с течением времени. Подобного рода анализ достоин отдельного рассмотрения, а в данном случае мы воспользуемся только актуальными данными, для чего в Power Query необходимо отфильтровать все данные в таблице по значению TRUE столбца «Признак актуальности записи». Например, такое поле содержится в представлении «AMO Дополнительные параметры сделок».
- Преобразование пользовательских полей. Система amoCRM позволяет создавать свои собственные поля для стандартных сущностей. Набор этих полей заранее не известен и в каждом отдельном случае отличается от других. Поэтому для хранения их в реляционной базе данных используется метод при котором каждое поле хранится в отдельной записи таблицы и содержит информацию о поле и его значении. С данными в таком виде работать не очень удобно и поэтому мы воспользуемся Power Query для того чтобы преобразовать строки в столбцы.
Несмотря на то что Power Query довольно удобный и функциональный инструмент для сбора и обработки данных, мы рекомендуем стараться минимизировать его использование. Именно для этого данные при загрузке сервис в БД подвергаются предварительной обработке и хранятся в виде наиболее удобном для загрузки и дальнейшего использования в Power BI.
Связи
После того как мы выбрали необходимые таблицы и загрузили их в Power BI встает другой вопрос: как сделать так чтобы данные из разных таблиц использовались как единое целое? Для этого существуют связи.
Прежде всего стоит остановиться поподробнее на том, что вообще такое связь между таблицами и какая она бывает в Power BI. Когда модель содержит несколько объектов, информация хранится в нескольких таблицах и связывается через отношения. В табличной модели отношение всегда связывает две таблицы и основывается на одном столбце.
В PowerBI реализованы два типа связи:
- один к одному (1:1) — обе сущности могут иметь только одну запись по обе стороны от отношения. Каждое значение первичного ключа может либо относится только к одной записи в связанной таблице, либо вообще не иметь связки.
- многие к одному (*:1) — это наиболее распространенный тип, который используется по умолчанию. Это означает, что у столбца в одной таблице может быть несколько экземпляров значения, а у другой связанной таблицы есть только один уникальный экземпляр значения. Как правило, таблица фактов и таблица измерений связаны между собой таким отношением, причем у последней обычно связь «один».
Совсем недавно в Power BI в стадии бета-тестирования появилась возможность создавать связь многие ко многим (*:*). В таком случае ни одна таблица не содержит уникальных значений. Для таких отношений можно устанавливать, какая таблица фильтрует другую таблицу, или иметь двунаправленную фильтрацию, где обе таблицы фильтруют друг друга. В новой версии Power BI Desktop значение по умолчанию задано как «многие ко многим», когда определено, что ни одна таблица не содержит уникальных значений для столбцов в отношении.
Не стоит также забывать и про кросс-фильтрацию, которая поможет в дальнейшем при построении отчета. Существует два типа:
- двунаправленная — направление фильтрации, в котором обе таблицы обрабатываются так, будто они представляют из себя одну таблицу. Мы советуем использовать ее при работе с осторожностью, и только в тех случаях когда это действительно необходимо.
- однонаправленная — направление фильтрации, при котором фильтрация производится только в той таблице, из которой выходит это направление.
Используя Power BI, можно создать связь между двумя таблицами, перетащив идентификатор одной таблицы на столбец другой таблицы, по которому и происходит связь.
После загрузки данных в Power BI, он автоматически попытается определить связи между существующими представлениями (если только эта функция не отключена в настройках приложения). Поиск производится до момента обнаружения столбцов со схожими названиями, однородного пересекающегося набора значений и одинакового типа данных. Отметим, что связи могут быть не обнаружены между таблицами в том числе потому, что в них нет никаких данных. В таком случае необходимо вручную проставлять все возможные связи между сущностями. Также стоит заметить, что не все связи могут быть активированы. Если существует двунаправленная кросс-фильтрация между таблицами, то она может препятствовать подключению третьей таблицы к этим обеим сущностям, поскольку в данном случае они воспринимаются как единая таблица, а двойной связи к одной и той же таблице существовать не может. Автоматическое определение связей позволяет сэкономить время и не тратить ресурсы на создание связей между сущностями. С другой стороны, бывают ситуации, когда связи определяются неверно, либо же PowerBI их вообще не находит, как в случае, описанном выше.
Следует отметить одну интересную особенность, которая по сути является правилом, по которому Вы можете проверять корректность построенной модели данных. Практически все связи на концах «Снежинки» относятся к типу «один к одному», в то время как все таблицы фактов связаны между своими соседями по принципу многие к одному. Также все граничные таблицы модели данных имеют двунаправленную кросс-фильтрацию; в свою очередь в центре схемы все связи однонаправленные.
Оформление модели
После загрузки данных в Power BI может сложиться ощущение, что модель сильно перегружена. На самом деле, наступает следующий немаловажный шаг – приведение модели в читаемый вид, с которым уже можно работать.
В первую очередь рекомендуем скрыть все вспомогательные столбцы, такие как идентификаторы, используемые для связи различных таблиц между собой. Эти столбцы не используются в визуализациях, и поэтому они будут только мешаться при работе над отчетом.
Далее пришло время поработать с мерами. Мера — это выражение на DAX, которое вычисляет необходимый показатель. У нас уже есть показатели, хранящиеся в таблицах фактов, и Power BI позволяет их использовать в визуализациях и даже выполнять над ними простейшие действия, такие как подсчитать сумму, среднее и т.д., но увлекаться этим не стоит, так как это может легко привести к путанице. Оборачивание показателей мерами позволяет иметь четкое представление, какое именно значение мы получили в независимости от настроек визуализации.
Количество задач = DISTINCTCOUNT ( 'AMO Задачи'[Идентификатор задачи] )
Кроме этого, если Вы решите использовать Excel для работы с моделью созданной в Power BI, что иногда бывает очень удобно, то вам не обойтись без мер, так как Excel не воспринимает числовые значения как показатели, а работает только с мерами.
После этого, можно скрыть все поля, которые были обернуты в меры. В результате, мы очистим рабочую область от лишних элементов.
Тиражирование
В конце проделанной работы возникает логичный вопрос: требуется ли мне повторно проделывать описанные выше действия, если я хочу использовать построенную модель на других данных? Конечно же нет! В заключительной части нашей статьи хотелось бы остановиться на шаблонах в Power BI. Файл формате .pbit — это шаблон, создаваемый Power BI Desktop, который содержит запросы, настройки визуализации, модели данных, отчеты и другие данные, добавленные пользователем. Подобный формат позволяет создавать отчеты с целью их повторного использования. Вы можете создать файл PBIT, выбрав:
Файл → Сохранить как или Файл → Экспорт → Шаблоны Power BI
При запуске этого файла изначально данные не загружены в модель, что позволяет загрузить новую необходимую информацию и использовать уже готовые меры и отчетность. Необходимо также не забыть вывести наименования сервера и базы данных в отдельные параметры, чтобы в дальнейшем можно было загрузить данные из другого хранилища. Таким образом, сохранив нашу модель в качестве шаблона, мы создали файл, который можем в дальнейшем использовать для других проектов, связанных с amoCRM.
Таким образом, в рамках данной статьи мы постарались осветить не только основные шаги по загрузке и построению модели данных для amoCRM, но и описать, почему мы используем Azure SQL, а также рассказали некоторые теоретические основы, которые необходимо сегодня знать при работе в Power BI. Конечно, данная статья не способная раскрыть все тонкости построения модели, поскольку для каждой реальной задачи они свои.
Следующая наша статья будет посвящена построению отчетности в Power BI на базе рассмотренной модели.
Если у Вас остались вопросы или комментарии, пожалуйста, напишите их под этой статьей. Надеемся, что прочитанный материал окажется для Вас полезным!
