Основные сведения о DAX в табличных моделях (табличные службы SSAS)

Выражения анализа данных (DAX) — это язык формул, используемый для создания пользовательских вычислений в Microsoft PowerPivot для книг Microsoft Excel и проектов табличных моделей служб SQL Server 2012 Analysis Services (SSAS). К формулам DAX относятся функции, операторы и значения, позволяющие проводить расширенные вычисления над реляционными таблицами и столбцами, которые включают поиск связанных значений и данных в связанных таблицах.

В то время как DAX может использоваться и в книгах PowerPivot, и в проектах табличной модели, описание в этом разделе более направлено на проекты табличной модели, разрабатываемые в среде SQL Server Data Tools (SSDT). Перед прочтением этого раздела следует хорошо ознакомиться с табличными моделями и средой разработки проектов табличных моделей в среде SQL Server Data Tools (SSDT).

Разделы данной темы:

• DAX в табличных моделях

• Формулы DAX в вычисляемых столбцах, мерах и фильтрах строк

• Типы данных DAX

• Операторы DAX

• Формулы DAX

• Функции языка DAX

• Контекст в формулах DAX

• Формулы и реляционная модель

• Работа с таблицами и столбцами

• Обновление результатов формул (обработка)

• Устранение неполадок в формулах

• Дополнительные ресурсы

DAX в табличных моделях

И в PowerPivot, и в проектах табличных моделей, разрабатываемых в среде SQL Server Data Tools (SSDT), функционально формулы DAX одинаковым образом вычисляют значения в соответствующих наборах данных в памяти. Однако имеются различия между созданием формул DAX в книге и в средствах разработки моделей, а также в месте вычисления контекста некоторых мер.

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

В проектах табличных моделей формулы вычислений создаются в конструкторе моделей в среде SQL Server Data Tools (SSDT) авторами моделей. Значения вычисляемых столбцов, вычисляемые по формулам DAX, немедленно отображаются в таблице в конструкторе моделей, за исключением функции просмотра мер в сетке мер, но меры не вычисляются, пока пользователь не укажет фильтр в клиенте для составления отчетов, например Power View или Microsoft Excel.

При импорте книги PowerPivot в новый проект табличной модели с помощью функции «Импорт» из шаблона проекта PowerPivot в новой табличной модели автоматически создаются формулы DAX для вычисляемых столбцов. Формулы DAX для неявных и явных мер в книге автоматически создаются в новой табличной модели в качестве явных мер. Поскольку в книгах PowerPivot еще не существуют функции ролей и фильтра защищенных строк, потребуется создать как минимум одну роль в новой табличной модели, чтобы предоставить доступ к данным модели членам этой роли. Вычисления DAX в фильтрах строк необходимы лишь в случае, если необходимо защитить данные таблицы на уровне строк.

Формулы DAX в вычисляемых столбцах, мерах и фильтрах строк

Для табличных моделей, созданных в среде SQL Server Data Tools (SSDT), формулы DAX используются в вычисляемых столбцах, мерах и фильтрах строк.

Вычисляемые столбцы

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

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

Если вычисляемый столбец содержит допустимую формулу DAX, значения для каждой из строк вычисляются сразу после ввода формулы. Затем значения сохраняются в базе данных. Например, в таблице Date, если в строку формул вводится формула =[Calendar Year] & " Q" & [Calendar Quarter], для каждой строки таблицы значение рассчитывается следующим образом: берется значение из столбца Calendar Year (в той же таблице Date), к нему добавляется пробел и заглавная буква Q, а затем добавляются значения из столбца Calendar Quarter (в той же таблице Date). Результат для каждой из строк в вычисляемом столбце рассчитывается немедленно и выглядит аналогично следующему: 2010 Q1. Значения столбца вычисляются повторно лишь при обработке данных.

Дополнительные сведения см. в разделе Вычисляемые столбцы (табличные службы SSAS).

Меры

Меры являются динамическими формулами, результаты которых изменяются в зависимости от контекста. Меры используются в форматах отчетов, поддерживающих объединение и фильтрацию данных модели с помощью нескольких атрибутов, например в отчетах Power View, сводных таблицах и диаграммах Excel. В проектах табличных моделей меры определяются разработчиком модели с помощью сетки мер (и строки формул) в конструкторе моделей среды SQL Server Data Tools (SSDT).

