В Power BI данные об одном процессе или объекте анализа могут быть распределены по нескольким таблицам. Чтобы программа правильно сопоставила информацию из этих таблиц, между ними создают связи.
Для этого нужно понять, какую роль играет каждая таблица в модели, по каким столбцам таблицы можно связать и как между ними должны распространяться фильтры.
Организовать такую модель данных можно с помощью звездной схемы. В ней таблицы разделяют по назначению. Одни хранят данные о событиях, другие содержат информацию об объектах, которые участвуют в этих событиях.
Прежде, чем познакомиться со структурой звездной схемы, разберем ее элементы и по каким принципам они связаны между собой.
Из каких таблиц состоит звездная схема
Звездная схема состоит из двух основных типов таблиц — таблицы фактов и таблиц измерений. Таблица фактов хранит данные о событиях или состояниях, а таблицы измерений содержат характеристики объектов, связанных с этими данными.
Таблица фактов
Таблица фактов хранит данные о событиях или состояниях, которые нужно анализировать. Там могут быть, например, продажи, заказы, платежи, остатки товаров на определенную дату, обращения.
Например, в Power BI таблица Продажи может выглядеть так:
В ней хранятся идентификаторы связанных объектов — товара (ProductID) и клиента (CustomerID), а также числовые показатели — количество проданного товара и сумма продажи.
Также для таблицы фактов важно определить уровень детализации (гранулярность), то есть что означает одна ее строка. В примере выше одна строка соответствует одной позиции заказа. В другой модели одна строка может описывать все продажи определенного товара за день.
Уровень детализации должен быть одинаковым для всех строк таблицы.
Таблицы измерений
Таблицы измерений содержат характеристики объектов, связанных с данными из таблицы фактов. Например, это могут быть товары, клиенты, даты, магазины, сотрудники или регионы.
Допустим, с таблицей Продажи связаны таблицы Товары и Клиенты.
Товары
Клиенты
По каким столбцам связывают таблицы
Таблицы связывают по ключевым столбцам, значения которых позволяют сопоставить строки одной таблицы со строками другой. Обычно для этого используют ключи — значения, по которым можно однозначно определить или сопоставить записи.
Например, в таблице Товары у каждого товара есть свой ProductID:
Значение ProductID здесь должно однозначно идентифицировать товар. Поэтому каждый идентификатор встречается в таблице Товары только один раз.
В таблице Продажи тоже есть столбец ProductID. Но там он показывает, какой товар относится к каждой строке продаж.
Так как один и тот же товар может участвовать в разных продажах, ProductID в таблице Продажи может повторяться.
Например, по значению 101 мы можем визуально определить, что в первой и третьей строках речь идет о товаре «Ноутбук A» из таблицы Товары.
Таким образом, таблицы Товары и Продажи можно связать по столбцам Товары[ProductID] и Продажи[ProductID].
Важно, что столбцы, по которым связывают таблицы, должны иметь одинаковый тип данных. Например, если ProductID в таблице Товары имеет числовой тип, таким же должен быть тип ProductID в таблице Продажи.
Связи между таблицами в звездной схеме
После того как столбцы для связи выбраны, нужно определить кратность связи. Она показывает, сколько строк одной таблицы может соответствовать строкам другой.
Чтобы посмотреть связи между таблицами в Power BI, нужно зайти в «Представление модели».
На схеме таблицы соединены линиями, а символы 1 и * на концах линий показывают кратность связи.
В Power BI есть четыре обозначения кратности:
- «один ко многим» (1 : *),
- «многие к одному» (* : 1),
- «один к одному» (1 : 1),
- «многие ко многим» (* : *).
Чтобы увидеть, по каким именно столбцам создана связь, наведите указатель на линию или выберите ее, чтобы открыть «Свойства».
В Power BI кратность обычно определяется автоматически, но ее можно проверить или изменить в «Свойствах».
Один ко многим
Тип связи «один ко многим» (1 : *) реализует принцип «одной строке таблицы измерений могут соответствовать многие строки таблицы фактов».
Вернемся к таблицам Товары и Продажи. В таблице Товары каждый ProductID встречается один раз, а в таблице Продажи один и тот же идентификатор товара может повторяться. Связь между таблицами можно представить в таком виде:
Товары[ProductID] (1) -- (*) Продажи[ProductID]
Обозначение 1 показывает сторону, где значения связанного столбца уникальны, а * — сторону, где они могут повторяться.
Например, ProductID = 101 встречается в таблице Товары один раз, потому что он обозначает один товар — «Ноутбук A». В таблице Продажи этот же ProductID может находиться в нескольких строках, если товар продавался несколько раз.
Поэтому в связи 1 : * столбец на стороне 1 должен содержать уникальные значения.
Если бы в таблице Товары значение ProductID = 101 встречалось несколько раз, этот столбец нельзя было бы использовать как сторону 1, пока есть дубликаты.
В звездной схеме на стороне 1 обычно находится таблица измерений, а на стороне * — таблица фактов:
Таблица измерений (1) -- (*) Таблица фактов
Например:
Товары (1) -- (*) Продажи
Многие к одному
Связь «многие к одному» (* : 1) описывает ту же зависимость, что и «один ко многим», но таблицы указываются в обратном порядке.
В нашем примере:
Продажи[ProductID] (*) -- (1) Товары[ProductID]
В таблице Продажи один и тот же ProductID может встречаться во многих строках, а в таблице Товары каждому ProductID соответствует только одна строка.
Поэтому одну и ту же связь можно записать двумя способами:
Товары[ProductID] (1) -- (*) Продажи[ProductID]
или:
Продажи[ProductID] (*) -- (1) Товары[ProductID]
То есть 1 : * и * : 1 обозначают одну и ту же связь между таблицами, но в разном порядке.
Один к одному
При связи «один к одному» (1 : 1) значения в связанных столбцах уникальны в обеих таблицах. Каждой строке одной таблицы может соответствовать не более одной строки другой.
Добавим еще одну таблицу — Данные клиентов.
Она связывается с уже существующей таблицей Клиенты:
Клиенты[CustomerID] (1) -- (1) Данные клиентов[CustomerID]
Здесь CustomerID в обеих таблицах встречается только один раз. Клиенту 15 соответствует одна строка с дополнительными данными, клиенту 18 — также одна строка и так далее.
Связи 1 : 1 поддерживаются в Power BI, но встречаются редко. Для классической звездной схемы гораздо характернее связь 1 : *, где одной строке таблицы измерений соответствуют несколько строк таблицы фактов.
Многие ко многим
При связи «многие ко многим» (* : *) одному значению в первой таблице могут соответствовать несколько строк второй, и наоборот. Когда значения в связанных столбцах повторяются с обеих сторон, такую кратность можно реализовать через * : *.
Например:
Таблица A (*) -- (*) Таблица B
Однако отношение «многие ко многим» между объектами необязательно реализовывать прямой связью * : *. Если такое отношение возникает между двумя таблицами измерений, Microsoft рекомендует использовать промежуточную таблицу (bridge table). Она хранит соответствия между объектами, а с исходными таблицами создаются связи 1 : *.
Если промежуточная таблица содержит только идентификаторы связанных объектов, Microsoft называет ее безфактной таблицей фактов (factless fact table).
Например, один клиент может иметь несколько банковских счетов, а один совместный счет может принадлежать нескольким клиентам.
Создадим таблицу Счета, в которой каждый счет хранится один раз:
В таблице Клиенты каждый клиент хранится один раз:
| CustomerID | Клиент |
| 15 | Компания А |
| 18 | Компания Б |
| 22 | Компания В |
Связь между клиентами и счетами зададим через промежуточную таблицу КлиентыСчета. Каждая ее строка содержит пару идентификаторов клиента и счета:
Из нее видно, что у клиента 15 два счета, а счет 502 связан сразу с двумя клиентами.
В модели получатся две связи:
Клиенты (1) -- (*) КлиентыСчета Счета (1) -- (*) КлиентыСчета
Или в одной схеме:
Клиенты (1) -- (*) КлиентыСчета (*) -- (1) Счета
Таким образом, отношение «многие ко многим» между клиентами и счетами представлено через промежуточную таблицу и две связи «один ко многим». Промежуточная таблица хранит пары идентификаторов, которые показывают, какие клиенты связаны с какими счетами.
Как работают фильтры между связанными таблицами
По связям Power BI может распространять фильтры между таблицами. То, в какую сторону может передаваться фильтр, называется направлением перекрестной фильтрации.
Рассмотрим связь:
Товары (1) -- (*) Продажи
Чтобы увидеть, как фильтр проходит по этой связи, в Представлении отчета добавим срез по полю Товары[Категория].
Срез позволяет выбрать одно из значений поля и отфильтровать связанные визуализации на странице.
Рядом добавим таблицу с данными из Продаж: ProductID, CustomerID, количество и сумму. Пока в срезе ничего не выбрано, в ней отображаются все продажи.
Теперь выберем в срезе категорию «Ноутбуки». В таблице Товары к этой категории относится товар с ProductID = 101. Через связь фильтр распространяется на Продажи, поэтому в визуализации остаются только строки с идентификатором 101:
При этом сама исходная таблица Продажи не изменяется. Строка с ProductID = 103 из нее не удаляется. Фильтр определяет, какие данные будут отображаться в визуализациях и учитываться в расчетах.
Направление перекрестной фильтрации задается в свойствах связи.
Для связи 1 : * можно выбрать «Простое» или «Оба».
- При значении «Простое» фильтр распространяется в одном направлении — со стороны 1 на сторону *. В нашем примере из таблицы Товары в таблицу Продажи:
Товары (1) → (*) Продажи
Поэтому выбор категории в таблице Товары может отфильтровать данные о продажах, а фильтр из Продаж в обратную сторону по этой связи не передается.
- При значении «Оба» фильтр может распространяться в обе стороны:
Товары (1) ↔ (*) Продажи
Это двунаправленная фильтрация. Microsoft рекомендует использовать ее только при необходимости, поскольку она может создавать неоднозначные пути распространения фильтров и отрицательно влиять на производительность.
Активные и неактивные связи
Между двумя таблицами в Power BI можно создать несколько связей, но одновременно активной может быть только одна. Именно по активной связи фильтр распространяется по умолчанию. Остальные связи становятся неактивными.
Для примера создадим таблицу Заказы:
И отдельную таблицу Календарь со столбцом Date.
С ней можно создать две связи:
Календарь[Date] (1) -- (*) Заказы[OrderDate]
Она сейчас активна, поэтому обозначена сплошной линией.
Календарь[Date] (1) -- (*) Заказы[ShipDate]
Так как эта связь неактивна, она обозначена пунктирной линией.
Если отчет анализирует заказы по дате оформления, связь с OrderDate делают активной. Связь с ShipDate при этом остается неактивной.
Структура звездной схемы
В звездной схеме в центре находится таблица фактов, а вокруг нее — связанные таблицы измерений.
Например, таблица Продажи может быть связана с таблицами Товары, Клиенты, Календарь и Магазины:
Обычно таблицы измерений находятся на стороне 1, а таблица фактов — на стороне *:
Товары (1) -- (*) Продажи Клиенты (1) -- (*) Продажи Календарь (1) -- (*) Продажи Магазины (1) -- (*) Продажи
Как построить звездную схему в Power BI
Чтобы построить звездную схему, сначала нужно определить структуру данных, а затем создать и проверить связи между таблицами.
Шаг 1 — Определить, что означает одна строка таблицы фактов
Сначала нужно решить, что означает одна строка таблицы фактов.
Например, одна строка таблицы Продажи = одна позиция заказа.
Напоминаем, что все строки одной таблицы фактов должны иметь одинаковый уровень детализации.
Шаг 2 — Выделить таблицу фактов
Определим таблицу фактов Продажи. В ней будет больше столбцов, чем в той, которую мы использовали в примерах выше.
В таблицу Продажи включим идентификаторы связанных объектов и данные о самой операции:
- ProductID
- CustomerID
- OrderID
- DateKey
- StoreID
- количество
- сумма
- скидка
- себестоимость
Тут есть данные, которые нужно анализировать, а также ключи для связи с таблицами измерений.
Шаг 3 — Выделить измерения
Отдельно создадим таблицы с характеристиками объектов:
- Товары
- Клиенты
- Календарь
- Магазины
Если исходные данные находятся в одной большой таблице, разделить и подготовить их можно с помощью Power Query.
Шаг 4 — Проверить ключи
Перед созданием связей проверьте ключевые столбцы:
- ProductID должен быть уникальным в таблице Товары;
- CustomerID — в Клиенты;
- DateKey — в Календарь;
- StoreID — в Магазины.
В таблице Продажи эти значения могут повторяться. Также у каждой пары связанных столбцов должны совпадать типы данных.
Шаг 5 — Создать связи
Перейдите в Представление модели и перетащите ключ из каждой таблицы измерений на соответствующий столбец таблицы Продажи.
Должны получиться четыре связи:
Товары[ProductID] (1) -- (*) Продажи[ProductID] Клиенты[CustomerID] (1) -- (*) Продажи[CustomerID] Календарь[DateKey] (1) -- (*) Продажи[DateKey] Магазины[StoreID] (1) -- (*) Продажи[StoreID]
В результате Продажи окажутся в центре модели, а таблицы измерений — вокруг нее.
Шаг 6 — Проверить свойства каждой связи
Выберите каждую линию в Представлении модели и проверьте в Свойствах:
- связанные столбцы,
- кратность 1 : *,
- направление перекрестной фильтрации,
- активность связи.
Шаг 7 — Проверить модель отчетом
- Создайте таблицу или диаграмму, например с такими столбцами:
категория — Товары[Категория]
сумма — Продажи[Сумма]
- Затем добавьте срез по Товары[Категория]. Например, при выборе «Ноутбуки» в расчет должны попасть только продажи с ProductID = 101.
Если значения и фильтры меняются так, как ожидается, то основные связи модели настроены правильно.
