Совместимость формул в режиме DirectQuery

Статья
07/02/2013

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

В этом разделе мы будем использовать термин модель в оперативной памяти для обозначения как моделей PowerPivot, использующих локальный кэш, так и табличных моделей, полностью размещаемых в данных памяти сервера служб Analysis Services, запущенного в табличном режиме. Воспользуемся моделями Модели DirectQuery, чтобы ссылаться на табличные модели, развернутые в режиме DirectQuery. Дополнительные сведения о режиме DirectQuery см. в разделе Режим DirectQuery (табличные службы SSAS).

Семантические различия
Далее описаны типы различий, которые могут возникнуть при использовании этой же формулы в режиме DirectQuery.
- Сравнения
- Приведения
- Математические функции и арифметические операции
- Поддерживаемые числовые диапазоны и диапазоны даты и времени.
- Валюта
- Агрегатные функции
- Текстовые функции
Функции, поддерживаемые в режиме DirectQuery
В этом разделе приведен список функций, используемых в режиме DirectQuery, которые, однако, могут возвращать другие результаты.
Функции, неподдерживаемые в режиме DirectQuery
В разделе перечисляются функции, которые не могут использоваться в моделях, развернутых в режиме DirectQuery.

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

Обзор различий между режимами в памяти и DirectQuery

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

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

По контрасту язык DAX предназначен для максимально приближенной эмуляции функций Microsoft Excel. Например, при обработке значений NULL, пустых строк и нулевых значений Excel выполняет попытку найти лучший ответ вне зависимости от точного типа данных, поэтому механизм xVelocity выполняет это же действие. Однако, когда табличная модель развертывается в режиме DirectQuery и происходит передача формул в реляционный источник данных для их оценки, данные должны обрабатываться в соответствии с семантикой реляционного источника данных, в которой обычно требуется точно определенная обработка пустых строк, в отличие от значений NULL. По этой причине при обработке одной и той же формулы могут получаться различные результаты при оценке кэшируемых данных и данных, получаемых непосредственно от реляционного источника.

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

Список семантических различий

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

Сравнения

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

В следующих примерах сравнения всегда будет отображаться ошибка при вычислении средствами источника данных DirectQuery:

Числовой тип данных и любой тип строковых данных
Числовой тип данных и логическое значение
Любой строковый тип данных и логическое значение

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

Сравнение строк и чисел
ПРИМЕР: “2” < 3

Выполняется сравнение текстовой строки и числа при помощи формулы. Как в режиме DirectQuery, так и в модели в памяти используется выражение true.

В модели в памяти результат — true, поскольку результаты в виде строк неявно приводятся к численному типу данных для сравнения с другими числами. SQL также неявно приводит текстовые числа как числа для сравнения с числовыми типами данных.

Обратите внимание, что такое поведение отличается от поведения в первой версии PowerPivot, в которой было бы возвращено значение false, поскольку текстовое значение «2» будет расцениваться как более высокое, чем любое число.
Сравнение текстовых и булевых значений
ПРИМЕР: “VERDADERO” = TRUE

В этом выражении сравнивается текстовая строка с логическим значением. Главным образом для моделей DirectQuery или моделей в памяти сравнение строкового значения с логическим значением приводит к ошибке. Единственными исключениями для данного правила являются случаи, когда строка содержит слово TRUE или слово FALSE. Если строка содержит любые значения TRUE или FALSE, выполняется преобразование в логическое значение и выполняется сравнение, приводящее к итоговому логическому результату.
Сравнение значений NULL
ПРИМЕР: EVALUATE ROW("X", BLANK() = BLANK())

В этой формуле сравнивается эквивалент значения NULL в SQL со значением NULL. Возвращается значение true для модели в памяти и модели DirectQuery. Затем выполняется подготовка в модели DirectQuery, необходимая для того, чтобы гарантировать похожее поведение модели в памяти.

Обратите внимание, что в Transact-SQL значение NULL никогда не равно NULL. Однако в DAX пустое значение равно другому пустому значению. Такое поведение является одинаковым для всех моделей в памяти. Важно заметить, что в режиме DirectQuery используется по большей части семантика SQL Server. Однако в этом случае она отделена от него так, что сравнения со значением NULL действуют по-другому.

Приведения

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