Формулы в мерах могут использовать стандартные агрегатные функции, автоматически создаваемые с использованием автосуммирования, такие как COUNT или SUM, либо пользовательские формулы, созданные на языке DAX. При определении формулы для меры в строке формул в подсказке отображается предварительный просмотр будущих результатов для всего текущего контекста, но в других местах результаты не обновляются сразу же. Другие сведения о мере также отображаются на панели Свойства.

Результаты вычисления (отфильтрованные) не отображаются немедленно потому, что результат меры нельзя определить без контекста. Для вычисления меры требуется клиентское приложение создания отчетов, которое может предоставить контекст, необходимый для получения данных для каждой из ячеек, а затем вычислить выражения для ячеек. Таким клиентом может быть сводная таблица или диаграмма Excel, отчет Power View или запрос многомерных выражений. Вне зависимости от используемого клиентского средства создания отчетов для каждой из ячеек в результатах выполняется отдельный запрос. Иными словами, каждое сочетание заголовков строки и столбца в сводной таблице и каждая выборка срезов и фильтров в отчете Power View создает уникальное подмножество данных, по которым и вычисляется мера. Например, в мере с формулой Total Sales:=SUM([Sales Amount]), когда пользователь помещает меру «Total Sales» (Сумма продаж) в окно «Значения» в сводной таблице, а затем помещает столбец «Product Category» (Категория продукта) из таблицы «Product» (Продукт) в окно «Фильтры», величина «Sales Amount» (Сумма продаж) рассчитывается и отображается для каждой категории продуктов.

В отличие от вычисляемых столбцов и фильтров строк, синтаксис меры включает имя меры перед формулой. В предыдущем примере имя Total Sales (Сумма продаж) предшествует формуле. После создания меры ее имя и атрибуты появятся в списке полей клиентского приложения создания отчетов, а мера станет доступной всем пользователям модели, в зависимости от их перспектив и ролей.

Дополнительные сведения см. в разделе Меры (табличные службы SSAS).

Фильтры строк

Фильтры строк определяют, какие строки таблицы могут видеть члены определенной роли. Фильтры строк можно создать для каждой из таблиц в модели с помощью формул DAX. Фильтры строк создаются для конкретной роли с помощью диспетчера ролей в среде Среда SQL Server Management Studio. В среде Среда SQL Server Management Studio фильтры строк также можно определить для развернутой модели с помощью диалогового окна «Свойства роли».

В фильтре строк формула DAX, результат вычисления которой должен быть равен логическому значению TRUE или FALSE, определяет строки, которые могут возвращаться результатами запроса, выполняемого членами определенной роли. Строки, не включенные в формулу DAX, возвращать нельзя. Например, при использовании в таблице Customers (Клиенты) выражения DAX =Customers[Country] = “USA” члены роли Sales (Продажи) смогут просматривать только данные для клиентов из США, а агрегаты (SUM и др.) возвращаются только для клиентов из США.

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

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

Дополнительные сведения см. в разделе Роли (табличные службы SSAS).

Типы данных DAX

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

Табличные модели и DAX поддерживают следующие типы данных:

Табличные модели также включают тип данных Table, используемый как входной или выходной многими функциями DAX. Например, функция FILTER принимает в качестве входного аргумента таблицу и возвращает другую таблицу, которая содержит только строки, удовлетворяющие условиям фильтра. Применение табличных функций в сочетании с агрегатными функциями позволяет выполнять сложные вычисления с динамически определяемыми наборами данных.

Хотя типы данных обычно устанавливаются автоматически, важно понимать, как они работают, в особенности в формулах DAX. Например, ошибки в формулах или непредвиденных результаты часто связаны с использованием определенного оператора, который недопустим для указанного в аргументе типа данных. Например, формула = 1 & 2 возвращает строковое значение 12. В то же время формула = “1” + “2” возвращает целочисленный результат 3.

Подробные сведения о типах данных в табличной модели и о явных и неявных преобразованиях типов данных в DAX см. в разделе Поддерживаемые типы данных (табличные службы SSAS).

Операторы DAX

В языке DAX используются четыре различных типа операторов вычислений в формулах:

• Операторы сравнения, которые сравнивают значения и возвращают логические значения (TRUE/FALSE).

• Арифметические операторы, которые выполняют арифметические вычисления и возвращают числовые значения.

• Операторы объединения текста, которые соединяют две и более текстовые строки.

• Логические операторы, которые объединяют два и более выражения, возвращая один результат.

Подробные сведения об операторах, которые используются в формулах DAX, см. в разделе Справочник по операторам DAX для PowerPivot.

Формулы DAX

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

