Агрегации, определяемые пользователем

Агрегаты в Power BI могут повысить производительность запросов в больших семантических моделях DirectQuery. С помощью агрегатов данные кэшируются на агрегированном уровне в памяти. Вы можете вручную настроить агрегаты в Power BI в модели данных, как описано в этой статье. Для подписок Premium можно включить функцию автоматического агрегирования в параметрах модели и тем самым создавать их автоматически.

Создание таблиц агрегирования

В зависимости от типа источника данных можно создать таблицу агрегирования в источнике данных как таблицу или представление, собственный запрос. Для максимальной производительности создайте таблицу агрегирования в виде таблицы импорта, созданной в Power Query. Используйте диалоговое окно "Управление агрегатами" в Power BI Desktop, чтобы определить агрегаты для столбцов агрегирования с сводных данных, таблицей сведений и свойствами столбцов сведений.

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

Управление агрегациями

В области данных любого представления Power BI Desktop щелкните правой кнопкой мыши таблицу агрегирования и выберите Управление агрегатами.

В диалоговом окне управления агрегатами отображается строка для каждого столбца таблицы, где можно указать поведение агрегирования. В следующем примере запросы к таблице сведений о Sales перенаправляются во внутреннюю таблицу агрегации Sales Agg.

В этом примере агрегирования на основе связей записи GroupBy являются необязательными. За исключением DISTINCTCOUNT, они не влияют на агрегирование и в основном предназначены для удобства чтения. Без записей GroupBy агрегации по-прежнему затрагиваются на основе связей. Это поведение отличается от примера больших данных далее в этой статье, где требуются записи GroupBy.

Проверки

Диалоговое окно Управление агрегатами применяет проверки:

• Столбец сведений должен иметь тот же тип данных, что и столбец агрегирования, за исключением функций суммирования строк таблицы Count и Count. Строки таблицы count и Count доступны только для целых столбцов агрегирования и не требуют соответствующего типа данных.
• Цепное агрегирование, охватывающее три или более таблиц, не допускается. Например, агрегации в таблице A не могут ссылаться на таблицу B, которая имеет агрегации, ссылающиеся на таблицу C.
• Повторяющиеся агрегаты, в которых две записи используют одну и ту же функцию сводки и ссылаются на одну и ту же таблицу сведений и столбец сведений , не допускаются.
• Таблица сведений должна использовать режим хранения DirectQuery, а не импорт.
• Группировка по столбцу внешнего ключа, используемого неактивной связью, и использование функции USERELATIONSHIP для агрегации данных не поддерживается. В качестве альтернативы можно использовать функцию TREATAS вместо USERELATIONSHIP. При использовании TREATAS убедитесь, что между таблицами нет активных связей. Агрегаты по-прежнему могут быть поражены при использовании TREATAS с этой конфигурацией.
• Агрегаты на основе столбцов GroupBy могут использовать связи между таблицами агрегирования, но создание связей между таблицами агрегирования не поддерживается в Power BI Desktop. При необходимости, вы можете создавать связи между таблицами агрегирования с помощью стороннего инструмента или решения на основе скриптов через конечные точки XMLA (XML for Analysis).

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

Таблицы агрегирования скрыты

Пользователи с доступом только для чтения к модели не могут запрашивать таблицы агрегирования. Доступ только для чтения позволяет избежать проблем безопасности при использовании безопасности на уровне строк (RLS). Потребители и запросы ссылаются на таблицу сведений, а не таблицу агрегирования и не нужно знать о таблице агрегирования.

По этой причине таблицы агрегирования скрыты из представления отчета. Если таблица еще не скрыта, диалоговое окно Управление агрегированиями сделает ее скрытой при выборе Применить все.

Режимы хранения

Функция агрегирования работает с режимами хранения на уровне таблицы. Таблицы Power BI могут использовать DirectQuery, Import, или Dual режимы хранения. DirectQuery отправляет запросы непосредственно на сервер, тогда как режим импорта данных кэширует данные в памяти и отправляет запросы к кэшированным данным. Все источники данных Power BI Import и DirectQuery, не являющиеся многомерными, работают с агрегатами.

