Вычисляемые таблицы впервые были представлены в обновлении Power BI Desktop в сентябре 2015 года. Название говорит само за себя; это таблицы, созданные путем вычислений. Так как это таблицы внутри памяти, их расчет основан на DAX (язык выражения для анализа данных). Существует много преимуществ использования вычисляемых таблиц, таких как их использование для ролевых измерений (например, имеющих более одного измерения даты в модели). Существуют некоторые функции и выражения DAX, которые возвращают таблицу в результате и используют их в качестве таблицы в вашей модели, что порой очень полезно. Например, вы можете создать таблицу для 10 лучших клиентов, а затем использовать ее в качестве основной исходной таблицы во многих отчетах. В этой статье мы объясним вам как использовать вычисляемые таблицы на некоторых примерах.

Необходимое условие

Для запуска этого примера вам необходимо установить базу данных Adventure Works DW на SQL Server или загрузить версию Excel из нее:

AdventureWorksDW2012 Excel File

Ролевое измерение

Самая первая функциональность, которая приходит на ум, когда мы говорим о вычисляемых таблицах, - это способность создавать ролевые измерения. Ролевые измерения - это измерения с той же структурой и строками данных, которые играют разные роли в нашей модели данных. Например, Date Dimension является общим измерением. Однако в таблице транзакций продаж у вас может быть несколько столбцов даты для связи с измерением даты. в приведенном ниже примере у нас есть три поля даты в таблице FactInternetSales: дата заказа (Order Date), дата отправки (Ship Date) и дата выполнения (Due Date).

Три поля должны быть связаны с тремя разными размерами даты (потому что табулярная модель, на которой основана Power BI, не поддерживает встроенные параметры ролевых измерений). Таким образом, вы можете просто загрузить измерение даты в разделе Get Data с SQL Server или пустым запросом из сценария Power Query . Ниже приведен пример измерения даты, загруженного в Power BI Desktop (через Get Data):

Теперь мы можем создать ролевые измерения с созданием вычисляемой таблицы:

и это создаст для нас таблицу в памяти и позволит написать определение таблицы

Язык определения таблицы - DAX (Data Analysis eXpression). Теперь давайте просто проследим, как это работает в действии. Нам нужна точная копия таблицы DimDate, поэтому мы можем просто использовать функцию ALL в DAX, как показано ниже:

Как только мы напечатаем это выражение и нажмем Enter, мы увидим результат под ним в виде строк данных, а также список столбцов на панели Fields. Мы создали свое ролевое измерение так же просто. Теперь мы можем установить отношения;

Для вышеописанных отношений мы создали также измерение Due Date и переименовал оригинальный DimDate в Date Order.

Структура в памяти, меньшее время обновления

Вычисляемая таблица загружается в память, поэтому ваш размер файла Power BI будет увеличиваться. Однако вам не нужно читать их снова из внешнего источника данных. Да, вы можете создавать несколько представлений в исходной базе данных и подключаться к ним через раздел Get Data с помощью Power Query, однако их данные должны заполняться из исходной базы данных каждый раз, когда происходит обновление (как по расписанию, так и вручную).

Без вычисляемых таблиц: Ниже приведен пример трех таблиц дат, загруженных из внешнего источника данных:

С вычисляемыми таблицами: и здесь загружено только одно измерение даты (для приведенного выше примера ролевого измерения):

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

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

Функции таблицы DAX

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

Топ-100 клиентов в качестве вычисляемой таблицы

Существует много примеров того, как бизнес рассматривает 10 лучших или 20 лучших клиентов и фильтрует всю панель инструментов и набор отчетов только для них. Обычно основная причина заключается в том, что топ-10, 20 клиентов принесет большую часть дохода бизнесу. К счастью, в DAX есть функция TOPN, которая помогает нам построить такие вычисления. Функция TOPN возвращает таблицу. С помощью TOPN мы можем выбрать, сколько строк мы хотим в наборе результатов, и использовать функцию группировки (если она есть) и агрегация (если таковая имеется).

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

Summarize

Summarize - это функция DAX, которая генерирует группировку по списку из таблицы. Summarize работает похожим образом с Group By в T-SQL. Поэтому, если мы хотим создать таблицу с CustomerKeys и их общую сумму продаж, мы можем написать это выражение:

Customer Sales = SUMMARIZE(FactInternetSales,FactInternetSales[CustomerKey], "Total Sales", SUM(FactInternetSales[Total Sales]))

Вот подробности о параметрах, которые я передал в приведенном выше выражении для функции Summarize:

• Первый параметр: Таблица-источник. FactInternetSales - это исходная таблица, к которой мы хотим применить операция (Summarize).
• Второй параметр: группа по столбцу. CustomerKey в таблице FactInternetSales - это столбец, который мы хотим использовать в качестве ключа для группировки.
• Третий параметр: имя столбца вывода. Мы назвали выведенное вычисленное имя столбца как Total Sales.
• Четвертый параметр: вычисление столбца вывода. Здесь мы пишем расчет для выходного столбца, который является просто суммой Total Sales Column.

Таким образом, у нас будет таблица с CustomerKey и Total Sales.

TOPN

Теперь, когда у нас есть список клиентов с их полным объемом продаж, легко получить 100 лучших клиентов. Мы можем просто использовать функцию TOPN, подобную этой, для создания другой вычисляемой таблицы (можно было сделать этот пример только с одной вычисляемой таблицей вместо двух, но мы сделали это только с двумя таблицами, чтобы лучше понять логику);

Top 10 Customers = TOPN(100,'Customer Sales','Customer Sales'[Total Sales],DESC)

И это выражение означает:

• Первый параметр: количество возвращаемых строк. 100 для 100 лучших клиентов.
• Второй параметр: Исходная таблица. Продажи клиентов являются источником этой операции, которую мы хотим получить от 100 лучших клиентов.
• Третий параметр: порядок по столбцу. Мы хотим заказать таблицу на основе общих продаж каждого клиента.
• Четвертый параметр: порядок по выражению (ASC или DESC). Чтобы получить 100 лучших клиентов, мы должны упорядочить список по Total Sales DESC.

И вот результат:

Мы также переименовали столбец Total Sales в Top Customer Sales (как вы видите на скриншоте выше).

Теперь я могу просто создать отчет в Power BI, чтобы показать разницу между Total Sales и Top Customer Sales:

Отлично. Мы использовали вычисляемые таблицы для получения некоторой информации из 100 лучших клиентов и сравнили ее с общей суммой. Есть много других случаев, для которых вы можете использовать Calculated Table. Крис Уэбб упоминает использование функции Calendar в своем блоге как вычисляемую таблицу, чтобы начать строить измерение даты.

Ограничения

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

Другим ограничением, которое  хотелось бы упомянуть на этом этапе, является не наследование форматирования. Под этим подразумевается, что вычисляемая таблица не наследует формат из исходной таблицы. В некоторых сложных сценариях, где вычисление исходит из многих таблиц, это может не понадобиться. Но для нашего примера обыгрывания ролей, если наше исходное измерение даты имеет некоторую конфигурацию форматирования. такие как установка DateKey на «Do not Summarize» или какую-либо другую конфигурацию, тогда хотелось бы видеть то же самое в вычисляемой таблице, извлеченной из этого.

Форматирование, применяемое к расчетным столбцам таблицы, также будет перезаписано после каждого изменения выражения DAX.

Загрузите демонстрационный файл Power BI отсюда:

ScenariosOfUsingCalculatedTables.PBIX File

No Comments