Основные сведения о формулах

Язык DAX позволяет разработчикам табличных моделей определять нестандартные вычисления как в таблицах моделей (в составе вычисляемых столбцов), так и в мерах, связанных с таблицами, но неотображаемых в них напрямую. Язык DAX также позволяет разработчикам моделей защищать данные посредством создания вычислений, которые возвращают логическое значение; это значение определяет, разрешено ли пользователям-членам определенной роли запрашивать строки в заданной или в связанной таблице.

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

И при создании простых формул, и при создании сложных достаточно выполнять следующие указания.

1. Каждая формула должна начинаться со знака равенства.

2. Введите или выберите имя функции либо введите выражение.

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

   Также можно нажать кнопку Fx, чтобы отобразить список доступных функций. Чтобы выбрать функцию из раскрывающегося списка, выделите ее с помощью клавиш со стрелками, затем нажмите кнопку OК, чтобы добавить функцию в формулу.

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

5. Проверьте наличие синтаксических ошибок: убедитесь, что закрыты все скобки и правильно указаны ссылки на столбцы, таблицы и значения.

6. Нажмите клавишу ВВОД, чтобы принять формулу.

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

В этом примере рассматривается более сложная формула в мере «Days in Current Quarter» (Дни в текущем квартале):

Days in Current Quarter:=COUNTROWS( DATESBETWEEN( 'Date'[Date], STARTOFQUARTER( LASTDATE('Date'[Date])), ENDOFQUARTER('Date'[Date])))

Эта мера используется при расчете коэффициента сопоставления между текущим неполным периодом и предыдущим периодом. Формула должна учитывать долю прошедшего времени текущего периода, чтобы сравнение с предыдущим периодом проводилось в правильном соотношении. В этом случае деление [Дней текущего квартала до даты] / [Дней в текущем квартале] дает пропорцию, прошедшую в текущем периоде.

Эта формула содержит следующие элементы:

Использование автозаполнения формул

И в строке формул в конструкторе моделей, и в окне формулы фильтров строк в диалоговом окне «Диспетчер ролей» реализована функция автозаполнения. Автозаполнение помогает соблюдать правильный синтаксис формул, предлагая варианты для каждого из элементов в формуле.

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

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

Использование нескольких функций в одной формуле

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

Многие функции предназначены для использования исключительно в качестве вложенных. Эти функции возвращают таблицу, которая не может быть непосредственно сохранена в качестве результата, но может быть передана табличной функции в качестве входного параметра. Например, в качестве первого аргумента функций SUMX, AVERAGEX и MINX требуется таблица.

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

Функции языка DAX

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

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

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

• Если нужно настроить вычисления на уровне строк, то в языке DAX имеются функции, использующие в зависимости от контекста либо текущее значение строки, либо связанное значение в качестве своего рода параметра. Основные сведения о работе этих функций см. в далее в подразделе Контекст в формулах DAX (SSAS — табличные модели).

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

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

Функции даты и времени

Функции даты и времени в DAX схожи с функциями даты и времени Microsoft Excel. Однако функции DAX основаны на типах данных datetime, используемых в Microsoft SQL Server. Дополнительные сведения см. в разделе Функции даты и времени (DAX).

Функции фильтров

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

Информационные функции

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

Логические функции

Логические функции обрабатывают выражение и возвращают сведения о значениях в выражении. Например, функция TRUE в языке DAX сообщает, возвращает ли вычисляемое выражение значение TRUE. Дополнительные сведения см. в разделе Логические функции (DAX).

Математические и тригонометрические функции

Математические функции в DAX весьма схожи с математическими и тригонометрическими функциями Excel. Числовые типы данных, используемые в функциях DAX, имеют незначительные отличия. Дополнительные сведения см. в разделе Математические и тригонометрические функции (DAX).

Статистические функции

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

Текстовые функции

Текстовые функции DAX во многом схожи со своими аналогами в Excel. Можно вернуть часть строки, искать текст в строке или объединить строковые значения. DAX также предоставляет функции для управления форматами дат, времени и чисел. Дополнительные сведения см. в разделе Текстовые функции (DAX).

Функции логики операций со временем

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

Функции с табличными значениями

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

Функции фильтров Возвращают столбец, таблицу или значения, относящиеся к текущей строке.

Агрегатные функции Выполняют агрегатные вычисления с любыми выражениями, заданными на строках таблицы.

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

Контекст в формулах DAX

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