Чтобы задать режим хранения агрегированной таблицы для импорта, чтобы ускорить запросы, выберите агрегированную таблицу в представлении модели Power BI Desktop. В области свойств разверните узел Advanced, в раскрывающемся списке в режиме храненияи выберите Импорт. После настройки режима хранения импорта его нельзя изменить снова.

Дополнительные сведения о режимах хранения таблиц см. в разделе "Управление режимом хранения" в Power BI Desktop.

RLS для агрегаций

Чтобы правильно работать для агрегирования, выражения RLS должны фильтровать как таблицу агрегирования, так и таблицу сведений.

В следующем примере выражение RLS в таблице Geography работает для агрегирования, так как Geography находится на стороне фильтрации связей с таблицей Sales и таблицей Sales Agg. Запросы, использующие таблицу агрегирования, и запросы, которые не используют её, оба успешно применяют RLS.

Выражение RLS в таблице Product фильтрует только подробную таблицу Sales, а не агрегированную таблицу Sales Agg. Так как таблица агрегирования является другим представлением данных в таблице сведений, небезопасно отвечать на запросы из таблицы агрегирования, если фильтр RLS не может быть применен. Фильтрация только таблицы сведений не рекомендуется, так как запросы пользователей из этой роли не получают преимущества от агрегирования.

Выражение RLS, которое фильтрует только таблицу агрегирования Sales Agg, а не таблицу деталей продаж Sales, не разрешается.

Для агрегирования на основе столбцов GroupBy выражение RLS, примененное к таблице сведений, может фильтровать таблицу агрегирования, так как все столбцы GroupBy в таблице агрегатов рассматриваются таблицей сведений. С другой стороны, фильтр RLS в таблице агрегирования не может фильтровать таблицу сведений, поэтому она запрещена.

Агрегирование на основе связей

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

В следующем примере модель получает данные из одного источника данных. Таблицы используют режим хранения DirectQuery. Таблица фактов Sales содержит миллиарды строк. Установка режима хранилища Sales на импорт для кэширования будет потреблять значительное количество памяти и создавать дополнительную нагрузку на ресурсы.

Вместо этого создайте таблицу агрегирования Sales Agg. В таблице Sales Agg количество строк равно сумме SalesAmount, сгруппированных по CustomerKey, DateKeyи ProductSubcategoryKey. Таблица sales Agg имеет более высокую степень детализации, чем sales, поэтому вместо миллиардов, она может содержать миллионы строк, которые проще управлять.

Если в следующих таблицах измерений чаще всего используются запросы с высокой бизнес-ценностью, они могут фильтровать Sales Agg, используя одно ко многим или отношениях "многие ко одному".

• География
• Клиент
• Дата
• Подкатегория продукта
• Категория продукта

На следующем рисунке показана эта модель.

В следующей таблице показаны агрегации для таблицы Sales Agg.

Изменение режима хранения агрегированной таблицы Sales Agg на Import открывает диалоговое окно, указывающее, что связанные таблицы измерений можно задать для режима хранения Dual.

диалоговое окноStorage mode dialogрежима хранения

Если для связанных таблиц измерений задано значение "Dual", они могут работать как "Импорт", так и "DirectQuery" в зависимости от подзапроса. В примере:

• Запросы, которые агрегируют метрики из таблицы Sales Agg в режиме импорта и группируются по атрибутам из связанных двух таблиц, возвращают результаты из кэша в памяти.
• Запросы, которые агрегируют метрики из таблицы DirectQuery Sales и группируются по атрибутам из связанных двух таблиц, возвращают результаты в режиме DirectQuery. Логика запроса, включая операцию GroupBy, передается в исходную базу данных.

Дополнительные сведения о режиме двойного хранения см. в разделе Управление режимом хранения в Power BI Desktop.

Обычные и ограниченные связи

