Bad — Practices при создании отчета. Таблицы — часть вторая.
Всем привет! Продолжаем разбирать Bad-practice при использовании таблиц в Power BI. Далее под словом «таблица» понимаем любой табличный визуальный элемент.
Bad Practice № 7. Не использовать заголовки таблиц.
Заголовок таблицы, да и любого другого визуального элемента позволяет дать пользователю отчета больше информации о том, что именно он видит. При этом игнорирование заголовков не позволяет достичь значимого увеличения доступного пространства холста.
Стоит отметить, что текст заголовка можно делать динамическим. Особенно это важно при печати отчета с примененными фильтрами.
Bad Practice № 8. Пытаться запихнуть в таблицу максимум информации.
Достаточно часто встречаются ситуации, когда либо заказчик просит, либо разработчик по своей инициативе добавляет в таблицу или матрицу огромное количество информации. При этом преследуется достаточно благая цель — дать пользователям возможность экспортировать ту информацию, которую они хотят…
Обычно затем возникают три вопроса:
- Почему Power BI не показывает всю информацию в таблице?
- Как обойти ограничение Power BI на экспорт данных?
- Как заставить отчёт работать быстрее?
На всякий случай напомним еще раз — Power BI это инструмент анализа данных, а не их экспорта. Если нужно экспортировать небольшой объем информации — это сделать легко и быстро, можно даже подключиться к модели данных напрямую из Excel. Но не нужно пытаться заставить его выполнять задачи, для которых сервис не предназначен.
Итак, почему же «большие» таблицы это плохо? Очень просто — значение в каждой ячейке таблицы рассчитывается по отдельности.
В таблице, показанной на рис. 1, значение меры «Прибыль» рассчитывается 7 раз — один раз для каждой страны + Итог. Если запустить анализатор производительности, то увидим, что DAX-запрос выполняется 9 мс, что вроде как очень мало.
Рис. 1. Простая таблица.
Рис. 2. Время выполнения DAX-запроса
Изменим тип визуального элемента на матрицу и добавим новое измерение — год. Теперь в матрице 6 значений в строках и 4 значения в столбцах. Общее количество расчетов меры = (R+1)*(C+1), где R — количество строк и C — количество столбцов. Итого: 35 раз. DAX-запрос выполняется уже 19 мс, что тоже немного. И вроде бы, разница незаметна… Есть одно «Но» — мы работаем с достаточно качественной моделью данных и использую меру, в которой из функций один лишь SUM.
Обратите внимание — на этом и последующих рисунках видно, что я допустил очень грубую ошибку при разработке отчета? Если не догадались какую — ответ в предыдущем посте.
Рис. 3. Матрица с небольшим количеством значений
Рассмотрим другой пример — матрица с иерархией дат и мерой, которая всего-лишь считает накопительный итог с начала года —
Продажи с начала года =
TOTALYTD ( [Продажи (онлайн (руб.))]; ‘DimDate'[FullDateAlternateKey] )
DAX-запрос выполняется уже 403 секунды.
Кстати, на скриншоте видно, что на уровне дней моя мера с TotalYTD работает некорректно. Подумайте почему 🙂 Ответ в конце этого поста.
Рис. 4. Матрица с накопительным итогом
Добавим еще измерение, увеличив количество столбцов и срез на страницу, чтобы исключить влияние кэширования. Запрос уже выполняется 608 мс, при этом возвращается пустая матрица:
Рис. 5. Пустая матрица
Рис. 6. Время выполнения запроса для пустой матрицы.
Теперь делаем еще несколько замеров:
- Добавляем в ту же матрицу меру — Накопительный итог за тот же период прошлого года — запрос DAX будет выполняться в районе 1100 секунд, что практически в 2 раза больше
- Убираем добавленную меру и применяю условное форматирование на основании значений меры Отклонение к прошлому году — время выполнения запроса практически такое-же, как в предыдущем случае. Это говорит о том, что при использовании условного форматирования та мера, на основании которой применяется формат всё равно рассчитываются.
На закуску — Вы разработали отчет, в котором красивая матрица с большим количеством столбцов и мер, добавили несколько фильтров на страницу. Даже оптимизировали часть расчётов и выключили автоматические дату и время. Кажется, что всё хорошо — страница пересчитывается за жалкие 5 секунд. Главное не забыть, что опубликованным отчётом могут пользоваться несколько десятков или сотен человек одновременно, а таких отчётов у Вас уже опубликовано 10 или 15. Поскольку компания крупная — Вы даже купили Premium P1.
Вопрос — как скоро будут использованы все доступные вычислительные ресурсы?
Как с этим жить?
- Избегать использования широких таблиц со множеством мер на страницах отчётов.
- Активнее использовать страницы детализации, вместо того, чтобы добавлять кучу фильтров на страницу с одной матрицей.
- Если таблицы нужны исключительно для того, чтобы позволить пользователям смотреть данные в максимальном количестве разрезов после экспорта в Excel — научить их создавать свои пользовательские отчеты и фильтры.
- Если Вы активно используете в широких и длинных таблицах меры — рассмотрите возможность замены мер на столбцы, рассчитанные в Power Query или источнике данных. Но применяйте этот метод с осторожностью.
- Если пользователям жизненно важно строить именно табличные отчёты — покажите им, как работает Power Query в Excel и научите подключаться к источнику данных.
- Функция Analyze in Excel по-своему прекрасна, но если пользователи будут активно с ней работать и добавлять меры в сводные таблицы или Cube-формулы — это также может «повесить» вашу модель. Отчеты также будут грузиться медленней.
Bad Practice № 9. Использовать таблицы с темной заливкой.
Будим кратоки — отчёты с таблицами распечатывают значительно чаще, чем с графиками. Вы хотите всё переделывать?
На этом завершаем цикл про «табличные» Bad Practices.
Всем быстрых отчётов и пишите вопросы в комментариях.
P.S. Если любите видео, то кое-что интересное есть на Youtube-канале IQBI.
Ход рассуждений для ответа: Посмотрев на рис. 4. можно заметить, что используется встроенная иерахия дат. Поскольку мера все-таки что-то считает, то в матрице использован столбец дат из корректной таблицы DimDate. Это говорит о том, что таблица DimDate не отмечена как таблица дат, поскольку в этом случае встроенные иерархии даты были бы отключены. Но это не объясняет полностью ошибку. Скорее всего связь между таблицей фактов и таблицей дат создана не между столбцами с типом данных «Дата» а между какими-то другими.
Ответ: В свойствах таблицы DimDate не отмечено, что она является таблицей дат, при этом связь между таблицей фактов и таблицей дат создана по столбцам с типом данных «Целое число».
Если Вы дочитали до этого места, то большая просьба — когда задаете вопросы в профильных группах, то по возможности готовьте пример модели, просто с урезанными данными. Гадание на скриншотах — достаточно затратно по времени….
Наши курсы по Power BI:
Курс Аналитик BI
Курс DAX Mastering
Курс Финансовый анализ в Power BI
Наши каналы:
Telegram