Мы все с вами работаем с множеством информационных и прочих систем, которые накапливают данные, верно? Вы, наверняка, задумывались о том, какое большое количество сущностей и взаимосвязей между ними может быть реализовано в рамках только одной системы? Думаю, что задумывались – потому как функционал моделирования, распределения данных по таблицам и работа со связями, является основой для любого отчета Power BI. А у специаилстов есть свойство переносить накопленый опыт на окружающие сферы;)
В этой статье мои коллеги Сергей Казицин и Александра Салих рассмотрели значимость связей между таблицами в Power BI и объяснили, почему правильное их построение является ключевым фактором успеха в работе с данными. И все это с практическими примерами на основании структуры выгрузки из CRM и рекомендациями для эффективного использования связей в Power BI.
Связи и модели данных
Как уже было сказано в нашей предыдущей статье про основы моделирования данных, связь между таблицами в Power BI – это способ объединения данных из разных таблиц на основе общего (ключевого) поля. Она позволяет объединять данные из различных таблиц для создания связанной модели данных. После установки связи мы можем использовать поля из связанных таблиц в одном запросе или отчете. Это позволяет нам легко анализировать и визуализировать данные, связывать их и получать более полное представление о нашей информации.
На практике рекомендуют применять два основных типа организации таблиц – Звезда (Star Schema) и Снежинка (Snowflake Schema). В вышеуказанной статье мы уже разбирали основные отличия Звезды от Снежинки и отметили, что схема Звезда является наиболее предпочтительной. Ее преимущества состоят в том, она обладает простой и понятной структурой, что упрощает проектирование и понимание связей между таблицами. Кроме того, схема Звезда легко масштабируется при добавлении новых измерений или фактов, не требуя изменений в существующей структуре.
Несмотря на преимущества схемы Звезда, схема Снежинка может быть полезна в определенных сценариях, особенно когда требуется более глубокая нормализация данных или есть необходимость в дополнительных уровнях детализации. Однако, для большинства простых и средних аналитических задач, Звезда является более простой, эффективной и предпочтительной.
Напомним, чтобы правильно связать таблицы по схеме Звезда, необходимо определить таблицы измерений и таблицы фактов, и далее таблицы измерений связать с таблицами фактов по общему ключу.
Кроме правильной организации таблиц, важно выбрать подходящий тип связи между таблицами. Тип связи определяет, как данные в одной таблице связаны с данными в другой таблице. Например, тип связи может указывать, что в приведенной на рисунке ниже схеме, каждая связь в таблице leads может иметь несколько связанных записей в таблице leads_notes и leads_tags, но только одну связанную запись в таблице leads_attributes.
Типы связей между таблицами включают один к одному (One-to-One), один ко многим (One-to-Many) и многие ко многим (Many-to-Many). Правильно определенные связи обеспечивают точность и согласованность данных при выполнении запросов и фильтрации.
Принципы связывания
Рассмотрим небольшой пример построения связей между таблицами на основе базовой выгрузки данных сервиса myBI Connect из источника amoCRM. Выберем для загрузки несколько таблиц:
- Таблицы измерений и атрибутов измерений
- users;
- leads;
- leads_tags;
- leads_attributes;
- leads_notes.
2. Таблица фактов
- leads_facts.
После подключения к источнику данных и выгрузки таблиц, Power BI автоматически определил связи между таблицами.
Рассмотрим, на сколько корректно…
Первый пример – это таблица leads (измерения) и таблица leads_facts (факты).
Power BI указал связь “один к одному”. Каждая сделка имеет один уникальный набор фактов, поэтому данный выбор кажется логичным. По каким полям проводится соединение? Как мы писали выше, соединение производится по ключевым полям. Существует три типа ключей:
- суррогатный ключ;
- натуральный ключ;
- внешний ключ.
Суррогатный ключ представляет собой уникальный идентификатор, который используется в моделировании данных. В отличие от натурального ключа, который основывается на реальных данных, суррогатный ключ создается искусственно и не имеет смысловой связи с данными. Он обычно представляет собой числовое значение, которое автоматически генерируется базой данных при добавлении новой записи. Единственное предназначение суррогатного ключа — служить первичным ключом.
Первичный ключ – это специальный столбец таблицы, предназначенный для уникальной идентификации всех записей таблицы. Он должен содержать уникальное значение для каждой строки данных и не может содержать нулевые значения. Первичным ключем является либо существующий столбец таблицы, либо столбец, который специально создается базой данных в соответствии с определенной последовательностью. В нашем примере поле id таблицы leads является суррогатным ключом, которое определяет идентификатор записи в таблице.
Натуральный ключ может быть любым значимым атрибутом или комбинацией атрибутов, которые однозначно идентифицируют каждую сущность в таблице. Например, в таблице leads поле lead_id является натуральным ключом, так как оно является уникальным идентификатором сделки в amoCRM.
Внешний ключ связывается с первичным ключом другой таблицы. Как видим, в таблице leads_facts поле leads_id является внешним ключом, которое как раз подходит для связи с таблицей leads.
Соответственно, чтобы связать приведенные выше таблицы, необходимо провести связь от поля leads.id к полю leads_facts.leads_id. После соединения по краям связи мы видим цифры один, что и указывает на связь “один к одному”.
Кроссфильтрация
Важным параметром связи является направление кроссфильтрации.
Направление кроссфильтрации относится к способу взаимодействия и фильтрации данных между связанными таблицами. В Power BI существует два основных направления кроссфильтрации:
- Однонаправленная кроссфильтрация (Простое): это обычный фильтр с направлением от одного ко многим в связи между таблицами. В нашем случае это связь между таблицами фактов и измерений, где таблица измерений является срезами, которые фильтруют таблицы фактов.
- Двунаправленная кроссфильтрация (Оба): В этом случае фильтрация применяется на обеих сторонах связи таблиц, в результате обе связанные таблицы взаимно фильтруются друг другом.
Поскольку в нашем случае между приведенными выше таблицами имеется тип связи “один к одному”, при данной связи единственным возможным направлением перекрестной фильтрации является “Оба”. При данном типе связей выбор других вариантов не позволяется самим Power BI, поэтому здесь ошибиться невозможно.
Рассмотрим соединений таблицы leads_facts с таблицей измерений users.
Power BI правильно обнаружил связь между таблицами leads_facts и users и провел связь “многие к одному” и выбрал простое направление перекрестной фильтрации. Это объясняется тем, что для одного пользователя может быть несколько сделок.
Далее, рассмотрим пример соединения таблиц leads и таблиц атрибутов измерений – leads_tags, leads_attributes, leads_notes.
На схеме видим, что измерения в таблицах разделены на дополнительные нормализованные таблицы, это указывает на схему Снежинка. Как мы уже говорили выше, схема Звезда предпочтительнее, но в данном случае у нас есть множество атрибутов измерений, соответственно правильнее поместить их в отдельные таблицы и соединить с измерениями.
Таблицы leads_notes, leads_tags могут содержать сразу несколько атрибутов для измерения leads. Поэтому здесь необходимо использовать связь “многие к одному” на основе внешнего ключа leads_id таблиц атрибутов и суррогатного ключа id таблицы измерения. Но, Power BI неправильно определил тип связи и выбрал связь “один к одному” для таблицы leads_notes. Это произошло вследствие того, что в нашем случае таблица leads_notes не содержит данных и Power BI использовал тип связи по умолчанию.
Несмотря на то, что однонаправленная кроссфильтрация используется в большинстве случаев, в нашем случае необходим двунаправленный фильтр. Без такого фильтра мы не сможем использовать таблицы атрибутов в качестве дополнительных срезов для формирования необходимой выборки на уровне таблицы фактов. Таблица leads_attributes является сводной, поэтому может содержать только одну запись для каждой записи из таблицы leads, следовательно в данном случае нам необходимо использовать тип связи “один к одному”. Проведем необходимые преобразования. После преобразования схема будет выглядеть как показано на рисунке.
Если мы посмотрим внимательно, то заметим, что в случае фильтрации таблицы leads_facts таблицей users, у нас будет производится фильтрация так же и таблицы leads, из-за наличия двунаправленного фильтра. Далее, фильтрация также будет распространяться на присоединенные таблицы leads_tags, leads_notes, leads_attributes. Ниже показано направления распространения фильтра.
Будет производится фильтрация, которая абсолютна не предусмотрена структурой наших данных, и может привести к непредсказуемым последствиям и нарушением логики работы данных.
Кроме этого, появится еще одна проблема. Попробуем соединить таблицу leads с еще одной таблицей фактов calls_facts.
Power BI обнаружил связь между этими двумя таблицами. Он выбрал связь по умолчанию “один к одному”, так как в таблице calls_facts у нас нет данных. У одной сделки может быть несколько звонков, поэтому необходимо использовать связь “многие к одному” и прямое направление кроссфильтрации. Вдобавок, нам необходимо соединить таблицу calls_facts с таблицей users. Power BI в данном случае выбрал связь “один к одному”, однако это неверно. Один пользователь может делать несколько звонков, поэтому связь должна быть “многие к одному”. Так же мы видим, что проведенная Power BI связь неактивна.
Если мы попробуем активизировать связь, то получим следующее предупреждение:
Видим, что двунаправленная фильтрация между таблицами leads и leads_facts может вызывать неоднозначные связи. Поменяем связь между этими двумя таблицами на “многие к одному”, при этом выберем простое направление перекрестной фильтрации. Как видим на картинке ниже, неактивная связь теперь активируется. Также, исключилась нежелательная фильтрация таблиц атрибутов измерений.
Несмотря на то, что в структуре базы данных, на основе которой построена наша модель, между таблицами leads и leads_facts проведена связь “один к одному”, в контексте Power BI она не подходит из-за двунаправленности фильтра. После изменения направления фильтрации, мы сделали более правильную модель, которая обеспечит корректность выводимых данных.
Хотели обратить ваше внимание на тот факт, что в случае работы с представлениями, а не таблицами, корректность автоматического определения связей Power BI снижена. Принцип работы Power BI заключается в том, что в случае с таблицами он проверяет связи, заложенные в базе данных. После загрузки на основе данных определяется их кратность и фильтрация. В случае с представлениями, первый шаг (определение связей на уровне базы данных) отсутствует, так как между представлениями нет связей. Поэтому автоопределение связей включается уже после загрузки на основании одинаковых столбцов в разных таблицах и совпадающих данных, с попутным определением кратности и фильтрации.
Рекомендации по построению связей
Исходя из всего вышеописанного, хочется выделить несколько рекомендаций по правильному построению связей между таблицами в Power BI:
- Тщательно проанализируйте структуру данных и связи между таблицами перед созданием модели данных. Понимание взаимосвязей между таблицами поможет определить правильные ключи связи и типы связей. Лучше не пытаться объединить данные в одну таблицу, а правильно связать таблицы между собой. Предпочтительнее организовать таблицы в схему Звезда. Для тех, кто затрудняется самостоятельно расставить правильные связи между таблицами, мы подготовили уже настроенные модели данных для различных источников на основе базовой выгрузки сервиса mybi connect для Power BI.
- Проверяйте автоматическое определение связей Power BI, а лучше не используйте совсем.
- Убедитесь, что каждая таблица имеет уникальный ключ, который можно использовать для установления связи с другими таблицами. Уникальные ключи обеспечивают точное соответствие записей при связывании таблиц.
- Определите тип связи, который наиболее точно отражает взаимосвязь между таблицами. Различные типы связей, такие как “один к одному”, “один ко многим” и “многие ко многим”, имеют свои особенности и подходят для разных сценариев.
- Убедитесь, что поля, используемые для связи между таблицами, имеют одинаковые типы данных. Это гарантирует правильное сопоставление записей при выполнении запросов.
- При создании связей обратите внимание на производительность модели данных. Лучше избегать создания избыточных или ненужных связей между таблицами.
- Обдумайте направление кроссфильтрации. Если необходимо использовать двунаправленную фильтрацию, старайтесь избежать неоднозначности связей фильтруемых таблиц и излишней фильтрации.
- Перед окончательным развертыванием модели данных проведите тестирование и проверку связей. Убедитесь, что запросы выполняются корректно, данные согласованы и отчеты отображают точные результаты.
Преимущества правильно построенных связей
В заключении подчеркнем важность связей между таблицами в Power BI. Связи являются фундаментальным элементом построения эффективных и гибких моделей данных. Они обеспечивают согласованность и целостность информации, позволяют объединять данные из различных источников и создавать сложные аналитические отчеты. Правильно определенные связи помогают избежать дублирования данных, сократить объем хранимой информации и повысить эффективность обработки данных. Благодаря правильным связям повышается точность результатов анализа и появляется возможность быстро получать нужную информацию. Поэтому рекомендуется провести аудит своих таблиц и проверить правильность связей, чтобы повысить качество вашей модели данных и быть уверенными в корректности расчетов.