Логические значения обрабатываются как численные в ходе арифметических операций, например в виде TRUE + 1 или с применением функции к столбцу логических значений. Операция НЕ также возвращает числовое значение.
Логические значения при сравнениях или с операторами EXACT, AND, OR, && или || всегда обрабатываются как логические значения.

Приведение строковых данных в логические
В моделях «в памяти» и DirectQuery приведения к типу Boolean разрешены только для следующих строк: “” (пустая строка), “true”, “false”; где пустая строка приводится к значению false.

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

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

Модели, в которых используется хранение данных в памяти, поддерживают более ограниченный диапазон текстовых форматов для дат, чем строковые форматы для дат, поддерживаемые в SQL Server. Однако язык DAX поддерживает настраиваемые форматы даты и времени. Дополнительные сведения см. в разделах DAX Predefined Date formats и Custom date formats.
Преобразование из строки в другие нелогические значения
При преобразовании из строк в нелогические значения режим DirectQuery ведет себя так же, как SQL Server. Дополнительные сведения см. в разделе Функции CAST и CONVERT (Transact-SQL).
Преобразование из чисел в строку не разрешено
ПРИМЕР: CONCATENATE(102,”,345”)

Преобразование из чисел в строки в SQL Server не допускается.

Эта формула возвращает ошибку в табличных моделях и в режиме DirectQuery. Однако в PowerPivot она дает результат.
Две попытки преобразования не поддерживаются в DirectQuery
Модели в памяти часто пытаются выполнить вторую попытку преобразования после первой неудачной попытки. Этого никогда не происходит в режиме DirectQuery.

ПРИМЕР: TODAY() + “13:14:15”

В этом выражении первый параметр принадлежит к типу datetime, а второй параметр принадлежит к типу string. Однако преобразования при комбинировании операндов обрабатываются по-разному. DAX выполняет неявное преобразование из string в double. В моделях в памяти в ядре формулы предпринимается попытка преобразования в double напрямую и при неудачной попытке последует попытка преобразования в datetime.

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

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

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

Порядок сложения
При создании формулы, в которой происходит сложение последовательности чисел, модель в памяти может обработать эти числа в ином порядке, чем модель DirectQuery. Поэтому при совершении операций с очень большими положительными и отрицательными числами может произойти ошибка в одной операции, хотя в другой может быть выдан результат.
Использование функции возведения в степень
ПРИМЕР: POWER(-64, 1/3)

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

В модели в памяти формула возвращает -4.
Операции численного переполнения
В Transact-SQL операции, приводящие к численному переполнению, возвращают ошибку переполнения, поэтому формулы, которые приводят к переполнению, также выдают ошибку в режиме DirectQuery.

Однако эта же формула при использовании в модели в памяти возвращает 8-байтовое целое. Это происходит потому, что механизм обработки формул не выполняет проверок в случае численного переполнения.
Функции LOG с пробелами возвращают другие результаты
SQL Server обрабатывает значения NULL и пустые значения иначе, чем механизм xVelocity. В результате этого следующая формула возвращает ошибку в режиме DirectQuery, однако в режиме в памяти возвращается бесконечность (–inf).

EXAMPLE: LOG(blank())

Те же ограничения применяются к другим логарифмическим функциям, LOG10 и LN.

Дополнительные сведения о типе данных blank в DAX см. в разделе Спецификация синтаксиса DAX для PowerPivot.
Деление на 0 и деление на пустые данные
В режиме DirectQuery при делении на нуль (0) или при делении на пустые данные (BLANK) всегда выдается ошибка. SQL Server не поддерживает концепцию бесконечности, и, поскольку естественным результатом при делении на нуль становится бесконечность, происходит ошибка. Однако SQL Server поддерживает деление на значения NULL, а результат всегда должно равняться NULL.

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

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

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

1/BLANK

1/0

0.0/BLANK

0/0

Выражение BLANK/BLANK — особый вариант, при котором возвращается BLANK как в модели в памяти, так и в режиме DirectQuery.

Поддерживаемые числовые диапазоны и диапазоны даты и времени.

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

При умножении значений типов Currency и Real, когда результат превышает максимально допустимое значение, в режиме DirectQuery это не приводит к ошибке и возвращается значение NULL.
При использовании модели в памяти ошибка не возникает и возвращается максимальное значение.

