Связи в Power BI и Power Pivot

Power Pivot – база данных разработанная с нуля и оптимизированная на отчеты и анализ в Power BI, Power Pivot для Excel и SQL Server Analysis Services Tabular. Таким образом, все три продукта используют единую технологию Power Pivot и как следствие применяются одинаковые связи в Power BI и Power Pivot.

В основе Power Pivot – обрабатываемая в памяти база данных с хранением данных по столбцам (column-oriented) Vertipaq. Vertipaq – та основа которая позволяет Power Pivot быстро обрабатывать большие массивы данных и при этом компактно хранить их. Несмотря на то что Power Pivot называется базой данных, он не представляет из себя классическую реляционную базу данных, а следовательно не поддерживает весь функционал реляционных баз. Принцип работы базы данных ориентированной на хранение данных по столбцам, является ключевым для понимания того как же работают такие базы.

Типы связей

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

Один ко многим

Связь «Один ко многим» — основа Power Pivot и, пожалуй, самая используемая связь между данными. Чаще всего в качестве объяснения такой связи приводится пример справочников и имеет отношение к процессу нормализации таблиц. Например в Российской Федерации есть справочник «ОКАТО» (Общероссийский Классификатор Административно Территориальных Образований) что бы нормализовать таблицу в которой содержится адрес можно название населенного пункта заменить на значение из справочника ОКАТО. Таким образом, будут сведены к минимуму ошибки ввода данных в названиях населенных пунктов. В самом справочнике ОКАТО каждый код уникален, а в таблице данных адресов населенный пункт может повторятся.

В принятой терминологии, таблицы справочников принято называть «Измерениями», а таблицы с данными «Фактами», хотя в различной литературе могут использовать и другие названия.

Работая с любой реализацией Power Pivot необходимо помнить что Vertipaq оптимизирован на работу с данными в связи «один ко многим» и стараться придерживаться только такого типа связи.

Один к одному

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

Несомненно что при проектировании реляционных баз данных может возникнуть ситуация когда разумно будет разнести данные по двум таблицам установив между ними связь «один к одному». Но при работе с Power Pivot такой тип связи может только замедлить обработку данных, по этому рекомендуется загружать в модель данных не таблицы, а результат запроса к реляционной базе. Фактический объединяя в результате выполнения запроса данные двух таблиц в одну.

Многие ко многим

При разработке Power Pivot преднамеренно отказались от поддержки связи «многие ко многим». Такой тип связи значительно замедляет обработку данных. И именно по этому использование его в системах аналитической обработки данных представляется нецелесообразным.

Различия в пользовательском интерфейсе

В пользовательском интерфейсе Power Pivot для Excel 2010/2013 есть небольшое отличие от версии Power Pivot для Excel 2016. В версии 2010/2013 связь между двумя таблицами обозначалась точкой со стороны фактов и стрелочкой со стороны измерений. Такой способ отображения вводил некоторых пользователей в заблуждение. По этому в версии 2016 отображение связей было изменено, теперь со стороны фактов показывается звездочка «*», а со стороны измерений цифра «1». Что более соответствует данным, много фактов «*» к одному измерению «1».

Перекрестная фильтрация

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