Manipulação de dados MDX – Noções básicas sobre a ordem de aprovação e a ordem de resolução
Aplica-se a:
SQL Server Analysis Services Azure Analysis Services Fabric/Power BI Premium
Quando um cubo é calculado como resultado de um script MDX, ele pode passar por várias fases de cálculo dependendo do uso de diversos recursos relacionados ao cálculo. Cada uma dessas fases é chamada de fase de cálculo.
Uma fase de cálculo pode ser denominada por uma posição ordinal, chamada número de fase de cálculo. A quantidade de fases de cálculo necessária para computar por completo todas as células de um cubo é conhecida como profundidade de fase de cálculo do cubo.
Dados da tabela de fatos e de write-back afetam somente a passagem 0. Os scripts preenchem os dados depois da passagem 0; cada atribuição e instrução calculada de um script criam uma nova passagem. Fora do script MDX, as referências à passagem 0 absoluta referem-se à última passagem criada pelo script para o cubo.
São criados membros calculados em todas as passagens, mas a expressão é aplicada à passagem atual. Passagens anteriores contêm a medida calculada, mas com um valor nulo.
Ordem de resolução
A ordem de resolução determina a prioridade de cálculo no caso de expressões concorrentes. Em uma mesma passagem, a ordem de resolução determina duas coisas:
A ordem na qual a Microsoft SQL Server SQL Server Analysis Services avalia dimensões, membros, membros calculados, rollups personalizados e células calculadas.
A ordem na qual SQL Server Analysis Services calcula membros personalizados, membros calculados, rollup personalizado e células calculadas.
O membro com a ordem de resolução mais alta tem precedência.
Observação
A exceção a essa precedência é a função Aggregate. A ordem de resolução de membros calculados com a função Aggregate é inferior a qualquer medida calculada de intersecção.
Valores e precedência da ordem de resolução
Os valores da ordem de resolução podem variar de -8181 a 65535. Nesse intervalo, alguns valores da ordem de resolução correspondem a tipos específicos de cálculos, como mostra a tabela a seguir.
| Cálculo | Ordem de resolução |
|---|---|
| Fórmulas de membro personalizado | -5119 |
| Operadores unários | -5119 |
| Cálculo de totais visuais | -4096 |
| Todos os outros cálculos (se não for especificado de outra forma) | 0 |
É altamente recomendável o uso exclusivo de números inteiros positivos ao definir valores da ordem de resolução. Se você atribuir valores inferiores aos valores da ordem de resolução mostrados na tabela anterior, a fase de cálculo pode tornar-se imprevisível. Por exemplo, o cálculo de um membro calculado receberá um valor de ordem de resolução inferior ao valor da fórmula de rollup personalizado de -5119. Esse valor de ordem de resolução baixo faz com que os membros calculados sejam calculados antes das fórmulas de rollup personalizado, podendo produzir resultados incorretos.
Criando e alterando a ordem de resolução
No Designer de Cubo, no Painel de Cálculos, é possível alterar a ordem de resolução de membros calculados e células calculadas mudando a ordem dos cálculos.
No formato MDX, você pode usar a palavra-chave SOLVE_ORDER para criar ou alterar membros calculados e células calculadas.
Exemplos de ordem de resolução
Para ilustrar as complexidades inerentes da ordem de resolução, a sequência de consultas MDX a seguir começa com duas consultas, sem problemas de ordem de resolução individualmente. Em seguida, essas duas consultas são combinadas em uma consulta que requer uma ordem de resolução.
Consultar 1-Diferenças em Receitas e Despesas
Para a primeira consulta MDX, calcule a diferença entre vendas e custos de cada ano construindo uma consulta MDX simples semelhante a este exemplo:
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]
Nessa consulta, existe apenas um membro calculado, Year Difference. Como há somente um membro calculado, a ordem de resolução não é uma preocupação, pois o cubo não usará nenhum membro calculado.
Essa consulta MDX produz um conjunto de resultados semelhante à tabela a seguir.
| Valor das Vendas pela Internet | Custo Total do Produto da Internet | |
|---|---|---|
| AS 2007 | $9,791,060.30 | $5,718,327.17 |
| CY 2008 | $9,770,899.74 | $5,721,205.24 |
| Diferença anual | ($20,160.56) | $2,878.06 |
Consultar 2 percentuais de renda após despesas
Para a segunda consulta MDX, calcule a porcentagem da receita menos as despesas de cada ano, usando a consulta MDX a seguir:
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]
Essa consulta MDX, como a anterior, possui apenas um membro calculado, Profit Margin, e, portanto, não tem complicações com a ordem de resolução.
Essa consulta MDX produz um conjunto de resultados um pouco diferente, semelhante à tabela a seguir.
| Valor das Vendas pela Internet | Custo Total do Produto da Internet | Margem de Lucro | |
|---|---|---|---|
| AS 2007 | $9,791,060.30 | $5,718,327.17 | 41.60% |
| CY 2008 | $9,770,899.74 | $5,721,205.24 | 41.45% |
A diferença entre os conjuntos de resultados da primeira e da segunda consultas é proveniente da diferença no posicionamento do membro calculado. Na primeira consulta, o membro calculado faz parte do eixo ROWS e não no eixo COLUMNS mostrado na segunda consulta. A diferença de posicionamento passa a ser importante na próxima consulta, que combina os dois membros calculados em uma única consulta MDX.
Consultar diferença de três anos combinados e cálculos de lucro líquido
Nessa consulta final, a combinação dos exemplos anteriores em uma única consulta MDX, a ordem de resolução passa a ser importante devido aos cálculos nas colunas e nas linhas. Para confirmar se os cálculos são feitos na sequência correta, defina a sequência de cálculo usando a palavra-chave SOLVE_ORDER .
A palavra-chave SOLVE_ORDER especifica a ordem de resolução de membros calculados em uma consulta MDX ou em um comando CREATE MEMBER . Os valores de números inteiros usados com a palavra-chave SOLVE_ORDER são relativos, não precisam começar em zero e nem serem consecutivos. O valor simplesmente orienta o MDX a calcular um membro com base nos valores derivados do cálculo dos membros com um valor mais alto. Se um membro calculado for definido sem a palavra-chave SOLVE_ORDER , o valor padrão desse membro calculado será zero.
Por exemplo, se você combinar os cálculos usados nos dois primeiros exemplos de consulta, os dois membros calculados, Year Difference e Profit Margin, encontram-se em uma intersecção em uma única célula do conjunto de dados do resultado da consulta MDX de exemplo. A única maneira de determinar como SQL Server Analysis Services avaliará essa célula é pela ordem de resolução. As fórmulas usadas para construir essa célula produzirão resultados diferentes de acordo com a ordem de resolução dos dois membros calculados.
Primeiro, tente combinar os cálculos usados nas duas primeiras consultas na consulta MDX a seguir:
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]
Neste exemplo de consulta MDX combinada, Profit Margin tem a ordem de resolução mais alta, portanto terá precedência quando as duas expressões interagirem. SQL Server Analysis Services avalia a célula em questão usando a Profit Margin fórmula . Os resultados desse cálculo aninhado são mostrados na tabela a seguir.
| Valor das Vendas pela Internet | Custo Total do Produto da Internet | Margem de Lucro | |
|---|---|---|---|
| AS 2007 | $9,791,060.30 | $5,718,327.17 | 41.60% |
| CY 2008 | $9,770,899.74 | $5,721,205.24 | 41.45% |
| Diferença anual | ($20,160.56) | $2,878.06 | 114.28% |
O resultado na célula compartilhada baseia-se na fórmula para Profit Margin. Ou seja, SQL Server Analysis Services calcula o resultado na célula compartilhada com os Year Difference dados, produzindo a fórmula a seguir (o resultado é arredondado para maior clareza):
((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
ou
(23,038.63) / (20,160.56) = 114.28%
Isso está claramente incorreto. No entanto, SQL Server Analysis Services calcula o resultado na célula compartilhada de forma diferente se você alternar os pedidos de resolução para os membros calculados na consulta MDX. A consulta MDX combinada a seguir inverte a ordem de resolução dos membros calculados:
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]
Como a ordem dos membros calculados foi alternada, SQL Server Analysis Services usa a Year Difference fórmula para avaliar a célula, conforme mostrado na tabela a seguir.
| Valor das Vendas pela Internet | Custo Total do Produto da Internet | Margem de Lucro | |
|---|---|---|---|
| AS 2007 | $9,791,060.30 | $5,718,327.17 | 41.60% |
| CY 2008 | $9,770,899.74 | $5,721,205.24 | 41.45% |
| Diferença anual | ($20,160.56) | $2,878.06 | (0.15%) |
Como essa consulta usa a fórmula Year Difference com os dados de Profit Margin , a fórmula da célula compartilhada assemelha-se a este cálculo:
(($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
Ou
0.4145 - 0.4160= -0.15
Considerações adicionais
A ordem de resolução pode ser uma questão bastante complexa com que lidar, especialmente em cubos com um grande número de dimensões envolvendo membro calculado, fórmulas de rollup personalizado ou células calculadas. Quando SQL Server Analysis Services avalia uma consulta MDX, SQL Server Analysis Services leva em conta os valores de ordem de resolução para tudo o que está envolvido em uma determinada passagem, incluindo as dimensões do cubo especificadas na consulta MDX.
Consulte Também
CalculationCurrentPass (MDX)
CalculationPassValue (MDX)
Instrução CREATE MEMBER (MDX)
Manipulando dados (MDX)