В общем, поскольку приемлемый диапазон дат различается для Excel и SQL Server, совпадение результатов можно гарантировать только тогда, когда даты находятся в схожих допустимых пределах, что справедливо и по отношению к следующим датам:

Первая дата: 1 марта 1900 г.
Последняя дата: 31 декабря 9999 г.

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

Поддержка функции CEILING для вычислений с плавающей запятой
ПРИМЕР: EVALUATE ROW("x", CEILING(-4.398488E+30, 1))

Эквивалент Transact-SQL для функции DAX CEILING поддерживает вычисления с числами 10^19 или меньшими. Основное правило состоит в том, что значения с плавающей запятой должны подходить для bigint.
Функции datepart с датами вне диапазона
Результаты обработки данных в режиме DirectQuery гарантированно совпадают с аналогичными результатами для моделей в памяти только тогда, когда дата, используемая в качестве аргумента, принадлежит диапазону допустимых дат. Если эти условия не удовлетворяются, либо возникает ошибка, либо при обработке формулы в режиме DirectQuery и в модели в памяти будут получены разные результаты.

ПРИМЕР: MONTH(0) либо YEAR(0)

В режиме DirectQuery выражения возвращают 12 и 1899 соответственно.

В моделях в памяти выражения возвращают 1 и 1900 соответственно.

ПРИМЕР: EOMONTH(0.0001, 1)

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

ПРИМЕР: EOMONTH(blank(), blank()) либо EDATE(blank(), blank())

Результаты этого выражения должны совпадать в режиме DirectQuery и в режиме в памяти.
Усечение временных значений
ПРИМЕР: SECOND(1231.04097222222)

В режиме DirectQuery результат усекается в соответствии с правилами SQL Server и результатом выполнения выражения будет 59.

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

В следующем примере показано, как вычисляется это значение:
1. Дробный ввод (0,04097222222) умножается на 24.
2. Результирующее значение часа (0,98333333328) умножается на 60.
3. Полученное значение в минутах — 58,9999999968.
4. Дробное значение в минутах (0,9999999968) умножается на 60.
5. Полученное второе значение (59,999999808) округляется до 60.
6. 60 является эквивалентом 0.
Тип данных SQL Time не поддерживается
Для моделей в памяти не поддерживается использование нового типа данных SQL Time. В режиме DirectQuery формулы, которые ссылаются на столбцы с этим типом данных, будут возвращать ошибку. Столбцы данных времени не могут быть импортированы в модель в памяти.

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

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

Валюта

В режиме DirectQuery, если результат арифметической операции имеет тип Currency, значение должно принадлежать следующему диапазону:

Не менее: -922337203685477.5808
Не более: 922337203685477.5807

Сочетание типов данных «Валюта» и «REAL»
ПРИМЕР: Currency sample 1

Если умножить типы данных Currency и Real и результат окажется выше значения 9223372036854774784 (0x7ffffffffffffc00), в режиме DirectQuery не произойдет ошибки.

В модели в памяти происходит ошибка, если абсолютное значение результата превышает 922337203685477.4784.
Выполнение операции приводит к возникновению значения, не принадлежащего допустимому диапазону
ПРИМЕР: Currency sample 2

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

Агрегатные функции

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

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

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

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

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

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

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

В операциях, символ в которых возвращается с помощью функций LEFT, RIGHT и т. п., возможен возврат верного символа, но в другом регистре или результатов может не получиться.
ПРИМЕР: LEFT([“text”], 2)

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

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

Хранение строк и параметры сортировки в табличных моделях

Это также относится к другим текстовым функции, в том числе к функциям RIGHT, MID и т. п.
Длина строки влияет на результаты
ПРИМЕР: SEARCH(“within string”, “sample target text”, 1, 1)

При поиске строки с использованием функции SEARCH (поиск), при котором искомая строка длиннее внутренней строки, в режиме DirectQuery возникнет ошибка.

Для модели в памяти возвращается искомая строка, однако ее длина усекается до длины строки <внутри текста>.

ПРИМЕР: EVALUATE ROW("X", REPLACE("CA", 3, 2, "California") )

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

