Вы когда-нибудь задумывались, что делает Power BI таким быстрым и мощным с точки зрения производительности? Настолько мощным, что он выполняет сложные вычисления над миллионами строк за мгновение.

В этой статье мы подробно рассмотрим, что находится «под капотом» Power BI: как данные хранятся, сжимаются, запрашиваются и, наконец, возвращаются в отчёт. После прочтения, надеюсь, у вас появится лучшее понимание того, что происходит в фоновом режиме, и вы сможете оценить важность создания оптимальной модели данных для достижения максимальной производительности с использованием движка Power BI.

Заглянем внутрь системы — движок формул и движок хранения

Прежде всего, хочу познакомить вас с движком VertiPaq, «мозгом и мышцами» системы, который стоит не только за Power BI, но и за Analysis Services Tabular и Excel Power Pivot. По правде говоря, VertiPaq — это лишь одна часть хранилища данных в Табличной модели, помимо DirectQuery, о котором мы поговорим в одной из следующих статей.

Когда вы отправляете запрос для получения данных для Power BI отчёта, происходит следующее:

• Движок формул (Formula Engine, FE) принимает запрос, обрабатывает его, генерирует план запроса и, наконец, выполняет его.

• Движок хранения (Storage Engine, SE) извлекает данные из Табличной модели, чтобы удовлетворить запрос, сформированный движком формул.

Хранилищный движок использует два разных метода для получения данных: VertiPaq хранит снимок данных в памяти. Этот снимок можно периодически обновлять из исходного источника данных.

Напротив, DirectQuery не хранит данные. Он перенаправляет запрос непосредственно в источник данных для каждого запроса.

Данные в табличной модели обычно хранятся либо в виде снимка в памяти (VertiPaq), либо в режиме DirectQuery. Однако существует возможность реализации гибридной модели Composite, которая использует обе архитектуры одновременно.

Движок формул — «мозг» Power BI

Как я уже отметил, движок формул принимает запрос, и, поскольку он способен «понимать» DAX (и MDX тоже, но это выходит за рамки этой серии), он «переводит» DAX в конкретный план запроса, состоящий из физических операций, которые нужно выполнить, чтобы вернуть результаты.

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

Движок хранения — «мускулы» Power BI

После того как запрос был обработан и выполнен Формульным движком, на сцену выходит движок хранения. Он физически проходит по данным, хранящимся в Табличной модели (VertiPaq), или напрямую обращается к другому источнику данных (например, SQL Server, если используется режим хранения DirectQuery).

Когда речь идёт о выборе движка хранения для таблицы, есть три возможных варианта:

• Режим импорта (Import mode) — использует движок VertiPaq. Данные таблицы хранятся в памяти как снимок. Данные могут периодически обновляться.

• Режим DirectQuery — данные извлекаются из источника данных во время запроса. Данные находятся в исходном источнике до, во время и после выполнения запроса.

• Режим Dual — комбинация первых двух вариантов. Данные из таблицы загружаются в память, но во время запроса могут также извлекаться напрямую из источника.

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

Движок хранения VertiPaq

Поскольку мы уже набросали общую картину, позвольте объяснить подробнее, что делает VertiPaq в фоновом режиме для повышения производительности Power BI отчётов.

Когда мы выбираем режим импорта для Power BI таблиц, VertiPaq выполняет следующие действия:

• Читает источник данных, преобразует данные в колонковую структуру, кодирует и сжимает данные внутри каждого столбца.

• Создаёт словарь и индекс для каждого столбца.

• Подготавливает и устанавливает связи.

• Вычисляет все вычисляемые столбцы и вычисляемые таблицы и сжимает их.

Два основных признака VertiPaq:

• VertiPaq — это колонковая база данных.

• VertiPaq — это база данных в памяти.

Как видно на иллюстрации выше, колонковые базы данных хранят и сжимают данные иначе, чем традиционные базы данных с хранением по строкам. Колонковые базы данных оптимизированы для обработки данных по столбцам, что позволяет значительно ускорить сканирование — это означает, что каждый столбец структурирован по-своему и физически отделён от других столбцов.

Не углубляясь в детальный анализ преимуществ и недостатков баз данных с хранением по строкам и колонкам (это потребовало бы отдельной серии статей), хочу выделить несколько ключевых отличий с точки зрения производительности.

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

Иными словами, колонковые базы данных требуют больше вычислительных ресурсов (CPU), в то время как базы данных с хранением по строкам увеличивают нагрузку на операции ввода-вывода (I/O), так как требуют многократного обращения к данным.