Агрегация результатов на основе связей требует регулярных связей.

Регулярные связи включают следующие сочетания режима хранения, в которых обе таблицы находятся из одного источника:

Единственный случай, когда связь между источниками является регулярной, это когда для обеих таблиц установлено значение «Import». Связи "многие ко многим" всегда ограничены.

Сведения о попаданиях агрегирования источника, которые не зависят от связей, см. Агрегирования на основе столбцов GroupBy.

Примеры агрегирования запросов на основе связей

Следующий запрос использует агрегирование, так как столбцы в таблице Date имеют гранулярность, которая позволяет использовать агрегирование. В столбце SalesAmount используется агрегация Sum.

Следующий запрос не использует агрегирование. Несмотря на запрос суммы значения SalesAmount, запрос выполняет операцию GroupBy по столбцу в таблице Product, который не обладает необходимой детализацией для использования агрегации. Если вы наблюдаете связи в модели, подкатегория продукта может иметь несколько строк Product. Запрос не может определить, к какому продукту необходимо агрегироваться. В этом случае запрос возвращается к DirectQuery и отправляет SQL-запрос в источник данных.

запрос

Агрегации нужны не только для простых подсчётов, выполняющих сумму. Сложные вычисления также могут извлечь выгоду. Концептуально сложное вычисление разбивается на подзапросы для каждой суммы, min, max и count. Каждый вложенный запрос оценивается, чтобы определить, может ли он использовать агрегирование. Эта логика не является верной во всех случаях вследствие оптимизации плана запросов, но в общем случае она должна быть применима. В следующем примере используется агрегирование:

Функция COUNTROWS может выиграть благодаря агрегациям. Следующий запрос использует агрегирование, так как для таблицы Sales определена агрегирование строк таблицы Count.

Функция AVERAGE может получать преимущества от агрегаций. В следующем запросе используется функция агрегирования, так как среднее значение представляется как сумма, разделенная на количество. Поскольку для столбца UnitPrice определены агрегации как для суммы, так и для количества, используется агрегирование.

В некоторых случаях функция DISTINCTCOUNT может воспользоваться агрегациями. Следующий запрос использует агрегирование, так как в таблице агрегирования есть запись GroupBy для CustomerKey, которая поддерживает уникальность CustomerKey в таблице агрегирования. Этот метод по-прежнему может повлиять на порог производительности, когда более 2–5 миллионов различных значений могут повлиять на производительность запросов. Однако это может быть полезно в сценариях, когда в таблице подробных сведений есть миллиарды строк, но 2–5 миллионов разных значений в столбце. В этом случае ФУНКЦИЯ DISTINCTCOUNT может выполняться быстрее, чем сканировать таблицу с миллиардами строк, даже если она была кэширована в память.

Функции временного интеллекта в выражениях анализа данных (DAX) учитывают агрегирование. Следующий запрос использует агрегирование, потому что функция DATESYTD создает таблицу значений CalendarDay, а таблица агрегирования имеет такую степень детализации, которая охватывается столбцами группировки в таблице Date. Это пример табличного фильтра для функции CALCULATE, которая может работать с агрегациями.

Агрегирование на основе столбцов GroupBy

Модели больших данных на основе Hadoop имеют разные характеристики, отличные от трехмерных моделей. Чтобы избежать соединения между большими таблицами, модели больших данных часто не используют связи; вместо этого, они денормализуют, перемещая атрибуты измерений в таблицы фактов. Для интерактивного анализа такие модели большого объема данных можно открыть с помощью агрегаций на основе столбцов GroupBy.

В следующей таблице содержится числовой столбец перемещения для агрегирования. Все остальные столбцы являются атрибутами для группировки данных. Таблица содержит данные Интернета вещей и большое количество строк. Режим хранения — DirectQuery. Запросы к источнику данных, агрегирующиеся по всей модели, медленно выполняются из-за объема.

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

таблица

Определите сопоставления агрегирования для таблицы агрегация активности драйвера в диалоговом окне Управление агрегациями.

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

