Обработка данных многомерных выражений — понимание порядка передачи и порядка решения
Применимо к:
SQL Server Analysis Services Azure Analysis Services Fabric/Power BI Premium
В ходе вычисления куба в скрипте многомерных выражений вычисление происходит за несколько шагов, в зависимости от того, для какой цели используются те или иные вычислительные функции. Каждый из этих шагов называется этапом вычисления.
Этап вычисления можно идентифицировать по его порядковому номеру — номеру этапа вычислений. Количество этапов, необходимых для полного вычисления всех ячеек куба, называется глубиной вычисления куба.
Таблица фактов и данные обратной записи обрабатываются только на этапе 0. Для каждой операции присваивания и каждой инструкции вычисления в скрипте создается новый этап. За пределами скрипта многомерных выражений обращения к этапу с абсолютным номером 0 указывают на последний этап, созданный скриптом для куба.
Вычисляемые элементы создаются на всех этапах, но выражение применяется к текущему этапу. Предыдущие этапы содержат вычисляемую меру, но со значением NULL.
Порядок вычисления
Порядок вычисления определяет приоритет вычисления конкурирующих выражений. В рамках одного этапа порядок вычисления определяет две вещи.
Порядок, в котором microsoft SQL Server SQL Server Analysis Services оценивает измерения, элементы, вычисляемые элементы, пользовательские свертки и вычисляемые ячейки.
Порядок, в котором SQL Server Analysis Services вычисляет пользовательские члены, вычисляемые элементы, настраиваемые свертки и вычисляемые ячейки.
Приоритет имеет элемент с наивысшим порядком вычисления.
Примечание
Исключением из этого правила является агрегатная функция. Порядок разрешения вычисляемых элементов с агрегатной функцией меньше, чем порядок разрешения любой пересекающейся вычисляемой меры
Значение и приоритет порядка разрешения
Порядок вычисления может принимать значения от -8181 до 65535. Некоторые значения этого диапазона соответствуют определенным типам вычислений (см. следующую таблицу).
| Вычисление | Порядок вычисления |
|---|---|
| Нестандартные формулы элементов | -5119 |
| Унарные операторы | -5119 |
| Вычисление визуальных сумм | -4096 |
| Другие вычисления (если не указано иное) | 0 |
Настоятельно рекомендуется в качестве порядка вычисления использовать только положительные целочисленные значения. При указании значений, меньших, чем значения порядка вычисления из предыдущей таблицы, выполнение этапа вычисления может стать непредсказуемым. Пусть расчет вычисляемого элемента имеет меньшее значение порядка вычисления, чем значение по умолчанию для пользовательской формулы свертки (-5119). Из-за более низкого значения порядка вычисления вычисляемые элементы будут рассчитываться перед пользовательскими формулами свертки, что может привести к неверному результату.
Создание и изменение порядка вычисления
Порядок вычисления для вычисляемых элементов и вычисляемых ячеек можно задать в конструкторе кубов на панели Вычисления, изменив порядок следования расчетов.
В многомерных выражениях для создания и изменения вычисляемых элементов и ячеек используется ключевое слово SOLVE_ORDER .
Примеры порядка вычисления
Чтобы проиллюстрировать потенциальную сложность работы с порядком вычисления, начнем с двух запросов, в каждом из которых проблемы определения порядка вычисления не возникает. Затем оба запроса объединяются в один, в котором требуется указание порядка вычисления.
Запрос 1. Различия в доходах и расходах
Для следующего запроса многомерных выражений вычислите разницу в продажах и ценах по каждому году, создав простой запрос многомерных выражений на основе следующего примера:
WITH
MEMBER
[Date].[Calendar].[Year Difference] AS ([Date].[Calendar].[Calendar Year].&[2008] - [Date].[Calendar].[Calendar Year].&[2007] )
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Internet Total Product Cost] }
ON COLUMNS ,
{ [Date].[Calendar].[Calendar Year].&[2007], [Date].[Calendar].[Calendar Year].&[2008], [Date].[Year Difference] }
ON ROWS
FROM [Adventure Works]
В этом запросе есть только один вычисляемый элемент — Year Difference. Поскольку вычисляемый элемент только один, указание порядка вычисления не требуется, если только в кубе не используются другие вычисляемые элементы.
Запрос многомерных выражений возвращает примерно следующую результирующую таблицу.
| Internet Sales Amount | Общая себестоимость продукции для заказов в Интернете | |
|---|---|---|
| CY 2007 | $9,791,060.30 | $5,718,327.17 |
| CY 2008 | $9,770,899.74 | $5,721,205.24 |
| Разность за год | ($ 20 160,56) | $2,878.06 |
Запрос 2 процента дохода после расходов
Второй запрос позволяет вычислить процент чистой прибыли за каждый год. Используется следующий запрос многомерных выражений:
WITH
MEMBER
[Measures].[Profit Margin] AS
([Measures].[Internet Sales Amount] - [Measures].[Internet Total Product Cost] ) / [Measures].[Internet Sales Amount] ,
FORMAT_STRING="Percent"
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Internet Total Product Cost], [Measures].[Profit Margin] }
ON COLUMNS ,
{ [Date].[Calendar].[Calendar Year].&[2007], [Date].[Calendar].[Calendar Year].&[2008] }
ON ROWS
FROM [Adventure Works]
Этот запрос многомерных выражений, как и предыдущий, имеет только один вычисляемый элемент Profit Margin, поэтому сложностей с порядком вычисления не возникает.
Запрос многомерных выражений возвращает немного другая результирующая таблица наподобие следующей.
| Internet Sales Amount | Общая себестоимость продукции для заказов в Интернете | Коэффициент прибыли | |
|---|---|---|---|
| CY 2007 | $9,791,060.30 | $5,718,327.17 | 41.60% |
| CY 2008 | $9,770,899.74 | $5,721,205.24 | 41.45% |
Отличия результирующих наборов первого и второго запросов обусловлены тем, что вычисляемый элемент в них размещен по-разному. В первом запросе вычисляемый элемент является частью оси ROWS, а во втором — оси COLUMNS. Это отличие становится важным в следующем запросе, при объединении вычисляемых элементов в одном запросе многомерных выражений.
Запрос 3-комбинированной разницы за год и расчет чистой прибыли
В последнем запросе, который объединяет два предыдущих в один запрос многомерных выражений, порядок вычисления становится существенным, поскольку вычисления производятся как в столбцах, так и в строках. Чтобы гарантировать верную последовательность вычислений, ее необходимо определить с помощью ключевого слова SOLVE_ORDER .
Ключевое слово SOLVE_ORDER указывает порядок вычисления вычисляемых элементов в запросе многомерных выражений или в команде CREATE MEMBER . Целые значения, заданные с помощью ключевого слова SOLVE_ORDER , являются относительными, они не обязательно должны начинаться с нуля и следовать друг за другом. В многомерных выражениях они лишь сообщают, что элементы следует рассчитывать на основе значений, полученных при расчете других элементов с более высоким порядком разрешения. Если в определении вычисляемого элемента не указано ключевое слово SOLVE_ORDER , значением порядка разрешения по умолчанию для этого вычисляемого элемента является ноль.
Например, если объединить вычисления двух первых запросов многомерных выражений, то два вычисляемых элемента — Year Difference и Profit Margin— пересекаются в одной ячейке результирующего набора данных в примере запроса многомерных выражений. Единственный способ определить, как SQL Server Analysis Services будет оценивать эту ячейку, — по порядку решения. Формулы, используемые для расчета значения этой ячейки, возвращают различные результаты в зависимости от указанного порядка вычисления данных вычисляемых элементов.
Для начала объединим вычисления из предыдущих двух примеров в следующий запрос многомерных выражений:
WITH
MEMBER
[Date].[Calendar].[Year Difference] AS ([Date].[Calendar].[Calendar Year].&[2008] - [Date].[Calendar].[Calendar Year].&[2007] ) ,
SOLVE_ORDER = 1
MEMBER
[Measures].[Profit Margin] AS
( [Measures].[Internet Sales Amount] - [Measures].[Internet Total Product Cost] ) / [Measures].[Internet Sales Amount] ,
FORMAT_STRING="Percent" ,
SOLVE_ORDER = 2
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Internet Total Product Cost], [Measures].[Profit Margin] }
ON COLUMNS ,
{ [Date].[Calendar].[Calendar Year].&[2007], [Date].[Calendar].[Calendar Year].&[2008], [Date].[Year Difference] }
ON ROWS
FROM [Adventure Works]
В этом совмещенном запросе многомерных выражений формула Profit Margin имеет наивысший порядок вычисления, поэтому она будет приоритетной при пересечении двух выражений. SQL Server Analysis Services вычисляет ячейку с помощью формулыProfit Margin. Результат этих вложенных вычислений приведен в следующей таблице.
| Internet Sales Amount | Общая себестоимость продукции для заказов в Интернете | Коэффициент прибыли | |
|---|---|---|---|
| CY 2007 | $9,791,060.30 | $5,718,327.17 | 41.60% |
| CY 2008 | $9,770,899.74 | $5,721,205.24 | 41.45% |
| Разность за год | ($ 20 160,56) | $2,878.06 | 114,28 % |
Результат в общей ячейке вычисляется по формуле Profit Margin. То есть SQL Server Analysis Services вычисляет результат в общей ячейке с Year Difference данными, создавая следующую формулу (результат округляется для ясности):
((9,770,899.74 - 9,791,060.30) - (5,721,205.24 - 5,718,327.17)) / (9,770,899.74 - 9,791,060.30) = 1.14275744
или
(23,038.63) / (20,160.56) = 114.28%
Это в корне неверно. Однако SQL Server Analysis Services вычисляет результат в общей ячейке по-разному при переключении порядка решения для вычисляемых элементов в запросе многомерных выражений. В следующем объединенном запросе многомерных выражений порядок вычисления вычисляемых элементов изменен на обратный.
WITH
MEMBER
[Date].[Calendar].[Year Difference] AS ([Date].[Calendar].[Calendar Year].&[2008] - [Date].[Calendar].[Calendar Year].&[2007] ) ,
SOLVE_ORDER = 2
MEMBER
[Measures].[Profit Margin] AS
( [Measures].[Internet Sales Amount] - [Measures].[Internet Total Product Cost] ) / [Measures].[Internet Sales Amount] ,
FORMAT_STRING="Percent" ,
SOLVE_ORDER = 1
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Internet Total Product Cost], [Measures].[Profit Margin] }
ON COLUMNS ,
{ [Date].[Calendar].[Calendar Year].&[2007], [Date].[Calendar].[Calendar Year].&[2008], [Date].[Year Difference] }
ON ROWS
FROM [Adventure Works]
После переключения порядка вычисляемых элементов SQL Server Analysis Services использует формулу Year Difference для вычисления ячейки, как показано в следующей таблице.
| Internet Sales Amount | Общая себестоимость продукции для заказов в Интернете | Коэффициент прибыли | |
|---|---|---|---|
| CY 2007 | $9,791,060.30 | $5,718,327.17 | 41.60% |
| CY 2008 | $9,770,899.74 | $5,721,205.24 | 41.45% |
| Разность за год | ($ 20 160,56) | $2,878.06 | (0.15%) |
Поскольку в этом запросе используется формула Year Difference с данными Profit Margin , то формулу для общей ячейки можно записать следующим образом:
(($9,770,899.74 - 5,721,205.24) / $9,770,899.74) - ((9,791,060.30 - 5,718,327.17) / 9,791,060.30) = -0.15
либо
0.4145 - 0.4160= -0.15
Дополнительные сведения
Работать с порядками вычисления весьма сложно, особенно в кубах с большим количеством измерений, содержащих вычисляемые элементы, пользовательские формулы сверток и вычисляемые ячейки. Когда SQL Server Analysis Services оценивает запрос многомерных выражений, SQL Server Analysis Services учитывает значения порядка решения для всего, что участвует в заданном проходе, включая размеры куба, указанного в запросе многомерных выражений.
См. также:
CalculationCurrentPass (многомерные выражения)
CalculationPassValue (многомерные выражения)
Инструкция CREATE MEMBER (многомерные выражения)
Манипулирование данными (многомерные выражения)