Итак, мы уже нарисовали общую картину архитектуры, которая позволяет Power BI раскрыться как выдающийся инструмент для бизнес-анализа. Теперь мы готовы углубиться в конкретные архитектурные решения и, соответственно, использовать эти знания, чтобы извлечь максимум из Power BI отчётов, оптимизируя модель данных для наилучшей производительности движка.

Как сжатие данных внутри VertiPaq ускоряет Power BI

Как вы, возможно, помните из предыдущей части статьи, мы лишь слегка коснулись возможностей VertiPaq — мощного хранилищного движка, который отвечает за молниеносную производительность большинства Power BI отчётов (независимо от того, используете ли вы режим импорта или гибридную модель Composite).

3, 2, 1… Приготовьтесь к подробному разбору

Одной из ключевых характеристик VertiPaq является то, что это колонковая база данных. Мы узнали, что колонковые базы данных хранят данные по столбцам, что позволяет значительно ускорить обработку данных, ориентированную на чтение по столбцам.

Этот факт позволяет VertiPaq применять разные типы сжатия к каждому столбцу независимо, выбирая оптимальный алгоритм сжатия в зависимости от значений в конкретном столбце.

Сжатие происходит благодаря применению различных методов кодирования значений в столбцах. Но прежде чем углубиться в подробное описание техник кодирования, имейте в виду, что эта архитектура не ограничивается только Power BI — на заднем плане стоит табличная модель, которая также используется в Analysis Services Tabular и Excel Power Pivot.

Кодирование значений (Value Encoding)

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

Как это выглядит на практике? Допустим, у нас есть столбец, содержащий количество звонков в день, и значения в этом столбце варьируются от 4.000 до 5.000. Как работает VertiPaq в этом случае? Он находит минимальное значение в этом диапазоне (то есть 4.000), затем вычисляет разницу между этим значением и всеми остальными значениями в столбце, сохраняя эту разницу как новое значение.

На первый взгляд, экономия в 3 бита на значение может показаться несущественной, но если умножить это на миллионы или даже миллиарды строк, вы оцените, сколько памяти можно сэкономить.

Как я уже подчёркивал, кодирование значений применяется исключительно к столбцам с целочисленным типом данных (тип данных «валюта» также хранится как целое число).

Кодирование с хешированием (Hash Encoding)

Это, вероятно, самый часто используемый тип сжатия в VertiPaq. Используя хеширование, VertiPaq создаёт словарь уникальных значений внутри одного столбца, а затем заменяет «реальные» значения индексами из словаря.

Вот пример, чтобы стало понятнее:

Как вы могли заметить, VertiPaq определил уникальные значения в столбце "Subjects", создал словарь, присвоив индекс этим значениям, и в конце концов сохранил индексы как указатели на «реальные» значения. Наверное, вы знаете, что целые числа занимают гораздо меньше памяти, чем текст, и именно эта логика лежит в основе этого типа сжатия данных.

Кроме того, благодаря возможности создавать словарь для любого типа данных, VertiPaq практически независим от типа данных.

Это приводит нас к ещё одному важному моменту: не имеет значения, является ли столбец текстовым, с типом данных bigint или float — с точки зрения VertiPaq это одно и то же. Он должен создать словарь для каждого из этих столбцов, что подразумевает, что все эти столбцы будут обеспечивать одинаковую производительность как по скорости, так и по выделенному объёму памяти! Конечно, при условии, что нет существенных различий в размерах словарей между этими столбцами.

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

Алгоритм RLE 

Третий алгоритм (RLE примечание — Run-Length-Encoding, Кодирование длин серий) создаёт своего рода таблицу отображений, содержащую диапазоны повторяющихся значений, избегая хранения каждого повторяющегося значения отдельно.

Снова, для лучшего понимания этого концепта, давайте взглянем на пример:

На практике VertiPaq не сохраняет значения Start, потому что он может быстро вычислить, где начинается следующий элемент, суммируя предыдущие значения Count.

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

Ещё один момент, который стоит учитывать относительно RLE: на самом деле, VertiPaq не хранит данные так, как показано в иллюстрации выше. Сначала VertiPaq выполняет хеширование и создает словарь для значений в столбце, а затем применяет алгоритм RLE, чтобы сжать данные. Таким образом, логика будет выглядеть следующим образом:

RLE применяется после Value или Hash Encoding в тех случаях, когда VertiPaq считает, что имеет смысл дополнительно сжать данные (когда данные упорядочены так, чтобы RLE обеспечило лучшее сжатие).

Размышления о повторном кодировании

Несмотря на то, насколько эффективно работает VertiPaq, он тоже может делать неверные выборы, основываясь на неправильных предположениях. Прежде чем объяснить, как работает повторное кодирование, давайте кратко рассмотрим процесс сжатия данных для конкретного столбца:

1. VertiPaq сканирует выборку строк из столбца.

2. Если тип данных столбца не целочисленный, VertiPaq использует хеширование, не пытаясь применять другие методы сжатия.

3. Если тип данных столбца — целое число, то оцениваются дополнительные параметры: если числа в выборке увеличиваются линейно, VertiPaq предполагает, что это, вероятно, первичный ключ, и выбирает кодирование значений (Value encoding).

4. Если числа в столбце достаточно близки друг к другу (например, диапазон от 4.000 до 5.000 звонков в день), VertiPaq применит кодирование значений. Напротив, если значения сильно различаются в диапазоне (например, от 1.000 до 1.000.000), то кодирование значений теряет смысл, и VertiPaq применяет хеширование.

Однако иногда бывает так, что VertiPaq принимает решение о том, какой алгоритм использовать, основываясь на выборке данных, а затем появляется выброс, и ему приходится повторно закодировать столбец с нуля.

Рассмотрим наш предыдущий пример с количеством звонков: VertiPaq сканирует выборку и решает применить кодирование значений. Затем, после обработки 10 миллионов строк, он вдруг находит значение 500.000 (это может быть ошибка или что-то другое). Теперь VertiPaq пересматривает свой выбор и решает повторно применить алгоритм сжатия, используя хеширование вместо кодирования значений. Конечно, это повлияет на весь процесс с точки зрения времени, необходимого для повторной обработки.

Перечень факторов, которые VertiPaq учитывает при выборе алгоритма сжатия:

• Количество уникальных значений в столбце (кардинальность).

• Распределение данных в столбце — столбец с большим количеством повторяющихся значений может быть лучше сжат, чем столбец с часто изменяющимися значениями (можно применить RLE).

• Количество строк в таблице.

• Тип данных столбца — влияет только на размер словаря.

Снижение размера модели данных на 90% — реальная история!

После того как мы разобрали теоретическую основу для понимания архитектуры движка хранения VertiPaq и типов сжатия, которые он использует для оптимизации модели данных Power BI, настал момент применить знания на реальном практическом примере.

Начальная точка = 776 МБ

Наша модель данных довольно простая, но требует много памяти. У нас есть таблица фактов (factChat), которая содержит данные о чатах службы поддержки, и одна размерная таблица (dimProduct), которая связана с таблицей фактов. Таблица фактов содержит около 9 миллионов строк, что, казалось бы, не должно быть проблемой для Power BI, но таблица была импортирована без дополнительной оптимизации или преобразования.

Теперь этот файл pbix занимает целых 777 МБ! Не верите? Посмотрите:

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

…и становится ещё хуже!

Кроме того, размер в 776 МБ — это не единственная проблема, поскольку потребление памяти рассчитывается с учётом следующих факторов:

• Файл PBIX

• Словарь (о словаре вы узнали в первых разделах этой статьи)

• Иерархии столбцов

• Иерархии, созданные пользователем

• Связи

Теперь, если я открою «Диспетчер задач», перейду на вкладку «Подробности» и найду процесс msmdsrv.exe, я увижу, что он использует более 1 ГБ памяти.

Это уже становится проблемой! А мы даже ещё не взаимодействовали с отчётом. Давайте посмотрим, что можно сделать, чтобы оптимизировать нашу модель.

Правило №1 — Импортировать только действительно нужные столбцы

Первое и самое важное правило: держите в модели данных только те столбцы, которые вам действительно нужны для отчёта.

С учётом этого, нужно ли мне оба столбца: chatID (суррогатный ключ) и sourceID (первичный ключ из исходной системы)? Оба этих значения уникальны, так что даже если мне нужно посчитать общее количество чатов, я вполне могу обойтись только одним из них.

Для начала я удалю столбец sourceID и проверю, как изменится размер файла:

Удалив всего один ненужный столбец, мы сэкономили более 100 МБ! Давайте дальше проверим, что еще можно удалить без глубокого анализа (и мы к этому вернёмся чуть позже, обещаю).