В следующей таблице показаны агрегации для таблицы Driver Activity Agg.

Задайте режим хранения импорта для таблицы агрегированных данных о действиях драйвера Driver Activity Agg.

Пример запроса на агрегирование с использованием GroupBy

Следующий запрос использует агрегирование, так как столбец "Дата действия " охватывается таблицей агрегирования. Функция COUNTROWS использует подсчитанные строки таблицы.

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

Объединенные методы агрегирования

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

Например, следующая модель реплицирует месяц, квартал, семестри год в таблице Sales Agg. Нет связи между Sales Agg и таблицей Дата, но существуют связи с Клиент и Product Subcategory. Режим хранения Sales Agg — импорт.

В следующей таблице показаны записи, заданные в диалоговом окне Управление агрегатами для таблицы Sales Agg. Записи GroupBy, где Дата является таблицей деталей, обязательны для использования агрегирования в запросах, которые группируют по атрибутам Дата. Как и в предыдущем примере, записи GroupBy для CustomerKey и ProductSubcategoryKey не влияют на использование агрегирования, за исключением DISTINCTCOUNT, из-за наличия связей.

Примеры объединенных статистических запросов

Следующий запрос использует агрегирование, так как таблица агрегирования охватывает CalendarMonth, и вы можете получить доступ к CategoryName через связи "один ко многим". Запрос использует агрегирование СУММ для SalesAmount.

пример запроса

Следующий запрос не использует агрегирование, так как таблица агрегирования не охватывает CalendarDay.

Следующий запрос аналитики времени не использует агрегирование, так как функция DATEYTD создает таблицу значений CalendarDay , а таблица агрегирования не охватывает CalendarDay.

Приоритет агрегирования

Приоритет агрегирования позволяет одному вложенному запросу рассмотреть несколько таблиц агрегирования.

В следующем примере показана составная модель с несколькими источниками:

• Таблица DirectQuery driver Activity содержит более триллионов строк данных Интернета вещей, полученных из системы больших данных. Он служит детализацией запросов для просмотра отдельных операций чтения Интернета вещей в управляемых контекстах фильтра.
• Таблица агрегирования активности драйвера — это промежуточная таблица в режиме DirectQuery. Он содержит более миллиарда строк в Azure Synapse Analytics (прежнее название — хранилище данных SQL) и оптимизирован в источнике с помощью индексов columnstore.
• Таблица импорта действий драйвера Agg2 имеет высокую степень детализации, так как атрибуты группы являются небольшими и низкими кратностью. Количество строк может быть не более тысяч, поэтому оно может легко поместиться в кэш в памяти. Эти атрибуты используются важной информационной панелью для руководителей, поэтому запросы, ссылающиеся на них, должны выполняться максимально быстро.

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

таблицы

Диалоговое окно управляемых агрегаций для активности драйвера Agg2 задает полю приоритет значение 10, что выше, чем для активности драйвера. Более высокий параметр приоритета означает, что запросы, использующие агрегаты, сначала рассматривают Активность драйвера Agg2. Вложенные запросы, которые не на том уровне детализации, на котором Driver Activity Agg2 может предоставить ответ, могут вместо этого рассмотреть Driver Activity Agg. Запросы подробных сведений, которые не могут быть обработаны ни одной из таблиц агрегирования, могут направляться в Активность драйвера.

Таблица, указанная в столбце таблицы сведений, - это Driver Activity, а не Driver Activity Agg, поскольку агрегирование в цепочке не допускается.

В следующей таблице показаны агрегации для таблицы Активность драйвера 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 игнорирует таблицы агрегирования режима импорта с включенными источниками данных с поддержкой единого входа из-за потенциальных рисков безопасности. Для обеспечения оптимальной производительности запросов с агрегациями отключите SSO для этих источников данных.

Сообщество

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

• сообщество Power BI
• Ищите "Агрегаты Power BI" на Bing

Связанный контент

• Автоматические агрегации
• Составные модели