Время по фэн-шуй. Оптимизация справочника дат в Power BI

1158
Время по фэн-шуй. Оптимизация справочника дат в Power BI

Время по фэн-шуй. Оптимизация справочника дат в Power BIДобрый день, Я — Алан Джанибеков, штатный разработчик шаблонных отчётов и моделей данных Power BI на базовой выгрузке сервиса mybi connect.

Периодически я решаю вопросы с работой отчетов пользователей и самое частое обращение — «отчёт перестал грузиться или долго обновляется». На моей практике, был всего 1 случай, когда это было связано с использованием неоптимальных способов расчета. Все остальные — с некорректной работой с датами.

Поэтому в этой теоретической статье мы решили разобраться, как работать с датами в выгрузке myBI Connect.

Итак, когда вы создаёте проект и производите выгрузку сервис собирает все маркеры времени, которые попадают в БД, и как бы нормализует их. То есть, в таблицах фактов вместо самой даты появляется ссылка на неё в виде идентификатора даты. А справочник дат, выгружается отдельно в виде таблицы general_dates. Подробно, можно посмотреть в документации каждого источника данных, для примера Битрикс24: https://docs.mybi.ru/bitriks24-struktura-bazovoy-vygruzki/

Ещё одной особенностью выгрузки, является использование заглушки в виде конечной даты. Например, если у сделки есть дата создания, но она пока не закрыта, ей в выгрузке присваивается дата 31.12.9999, чтобы не нарушалось требование к заполнению поля в БД. Вот как выглядит эта таблица сразу после импорта в Power BI: 

Время по фэн-шуй. Оптимизация справочника дат в Power BI

На свою беду, при импорте данных, пользователи не обращают внимание на то, что представление таблицы дат “SHD Параметры дат” не может быть использована как таблица дат для Power BI сама по себе. Это справочная таблица из БД, а не календарь. Для полного понимания вот ссылка на справку Microsoft: https://docs.microsoft.com/ru-ru/power-bi/guidance/model-date-tables 

Внимательный читатель заметит требование: в таблице дат не должно быть отсутствующих дат, а в нашем справочнике дат — некоторые могут отсутствовать, потому в эти даты не было событий в системе, из которой вы получаете выгрузку. Например, в Битрикс24 или amoCRM в выходные не было отметок по сделкам.

Так как таблица дат не создавалась, то Power BI создаёт их автоматически. Это внутренняя таблица. Подробно об этой функции можно прочитать здесь:
https://docs.microsoft.com/ru-ru/power-bi/transform-model/desktop-auto-date-time

Ещё одна часто встречающаяся ошибка — это использование формата даты и времени в одном столбце. Их следует держать в разных столбцах. Дело в том, что если дата записана отдельно, то для неё нужно всего 1 значение в справочной таблице. Если дата записана вместе со временем, то значений становится 86400 — на каждую секунду этого дня должна быть строка в справочной таблице. Очевидно, что держать в памяти 1 строку на каждую дату в 86400 раз легче, чем строки на каждую секунду. Настоятельно рекомендую отказываться от подобных решений. А если используете — будьте уверены, что такая точность действительно необходима в вашем случае. 

Теперь рассмотрим к чему приводят эти неточности.

Если мы не убираем заглушку 31 декабря 9999 года, и не создаем календарь сами, мы создаётся внутренний календарь. Те из вас, кто прочитал справку Microsoft про автоматическую дата и время, помнят, что таблица создается между самой ранней и самой поздней датой. Допустим, дата начала 2022 год. Тогда: 9999-2021 = 7978 лет.

На столько лет Power BI создаст автоматический календарь. Умножим это 365,25 (дробная часть для учёта високосных годов) получим 2 913 965 строк.

А теперь еще добавим к этому время в секундах: d одном дне 24*60*60 = 86 400 секунд. И поскольку у нас будут автоматическими и дата, и время, то внутренняя таблица займёт 251 766 532 800 строк.

251 миллиард строк, а казалось бы всего-то календарь дат. Оказывается время очень ценный ресурс, чтобы экономить вычислительное время;)

Поэтому ситуации, когда файл PBIX размером около 3мб занимал около 9гб оперативной памяти не являются редкостью в моей практике решения пользовательских вопросов. Например, один из таких отчётов. Просто сняв галочку “автоматические дата и время”, я снизил размер файла в 3 раза:

Время по фэн-шуй. Оптимизация справочника дат в Power BI

Вот как представлено расходование ресурсов ПК. С автоматической датой и временем:

Время по фэн-шуй. Оптимизация справочника дат в Power BI

После отключения память освободилась:

Время по фэн-шуй. Оптимизация справочника дат в Power BI

Если вы больше доверяете Dax Studio вот скрины из него. До: 

Время по фэн-шуй. Оптимизация справочника дат в Power BI

После:

Время по фэн-шуй. Оптимизация справочника дат в Power BI

Как видите, у нас исчезли две таблицы. Стоит отметить, что вторую таблицу пользователь создал просто дублированием.

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

  1. Снять пресловутую галочку “автоматическое дата и время”. Для этого идём в меню: Файл -> Параметры и настройки -> Параметры. Галочки снимаем в параметре “Загрузка данных”, как и глобальных, так и для текущего файла.Время по фэн-шуй. Оптимизация справочника дат в Power BI2. Убираем заглушку. Для этого заходим в режим Power Query и в таблице general_dates или представлении SHD Параметры дат удаляем строку: 31.12.9999:Время по фэн-шуй. Оптимизация справочника дат в Power BIГалочку нужно снять.

3. Далее я буду описывать создание нового справочника дат так, как делаю обычно из удобства для себя и соблюдения единообразия в моделях и отчётах. Таблицу ”SHD Параметры дат” я переименовываю в “SHD Связь календарей”. Замечу, что SHD означает Shared Dimensions — общие измерения. Так мы приняли обозначать общие справочники в сервисе. Поэтому во время работы в Power BI они расположены рядом, что очень удобно во время создания отчётов.
Время по фэн-шуй. Оптимизация справочника дат в Power BIТеперь начинаем преобразования с таблицей “SHD Связь календарей”. Нам нужно добиться того, чтобы из всех полей осталось всего 3: Идентификатор даты, дата (отдельно) и время (тоже отдельно). Способов для этого масса. Например, правый клик по любому столбцу, выбираем опцию “удалить другие столбцы”. После проведения процедуры, вызываем опцию данного шага и выставляем на картинке:

Время по фэн-шуй. Оптимизация справочника дат в Power BI Время по фэн-шуй. Оптимизация справочника дат в Power BIМеняем тип данных столбца Дата и время тип “Время и переименовываем:

Время по фэн-шуй. Оптимизация справочника дат в Power BI

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

В Power query выбираем “Создать источник” ->”Пустой запрос”. Здесь уже выбираем Расширенный редактор и, стирая всё что там есть, вставляем скопированный код:

Время по фэн-шуй. Оптимизация справочника дат в Power BI

Я называю таблицу SHD Параметры времени.

Создаём календарь. Тут останавливаться тоже не буду. Есть множество пособий как это делать, в том числе в “Подробном руководстве…” это имеется.

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

SHD Параметры дат =
ADDCOLUMNS (
    CALENDAR (
        DATE ( YEAR ( MIN ( ‘SHD Параметры источников данных'[Дата начала интервала] ) )11 ),
        DATE ( YEAR ( MAX ( ‘SHD Параметры источников данных'[Дата окончания интервала] ) )11 )
    ),
    «Год»YEAR ( [Date] ),
    «Квартал»ROUNDUP ( MONTH ( [Date] ) / 30 ),
    «Номер месяца»MONTH ( [Date] ),
    «Месяц»FORMAT ( [Date], «MMMM» ),
    «Номер недели»WEEKNUM ( [Date], 2 ),
    «Номер дня недели»WEEKDAY ( [Date], 2 ),
    «День недели»FORMAT ( [Date], «DDDD» ),
    «День месяца»DAY ( [Date] )
)

Обращаю внимание, в моём случае календарь ограничен годами, которые есть в выгрузке. Если у вас добавлены и другие источники, выбирайте самостоятельно аргументы функции CALENDAR.

Новую таблицу отмечаем как таблицу дат:

Время по фэн-шуй. Оптимизация справочника дат в Power BI

Теперь строим связи. Из SHD Параметры дат поле Date связываем с полем Дата “Связи календарей”. А поле “Time” к полю “Время”:

Время по фэн-шуй. Оптимизация справочника дат в Power BI

Отмечу, что теперь таблица “SHD Связь календарей” нужна, чтобы строить связи с таблицами фактов. В самих визуализации никакие поля из этой таблицы укладываться не будут, поэтому для удобства её надо скрыть. А вот таблицами “SHD Параметры времени” и  “SHD Параметры дат” уже можно будет пользоваться. И, обратите внимание, ресурсы ПК расходуются теперь ещё меньше:

Время по фэн-шуй. Оптимизация справочника дат в Power BI

И сам файл занимает меньше места:

Время по фэн-шуй. Оптимизация справочника дат в Power BI

В Dax Studio:

Время по фэн-шуй. Оптимизация справочника дат в Power BI

А это всего лишь 5 минут оптимизации!

На этом я статью завершу. На данном этапе и этой информации, кажется,  достаточно.
В следующей статье, я покажу как правильно строить связи, почему НЕЛЬЗЯ заниматься объединением запросов и импортировать время из справочника в таблицу фактов, и как это всё заставить работать, прикладывая минимум усилий.


До новых встреч.