Меню Закрыть

Время по фэн-шуй. Оптимизация справочника дат в 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: 

На свою беду, при импорте данных, пользователи не обращают внимание на то, что представление таблицы дат “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 раза:

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

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

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

После:

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

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

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

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

Меняем тип данных столбца Дата и время тип “Время и переименовываем:

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

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

Я называю таблицу 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.

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

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

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

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

В Dax Studio:

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

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


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

Читать дальше