Manipulação de Dados MDX – Noções básicas sobre Ordem de Passagem e 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 muitos estágios de computação, dependendo do uso de vários recursos relacionados ao cálculo. Cada um desses estágios é chamado de passagem de cálculo.

Um passe de cálculo pode ser referenciado por uma posição ordinal, também conhecido como número de passe de cálculo. A contagem de passagens de cálculo necessárias para calcular totalmente todas as células de um cubo é conhecida como a profundidade de passagem de cálculo do cubo.

A tabela de fatos e os dados de write-back só afetam a passagem 0. Scripts preenchem dados após a passagem 0; cada atribuição e declaração de cálculo em um script cria uma nova passagem. Fora do script MDX, as referências ao passe absoluto 0 referem-se à última passagem criada pelo script para o cubo.

Membros calculados são criados em todos os passes, mas a expressão é aplicada na passagem atual. As instâncias anteriores contêm a medida calculada, mas com um valor nulo.

Ordem de Solução

A ordem de resolução determina a prioridade do cálculo no caso de expressões concorrentes. Em uma única passagem, a ordem de resolução determina duas coisas:

• A ordem na qual o Microsoft SQL Server SQL Server Analysis Services avalia dimensões, membros, membros calculados, rollups personalizados e células calculadas.

• A ordem em que o 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.

Resolver valores de pedido e precedência

Os valores de ordem de solução podem variar de -8181 a 65535. Nesse intervalo, alguns valores de ordem de resolução correspondem a tipos específicos de cálculos, conforme mostrado na tabela a seguir.

É altamente recomendável que você use apenas inteiros positivos ao definir valores de ordem de resolução. Se você atribuir valores inferiores aos valores da ordem de resolução mostrados na tabela anterior, o processo de cálculo poderá se tornar imprevisível. Por exemplo, o cálculo para um membro calculado recebe um valor de ordem de resolução inferior ao valor padrão da fórmula cumulativo personalizada de -5119. Um valor de ordem de resolução tão baixo faz com que os membros calculados sejam calculados antes das fórmulas de rollup personalizadas e possam produzir resultados incorretos.

Criando e alterando a ordem de resolução

No Designer de Cubo, no Painel de Cálculos, você pode alterar a ordem de resolução para membros calculados e células calculadas alterando a ordem dos cálculos.

No MDX, você pode usar a palavra-chave SOLVE_ORDER para criar ou alterar membros calculados e células calculadas.

Exemplos de Ordem de Solução

Para ilustrar as complexidades potenciais da ordem de solução, a seguinte série de consultas MDX começa com duas consultas que, individualmente, não têm problemas de ordem de solução. Essas duas consultas são combinadas em uma consulta que requer ordem de solução.

Consultar 1-Diferenças em Receitas e Despesas

Para a primeira consulta MDX, calcule a diferença de vendas e custos para cada ano construindo uma consulta MDX simples semelhante ao exemplo a seguir:

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]  

Nesta consulta, há apenas um membro calculado, Year Difference. Como há apenas um membro calculado, a ordem de resolução não é um problema, desde que o cubo não use membros calculados.

Essa consulta MDX produz um conjunto de resultados semelhante à tabela a seguir.

Consulta 2 - Percentual de Renda Após Despesas

Para a segunda consulta, calcule o percentual de renda após as despesas de cada ano usando a seguinte consulta MDX:

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, tem apenas um único membro calculado, Profit Margin, e, portanto, não tem nenhuma complicação de ordem de solução.

Essa consulta MDX produz um conjunto de resultados ligeiramente diferente, semelhante à tabela a seguir.

A diferença nos conjuntos de resultados entre a primeira consulta e a segunda consulta vem da diferença no posicionamento do membro calculado. Na primeira consulta, o membro calculado faz parte do eixo ROWS, não do eixo COLUMNS mostrado na segunda consulta. Essa diferença no posicionamento torna-se importante na próxima consulta, que combina os dois membros calculados em uma única consulta MDX.

Consulta sobre Diferença Combinada de 3 Anos e Cálculos do Lucro Líquido

Nesta consulta final, combinando ambos os exemplos anteriores em uma única consulta MDX, a ordem de solução torna-se importante devido aos cálculos em colunas e linhas. Para garantir que os cálculos ocorram na sequência correta, defina a sequência na qual os cálculos ocorrem usando a palavra-chave SOLVE_ORDER .

A palavra-chave SOLVE_ORDER especifica a ordem de solução de membros calculados em uma consulta MDX ou em um comando CREATE MEMBER. Os valores inteiros usados com a palavra-chave SOLVE_ORDER são relativos, não precisam começar em zero e não precisam ser consecutivos. O valor simplesmente instrui o MDX a calcular um membro com base em valores obtidos a partir do cálculo de membros de valor superior. 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 nas duas primeiras consultas de exemplo, os dois membros calculados Year Difference e Profit Margin se cruzam em uma única célula no conjunto de resultados da consulta de exemplo MDX. A única maneira de determinar como o 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 dependendo da ordem de resolução dos dois membros calculados.

Primeiro, tente combinar os cálculos usados nas duas primeiras consultas na seguinte consulta MDX:

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 combinado, Profit Margin tem a ordem de resolução mais alta, portanto, ela tem precedência quando as duas expressões interagem. O SQL Server Analysis Services avalia a célula em questão usando a Profit Margin fórmula. Os resultados desse cálculo aninhado, conforme mostrado na tabela a seguir.

O resultado na célula compartilhada é baseado na fórmula para Profit Margin. Ou seja, o 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   

or

(23,038.63) / (20,160.56) = 114.28%  

Isso está claramente incorreto. No entanto, o SQL Server Analysis Services calcula o resultado na célula compartilhada de forma diferente se você trocar as ordens de resolução para os membros calculados na consulta MDX. A seguinte consulta MDX combinada inverte a ordem de solução para os 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, o SQL Server Analysis Services usa a Year Difference fórmula para avaliar a célula, conforme mostrado na tabela a seguir.

Como essa consulta usa a Year Difference fórmula com os Profit Margin dados, a fórmula para a célula compartilhada se assemelha ao seguinte 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 um problema muito complexo para se lidar, especialmente em cubos com um alto número de dimensões envolvendo membros calculados, fórmulas de rollup personalizadas ou células calculadas. Quando o SQL Server Analysis Services avalia uma consulta MDX, o 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)