Формулы в табличных моделях могут вычисляться в разном контексте в зависимости от других структурных элементов, например на них влияет следующее:

• Фильтры, примененные в сводной таблице или отчете

• Фильтры, определенные в формуле

• Отношения, указанные с помощью специальных функций в формуле

Существуют различные типы контекста: контекст строки, контекст запроса и контекст фильтра.

Контекст строки

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

Предположим, создается вычисляемый столбец =[Freight] + [Tax], который складывает значения из двух столбцов Freight и Tax одной таблицы. Эта формула автоматически возвращает только значения из текущей строки в указанных столбцах.

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

Например, в следующей формуле функция RELATED используется для выборки значения суммы налогов из связанной таблицы в зависимости от региона, в который отправлен заказ. Значение суммы налогов определяется с использованием значения для региона из текущей таблицы путем поиска этого региона в связанной таблице и получения ставки налога для этого региона из связанной таблицы.

= [Freight] + RELATED('Region'[TaxRate])

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

Контекст нескольких строк

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

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

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

=MAXX(FILTER(Sales,[ProdKey]=EARLIER([ProdKey])),Sales[OrderQty])

Подробное пошаговое руководство по этой формуле см. в разделе Функция EARLIER.

Иначе говоря, функция EARLIER сохраняет контекст строки из операции, предшествующей текущей. Функция всегда хранит в памяти два набора контекста: один набор контекста представляет текущую строку для внутреннего цикла формулы, а другой набор контекста представляет текущую строку для внешнего цикла формулы. DAX автоматически формирует поток значений между двумя циклами, чтобы можно было создавать сложные агрегаты.

Контекст запроса

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

Внимание!
Для модели в режиме DirectQuery вычисляется контекст, а затем заданные операции для получения правильного подмножества данных и вычисления результатов преобразуются в инструкции SQL. Затем эти инструкции непосредственно выполняются в реляционном хранилище данных. Таким образом, хотя метод извлечения данных и вычисления результатов отличен, сам контекст остается неизменным.

Поскольку контекст изменяется в зависимости от места размещения формулы, то также могут меняться и результаты ее выполнения.

Предположим, создана простая формула, которая выполняет суммирование значений в столбце Profit таблицы Sales: =SUM('Sales'[Profit]). Если эта формула используется в вычисляемом столбце таблицы Sales, результаты формулы будут одинаковыми для всей таблицы, так как контекстом запроса для формулы всегда является весь набор данных в таблице Sales. Результаты будут включать значения прибыли для всех регионов, всех товаров, всех лет и т. д.

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

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

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

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

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

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

Дополнительные сведения о создании фильтров в формулах см. в разделе FILTER, функция.

Пример очистки фильтров для получения итоговых сумм см. в разделе Функция ALL.

Пример выборочной очистки и применения фильтров в формулах см. в разделе Функция ALLEXCEPT.

Определение контекста в формулах

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

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

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

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

Примеры контекста в формулах

1. Функция Функция RELATED расширяет контекст текущей строки, включая значения в связанном столбце. Это позволяет выполнять уточняющие запросы. Пример в этом разделе показывает взаимодействие между контекстом строки и фильтрации.

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

3. Функция Функция ALL задает контекст в формуле. Ее можно использовать для переопределения фильтров, которые применяются к результату контекста запроса.

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

5. Функции Функция EARLIER и Функция EARLIEST позволяют организовать цикл по таблицам с выполнением вычислений, ссылаясь на значения во внутреннем цикле. Пользователи, знакомые с понятием рекурсии, внутренними и внешними циклами, по достоинству оценят возможности, которые предоставляют функции EARLIER и EARLIEST. Пользователям, незнакомым с этими основными понятиями, рекомендуется пошагово выполнить примеры, чтобы понять, как внутренний и внешний контексты используются при вычислениях.

Формулы и табличная модель

Конструктор моделей в среде SQL Server Data Tools (SSDT) представляет собой область, в которой можно работать с множеством таблиц данных и связывать таблицы в табличную модель. В этой модели таблицы соединяются связями по столбцам с общими значениями (ключами). Табличная модель позволяет связывать значения со столбцами из других таблиц и создавать более содержательные вычисления. Как и в реляционной базе данных, поддерживается соединение множества уровней связанных таблиц и использование в результатах столбцов из любых таблиц.

