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

Пример набора данных

Если вы хотите следовать сценарию этого примера, вам понадобится база данных SQL Server с именем AdventureWorksDW. Мы внесли некоторые изменения в свой набор данных, чтобы он был немного больше по размеру его таблицы фактов, поэтому мы можем показать вам функциональность агрегатов. Вы можете скачать ссылку на базу данных здесь:

Download

Пример модели

В этом шаблоне модели мы будем анализировать данные FactInternetSales (которые мы считаем нашей большой таблицей фактов). Создайте отчет Power BI с подключением DirectQuery к SQL Server;

С помощью параметра DirectQuery выберите следующие таблицы:

FactInternetSales, DimCustomer, DimDate, DimProduct, DimProductCategory, DimProductSubCategory, DimPromotion и DimGeography.

Таблицы, загруженные в Power BI, будут иметь совместные связи, мы просто ограничили связь между таблицей DimDate и FactInternetSales с одной активной связью, основанной на DateKey (в DimDate) и OrderDateKey (в FactInternetSales). Это диаграмма отношений.

Что такое агрегированная  таблица

Агрегированная таблица представляет собой таблицу в Power BI, объединенную одним или несколькими полями из исходной таблицы DirectQuery. В нашем случае агрегированная таблица представляет собой сгруппированную таблицу по определенным полям из таблицы FactInternetSales. Совокупная таблица может быть создана всеми возможными способами. Вы можете создать агрегированную таблицу с операторами T-SQL из SQL Server. Или вы можете создать ее в Power Query. Вы можете создать его во всех других инструментах преобразования данных и языках запросов. Поскольку мы здесь делаем все с помощью Power BI, мы создадим агрегированную таблицу с Power Query.

Агрегированную таблицу можно создать в источнике данных с запросами T-SQL или в Power Query или в другом месте, где вы можете создать сгруппированную таблицу.

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

Перейдите в Power Query Editor и выберите таблицу FactInternetSales. Агрегированная таблица, которую мы собираемся создать в этом примере, будет состоять из трех полей: OrderDateKey, CustomerKey и ProductSubCategoryKey. Первые два поля существуют в FactInternetSales, но не в третьем. Однако, используя столбцы связей, мы можем получить это.

Прокрутите направо в столбцах таблицы FactInternetSales, чтобы найти Product, затем нажмите Expand. В параметрах Expand просто выберите ProductSubCategoryKey.

Теперь, когда вы получили ProductSubCategoryKey в таблице, вы можете применить Group By. На вкладке Transformation нажмите Group By;

Когда появится диалоговое окно Group By, выберите Advanced

Выберите три поля OrderDateKey, CustomerKey и ProductSubcategoryKey в полях Group By

Затем добавьте четыре агрегации, как показано ниже:

После построения этой таблицы назовем ее Sales Agg table. Вот скриншот этой таблицы.

Ключевое рассмотрение предстоящих шагов

Агрегационные столбцы в агрегированной таблице должны соответствовать определенным правилам.

Правило № 1: точное соответствие для типов данных агрегации по Sum, Min, Max, Average

Столбцы, к которым применяется такая операция, как Sum, Average, Min или Max, должны иметь точно такой же тип данных, что и исходный столбец источника после агрегации. Если они этого не делают, убедитесь, что вы изменили типы данных, чтобы они были одинаковыми.

Поле SalesAmount в FactInternetSales имеет десятичный тип данных (если нет, измените его на это)

В столбце SalesAmount_Sum таблицы Sales Agg также должен быть одинаковый тип данных. В этом случае Decimal.

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

Этот процесс в нашем примере должен быть завершен для двух столбцов SalesAmount_Sum и UnitPrice_Sum.

Правило №2: Тип данных с полным номером является обязательным для агрегаций по Count

Любые агрегации, которые используют Count в качестве функции агрегации, должны иметь тип данных всего числа или, скажем, Integer. В нашей примерной таблице у нас есть два столбца с функцией Count:

Вы должны убедиться, что эти два столбца имеют тип данных целого числа после Group by transform;

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

Агрегированная таблица - это таблица импорта

Совокупная таблица, которую мы создали, называется Sales Agg. Поскольку эта таблица намного меньше, чем таблица FactInternetSales, ее можно сохранить в памяти. Таким образом, мы получаем максимальную производительность отклика запроса, когда мы запрашиваем что-то на агрегированном уровне.

Теперь ваша модель данных в Power BI должна иметь таблицы ниже, после завершения этого шага:

Поздравляем, вы создали сводную таблицу, и теперь вы можете создавать отношения между этой таблицей и тремя таблицами измерений: DimCustomer, DimDate и DimProductSubcategory. Вот диаграмма полной взаимосвязи;

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

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

No Comments