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

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

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

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

Простые значения

В своей основе DAX — очень простой язык выражений, в котором, как в Excel, используются функции для выполнения операций над значениями и наборами данных. Основной набор функций прост и удобен в использовании, постепенно он становится более сложным и мощным. Первая группа функций DAX, с которой мы познакомимся, — агрегаты, которые сводят диапазон значений в единый результат. К таким функциям относятся SUM, AVERAGE, COUNT, DISTINCTCOUNT, MIN и MAX.

В предыдущей статье мы воспользовались функцией Auto Sum, чтобы получить простые значения для нескольких числовых полей в таблицах Internet Sales («Продажи в Интернете») и Reseller Sales («Продажи реселлеров»). Откройте файл Model.BIM в конструкторе моделей и выберите таблицу Internet Sales, используя вкладки в нижней части страницы. Под столбцом Order Quantity вы должны увидеть значение auto sum. Нижняя сетка именуется Calculation Area («Область вычислений»). Щелкните по этой ячейке и взгляните на выражение значения в строке формул над сеткой:

Sum of Order Quantity:=SUM([Order Quantity])

Я не сторонник соглашения об именовании «Sum of...». Оно подходит для небольших простых моделей, однако это не тот случай. Обратите внимание на значение в столбце Order Quantity в таблице Reseller Sales. Его имя Sum of Order Quantity 2 (конструктор должен формировать уникальные имена значений и произвольно назначать номер в конце).

Для каждого значения в таблице Internet Sales замените текст «Sum of...» на «Internet». В таблице Reseller Sales замените префикс «Sum of» на «Reseller» и удалите все цифры в конце имени. После каждого изменения нажимайте клавишу Enter и ждите, пока изменение будет сохранено. Переименование объектов — утомительная работа, но важно выполнить ее без ошибок перед созданием выражений, в которых используются эти имена.

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

Internet Sales Count:=COUNTROWS('Internet Sales')

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

К сожалению, интерактивный редактор несовершенен и не существует универсального способа исправить его ошибки. Иногда добавляются лишние знаки пунктуации или не распознаются нажатия клавиш. По большей части редактор функционирует исправно, но если возникают ошибки, перепроверьте квадратные и круглые скобки, а также кавычки; нажмите клавишу Escape для отмены действия; скопируйте и вставьте сценарий в приложение «Блокнот» и обратно; при необходимости закройте и вновь откройте редактор моделей.

Контекст фильтра и строки

Нажмите кнопку со значком Excel на панели инструментов конструктора моделей, а затем щелкните OK, чтобы просмотреть модель в PivotTable. Список полей отображается справа, когда выбрана PivotTable. Установите флажок рядом со значением Reseller Sales Amount («Количество продаж реселлеров») в таблице Reseller Sales, а затем щелкните на поле Country Region («Регион страны») в таблице Geography, чтобы вывести итоговые показатели продаж для каждой страны. Примечательно, что выражение SUM([Sales Amount]) применяется к каждой группе значений страны. Точнее, SUM применяется в контексте каждой строки таблицы Reseller Sales с фильтрацией по странам, отображаемым в группе строк PivotTable. Это означает, что каждая объединенная строка в PivotTable представляет тысячи строк в таблице Reseller Sales.

Теперь заменим естественный контекст фильтра и воспользуемся собственной логикой фильтрации. Для этого нужно настроить обработчик запросов, чтобы игнорировать любые группы или фильтры, и выполнять только наши инструкции. Оставьте программу Excel открытой и вернитесь в редактор моделей. В области вычислений перейдите в пустую ячейку под столбцом Sales Amount и введите приведенный ниже текст в строке формул. Переносы строк необязательны; их можно ввести, нажав клавиши Shift и Enter. Пробелы добавляются нажатием клавиши «Пробел» (не клавиши Tab):

US Reseller Sales:=CALCULATE( [Reseller Sales Amount],
ALL( 'Reseller Sales' ),
Geography[Country Region] = «United States»
)

Нажмите клавишу Enter, а затем воспользуйтесь окном Properties («Свойства»), чтобы назначить формат Currency («Валюта»). Вновь нажмите клавишу Enter и перейдите в ячейку, расположенную ниже; введите следующее выражение, а затем задайте формат Percent («Процент»):

Sales % of US Sales:=DIVIDE( [Reseller Sales Amount], [US Reseller Sales] )

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

Перейдите в Excel и нажмите кнопку Refresh All («Обновить все») на ленте данных. Выделив любую ячейку в PivotTable, вы увидите новые значения, добавленные к таблице Reseller Sales. Отметьте значения US Reseller Sales и Sales % of US Sales, чтобы добавить их в PivotTable.

Обратите внимание, что мера US Reseller Sales не агрегируется по странам в этой строке, так как мы использовали функцию ALL для отмены естественного фильтра. Сравнивая Reseller Sales Amount («Количество продаж реселлеров») для каждой страны, можно вычислить процент, относящийся к продажам в США. Это хорошо, если все фильтры жестко указаны в выражении, но выполнить фильтрацию по любым другим значениям не удастся. Чтобы в этом убедиться, добавьте поле Calendar Year («Календарный год») из таблицы Order Date («Дата заказа») в верхнюю часть списка Columns, и вы увидите, что значение US Reseller Sales не фильтруется по годам. Исправить этот изъян можно, заменив функцию ALL на функцию ALLEXCEPT:

US Reseller Sales:=CALCULATE( [Reseller Sales Amount],
ALLEXCEPT( 'Geography', Geography[Country Region] ),
Geography[Country Region] = «United States»
)

Функция ALLEXCEPT указывает обработчику запросов на необходимость учитывать все фильтры, кроме отмененных. Можно указать любое число исключений с помощью дополнительных параметров, вводимых перед закрывающейся скобкой. Эта функция возвращает объект таблицы второму параметру функции CALCULATE, а затем третий параметр применяет фильтр Country Region.

Сохраните свою работу. В следующей статье речь пойдет о безопасности и администрировании табличной модели, и вы найдете применение своим знаниям о DAX. В четвертой статье мы рассмотрим более сложные бизнес-приложения вычислений DAX.

Ресурсы

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

-DAX Patterns; Alberto Ferrari, Marco Russo

www.DaxPatterns.com;

-Microsoft Tabular Modeling Cookbook, Paul te Braak

www.amazon.com/Microsoft-Tabular-Modeling-Cookbook-Braak-ebook/dp/B00HK3VP4K/ref=sr_1_1?s=books&ie=UTF8&qid=1420856375&sr=1-1;

-DAX Formulas for PowerPivot, Rob Collie

www.powerpivotpro.com/the-book;

-Dashboarding and Reporting with Power Pivot and Excel, Kasper de Jonge

www.amazon.com/Dashboarding-Reporting-Power-Pivot-Excel/dp/1615470271.