Биг дата. Часть 2.

В первой части я рассказал про типичную ошибку начинающих аналитиков, когда функции Time Intelligence используются применительно к таблице фактов в модели из одной таблицы.

Во второй части я хочу поговорить про автоиерархию дат.

Автоиерархия дат — это параметр загрузки данных в Power BI Desktop.

По умолчанию этот параметр включен и применяется для всех новых файлов. Если же вы получили файл от кого-то и хотите включить или наоборот отключить автоиерархию, тогда вам необходимо установить/снять галочку в разделе ТЕКУЩИЙ ФАЙЛ

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

Это говорит о том, что для такого столбца в модели была создана скрытая таблица со следующими столбцами: День, №Месяца, Месяц, №Квартала, Квартал и Год. Сами таблицы не увидеть в модели данных в PowerBI Desktop, только с помощью внешнего инструмента, например, Dax Studio

Создание таблиц автоиерархий происходит только при выполнении следующих условий:

• режим хранения таблицы — импорт
• столбец дат имеет тип данных "Дата" или "Дата и время"
• столбец не является стороной "многие" в связях модели

На уровне визуализации при разработке отчёта мы можем обращаться только к иерархии, которая связана с полем даты, для которого была создана скрытая таблица. Такая иерархия состоит из четырёх полей: Год, Квартал, Месяц и День.

В визуализацию можно добавлять как всю иерархию

так и отдельные поля

Но, давайте посмотрим, как автоиерархия дат влияет на создание вычислений.

Создадим меру, которая подсчитывает объём заказов за предыдущий месяц

В этот раз я специально использовал функцию DATEADD, чтобы показать, что многие функции Time Intelligence используют стандартные уровни агрегации соответствующие уровням автоиерархии.

Добавим меру в простую визуализацию в виде таблицы

Как видим наша мера возвращает пустое значение. Это происходит потому, что мы по прежнему меняем контекст вычисления по столбцу таблицы фактов, а не по таблице дат, тогда как столбцы Год и Месяц мы взяли как раз из скрытой таблицы. Чтобы в этом убедиться, можно воспользоваться "Анализатором производительности" и посмотреть код, который генерируется при обновлении визуального элемента

Скопировав текст запроса, его можно посмотреть в DAX Studio

В переменной __DS0Core мы видим, что контекст вычисления задаётся с помощью столбцов скрытой таблицы дат, а раз этот контекст сохраняется, то как тогда нам его изменить? В PowerBI Desktop мы не можем обратиться к скрытым таблицам, но мы можем использовать расширенный синтаксис, который будет обращаться к столбцам связанной скрытой таблицы.

Модифицируем меру:

Проверяем работу меры

Обратите внимание: в мере мы меняем контекст ТОЛЬКО по столбцу дат, хотя в контексте вычисления у нас столбцы Год и Месяц, но результат верный - это происходит по тому, что для таблиц дат, которые связаны с таблицей фактов по столбцу с типом дата происходит автоматический сброс фильтров по другим столбцам таблицы дат. К этому мы вернёмся ещё раз в следующей части.

Если вернуться к кейсу из первой части и задать фильтр в срезе по столбцу Orders[Country], то в отличии от использования столбцов из таблицы фактов, здесь у нас никуда не пропадут пустые периоды:

По умолчанию строки, в которых все меры возвращают пустое значение скрываются, но для них можно задать свойство "Показать элементы без данных"

Какие же минусы в использовании автоиерархии дат и почему я против её использования?

1. Ограниченный набор измерений: Год, Квартал, Месяц и День. Так как таблицы скрыты и не доступны в PowerBI Desktop - вы не сможете добавить новые столбцы, например: номер недели, день недели, столбец Год-Месяц, признак рабочий день и так далее.
2. Если у вас в модели более одного столбца с датами, как у меня в примере: Orders[Order Date] и Orders[Ship Date], - то использование автоиерархии ещё больше затрудняет создание расчётов, так как вы не можете переключать связи между скрытыми таблицами.
3. Замусоренная модель. При включенном параметре автоиерархии создаётся скрытая таблица на КАЖДЫЙ столбец с типом дата или дата-время. Если в вашей модели будет десять таких столбцов, значит создастся десять скрытых таблиц. А если в столбце будет ошибка с годом, например, вместо 2021 ввели 2201, то скрытая таблица будет содержать все даты до 31/12/2201, забирая ресурсы памяти, так как скрытые таблицы автоиерархии являются вычисляемыми таблицами, которые рассчитываются в момент импорта или обновления данных.
4. Автоирерархия НЕ доступна при использовании инструмента "Анализ в Excel" или подключении к модели с помощью конструкторов отчетов, отличных от PowerBI.

Подводя итоги, повторю: автоиерархию можно использовать, если в вашей модели ТОЛЬКО один столбец с типом дата или дата-время. Вы используете простые меры и визуализации и ТОЛЬКО стандартные периоды: Год, Квартал, Месяц и День. Во всех остальных случаях я рекомендую самостоятельно создавать справочник Календарь.

О том, как это сделать я расскажу в третьей части "Биг дата".

Файл с моделью можно скачать по ссылке