Bad — Practices при создании отчета. Таблицы — часть вторая.

Всем привет! Продолжаем разбирать Bad-practice при использовании таблиц в Power BI. Далее под словом «таблица» понимаем любой табличный визуальный элемент.

Bad Practice № 7. Не использовать заголовки таблиц. 

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

Стоит отметить, что текст заголовка можно делать динамическим. Особенно это важно при печати отчета с примененными фильтрами. 

Bad Practice № 8. Пытаться запихнуть в таблицу максимум информации. 

Достаточно часто встречаются ситуации, когда либо заказчик просит, либо разработчик по своей инициативе добавляет в таблицу или матрицу огромное количество информации. При этом преследуется достаточно благая цель — дать пользователям возможность экспортировать ту информацию, которую они хотят…  

Обычно затем возникают три вопроса: 

1. Почему Power BI не показывает всю информацию в таблице? 
2. Как обойти ограничение Power BI на экспорт данных? 
3. Как заставить отчёт работать быстрее? 

На всякий случай напомним еще раз — Power BI это инструмент анализа данных, а не их экспорта. Если нужно экспортировать небольшой объем информации — это сделать легко и быстро, можно даже подключиться к модели данных напрямую из Excel. Но не нужно пытаться заставить его выполнять задачи, для которых сервис не предназначен. 

Итак, почему же «большие» таблицы это плохо? Очень просто — значение в каждой ячейке таблицы рассчитывается по отдельности. 

В таблице, показанной на рис. 1, значение меры «Прибыль» рассчитывается 7 раз — один раз для каждой страны + Итог. Если запустить анализатор производительности, то увидим, что DAX-запрос выполняется 9 мс, что вроде как очень мало. 

Рис. 2. Время выполнения DAX-запроса 

Изменим тип визуального элемента на матрицу и добавим новое измерение — год. Теперь в матрице 6 значений в строках и 4 значения в столбцах. Общее количество расчетов меры = (R+1)*(C+1), где R — количество строк и C — количество столбцов. Итого: 35 раз. DAX-запрос выполняется уже 19 мс, что тоже немного. И вроде бы, разница незаметна… Есть одно «Но» — мы работаем с достаточно качественной моделью данных и использую меру, в которой из функций один лишь SUM. 

Обратите внимание — на этом и последующих рисунках видно, что я допустил очень грубую ошибку при разработке отчета? Если не догадались какую — ответ в предыдущем посте. 

Рис. 3. Матрица с небольшим количеством значений 

Рассмотрим другой пример — матрица с иерархией дат и мерой, которая всего-лишь считает накопительный итог с начала года — 

Продажи с начала года = 

TOTALYTD ( [Продажи (онлайн (руб.))]; ‘DimDate'[FullDateAlternateKey] ) 

DAX-запрос выполняется уже 403 секунды. 

Кстати, на скриншоте видно, что на уровне дней моя мера с TotalYTD работает некорректно. Подумайте почему 🙂 Ответ в конце этого поста. 

Рис. 4. Матрица с накопительным итогом 

Добавим еще измерение, увеличив количество столбцов и срез на страницу, чтобы исключить влияние кэширования. Запрос уже выполняется 608 мс, при этом возвращается пустая матрица: 

Рис. 6. Время выполнения запроса для пустой матрицы. 

Теперь делаем еще несколько замеров: 

1. Добавляем в ту же матрицу меру — Накопительный итог за тот же период прошлого года — запрос DAX будет выполняться в районе 1100 секунд, что практически в 2 раза больше  

1. Убираем добавленную меру и применяю условное форматирование на основании значений меры Отклонение к прошлому году — время выполнения запроса практически такое-же, как в предыдущем случае. Это говорит о том, что при использовании условного форматирования та мера, на основании которой применяется формат всё равно рассчитываются. 

На закуску — Вы разработали отчет, в котором красивая матрица с большим количеством столбцов и мер, добавили несколько фильтров на страницу. Даже оптимизировали часть расчётов и выключили автоматические дату и время. Кажется, что всё хорошо — страница пересчитывается за жалкие 5 секунд. Главное не забыть, что опубликованным отчётом могут пользоваться несколько десятков или сотен человек одновременно, а таких отчётов у Вас уже опубликовано 10 или 15. Поскольку компания крупная — Вы даже купили Premium P1. 

Вопрос — как скоро будут использованы все доступные вычислительные ресурсы? 

Как с этим жить? 

1. Избегать использования широких таблиц со множеством мер на страницах отчётов. 
2. Активнее использовать страницы детализации, вместо того, чтобы добавлять кучу фильтров на страницу с одной матрицей. 
3. Если таблицы нужны исключительно для того, чтобы позволить пользователям смотреть данные в максимальном количестве разрезов после экспорта в Excel — научить их создавать свои пользовательские отчеты и фильтры. 
4. Если Вы активно используете в широких и длинных таблицах меры — рассмотрите возможность замены мер на столбцы, рассчитанные в Power Query или источнике данных. Но применяйте этот метод с осторожностью. 
5. Если пользователям жизненно важно строить именно табличные отчёты — покажите им, как работает Power Query в Excel и научите подключаться к источнику данных. 
6. Функция Analyze in Excel по-своему прекрасна, но если пользователи будут активно с ней работать и добавлять меры в сводные таблицы или Cube-формулы — это также может «повесить» вашу модель. Отчеты также будут грузиться медленней. 

Bad Practice № 9. Использовать таблицы с темной заливкой. 

Будим кратоки — отчёты с таблицами распечатывают значительно чаще, чем с графиками. Вы хотите всё переделывать? 

На этом завершаем цикл про «табличные» Bad Practices.  

Всем быстрых отчётов и пишите вопросы в комментариях. 

P.S. Если любите видео, то кое-что интересное есть на Youtube-канале IQBI. 

Ход рассуждений для ответа: Посмотрев на рис. 4. можно заметить, что используется встроенная иерахия дат. Поскольку мера все-таки что-то считает, то в матрице использован столбец дат из корректной таблицы DimDate. Это говорит о том, что таблица DimDate не отмечена как таблица дат, поскольку в этом случае встроенные иерархии даты были бы отключены. Но это не объясняет полностью ошибку. Скорее всего связь между таблицей фактов и таблицей дат создана не между столбцами с типом данных «Дата» а между какими-то другими. 

Ответ: В свойствах таблицы DimDate не отмечено, что она является таблицей дат, при этом связь между таблицей фактов и таблицей дат создана по столбцам с типом данных «Целое число». 

Если Вы дочитали до этого места, то большая просьба — когда задаете вопросы в профильных группах, то по возможности готовьте пример модели, просто с урезанными данными. Гадание на скриншотах — достаточно затратно по времени….

VK