Например, можно связать таблицу продаж, таблицу продуктов и таблицу категорий продуктов, и пользователи смогут работать с различными сочетаниями столбцов в сводных таблицах и отчетах. Связанные поля могут быть использованы для фильтрации связанных таблиц или для создания вычислений над подмножествами. (Сведения о реляционных базах данных и о работе с таблицами и соединениями см. в разделе Связи (табличные службы SSAS).)

Новой функцией в службах SQL Server 2012 Analysis Services (SSAS) является поддержка множественных связей между таблицами в табличных моделях. Во избежание путаницы или неверных результатов активной может одновременно быть только одна связь, однако можно изменять активную связь по необходимости для прохода по различным соединениям между данными в вычислениях.

Кроме того, в этом выпуске добавлена новая возможность: Функция USERELATIONSHIP (DAX) указывает одну или несколько связей, используемых в определенном вычислении.

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

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

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

Работа с таблицами и столбцами

Таблицы в табличных моделях сходны с таблицами Excel, но отличаются тем, что работают с данными и формулами.

• Формулы работают только с таблицами и столбцами, а не с отдельными ячейками, ссылками на диапазоны и массивами.

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

• Нельзя иметь неупорядоченные или неоднородные данные, как это можно делать на листе Excel. Каждая строка в таблице должна содержать одинаковое количество столбцов. Однако некоторые столбцы могут иметь пустые значения. Таблицы данных Excel и таблицы данных табличная модель не являются взаимозаменяемыми.

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

Ссылки на таблицы и столбцы в формулах

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

=SUM('New Sales'[Amount]) + SUM('Past Sales'[Amount])

При вычислении формулы конструктор моделей сначала проверяет общий синтаксис, а затем сравнивает указанные имена столбцов и таблиц с возможными столбцами и таблицами в текущем контексте. Если имя, использованное в формуле, определено неоднозначно либо столбец или таблица не найдены, будет выдана ошибка (строка #ERROR вместо значения данных в ячейках, в которых произошла ошибка). Дополнительные сведения о требованиях к именованию таблиц, столбцов и других объектов см. в подразделе «Требования к именам» раздела Спецификация синтаксиса DAX для PowerPivot.

Связи между таблицами

Создание связей между таблицами дает возможность выполнять уточняющие запросы данных из другой таблицы и сложные вычисления с помощью связанных значений. Например, можно использовать вычисляемый столбец для поиска всех заказов, связанных с текущим посредником, а затем суммировать их стоимость. Однако во многих случаях связь не является обязательной. Можно использовать функцию LOOKUPVALUE в формуле, чтобы вернуть значение в столбце result_columnName для строки, удовлетворяющей критериям, которые указаны в параметрах search_column и search_value.

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

Обновление результатов формул (обработка)

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

Обработка данных — это процесс замещения данных в модели новыми данными из внешнего источника данных.

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

• Значения в вычисляемом столбце вычисляются и хранятся в модели. Чтобы обновить значения в вычисляемом столбце, необходимо обработать модель одной из трех команд обработки: полная обработка, обработка данных и повторное вычисление. При любом изменении формулы ее результат должен всегда повторно вычисляться для всего столбца.

• Значение меры динамически вычисляется при добавлении меры пользователем в сводную таблицу или открытии отчета. Когда пользователь изменяет контекст, меняются значения меры. Результаты меры всегда отражают последнее состояние кэша в памяти.

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

Дополнительные сведения см. в разделе Обработка данных (табличные службы SSAS).

Устранение неполадок в формулах

Если во время определения формулы выводится ошибка, значит формула может содержать синтаксическую ошибку, семантическую ошибку или ошибку вычисления.

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

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

• Формула ссылается на несуществующий столбец, таблицу или функцию.

• Формула верна, но подсистема данных в процессе поиска данных обнаруживает несоответствие типов и возвращает ошибку.

• Формула передает функции неверное число или тип параметров.

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

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

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

Дополнительные ресурсы

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

Блог группы разработчиков служб Analysis Services и сводных таблиц содержит сведения, советы и новости по SQL Server 2012 Analysis Services (SSAS) и сводным таблицам.

DAX Wiki содержит внутреннюю и внешнюю информацию по DAX, включая многочисленные решения на языке DAX, предоставленные ведущими специалистами в области бизнес-аналитики.

См. также

Основные понятия

Меры (табличные службы SSAS)

Вычисляемые столбцы (табличные службы SSAS)

Роли (табличные службы SSAS)

Ключевые показатели эффективности (табличные службы SSAS)

Поддерживаемые источники данных (табличные службы SSAS)

Другие ресурсы

Справочник по выражениям анализа данных (DAX)