Действительно ли нам нужно оба времени начала чата — оригинальное и по UTC, одно из которых хранится как тип данных Date/Time/Timezone, а другое как Date/Time, и оба до уровня точности в секунды?

Я удалю столбец с оригинальным временем начала чата и оставлю только значения по UTC.

Ещё 100 МБ пустого пространства исчезли! Удалив всего два ненужных столбца, мы уменьшили размер файла аж на 30%.

Теперь это было без учёта подробностей потребления памяти. Давайте включим DAX Studio, мой любимый инструмент для отладки Power BI отчётов. Как я уже не раз подчёркивал, этот инструмент ОБЯЗАТЕЛЕН, если вы планируете серьёзно работать с Power BI — а ещё он бесплатный.

Одна из функций DAX Studio — это VertiPaq Analyzer, очень полезный инструмент, разработанный Марко Руссо и Альберто Феррари из sqlbi.com. Когда я подключаю свой файл pbix к DAX Studio, вот какие данные о размере своей модели данных я получаю:

Я могу увидеть, какие столбцы в моей модели данных самые «дорогие», и решить, могу ли я удалить некоторые из них, или мне нужно оставить их все.

У меня есть несколько кандидатов на удаление — столбцы sessionReferrer и referrer имеют высокую кардинальность и, следовательно, не могут быть оптимально сжаты. Более того, поскольку это текстовые столбцы, которые нужно кодировать с использованием алгоритма хеширования, их размер словаря чрезвычайно велик. Если внимательно посмотреть, то можно заметить, что эти два столбца занимают почти 40% размера таблицы!

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

Ещё один кандидат на удаление — столбец LastEditDate. Он просто показывает дату и время, когда запись была в последний раз отредактирована в хранилище данных. Опять же, я поговорил с пользователями отчёта, и они даже не знали о существовании этого столбца!

Я удалил эти три столбца, и вот результат:

Итого, мы уменьшили размер модели данных вдвое, всего лишь удалив несколько ненужных столбцов.

На самом деле, есть ещё несколько столбцов, которые можно было бы исключить из модели данных, но давайте теперь перейдём к другим методам оптимизации модели данных.

Правило №2 — Оптимизация кардинальности столбцов

Как было упомянуто в предыдущей части статьи, основное правило таково: чем выше кардинальность столбца, тем сложнее VertiPaq эффективно сжать данные. Особенно если мы не работаем с целочисленными значениями.

Давайте внимательнее посмотрим на результаты анализа в VertiPaq Analyzer:

Как вы видите, несмотря на то, что столбец chatID имеет более высокую кардинальность, чем столбец datetmStartUTC, он занимает почти в 7 раз меньше памяти. Поскольку это целочисленный суррогатный ключ, VertiPaq применяет кодирование значений (Value Encoding), и размер словаря не имеет значения. С другой стороны, для столбца типа Date/Time с высокой кардинальностью применяется хеширование (Hash Encoding), и размер словаря значительно увеличивается.

Существует несколько техник для снижения кардинальности столбца, таких как разбиение столбцов. Вот несколько примеров использования этой техники.

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

SELECT chatID/1000 AS chatID_div
,chatID % 1000 AS chatID_mod……….

Эту технику оптимизации нужно выполнять на стороне источника данных (в этом случае написав T-SQL запрос). Если использовать вычисляемые столбцы, пользы от этого не будет, поскольку оригинальный столбец всё равно должен быть сохранен в модели данных.

Аналогичная техника может значительно снизить потребление памяти, если в столбце есть десятичные значения. Вы можете просто разделить значения до и после десятичной точки, как объясняется в этой статье.

Поскольку в нашем случае десятичных значений нет, давайте сосредоточимся на задаче по оптимизации столбца datetmStartUTC. Есть несколько вариантов для оптимизации этого столбца. Первый — проверить, нужно ли вашим пользователям большее разрешение, чем уровень дня (то есть, можно ли удалить часы, минуты и секунды из ваших данных).

Давайте посмотрим, сколько памяти это сэкономит:

Первое, что мы замечаем, это то, что теперь наш файл занимает 271 МБ, то есть треть от начального размера. Результаты анализа в VertiPaq Analyzer показывают, что теперь этот столбец почти идеально оптимизирован, уменьшив его долю в модели данных с более 62% до всего 2.5%. Это просто огромная экономия!

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

Итак, я импортировал значения, округлённые до уровня часов:

SELECT chatID
                ,dateadd(hour, datediff(hour, 0, datetmStartUTC), 0) AS datetmStartUTC
                ,customerID
                ,userID
                ,ipAddressID
                ,productID
                ,countryID
                ,userStatus
                ,isUnansweredChat
                ,totalMsgsOp
                ,totalMsgsUser
                ,userTimezone
                ,waitTimeSec
                ,waitTimeoutSec
                ,chatDurationSec
                ,sourceSystem
                ,subject
                ,usaccept
                ,transferUserID
                ,languageID
                ,waitFirstClick
            FROM factChat

Оказалось, что пользователи также не нуждаются в столбце chatVariables для анализа, поэтому я также удалил его из модели данных.

Наконец, после отключения опции Auto Date/Time в настройках загрузки данных размер моей модели данных составил около 220 МБ! Однако меня всё ещё беспокоил один момент: столбец chatID по-прежнему занимал почти треть моей таблицы. А это всего лишь суррогатный ключ, который не используется в отношениях внутри моей модели данных.

Я рассматривал два разных решения: первое — просто удалить этот столбец и агрегировать количество чатов, посчитав их с помощью оператора GROUP BY. Однако не было бы никакой пользы от того, чтобы оставить столбец chatID, поскольку он не используется нигде в нашей модели данных. После того как я удалил его из модели, давайте снова проверим размер файла pbix:

Напомню, с какого размера мы начали: 776 МБ! Мне удалось уменьшить размер модели данных почти на 90%, применив несколько простых техник, которые позволили движку VertiPaq выполнить более оптимальное сжатие данных.

И это был реальный случай, с которым я столкнулся за последний год!

Общие правила для уменьшения размера модели данных

В заключение, вот список общих правил, которые стоит учитывать при попытке уменьшить размер модели данных:

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

2. Старайтесь оптимизировать кардинальность столбцов, когда это возможно. Золотое правило здесь: тестируйте, тестируйте, тестируйте… и если есть значительная выгода, например, от разбиения одного столбца на два или замены десятичного столбца на два целых числа, то делайте это! Но также помните, что измерения нужно будет переписать, чтобы учитывать эти изменения в структуре, для того чтобы отобразить ожидаемые результаты. Так что, если ваша таблица не слишком большая или если вам нужно переписать сотни измерений, возможно, не стоит разбирать столбец. Как я уже сказал, это зависит от каждого конкретного случая, и вы должны тщательно оценить, какое решение имеет больше смысла.

3. Точно так же, как и со столбцами, оставляйте в модели данных только те строки, которые вам действительно нужны. Например, возможно, вам не нужно импортировать данные за последние 10 лет, а достаточно всего 5. Это также уменьшит размер вашей модели данных. Поговорите с пользователями, узнайте, что им действительно нужно, прежде чем слепо загружать все в модель данных.

4. Агрегируйте данные, когда это возможно. Это означает — меньше строк, меньшая кардинальность, всё то хорошое, чего вы хотите достичь. Если вам не нужны данные с точностью до часов, минут или секунд, не импортируйте их. Агрегации в Power BI (и в Табличной модели в целом) — это очень важная и обширная тема, которая выходит за рамки этой статьи, но я настоятельно рекомендую вам ознакомиться с блогом Фила Симарка и его серией статей о креативном использовании агрегаций.

5. Избегайте использования вычисляемых столбцов DAX, когда это возможно, поскольку они не сжимаются оптимальным способом. Вместо этого старайтесь перенести все вычисления в источник данных (например, в SQL базу данных) или выполнять их с помощью Power Query редактора.

6. Используйте правильные типы данных. Например, если ваша гранулярность данных на уровне дня, нет необходимости использовать тип данных Date/Time. Тип данных Date вполне подойдёт.

7. Отключите опцию Auto Date/Time для загрузки данных. Это удалит кучу автоматически создаваемых таблиц дат в фоновом режиме.

---

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

• 15 июля в 20:00 — Как строить визуализацию на больших данных: Superset + ClickHouse

• 17 июля в 20:00 — Событийная интеграция Postgres

• 23 июля в 20:00 — Мониторинг Postgres: Prometheus+Grafana

Хотите понять, насколько вы разбираетесь в PostgreSQL? Пройдите вступительное тестирование — оно поможет оценить уровень знаний.

Чтобы оставаться в курсе самых актуальных технологий и трендов, подписывайтесь на Telegram-канал OTUS.