В моделях в памяти формула повторяет режим работы в Excel, в котором исходная строка объединяется со строкой для замены, причем возвращается значение CACalifornia.
Неявное выполнение функции TRIM в середине строк
ПРИМЕР: TRIM(“ A sample sentence with leading white space”)

В режиме DirectQuery функция DAX TRIM переводится в инструкцию SQL LTRIM(RTRIM(<column>)). В результате удаляется только ведущий и замыкающий пробелы.

Однако та же формула в модели в памяти удалит пробелы внутри строки, подобно поведению Excel.
Неявное выполнение функции RTRIM с использованием функции LEN
ПРИМЕР: LEN(‘string_column’)

Как и в SQL Server, в режиме DirectQuery автоматически удаляются пробелы в конце строковых столбцов: то есть выполняется неявная инструкция RTRIM. Поэтому формулы, использующие функцию LEN, могут возвращать различные значения при наличии конечных пробелов в строке.
Подсистема в памяти поддерживает дополнительные параметры функции SUBSTITUTE
ПРИМЕР: SUBSTITUTE([Title],”Doctor”,”Dr.”)

ПРИМЕР: SUBSTITUTE([Title],”Doctor”,”Dr.”, 2)

В режиме DirectQuery можно использовать версию этой функции, в которой указывается три (3) параметра: ссылка на столбец, старый текст и новый текст. Если использовать вторую формулу, возникает ошибка.

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

Это ограничение не применяется в режиме DirectQuery.
Операции с подстроками возвращают различные результаты в зависимости от типа символов
ПРИМЕР: MID([col], 2, 5)

Если вводимый текст принадлежит к типам varchar или nvarchar, результат формулы всегда один и тот же.

Однако если текст обладает фиксированной символьной длиной, а значение параметра <num_chars> превышает длину целевой строки, то в режиме DirectQuery добавляется пробел в конце целевой строки.

В модели в памяти результат завершается на последнем строковом символе без заполнения.

Функции, поддерживаемые в режиме DirectQuery

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

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

CONCATENATE

FIND

LEFT

LEN

MID

REPLACE

REPT

RIGHT

SUBSTITUTE

TRIM

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

COUNT

STDEV.P

STDEV.S

STDEVX.P

STDEVX.S

VAR.P

VAR.S

VARX.P

VARX.S

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

DATE

EDATE

EOMONTH

DATE

TIME

SECOND

Математические и численные функции

CEILING

LOG

LOG10

POWER

Запросы к таблицам в языке DAX

При оценке формул для модели DirectQuery при помощи запросов к таблицам в DAX существуют некоторые ограничения. DirectQuery не поддерживает ссылок на тот же столбец дважды в предложении ORDER BY. В этой ситуации не удастся создать эквивалентную инструкцию Transact-SQL, поэтому запрос завершится неудачей.

Для модели в памяти повтор предложения ORDER BY на результаты не повлияет.

Функции, неподдерживаемые в режиме DirectQuery

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

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

Следующие функции DAX не могут использоваться в моделях DirectQuery.

Функции пути

PATH

PATHCONTAINS

PATHITEM

PATHITEMREVERSE

PATHLENGTH

Прочие функции

COUNTBLANK

FIXED

FORMAT

RAND

RANDBETWEEN

Функции логики операций со временем Даты начала и окончания

DATESQTD

DATESYTD

DATESMTD

DATESQTD

DATESINPERIOD

TOTALMTD

TOTALQTD

TOTALYTD

DATESINPERIOD

SAMEPERIODLASTYEAR

PARALLELPERIOD

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

OPENINGBALANCEMONTH

OPENINGBALANCEQUARTER

OPENINGBALANCEYEAR

CLOSINGBALANCEMONTH

CLOSINGBALANCEQUARTER

CLOSINGBALANCEYEAR

Функции логики операций со временем Предыдущий и следующий периоды

PREVIOUSDAY

PREVIOUSMONTH

PREVIOUSQUARTER

PREVIOUSYEAR

NEXTDAY

NEXTMONTH

NEXTQUARTER

NEXTYEAR

Функции логики операций со временем Периоды и расчеты за периоды

STARTOFMONTH

STARTOFQUARTER

STARTOFYEAR

ENDOFMONTH

ENDOFQUARTER

ENDOFYEAR

FIRSTDATE

LASTDATE

DATEADD

См. также

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

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

Поделиться через