Определяемые пользователем агрегаты
Агрегаты в Power BI могут повысить производительность запросов в больших семантических моделях DirectQuery. С помощью агрегатов данные кэшируются на агрегированном уровне в памяти. Агрегаты в Power BI можно настроить вручную в модели данных, как описано в этой статье. Для подписок Premium автоматически включите функцию автоматической агрегирования в Параметры модели.
Создание таблиц агрегирования
В зависимости от типа источника данных таблица агрегирования может быть создана в источнике данных в виде таблицы или представления собственного запроса. Для повышения производительности создайте таблицу агрегатов в виде таблицы импорта, созданной в Power Query. Затем вы используете диалоговое окно "Управление агрегатами" в Power BI Desktop, чтобы определить агрегаты для столбцов агрегирования с сводных данных, таблицей сведений и свойствами столбцов сведений.
Многомерные источники данных, такие как хранилища данных и киоски данных, могут использовать агрегаты на основе связей. Источники больших данных на основе Hadoop часто используют базовые агрегаты для столбцов GroupBy. В этой статье описываются типичные различия в моделировании данных Power BI для каждого типа источника данных.
Управление агрегатами
В области данных любого представления Power BI Desktop щелкните правой кнопкой мыши таблицу агрегирования и выберите пункт "Управление агрегатами".
В диалоговом окне "Управление агрегатами" отображается строка для каждого столбца в таблице, где можно указать поведение агрегирования. В следующем примере запросы к таблице сведений о продажах перенаправляются во внутреннюю таблицу агрегирования Sales Agg .
В этом примере агрегирования на основе связей записи GroupBy являются необязательными. За исключением DISTINCTCOUNT, они не влияют на агрегирование и в основном предназначены для удобства чтения. Без записей GroupBy агрегаты по-прежнему будут получать попадания на основе связей. Это отличается от примера больших данных далее в этой статье, где требуются записи GroupBy.
Проверки
Диалоговое окно "Управление агрегатами" обеспечивает проверку:
- Столбец сведений должен иметь тот же тип данных, что и столбец агрегирования, за исключением функций суммирования строк таблицы Count и Count. Строки таблицы count и Count доступны только для целых столбцов агрегирования и не требуют соответствующего типа данных.
- Агрегирование в цепочке, охватывающее три или более таблиц, не допускается. Например, агрегаты таблицы A не могут ссылаться на таблицу B , которая содержит агрегаты, ссылающиеся на таблицу C.
- Повторяющиеся агрегаты, в которых две записи используют одну и ту же функцию суммирования и ссылаются на один и тот же столбец сведений и таблицы сведений, не допускаются.
- Таблица сведений должна использовать режим хранения DirectQuery, а не импорт.
- Группирование по столбцу внешнего ключа, используемого неактивной связью, и использование функции USERELATIONSHIP для агрегирования попаданий не поддерживается.
- Агрегаты на основе столбцов GroupBy могут использовать связи между таблицами агрегирования, но связи создания между таблицами агрегирования не поддерживаются в Power BI Desktop. При необходимости можно создать связи между таблицами агрегирования с помощью стороннего средства или решения скриптов с помощью конечных точек XML для анализа (XMLA).
Большинство проверок применяются путем отключения раскрывающихся значений и отображения пояснительных текста в подсказке.
Таблицы агрегирования скрыты
Пользователи с доступом только для чтения к модели не могут запрашивать таблицы агрегирования. Доступ только для чтения позволяет избежать проблем безопасности при использовании с безопасностью на уровне строк (RLS). Потребители и запросы ссылаются на таблицу сведений, а не таблицу агрегирования и не нужно знать о таблице агрегирования.
По этой причине таблицы агрегирования скрыты из представления отчета . Если таблица еще не скрыта, при нажатии кнопки "Применить все" диалоговое окно "Управление агрегированиями" задает его скрытым.
режимы служба хранилища
Функция агрегирования взаимодействует с режимами хранения на уровне таблицы. Таблицы Power BI могут использовать режимы directQuery, импорта или двойного хранилища. DirectQuery запрашивает серверную часть напрямую, а импортирует данные в памяти и отправляет запросы в кэшированные данные. Все источники данных DirectQuery и импорта Power BI, не являющиеся многомерными, могут работать с агрегатами.
Чтобы задать режим хранения агрегированной таблицы для импорта, чтобы ускорить запросы, выберите агрегированную таблицу в представлении модели Power BI Desktop. В области "Свойства" разверните узел "Дополнительно", в раскрывающемся списке в служба хранилища режиме и выберите "Импорт". Изменение импорта необратимо.
Дополнительные сведения о режимах хранения таблиц см. в статье "Управление режимом хранения" в Power BI Desktop.
RLS для агрегирования
Чтобы правильно работать для агрегирования, выражения RLS должны фильтровать таблицу агрегирования и таблицу сведений.
В следующем примере выражение RLS в таблице Geography работает для агрегирования, так как Geography находится на стороне фильтрации связей с таблицей Sales и таблицей Sales Agg . Запросы, которые попали в таблицу агрегирования и запросы, которые не успешно применены RLS.
Выражение RLS в таблице Product фильтрует только таблицу сведений Sales, а не агрегированную таблицу Sales Agg. Так как таблица агрегирования является другим представлением данных в таблице сведений, небезопасно отвечать на запросы из таблицы агрегирования, если фильтр RLS не может быть применен. Фильтрация только таблицы сведений не рекомендуется, так как запросы пользователей из этой роли не получают преимущества от агрегирования.
Выражение RLS, которое фильтрует только таблицу агрегирования Sales Agg , а не таблицу сведений о продажах , запрещено.
Для агрегирования на основе столбцов GroupBy выражение RLS, примененное к таблице сведений, можно использовать для фильтрации таблицы агрегирования, так как все столбцы GroupBy в таблице агрегирования рассматриваются таблицей сведений. С другой стороны, фильтр RLS в таблице агрегирования не может быть применен к таблице сведений, поэтому запрещено.
Агрегирование на основе связей
Модели измерений обычно используют агрегаты на основе связей. Модели Power BI из хранилищ данных и киосков данных похожи на схемы star/snowflake с связями между таблицами измерений и таблицами фактов.
В следующем примере модель получает данные из одного источника данных. Таблицы используют режим хранения DirectQuery. Таблица фактов продаж содержит миллиарды строк. Установка режима хранения для импорта для кэширования будет потреблять значительные затраты на память и ресурсы.
Вместо этого создайте таблицу агрегирования Sales Agg . В таблице Sales Agg число строк равно сумме SalesAmount, сгруппированной по CustomerKey, DateKey и ProductSubcategoryKey. Таблица Sales Agg находится на более высокой детализации, чем Sales, поэтому вместо миллиардов она может содержать миллионы строк, которые проще управлять.
Если в следующих таблицах измерений чаще всего используются запросы с высокой бизнес-ценностью, они могут фильтровать sales Agg, используя связи "один ко многим" или "многие ко многим".
- Географический регион
- Клиент
- Дата
- Подкатегория продукта
- Категория продукта
На следующем рисунке показана эта модель.
В следующей таблице показаны агрегаты для таблицы Sales Agg .
Примечание.
Таблица Sales Agg , как и любая таблица, имеет гибкость загрузки различными способами. Агрегирование можно выполнить в исходной базе данных с помощью процессов ETL/ELT или выражения M для таблицы. Агрегированная таблица может использовать режим импорта хранилища с добавочным обновлением для семантических моделей или использовать DirectQuery и оптимизировать для быстрых запросов с помощью индексов columnstore. Эта гибкость позволяет сбалансированным архитектурам, которые могут распределять нагрузку запросов, чтобы избежать узких мест.
Изменение режима хранения агрегированной таблицы Sales Agg для импорта открывает диалоговое окно, указывающее, что связанные таблицы измерений могут быть заданы в режиме хранения двойной.
Если для связанных таблиц измерений задано значение "Двойной", они могут работать как "Импорт" или "DirectQuery" в зависимости от вложенных запросов. Ознакомьтесь со следующим примером:
- Запросы, которые агрегируют метрики из таблицы Sales Agg в режиме импорта и группируются по атрибутам из связанных двух таблиц, можно вернуть из кэша в памяти.
- Запросы, которые агрегируют метрики из таблицы DirectQuery Sales и группируются по атрибутам из связанных двух таблиц, можно возвращать в режиме DirectQuery. Логика запроса, включая операцию GroupBy, передается в исходную базу данных.
Дополнительные сведения о режиме двойного хранения см. в разделе "Управление режимом хранения" в Power BI Desktop.
Обычные и ограниченные связи
Агрегирование попаданий на основе связей требует регулярных связей.
Регулярные связи включают следующие сочетания режима хранения, в которых обе таблицы находятся из одного источника:
| Таблица со многими сторонами | Таблица на стороне 1 |
|---|---|
| Двойной | Двойной |
| Import | Импорт или двойной |
| DirectQuery | DirectQuery или Dual |
Единственным случаем, когда связь между источниками считается регулярной, является то, что обе таблицы имеют значение Import. Отношения "многие ко многим" всегда считаются ограниченными.
Сведения об агрегированиях между источниками , которые не зависят от связей, см. в разделе "Агрегаты" на основе столбцов GroupBy.
Примеры агрегирования запросов на основе связей
Следующий запрос попадает в агрегирование, так как столбцы в таблице date находятся на гранулярности, которая может попасть в агрегирование. В столбце SalesAmount используется агрегирование сумм .
Следующий запрос не попадает в агрегат. Несмотря на запрос суммы SalesAmount, запрос выполняет операцию GroupBy в столбце в таблице Product , которая не находится на гранулярности, которая может попасть в агрегат. Если вы наблюдаете связи в модели, подкатегория продукта может иметь несколько строк продукта . Запрос не сможет определить, к какому продукту следует агрегироваться. В этом случае запрос отменить изменения в DirectQuery и отправляет SQL-запрос в источник данных.
Агрегаты не просто для простых вычислений, которые выполняют простую сумму. Сложные вычисления также могут воспользоваться преимуществами. Концептуально сложное вычисление разбивается на вложенные запросы для каждой суммы, MIN, MAX и COUNT. Каждый вложенный запрос оценивается, чтобы определить, может ли он попасть в агрегат. Эта логика не имеет значения true во всех случаях из-за оптимизации плана запросов, но в целом она должна применяться. В следующем примере выполняется агрегирование:
Функция COUNTROWS может воспользоваться агрегатами. Следующий запрос попадает в агрегирование, так как для таблицы Sales определена агрегирование строк таблицы Count.
Функция AVERAGE может воспользоваться агрегатами. Следующий запрос попадает в агрегирование, так как среднее внутреннее значение сложено в сумму, разделенную счетчиком. Так как столбец UnitPrice содержит агрегаты, определенные как для СУММ, так и ДЛЯ COUNT, выполняется агрегирование.
В некоторых случаях функция DISTINCTCOUNT может воспользоваться агрегатами. Следующий запрос попадает в агрегат, так как в таблице агрегирования имеется запись GroupBy для CustomerKey, которая поддерживает уникальность CustomerKey в таблице агрегирования. Этот метод по-прежнему может повлиять на порог производительности, когда более двух-пяти миллионов различающихся значений могут повлиять на производительность запросов. Однако это может быть полезно в сценариях, когда в таблице подробных сведений есть миллиарды строк, но два до пяти миллионов разных значений в столбце. В этом случае ФУНКЦИЯ DISTINCTCOUNT может выполняться быстрее, чем сканировать таблицу с миллиардами строк, даже если она была кэширована в память.
Функции аналитики времени выражений анализа данных (DAX) поддерживают агрегирование. Следующий запрос попадает в агрегирование, так как функция DATEYTD создает таблицу значений CalendarDay , а таблица агрегирования находится на гранулярности, которая охватывает столбцы группы по столбцам в таблице Date . Это пример табличного фильтра для функции CALCULATE, которая может работать с агрегатами.
Агрегирование на основе столбцов GroupBy
Модели больших данных на основе Hadoop имеют разные характеристики, отличные от трехмерных моделей. Чтобы избежать соединения между большими таблицами, модели больших данных часто не используют связи, но денормализировать атрибуты измерения к таблицам фактов. Такие модели больших данных можно разблокировать для интерактивного анализа с помощью агрегатов на основе столбцов GroupBy.
В следующей таблице содержатся числовые столбцы перемещения , которые необходимо агрегировать. Все остальные столбцы являются атрибутами для группировки по. Таблица содержит данные Интернета вещей и большое количество строк. Режим хранения — DirectQuery. Запросы к источнику данных, агрегирующиеся по всей модели медленно из-за объемного объема.
Чтобы включить интерактивный анализ этой модели, можно добавить таблицу агрегирования, которая группирует большинство атрибутов, но исключает атрибуты высокой карта inality, такие как долгота и широта. Это значительно уменьшает количество строк и достаточно мало, чтобы удобно поместиться в кэш в памяти.
Вы определяете сопоставления агрегирования для таблицы Agg действия драйвера в диалоговом окне "Управление агрегатами ".
В агрегатах на основе столбцов GroupBy записи GroupBy не являются необязательными. Без них агрегаты не получают попадания. Это отличается от использования агрегатов на основе связей, в которых записи GroupBy являются необязательными.
В следующей таблице показаны агрегаты для таблицы Agg активности драйвера.
Вы можете задать режим хранения агрегированной таблицы действий драйвера для импорта.
Пример запроса агрегирования GroupBy
Следующий запрос попадает в агрегат, так как столбец даты действия охватывается таблицей агрегирования. Функция COUNTROWS использует агрегирование строк таблицы Counted.
Особенно для моделей, содержащих атрибуты фильтра в таблицах фактов, рекомендуется использовать агрегирование строк таблицы Count. Power BI может отправлять запросы в модель с помощью COUNTROWS в тех случаях, когда он явно не запрашивается пользователем. Например, в диалоговом окне фильтра отображается количество строк для каждого значения.
Объединенные методы агрегирования
Вы можете объединить методы связей и столбцов GroupBy для агрегирования. Агрегаты на основе связей могут требовать разделения денормализованных таблиц измерений на несколько таблиц. Если это затратно или нецелесообразно для определенных таблиц измерений, можно реплика указать необходимые атрибуты в таблице агрегирования для этих измерений и использовать связи для других.
Например, следующая модель реплика tes Month, Квартал, Семестр и Год в таблице Sales Agg. Между Sales Agg и таблицей date нет связей с клиентом и подкатегорией продукта. Режим хранения agg sales — Import.
В следующей таблице показаны записи, заданные в диалоговом окне "Управление агрегатами " для таблицы Sales Agg . Записи GroupBy, в которых дата является таблицей сведений, являются обязательными, чтобы получить агрегаты для запросов, группирующихся по атрибутам Date. Как и в предыдущем примере, записи GroupBy для CustomerKey и ProductSubcategoryKey не влияют на агрегирование попаданий, за исключением DISTINCTCOUNT, из-за наличия связей.
Примеры объединенных статистических запросов
Следующий запрос попадает в агрегат, так как таблица агрегирования охватывает CalendarMonth и CategoryName доступна через связи "один ко многим". SalesAmount использует агрегирование СУММ .
Следующий запрос не попадает в агрегат, так как таблица агрегирования не охватывает CalendarDay.
Следующий запрос аналитики времени не попадает в агрегат, так как функция DATEYTD создает таблицу значений CalendarDay, а таблица агрегирования не охватывает CalendarDay.
Приоритет агрегирования
Приоритет агрегирования позволяет учитывать несколько таблиц агрегирования одним вложенным запросом.
В следующем примере представлена составная модель , содержащая несколько источников:
- Таблица DirectQuery driver activity содержит более триллионов строк данных Интернета вещей, полученных из системы больших данных. Он служит детализацией запросов для просмотра отдельных операций чтения Интернета вещей в управляемых контекстах фильтра.
- Таблица Agg действия драйвера — это промежуточная таблица агрегирования в режиме DirectQuery. Он содержит более миллиарда строк в Azure Synapse Analytics (прежнее название — хранилище данных SQL) и оптимизирован в источнике с помощью индексов columnstore.
- Таблица импорта действия драйвера Agg2 имеет высокую степень детализации, так как атрибуты группы являются небольшими и низкими карта inality. Количество строк может быть не более тысяч, поэтому оно может легко поместиться в кэш в памяти. Эти атрибуты используются высокопрофилной информационной панелью исполнительной панели, поэтому запросы, ссылающиеся на них, должны быть как можно быстрее.
Примечание.
Таблицы агрегирования DirectQuery, использующие другой источник данных из таблицы сведений, поддерживаются только в том случае, если таблица агрегирования выполняется из источника SQL Server, SQL Azure или Azure Synapse Analytics (прежнее название — хранилище данных SQL).
Объем памяти этой модели относительно мал, но он разблокирует огромную модель. Она представляет сбалансированную архитектуру, так как она распределяет нагрузку запросов между компонентами архитектуры, используя их на основе их сильных сторон.
Диалоговое окно "Управляемые агрегаты" для действия драйвера Agg2 задает для поля приоритета значение 10, что выше, чем для Agg действия драйвера. Более высокий параметр приоритета означает, что запросы, использующие агрегаты, сначала рассматривают действие драйвера Agg2 . Вложенные запросы, которые не относятся к детализации, которые можно ответить на действия драйвера Agg2, могут вместо этого рассматривать действие драйвера . Подробные запросы, которые не могут быть ответы в любой таблице агрегирования, могут направляться в действие драйвера.
Таблица, указанная в столбце "Таблица сведений", является действием драйвера, а не параметром "Действие драйвера", так как агрегирование в цепочке запрещено.
В следующей таблице показаны агрегаты для таблицы "Действие драйвера" agg2 .
Определение того, попали ли запросы или пропустили агрегаты
Sql Profiler может определить, возвращаются ли запросы из подсистемы хранилища кэша в памяти или отправляются в источник данных DirectQuery. Вы можете использовать тот же процесс, чтобы определить, попадают ли агрегаты. Дополнительные сведения см. в разделе "Запросы, которые попали в кэш или отсутствуют".
Sql Profiler также предоставляет расширенное Query Processing\Aggregate Table Rewrite Query событие.
В следующем фрагменте JSON показан пример выходных данных события при использовании агрегата.
- matchingResult показывает, что вложенный запрос использовал агрегирование.
- dataRequest показывает столбцы GroupBy и агрегированные столбцы, используемые вложенным запросом.
- Сопоставление показывает столбцы в таблице агрегирования, с которыми были сопоставлены.
Сохранение кэшей в синхронизации
Агрегаты, которые объединяют режимы DirectQuery, Import и/или Dual storage, могут возвращать разные данные, если кэш в памяти не синхронизирован с исходными данными. Например, выполнение запроса не пытается маскировать проблемы с данными, отфильтровав результаты DirectQuery для сопоставления кэшированных значений. При необходимости существуют установленные методы для обработки таких проблем в источнике. Оптимизации производительности следует использовать только таким образом, чтобы не компрометировать вашу способность соответствовать бизнес-требованиям. Это ваша ответственность за то, чтобы знать потоки данных и разрабатывать их соответствующим образом.
Рекомендации и ограничения
Агрегаты не поддерживают динамические параметры запроса M.
Начиная с августа 2022 г. из-за изменений в функциональности Power BI игнорирует таблицы агрегирования режима импорта с включенными источниками данных с поддержкой единого входа из-за потенциальных рисков безопасности. Чтобы обеспечить оптимальную производительность запросов с агрегатами, рекомендуется отключить единый вход для этих источников данных.
Сообщество
Power BI имеет активное сообщество, где MVPs, бизнес-специалисты и одноранговые специалисты делятся опытом в группах обсуждений, видео, блогах и многое другое. При изучении агрегатов обязательно проверка эти дополнительные ресурсы: