Время по фэн-шуй ч.2. Связи лучше джойнов

1948

Добрый день. Это снова Алан Джанибеков! В прошлой статье про время и дату в Power BI мы договорились о том, что не будем необоснованно раздувать модель данных и показали, как это влияет на потребляемую ей память. Однако, это были только первые шаги. На сколько я вижу, пользователи не всегда понимают суть связей в модели данных и пренебрежительно относятся к ним. А это в свою очередь может привести не только к замедлению работы отчета, но и к некорректным вычислениям…

Я работаю с моделями сервиса myBI Connect, и иногда мне попадаются на глаза отчеты пользователей и вот один из примеров того, как можно усложнить отчет, а конечному потребителю отчёта подарить лишние минуты ожидания: 

Эти этапы не нужны были совсем, всё это решается связями. Вот как это выглядит при оптимальной сборке:

В первой статье я объяснял, что myBI Connect собирает все маркеры дат и создаёт из них справочник, присваивая ID каждой дате. Таблица фактов соединена со справочной таблицей базы данных SHD Связь Календарей, а справочная таблица соединена с календарём и таблицей времени. Как видите, всё очень просто, никакого rocket science. 

В результате нет никакой необходимости “джойнить” таблицы в Power Query. В этом шаге кроется ещё одна опасность. Я видел случай, когда автоматическое обновление отчета не завершалось успешно из-за того, что не прогрузилась таблица дат и шаг объединения в PQ не проходил в облаке. Поэтому старайтесь избегать данной процедуры, тем более сейчас мы разберём, как это всё делать в DAX.

Еще одна особенность модели – некоторые связи неактивны. Пользователи сервиса знают, что в некоторых таблицах есть по 2-3 поля идентификатор даты. Например, идентификатор даты открытия, закрытия и изменения сделки и других сущностей. Догадались для чего? Да, меняя связи, мы можем делать вычисления по дате закрытия или по дате открытия. Например, на какую сумму были закрыты сделки на вчера, если мы оцениваем менеджеров, или, на какую сумму были открыты сделки за прошлый месяц, если мы оцениваем рекламную кампанию.

Перейдем к практике. Импортируем даты открытия и закрытия сделок. Обратите внимание, корректность вычислений всегда опирается на модель данных, в нашем случае связь от поля “SHD связь календарей [Дата]” идет к идентификаторам даты открытия сделки или начала задачи. Поэтому будьте внимательны в расчётах. 

Создаём вычисляемый столбец для даты создания сделки: 

Дата открытия =
RELATED ( 'SHD Связь календарей'[Дата] )

Ничего хитрого. Самые нетерпеливые из вас сейчас побегут создать ещё один столбец для даты закрытия, используя ту же формулу… ну и попадутся 🙂 RELATED работает только с активной связью. Заходить в меню модели и по очереди переактивировать связи мы с вами, конечно, не будем, нужно искать другие пути. И он есть –  это функция USERELATIONSHIP.

Не буду дальше умничать, поскольку это будет плагиатом статьи “итальянцев”, рекомендую изучить – https://www.sqlbi.com/articles/userelationship-in-calculated-columns/

Кратко, USERELATIONSHIP применяется вместе с CALCULATE и для нашего случая будет выглядеть так:

Дата закрытия =
CALCULATE (
    SELECTEDVALUE ( 'SHD Связь календарей'[Дата] ),
    CALCULATETABLE (
        'AMO Сделки',
        USERELATIONSHIP ( 'AMO Сделки'[Идентификатор даты закрытия сделки], 
        'SHD Связь календарей'[Идентификатор даты] ),
        REMOVEFILTERS ( 'SHD Связь календарей' )
    )
)

Последняя функция меняет контекст строки на контекст фильтра.

Если вы пользуетесь моделями данных из myBI Market и не переименовывали поля и прочее – можете смело копировать и вставлять код.

В процессе обсуждений с коллегами, я узнал, что есть и другое решение. Его автором является широкоизвестный в узких кругах Юрий Полищук: 

Дата закрытия =
CALCULATE (
    MAX ( 'SHD Связь календарей'[Дата] ),
    TREATAS (
        'AMO Сделки'[Идентификатор даты закрытия сделки],
        'SHD Связь календарей'[Идентификатор даты]
    ),
    ALL ( 'SHD Связь календарей' )
)

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

Итак, всего 2 функции заменяют кучу телодвижений при создании “джойнов” в Power Query, экономят место и вычислительные мощности. 

В качестве благодарности за терпение к моей графомании, я добавлю в статью формулу для расчёта длительности сделки. Кто-то сразу вспомнил про DATEDIFF, нет, его использовать не получится, так как вы работаете с двумя вычисляемыми столбцами. У вас появится циклическая зависимость – во втором столбце остаются фильтры, которые работают по всей таблицей, в том числе по первому вычисляемому столбцу.

Здесь мы будем обходиться не вычисляемым столбцом, а мерой. НО:
1. Мера использует контекст фильтра!
2. DATEDIFF требует даты в скалярном виде (а не столбец, как будет брать мера).

Очередной фэйл, подумаете вы? Можно было уже давно в PQ всё сделать. Но мы на стороне светлой силы – корректной работы с моделью и эффективным использованием памяти! Если ваш отчет будет работать слишком долго, заказчики лишний раз подумают, перед тем как обращаться к вам повторно…

В Power BI, конечно, все предусмотрел, поэтому мы воспользуемся отличной фичей – контекстным переходом.

Чтобы мера начала принимать строки в качества контекста фильтра, мы просто добавляем CALCULATE в неё, а фильтры через запятую не используем. DAX поймёт, что вы устали, не понимаете, что ему ещё от вас надо, и просто будет фильтровать по строке. Ну а чтобы столбец превратился в одну скалярную дату, мы добавляем функции MAX или MIN. Эффект будет одинаковым: 

Длительность =
CALCULATE (
    DATEDIFF (
        MIN ( 'AMO Сделки'[Дата открытия] ),
        MAX ( 'AMO Сделки'[Дата закрытия] ),
        DAY
    )
)

Собственно, на этом и закончим. Если вдруг у вас есть вопросы по работе с нашей выгрузкой, или созданием модели данных, описывайте их в комментариях. Постараемся на них ответить.