Здравствуйте. В связи с выходом в mybi connect нового источника данных GetCourse, мы решили посвятить ему цикл статей о моделировании и обработке данных в Power BI с практическими примерами.
В данной статье речь пойдет о загрузке данных из GetCourse и создании базовой модели данных в Power BI. Следующие статьи будут посвящены когортному анализу по данным GetCourse и другим возможностям применения аналитики.
Выгрузка данных из GetCourse
Для начала работы необходимо получить данные из GetCourse. Данные будем выгружать с помощью нашего сервиса mybi connect – платформой бизнес-аналитики для автоматизации получения данных и визуализации. Для получения данных из GetCourse, необходимо добавить его в сервисе как источник данных по имеющейся на сайте mybi connect инструкции. После завершения настройки подключения, необходимо запустить историческую загрузку данных, выбрав напротив источника в колонке “Действия” пункт меню “Загрузить” и указав период для получения данных. По завершению загрузки в базе данных появится набор данных, определенных в базовой выгрузке. Об успешной загрузке можно судить по столбцу “Статус загрузки”, в качестве значения должно стоять “Успешно”.
Кстати, это можно сделать совершенно бесплатно, используя триал, в течение которого можно определиться подходит для вас mybi connect или нет.
В сервисе присутствует функционал выбора таблиц, которые будут загружены или не загружены в структуру базовой выгрузки. Чтобы получить необходимый нам набор данных, надо убедиться, что в базовой выгрузке включены следующие таблицы:
- ‘getcourse_deals_facts’,
- ‘getcourse_payments_facts’,
- ‘getcourse_users_facts’,
- ‘getcourse_deals’,
- ‘getcourse_deals_tags’,
- ‘getcourse_payments’,
- ‘getcourse_users’,
- ‘getcourse_deals_attributes’,
- ‘getcourse_users_attributes’.
На этом работа по настройке выгрузки данных завершена, в дальнейшем сервис будет автоматически подгружать новые или измененные сведения, по умолчанию раз в сутки. Более детально о работе с сервисом mybi connect вы можете узнать в ознакомительном видео.
Данные, которые mybi connect получает из GetCourse, загружаются в облачное хранилище, представляющее собой базу данных. БД располагаются на серверах Яндекса на территории РФ, СУБД PostgreSQL (версия 14).
Преимущества работы с промежуточной базой данных
Возможно, после изложенного выше у читателей возникнет вопрос: почему мы решили подключиться к данным GetCourse через базу данных, а не осуществить прямое подключение к источнику по API с помощью Power BI? Для ответа на этот вопрос, разберемся с особенностям выгрузки данных с помощью API.
API (Application Programming Interface) представляет собой набор определенных правил и инструментов, которые позволяют различным программам взаимодействовать друг с другом. API определяет способы, с помощью которых различные компоненты программного обеспечения могут обмениваться информацией и выполнять операции. К сервису отправляется запрос с интересующими параметрами и показателями в соответствии с правилами API. В ответе приходят данные, однако пользователь может столкнуться с определенными трудностями при их использовании.
При получении ответа от API, данные передаются в формате JSON, который содержит вложенность и разрозненность данных. Поэтому для работы с ними, необходимо будет сначала правильно разобрать их, затем создать для данных корректную модель. На это способны далеко не все пользователи.
Еще одна серьезная проблема – это ограничения на количество запросов и объем данных. При попытке забрать большое количество данных через API, с одной стороны пользователь может быть заблокирован, с другой стороны может потребоваться очень много времени на их получение.
Способ получения данных по API будет удобен, если необходимо получить небольшой объем данных за короткий промежуток времени. Но если требуется более глубокая аналитика, несколько источников данных, а также если нужны данные за весь период, то этот способ получения данных не подойдет.
Выходом в данном случае является инкрементная загрузка, которая лежит в основе сервиса mybi connect. Сервис mybi connect выгружает данные только за недостающий интервал времени, что в значительной мере позволяет снизить нагрузку на API сервиса и ускорить время получения данных.
Структура базы данных
Теперь рассмотрим подробнее структуру данных GetCourse в базе данных и те таблицы, которые мы выгрузили.
Схема выгружаемых данных GetCourse разработана на основе схемы «Звезда». Ранее мы рассматривали концепции схем “Звезда” и “Снежинка”, более подробно изложенные в статье “Основы моделирования в Power BI”.
Данные в базе представлены тремя основными сущностями:
- Заказы,
- Платежи,
- Пользователи.
Заказы
Сущность “Заказы” представлена следующими таблицами:
- ‘getcourse_deals_facts’,
- ‘getcourse_deals’,
- ‘getcourse_deals_attributes’,
- ‘getcourse_deals_tags’.
В данной структуре таблица ‘getcourse_deals_facts’ – таблица фактов, которая соединена с таблицей измерений – ‘getcourse_deals’. В свою очередь, таблица ‘getcourse_deals’ соединена с двумя таблицами измерений ‘getcourse_deals_tags’ и ‘getcourse_deals_attributes’.
Платежи
Сущность “Платежи” представлена следующими таблицами:
- ‘getcourse_payments_facts’,
- ‘getcourse_payments’.
Пользователи
Сущность “Пользователи” представлена следующими таблицами:
- ‘getcourse_users_facts’,
- ‘getcourse_users’,
- ‘getcourse_users_attributes’.
В структуре соединения таблиц для сущности “Пользователи” таблица измерений ‘getcourse_users‘ соединена с другой таблицей измерений ‘getcourse_users_attributes‘.
Общие таблицы измерений
Кроме описанных выше таблиц, специфичных для GetCourse, в структуре данных так же присутствуют общие таблицы измерений:
- ‘general_dates’,
- ‘general_traffic’,
- ‘general_locations’.
Благодаря таким таблицам, можно построить комплексные отчеты и провести сравнительный анализ данных из различных источников. Например, таблица ‘general_traffic‘ содержит данные по источникам, каналам и кампаниям в одном формате для всех источников, где есть данные по трафику. Поэтому можно сделать анализ трафика по разным рекламным кабинетам, например Google Analytics 4, VK Реклама или Яндекс.Директ.
Также необходимо отметить, что сущности могут соединяться между собой. Например, сущность “Заказы” будет связана с сущностью “Пользователь” через связь ‘getcourse_deals_facts‘ – ‘getcourse_users‘.
Таблицы и представления
Кроме таблиц, которые являются физическим хранилищем данных, сервис mybi connect также создает в базе данных представления. Представление являются виртуальной таблицей, созданной на основе выполнения SQL-запроса к одной или нескольким таблицам в базе данных. Одной из ключевых особенностей представлений является то, что они не содержат собственных физических данных, а предоставляют лишь виртуальное представление данных, доступное для запросов и анализа. Они отображают только необходимую пользователю информацию, что улучшает производительность и управляемость модели данных в Power BI.
Сервис автоматически создает двух пользователей: владельца базы данных с полными правами, и пользователя с правами только на чтение к представлениям, созданным на основе существующих таблиц. Рекомендуется использовать пользователя с правами на чтение для исключения случайных изменений данных в базе. Данные по пользователям необходимо получить в разделе “Хранилище” в личном кабинете.
В создании модели данных в Power BI рекомендуется импорт представлений, а не самих физических таблиц, из-за нескольких важных причин. Во-первых, импорт таблиц создает тесную зависимость между структурой базы данных и отчетом, что может стать источником проблем при изменениях в базе данных. Например, если происходит переименование столбца или изменение его типа данных, это требует соответствующих изменений в модели данных Power BI, что может быть трудоемким и повлечь за собой потенциальные ошибки.
Создание представлений для каждой физической таблицы обеспечивает промежуточный слой между физической структурой данных и отчетом. Это упрощает поддержание обратной совместимости при изменениях в базе данных, так как изменения можно вносить в представления, сохраняя при этом стабильность интерфейса Power BI.
Кроме того, автоматически создаваемые представления в Power BI имеют понятные наименования на русском языке, что значительно облегчает взаимодействие с ними в среде Power BI. Это делает процесс моделирования данных более интуитивным и удобным для пользователей, особенно при работе с большим объемом данных или сложными структурами.
Однако, при выгрузке представлений вместо таблиц, пользователь может столкнуться с тем, что Power BI может неправильно определить связи между таблицами в модели данных. Принцип работы Power BI заключается в том, что при выгрузке таблиц вместо представлений, он сначала проверяет связи, заложенные в базе данных. После загрузки на основе данных определяется их кратность и фильтрация. Поэтому при загрузке таблиц, автоопределение связей Power BI будет максимально корректным. В случае с представлениями, первый шаг (определение связей на уровне базы данных) отсутствует, так как между представлениями нет связей. Поэтому автоопределение связей включается уже после загрузки на основании одинаковых столбцов в разных таблицах и совпадающих данных, с попутным определением кратности и фильтрации. Поэтому при использовании представлений, необходимо самостоятельно проверить все связи, чтобы получить корректную модель данных.
В модели данных для GetCourse в Power BI мы будем использовать представления. В описании структуры базовой выгрузки указаны названия представлений для всех таблиц.
Загрузка данных в Power BI
Итак, теперь когда нужные нам данные из GetCourse загружены, необходимо подключиться к хранилищу данных из Power BI с целью загрузки данных.
Однако, перед тем, как начать этот шаг, требуется выполнить некоторые настройки.
- Поскольку серверы, управляемые mybi, имеют ограничение на количество одновременных соединений, при попытке работы с большим числом соединений пользователь может столкнуться с ошибками при получении данных. Для избежания данной ситуации рекомендуем отключить параллельную загрузку данных в отчете. Для этого в файле отчета в Power BI необходимо зайти в меню “Файл” -> “Параметры и настройки” -> “Параметры”. В разделе “Текущий файл” выбрать пункт “Загрузка данных”, и в параметре “Параллельная загрузка таблиц” выбрать значение “Один (отключить параллельную загрузку”).
- В дополнение к первому пункту рекомендуем также отключить автоматическое определение связей, чтобы пользователь мог самостоятельно проставить связи. Для отключения необходимо в файле отчета в Power BI зайти в меню “Файл” -> “Параметры и настройки” -> “Параметры”. В разделе “Текущий файл” выбрать пункт “Загрузка данных”, и в параметре “Связи” отключить пункт “Автоматически искать новые связи после загрузки данных”.
- В дополнение к предыдущим двум настройкам, необходимо также отключить создание автоматических даты и времени, так как в модели будет создан собственный календарь. Для отключения необходимо зайти в меню “Файл” -> “Параметры и настройки” -> “Параметры”. Галочки снимаем в параметре “Загрузка данных”, как и глобальных, так и для текущего файла.
После необходимых настроек, приступим к подключению к хранилищу. Для этого в Power BI в меню “Главная” надо выбрать “Получить данные” -> “Другие”. В открывшемся окне выбрать “База данных” -> “База данных PostgreSQL” и нажать на кнопку “Подключить”.
В открывшемся новом окне необходимо ввести значения для полей “Сервер” и “База данных”. Эти значения указываются согласно разделу “Хранилище” в личном кабинете. Порт “6432” необходимо добавить в строку имени сервера:
После подключения к хранилищу, пользователю будет представлен выбор необходимых представлений и таблиц с данными GetCourse. Представления, относящиеся к GetCourse обозначены префиксом GC, таблицы – префиксом getcourse, у других источников данных свои префиксы.
В соответствии с описанным выше, в окне выбора необходимо выбрать соответствующие представления. А также, общие таблицы измерений с префиксом SHD – Параметры дат, Параметры источников данных, Параметры трафика, Параметры местоположений.
После выбора представлений, первым шагом необходимо провести преобразования данных в редакторе Power Query. Отметим, что при использовании представлений, необходимо провести весьма небольшое количество преобразований, так как при создании представлений в базе данных уже были проведены некоторые преобразования. Вот некоторые рекомендации обработки данных, относящихся к базовой выгрузке данных GetCourse:
- Рекомендуем отфильтровать внутренние идентификаторы, значения которых равны 0, так как данная фильтрация помогает исключить пустые записи. Это применимо к таблицам измерений ‘GC Параметры заказов’, ‘GC Параметры платежей’, ‘GC Параметры пользователей’. Нужно просто отфильтровать нулевые значения непосредственно в фильтре столбца.
- В GetCourse пользователям доступно создание своих пользовательских полей. Это может быть поле для записи каких-то дат, пометок и любой другой информации, которую пользователь хотел бы сохранить. В нашем случае мы выгружаем две таблицы с пользовательскими полями – ‘GC Дополнительные параметры заказов’ и ‘GC Дополнительные параметры пользователей’. После загрузки таблицы представлены с заранее неизвестным набором строк, которые содержат информацию о поле и его значении. Работать с данными в таком формате может быть неудобным, поэтому мы воспользуемся инструментом Power Query “Столбец сведения” для преобразования строк в столбцы.
Необходимо оставить в таблице всего три столбца – например в случае с пользователями:
- ‘GC Дополнительные параметры пользователей‘[Идентификатор пользователя];
- ‘GC Дополнительные параметры пользователей‘[Название];
- ‘GC Дополнительные параметры пользователей‘[Значение].
Далее необходимо выбрать столбец ‘GC Дополнительные параметры пользователей‘[Название], и во вкладке “Преобразование” выбрать функцию “Столбец сведения”. В открывшемся окне выберем столбец ‘GC Дополнительные параметры пользователей‘[Значение] в качестве столбца значений. В расширенных параметрах выберем “Не агрегировать” в качестве функции агрегированного значения. Необходимо также убедиться, что в качестве имен для новых столбцов используется столбец ‘GC Дополнительные параметры пользователей‘[Название], о чем подтверждает верхняя надпись окна “Столбец сведения”.
В результате таблица будет иметь столбец для каждой пользовательской строки, что позволит легче определить значение каждого параметра.
Построение модели данных в Power BI
Следующим шагом после загрузки предварительно обработанных данных является создание модели данных. В Power BI модель данных представляет собой структуру, которая определяет, как данные организованы, связаны и хранятся в вашем отчете. Это включает в себя таблицы, поля и связи между таблицами.
О связях между таблицами, существующих в Power BI, и их роли при моделировании, мы уже подробнее писали в данной статье. Напомним, что связи бывают следующих типов:
- один к одному (1:1) – при данном типе связи каждой записи в одной таблице соответствует ровно одна запись в другой таблице;
- один ко многим (1:*) – это самый распространенный тип связи. Он подразумевает, что каждой записи в одной таблице соответствует одна или более записей в другой таблице. Например, один пользователь может иметь много заказов. Этот тип связи отображается в Power BI как “1” рядом с таблицей, от которой исходит связь, и “*” рядом с таблицей, к которой связь направлена. В обратном случае применяется тип связи – многие к одному (*:1). В большинстве случаев таблица фактов и таблица измерений связаны между собой таким типом связи, где таблица фактов находится на стороне “многие”, а таблица измерений на стороне “один”;
- многие ко многим (*:*) – этот тип связи указывает на то, что множество записей в одной таблице может соответствовать множеству записей в другой таблице, и наоборот. Power BI поддерживает такие связи, но чаще используется промежуточная таблица (мост), которая устанавливает отношение многие ко многим между двумя таблицами.
Правильно определенные связи обеспечивают точность и согласованность данных при выполнении запросов и фильтрации.
Связи между таблицами создаются путем перетягивания ключевого поля от одной таблице к соответствующему ключевому полю другой таблицы. Так же связи можно создать с помощью вкладки “Управление связями” на странице “Представление модели”.
В дополнении к типу связи, в свойствах каждой связи необходимо выбрать направление кроссфильтрации. В Power BI существует два основных направления кроссфильтрации:
- Однонаправленная кроссфильтрация (Простое направление) – это обычный фильтр с направлением от одного ко многим в связи между таблицами. В нашем случае это связь между таблицами фактов и измерений, где таблица измерений является срезами, которые фильтруют таблицы фактов;
- Двунаправленная кроссфильтрация (Оба направления) – в этом случае фильтрация применяется на обеих сторонах связи таблиц, в результате обе связанные таблицы взаимно фильтруются друг другом.
Параметр “Активировать связь” используется для активации связей. Если параметр принимает значение “Да”, это означает, что связь активирована и используется для автоматического объединения данных в визуализациях и отчетах. Когда связь не активирована (параметр установлен в “Нет”), Power BI не будет автоматически использовать эту связь при запросах данных и в создании отчетов. Придется вручную управлять этой связью, применять ее в необходимых местах и решать, как она влияет на анализ данных в отчетах. Чуть ниже мы рассмотрим пример неактивной связи.
Объединение таблиц для создания модели данных
В модели данных GetCourse в большинстве случаев таблицы фактов будут связаны с таблицами измерений и таблицами общих измерений активной связью “многие к одному” с однонаправленной кроссфильтрацией. Но для некоторых таблиц связи будут иными. Рассмотрим данные случаи.
Связь между ‘GC Параметры заказов‘ и ‘GC Теги заказов‘ является двунаправленной в связи с тем, что нам необходимо фильтровать строки ‘GC Параметры заказов‘ тегами из таблицы ‘GC Теги заказов‘. В случае однонаправленной связи, фильтрация проходила бы только от ‘GC Параметры заказов‘ к ‘GC Теги заказов‘.
Далее для GetCourse мы будем реализовывать когортный анализ, в котором мы разобьем пользователей по двум когортам – по дате регистрации и дате первой покупки. Анализ будет построен на основе таблицы ‘GC Платежи‘. В соответствии с этим, нам будут необходимы две даты – ‘GC Платежи'[Дата оплаты] и ‘GC Платежи'[Дата регистрации пользователя]. Для синхронизации этих дат, нам необходимо соединить их с таблицей ‘SHD Параметры дат‘. Отметим, что в нашей модели используется календарь, созданный программно. Более подробно о том, почему мы советуем такой подход, можно прочитать в этой статье.
Столбец ‘GC Платежи'[Дата оплаты] соединен с этой таблицей с помощью таблицы ‘SHD Связь календарей‘ через столбец ‘GC Платежи'[Идентификатор даты]. То есть получается, что таблица ‘GC Платежи‘ уже имеет одну активную связь с таблицей ‘SHD Параметры дат‘. Соответственно, если мы попробуем провести связь между ‘GC Платежи'[Дата регистрации пользователя] и ‘SHD Параметры дат'[Дата], то связь получится неактивной, так как Power BI не допускает иметь более одной активной связи между одними и теми же таблицами. Для нас единственным вариантом работы с неактивной связью будет ее активация во время вычисления необходимых мер. Подробнее об этом мы расскажем в следующей статье, которая будет посвящена когортному анализу.
После создания всех необходимых связей мы получим модель данных на основе GetCourse.
Оформление модели данных
После объединения таблиц наступает важный этап – необходимо привести модель к читаемому виду, который будет удобен для последующей работы.
- На этом этапе рекомендуется продумать какие поля можно скрыть, чтобы немного разгрузить модель и оставить для пользователя отчета только нужные поля. Например, лучше скрыть идентификаторы в таблицах, которые используются для установления связей, так как они не участвуют в визуализациях.
- Необходимо проверить, не установлено ли суммирование тех полей, для которых оно не актуально. Это может встречаться в таких полях как идентификаторы, номера, индексы и других. В таком случае рядом с полем в таблице появляется значок суммы. Для того, чтобы убрать суммирование поля, следует выбрать поле в таблице в представлении модели. В окне “Свойства” поля открыть вкладку “Дополнительно”, и в параметре “Суммировать по” выбрать “Нет”.
- Так же, советуем вместо показателей использовать меры на их основе, а сами показатели скрыть. Оборачивание показателей мерами позволяет иметь четкое представление, какое именно значение мы получили в независимости от настроек визуализации.
К примеру, рассмотрим показатели таблицы ‘GC Платежи’ – ‘GC Платежи'[Комиссии], ‘GC Платежи'[Получено] и ‘GC Платежи'[Сумма]. Рассчитаем меры для всех трех показателей:
Платежи: Комиссии := SUM ( 'GC Платежи'[Комиссии] )
Платежи: Получено := SUM ( 'GC Платежи'[Получено] )
Платежи: Сумма := SUM ( 'GC Платежи'[Сумма] )
Можно также добавить меру для расчета количества платежей на основе подсчета количества строк таблицы:
Платежи: Количество := COUNTROWS ( 'GC Платежи' )
Для лучшей наглядности, в названии мер мы используем название таблицы, к которой относится рассчитываемая мера, и, через двоеточие, само название меры.
Необходимо также не забыть о том, что для вновь созданных мер должен быть выбран правильный формат данных. Соответственно, для мер [Платежи: Комиссии], [Платежи: Получено], [Платежи: Сумма] во вкладке “Средства работы с мерами” выберем формат “Валюта”, в качестве символа валюты выберем “₽ Русский (Россия)”. В значении параметра числа десятичных знаком установим 0, чтобы не отображать копейки.
Теперь мы можем скрыть вышеперечисленные показатели из модели.
Расчет других необходимых мер для данной модели данных будет описан в наших следующих статьях из данного цикла. Более подробно с особенностью применения мер в Power BI может почитать здесь.
Кроме этого, если вы дочитали эту статью до конца, похоже вы действительно заинтересованы в саморазвитии, мы это ценим и в качестве бонуса предлагаем уже готовую модель для GetCourse, в которой рализованы все шаги из статьи и даже немного больше.
Стоимость модели снижена до формальной в 1 рубль, ее можно применить вместе с бесплатным триалом mybi connect, посмотреть как выглядит результат и разобраться в нем на наглядном примере. Как и другие наши модели и отчеты, она доступна в mybi market.
В данной статье мы постарались описать шаг за шагом построение модели данных в Power BI на основе базовой выгрузки из GetCourse сервиса mybi connect. Описанный процесс является базовым и не раскрывает всех деталей работы с данными при построении модели. Дополнение и изменение процесса моделирования будет зависеть от задач и целей пользователя. В следующих статьях мы рассмотрим проведение когортного анализа по данным GetCourse, а также построение базового отчета на основе разработанной модели.
