Физические и виртуальные связи в DAX

Связь в DAX бывает двух природ: физическая — нарисованная в модели линия — и виртуальная — собранная на лету прямо в мере. Они решают одну задачу (профильтровать одну таблицу значениями другой), но устроены принципиально по-разному. Понимание разницы отделяет крепкого моделиста от новичка. Разбор опирается на статью Марко Руссо и Альберто Феррари SQLBI о физических и виртуальных связях и материал Data Mozart по DP-500.

Как работает физическая связь

Физическая связь — это линия между таблицами, где одна сторона имеет уникальные значения в ключе. Когда вы фильтруете справочник, движок переносит список отфильтрованных значений ключа на соответствующий столбец факта — и делает это на уровне storage engine, максимально эффективно.

Выручка = SUMX ( Продажи, Продажи[Количество] * RELATED ( Товары[Цена] ) )

Здесь RELATED работает только благодаря физической связи: движок «знает» соответствие строк. Это и есть expanded table — внутреннее представление, в котором факт уже «расширен» столбцами связанных измерений. Оно доступно только физическим связям.

Когда физическая связь невозможна

Не всегда получается. Физическая связь требует уникального ключа хотя бы с одной стороны, а это не всегда выполнимо:

• ключ не уникален (повторы, много пустых значений);
• таблицы на разной грануляции (бюджет по месяцу — продажи по дню);
• связь по двум-трём столбцам сразу, которой движок не поддерживает физически;
• связь нужна лишь изредка, и тащить её в модель нежелательно.

В этих случаях связь делают виртуальной — прямо в мере.

Виртуальная связь: три исторических паттерна

Виртуальная связь применяет значения одной таблицы как фильтр на столбец другой — без линии в модели. За годы накопилось три способа; полезно узнавать их в чужом коде.

1. TREATAS — современный выбор (2017+). Берёт значения и «назначает» их фильтром на целевой столбец:

Выручка (виртуальная связь) =
CALCULATE (
    SUM ( Продажи[Сумма] ),
    TREATAS ( VALUES ( Товары[КодТовара] ), Продажи[КодТовара] )
)

2. INTERSECT (2016+). Пересекает множество значений факта с видимыми значениями измерения:

CALCULATE (
    [Выручка],
    INTERSECT ( ALL ( Продажи[КодТовара] ), VALUES ( Товары[КодТовара] ) )
)

3. CONTAINS + FILTER (легаси, ещё с 2010). Самый «ручной» и медленный:

CALCULATE (
    [Выручка],
    FILTER (
        ALL ( Продажи[КодТовара] ),
        CONTAINS ( VALUES ( Товары[КодТовара] ), Товары[КодТовара], Продажи[КодТовара] )
    )
)

Почему виртуальная связь дороже

Это не «такой же фильтр другими словами». Разница в движке:

• Физическая связь фильтрует в storage engine напрямую.
• Виртуальная требует, чтобы formula engine сначала получил списки значений обоих столбцов и прогнал внутренние циклы — больше storage-запросов, больше работы FE.

На маленьких справочниках разница незаметна. На больших фактах и широких ключах виртуальная связь заметно медленнее.

Физическая vs виртуальная: шпаргалка

Подводные камни

• Грануляция. Столбцы по обе стороны виртуальной связи должны быть совместимы по уровню — иначе результат неверный (задвоение или потери).
• Прозрачность. Виртуальной связи нет на схеме модели — её видно только в коде меры. Через полгода это легко забыть; комментируйте.
• Лишние пустые значения в ключе ломают и физическую связь — чистите ключи до загрузки.

План-факт через виртуальную связь

Практический случай: бюджет по месяцам и категориям, не связанный с моделью. TREATAS по двум полям сразу:

План на период =
CALCULATE (
    SUM ( Бюджет[План] ),
    TREATAS (
        SUMMARIZE ( Продажи, Календарь[ГодМесяц], Товары[Категория] ),
        Бюджет[ГодМесяц], Бюджет[Категория]
    )
)

Дальше % выполнения = DIVIDE ( [Выручка], [План на период] ) — план-факт без единой физической связи с бюджетом.

Что дальше

Природу связей разобрали полностью: физические (быстро, с expanded tables) и виртуальные (гибко, но дороже и без RELATED). Следующий урок — таблица-мост: как заменить m2m двумя нормальными связями «один ко многим».