Сегодня мы, Павел Ефремов и Мухаммед Пашаев (разработчики аналитических систем в Лемана Тех), поговорим об оптимизации моделей данных в Power BI Desktop — конкретно о движке Tabular. Небольшая вводная: в нашей компании Power BI — основной BI-инструмент (лучшие для лучших😏). Используем локальную версию Power BI Report Server, поэтому вопрос производительности моделей и отчетов стоит особо остро. Мы регулярно проводим ревью моделей (подробнее в нашей прошлой статье), стараясь придерживаться лучших практик — минимальная нужная гранулярность данных, никаких избыточных связей, двунаправленных связей, скрытых автоматических календарей и прочее. Тем не менее отчеты все равно порой залипают и работают медленно. Казалось бы, уже много где поковырялись, все оптимизировали, и все равно фрустрация не уходит. Так, вместе с коллегой мы взялись копать глубже, шерстить интернет и Microsoft-документацию и наткнулись на почти незаметное, но важное свойство табличной модели. Что, если мы скажем, что у Tabular Model есть свойство, благодаря которому можно уменьшить размер модели до 30%, ускорить обновление данных, снизить нагрузку на сервер и при этом не менять ни одной таблицы, связи или строку DAX?
Это открытие заставило нас по-новому взглянуть на оптимизацию в Power BI. Оказалось, что помимо привычных правил вроде избегания лишних связей или сокращения столбцов есть и менее заметные, но очень мощные приемы. Они не требуют переделывать модель, но при этом реально ускоряют отчеты и снижают нагрузку.
В этой статье мы расскажем:
почему даже «чистая» модель может тащить за собой лишний вес и откуда он берется;
как работает сжатие данных в VertiPaq (без углубления в теорию — только то, что влияет на практику);
и, главное, какое свойство и как именно нужно выставить, чтобы получить этот эффект.
С чего начать разговор об этом? Логично — с основ.
Тонкая оптимизация моделей
Существует ряд наиболее распространенных правил оптимальности, которым все в той или иной степени следуют.
Распространенные правила оптимизации:
упрощать структуру модели и тем самым снижать сложность DAX-логики;
удалять неиспользуемые столбцы;
агрегировать данные до требуемой гранулярности;
избегать чисел с плавающей запятой — использовать целочисленные типы или Fixed Decimal для лучшего сжатия в VertiPaq;
избегать вычисляемых столбцов и таблиц;
для столбцов с большой кардинальностью избегать текстового типа данных и т. п.
В итоге наступает момент, когда и основных методов уже недостаточно. Тогда в дело вступает более тонкая оптимизация. Одним из таких методов является управление свойством IsAvailableInMDX.
Свойство IsAvailableInMDX
IsAvailableInMDX — это свойство столбца табулярной модели, определяющее, будет ли он доступен в качестве среза в MDX-клиентах, например в Excel. Управлять значением свойства можно в Tabular Editor, по умолчанию значение равно True.
На физическом уровне свойс��во IsAvailableInMDX определяет, будет ли для столбца во время процессинга модели формироваться структура attribute hierarchy (не путать с user defined иерархиями).
Зачем нужна структура attribute hierarchy?
Структура attribute hierarchy позволяет помещать поле таблицы на ось строк или столбцов в MDX-запросах. Например, в сводной таблице Excel, подключенной к табличной модели, можно вывести столбец модели в область строк или столбцов.
С точки зрения клиентских приложений соединение с экземпляром SSAS выглядит прозрачным, безотносительно, какой это тип: Tabular или Multidimensional. Собственно, для обратной совместимости табулярных моделей с MDX-клиентами и нужна структура attribute hierarchy.
Кроме этого, Formula Engine может использовать структуру attribute hierarchy для оптимизации DAX-запросов.
Структура attribute hierarchy на практике
Со слов Marco Russo структура attribute hierarchy по своей сути является неким аналогом Dictionary, но значения в котором отсортированы. Наблюдать воочию наличие и объем attribute hierarchy можно в DAX Studio (VertiPaq Analyzer — Tables\Columns — Hier Size).
Здесь видно, какой объем занимает структура attribute hierarchy для каждого столбца и суммарно для таблицы.
Оценим, какую долю занимают attribute hierarchy в общем объеме.
По умолчанию структуры attribute hierarchy автоматически создаются для кажд��го столбца модели и могут занимать порядка 20-30 процентов от размера модели. При этом абсолютный размер attribute hierarchy напрямую зависит от кардинальности столбца.
Также важно отметить, что во время процессинга модели обработка структур attribute hierarchy выполняется отдельным этапом — processing hierarchy.
Резонный вопрос
Если не предполагается использование MDX-клиентов (в случае отчетов в режиме импорта), можно ли избавиться от этой структуры и уменьшить размер модели и заодно исключить из последующего процессинга этап формирования структур attribute hierarchy? Нужен эксперимент.
«Идеальный» эксперимент
Проверим влияние свойства IsAvailableInMDX на эталонную модель.
Эталонная модель для оценки влияния свойства IsAvailableInMDX:
receipt — таблица заголовков чеков, порядка 30 млн строк;
line — таблица линий чеков, порядка 145 млн строк;
product — таблица-справочник товаров, порядка 670 тыс. строк.
Влияние свойства IsAvailableInMDX на размер модели
После изменения свойства IsAvailableInMDX всех столбцов модели на значение False получаем следующие результаты:
Влияние отключения IsAvailableInMDX на размер модели:
attribute hierarchy «обнулились»;
объем модели в памяти сократился с 6,17 ГБ до 3,8 ГБ (на 38%);
объем файла на диске сократился с 2,56 ГБ до 1,91 ГБ (на 25%).
Влияние свойства IsAvailableInMDX на процессинг модели
Трассировка процессинга исходной модели с включенным свойством IsAvailableInMDX:
среди событий наблюдаются события обработки attribute hierarchy и их длительность;
время процессинга 40 минут.
Трассировка процессинга исходной модели с выключенным свойством IsAvailableInMDX:
Влияние отключения IsAvailableInMDX на процессинг модели:
события обработки attribute hierarchy отсутствуют;
общее время процессинга сократилось на 4 минуты (на 10%).
Сайд-эффекты на практике
Кроме преимуществ в виде сокращения размера модели и времени процессинга, все-таки есть и обратная сторона отключения IsAvailableInMDX. В некоторых аспектах наличие attribute hierarchy критично. Кроме этого, Formula Engine может использовать attribute hierarchy для оптимизации DAX-запросов.
Рассмотрим основные кейсы подробнее.
1) TOPNSKIP не работает без attribute hierarchy.
Для столбца, используемого в выражении <OrderBy_Expression> функции TOPNSKIP, свойство IsAvailableInMDX должно быть включено, иначе при выполнении DAX-запроса будет возвращаться ошибка.
Необходимо иметь в виду, что, даже если TOPNSKIP не задействована в пользовательском DAX, она может использоваться под капотом некоторых визуализаций (в основном все-таки используется TOPN). Также она используется в представлении данных на вкладке Table view.
2) Конфигурация свойства Sort by column для столбцов с отключенным IsAvailableInMDX может вызывать ошибки
3) Производительность функций DISTINCTCOUNT, MIN, MAX.
Влияние наличия attribute hierarchy на производительность функций DISTINCTCOUNT, MIN, MAX без фильтра или контекста:
на порядок отличаются тайминги и распределение Formula Engine\Storage Engine;
кардинально отличаются планы запросов.
Если же в запросе есть фильтры, то оптимизация с использованием attribute hierarchy становится неприменима.
Влияние наличия attribute hierarchy на производительность функций DISTINCTCOUNT, MIN, MAX с фильтром или контекстом:
нет разницы в тайминге и распределении Formula Engine\Storage Engine;
планы запросов идентичны.
Рекомендации
Отключение IsAvailableInMDX может заметно сократить размер модели и время процессинга, особенно для столбцов с высокой кардинальностью. При этом существуют некоторые ограничения, которые необходимо принимать во внимание и тестировать влияние в конкретных кейсах.
Общие рекомендации таковы:
Если предполагается использование MDX-клиентов (Excel) или функции TOPNSKIP, то выключать свойство IsAvailableInMDX нельзя.
Можно отключать свойство IsAvailableInMDX для неатрибутивных столбцов (не используются в качестве срезов)
- являющихся первичными или вторичными ключами;
- по которым выполняется агрегация в мерах;
при этом такие столбцы
- не должны участвовать в пользовательских иерархиях;
- не должны использоваться при конфигурации свойства Sort by column.Для прочих случаев следует тестировать влияние.
Заключение
Свойство IsAvailableInMDX из тех настроек, которые редко оказываются в центре внимания, но именно такие мелочи иногда приносят наибольший эффект. Наш эксперимент показал, что отключение этого свойства способно сократить размер модели на 20-30% и ускорить процессинг примерно на 10% без единого изменения в структуре таблиц или DAX-логике.
Эта настройка не заменяет классические методы оптимизации, но отлично дополняет их, открывая возможности для тонкой настройки производительности, особенно в средах Power BI Report Server, где каждый гигабайт памяти и каждая минута процессинга на счету.
Важно, однако, подходить к применению свойства осознанно:
не отключать IsAvailableInMDX глобально;
учитывать возможные сайд-эффекты: отсутствие поддержки TOPNSKIP, ошибки при Sort by Column и снижение скорости некоторых функций (DISTINCTCOUNT, MIN, MAX);
отключать только там, где это оправданно: для технических столбцов, идентификаторов и ключей с высокой кардинальностью, не участвующих в срезах и иерархиях.
И, конечно, все измерять: размер модели, время процессинга, поведение отчетов.
А чтобы подобные настройки не превращались в ручной ритуал, их можно автоматизировать — например, с помощью соответствующего правила Best Practice Analyzer в Tabular Editor, которое массово обновляет свойство IsAvailableInMDX.
Полезные ссылки
Определение свойства IsAvailableInMDX библиотеки Microsoft.AnalysisServices.Tabular
SQLBI — Exploring attribute hierarchies in Power BI and Analysis Services — Unplugged #31
Logical Architecture Overview (Analysis Services — Multidimensional Data)
Microsoft White Papers — Performance Tuning of Tabular Models in SQL Server 2012 Analysis Services
Power BI Blog — Best practice rules to improve your model’s performance
