Manipulation de données MDX - Présentation de l’ordre de passage et de l’ordre de résolution

S’applique à : SQL Server Analysis Services Azure Analysis Services Fabric/Power BI Premium

Lorsqu'un cube est calculé dans le cadre d'un script MDX, il peut subir de nombreuses étapes de calcul, en fonction de l'utilisation de diverses fonctionnalités liées au calcul. Chacune de ces étapes porte le nom de test de calcul.

Un test de calcul peut être désigné par sa position ordinale, appelée numéro du test de calcul. Le nombre de tests nécessaires au calcul complet de toutes les cellules d'un cube représente la profondeur du test de calcul du cube.

Les données de table de faits et d'écriture différée affectent uniquement le test 0. Les scripts remplissent les données après le test 0 ; chaque instruction d'assignation et de calcul d'un script crée un test. En dehors du script MDX, les références au test absolu 0 correspondent au dernier test créé par le script pour le cube.

Des membres calculés sont créés à tous les tests, mais l'expression est appliquée au test actuel. Les tests antérieurs contiennent la mesure calculée, mais avec une valeur Null.

Ordre de résolution

L'ordre de résolution détermine la priorité de calcul en cas de concurrence entre des expressions. Au sein d'un même test de calcul, l'ordre de résolution détermine deux choses :

• Ordre dans lequel Microsoft SQL Server SQL Server Analysis Services évalue les dimensions, les membres, les membres calculés, les cumuls personnalisés et les cellules calculées.

• Ordre dans lequel SQL Server Analysis Services calcule les membres personnalisés, les membres calculés, le cumul personnalisé et les cellules calculées.

Le membre qui a l'ordre de résolution le plus élevé est prioritaire.

Valeurs et priorité de l'ordre de résolution

Les valeurs d'ordre de résolution sont comprises entre -8 181 et 65 535. Dans cette plage, certaines valeurs d'ordre de résolution correspondent à des types de calculs spécifiques, comme illustré dans le tableau suivant.

Il est vivement recommandé d'utiliser uniquement des entiers positifs lors de la définition des valeurs d'ordre de résolution. Si vous assignez des valeurs inférieures aux valeurs d'ordre de résolution mentionnées dans le tableau précédent, le test de calcul peut devenir imprévisible. Par exemple, le calcul d'un membre calculé reçoit une valeur d'ordre de résolution qui est inférieure à la valeur de la formule de cumul personnalisé par défaut, à savoir -5 119. Une valeur d'ordre de résolution aussi faible entraîne le calcul des membres calculés avant le calcul des formules de cumul personnalisé, ce qui peut produire des résultats incorrects.

Création et modification de l'ordre de résolution

Dans le Concepteur de cube, dans le volet Calculs, vous pouvez modifier l’ordre de résolution des membres calculés et des cellules calculées en modifiant l’ordre des calculs.

Dans MDX, vous pouvez utiliser le mot clé SOLVE_ORDER pour créer ou modifier des membres calculés et des cellules calculées.

Exemples d'ordres de résolution

Afin d'illustrer la complexité potentielle de l'ordre de résolution, la série suivante de requêtes MDX commence par deux requêtes qui ne présentent individuellement aucun problème d'ordre de résolution. Ces deux requêtes sont ensuite combinées dans une requête qui nécessite un ordre de résolution.

Requête 1 - Différences dans les revenus et les dépenses

Pour la première requête MDX, calculez la différence entre les ventes et les coûts pour chaque année en construisant une requête MDX simple semblable à l'exemple suivant :

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]  

Dans cette requête, il n'existe qu'un seul membre calculé, Year Difference. L'ordre de résolution ne pose par conséquent aucun problème, tant que le cube n'utilise pas de membre calculé.

Cette requête MDX produit un jeu de résultats semblable au tableau suivant.

Requête 2 -Pourcentage de revenus après dépenses

Pour la seconde requête, calculez le pourcentage de revenus après dépenses pour chaque année à l'aide de la requête MDX suivante :

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]  

Cette requête MDX, comme la précédente, ne contient qu'un seul membre calculé, Profit Margin; elle ne présente donc aucune complication d'ordre de résolution.

Cette requête MDX produit un jeu de résultats légèrement différent, semblable au tableau suivant.

La différence entre les jeux de résultats des deux requêtes provient de la différence d'emplacement du membre calculé. Dans la première requête, le membre calculé fait partie de l'axe ROWS, et non de l'axe COLUMNS illustré dans la deuxième requête. Cette différence d'emplacement devient importante dans la requête suivante, qui combine les deux membres calculés dans une même requête MDX.

Requête 3- Calculs combinés de la différence d’année et du revenu net

Dans cette dernière requête combinant les deux exemples précédents en une seule requête MDX, l'ordre de résolution devient important en raison des calculs à la fois sur les colonnes et sur les lignes. Pour faire en sorte que les calculs s’exécutent dans le bon ordre, définissez l’ordre d’exécution des calculs à l’aide du mot clé SOLVE_ORDER .

Le mot clé SOLVE_ORDER spécifie l’ordre de résolution des membres calculés dans une requête MDX ou une commande CREATE MEMBER . Les valeurs entières utilisées avec le mot clé SOLVE_ORDER sont relatives, elles n’ont pas besoin de commencer à zéro ni d’être consécutives. La valeur indique simplement à la requête MDX de calculer un membre en fonction des valeurs dérivées du calcul des membres ayant une valeur supérieure. Si un membre calculé est défini sans le mot clé SOLVE_ORDER , la valeur par défaut de ce membre calculé est zéro.

Par exemple, si vous combinez les calculs utilisés dans les deux premiers exemples de requêtes, les deux membres calculés, Year Difference et Profit Margin, se croisent à une cellule unique dans le dataset des résultats de l'exemple de requête MDX. La seule façon de déterminer comment SQL Server Analysis Services évaluera cette cellule est d’utiliser l’ordre de résolution. Les formules utilisées pour construire cette cellule produiront différents résultats, en fonction de l'ordre de résolution des deux membres calculés.

Tout d'abord, essayez de combiner les calculs utilisés dans les deux premières requêtes dans la requête MDX suivante :

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]  

Dans cet exemple de requête MDX combinée, Profit Margin a l’ordre de résolution le plus élevé ; il est donc prioritaire quand les deux expressions interagissent. SQL Server Analysis Services évalue la cellule en question à l’aide de la Profit Margin formule . Le tableau suivant présente les résultats de ces calculs imbriqués.

Le résultat mentionné dans la cellule partagée est basé sur la formule de Profit Margin. Autrement dit, SQL Server Analysis Services calcule le résultat dans la cellule partagée avec les Year Difference données, en produisant la formule suivante (le résultat est arrondi pour plus de clarté) :

((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%  

Ce résultat est clairement incorrect. Toutefois, SQL Server Analysis Services calcule différemment le résultat dans la cellule partagée si vous changez les ordres de résolution pour les membres calculés dans la requête MDX. La requête MDX combinée suivante inverse l'ordre de résolution des membres calculés :

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]  

Comme l’ordre des membres calculés a été changé, SQL Server Analysis Services utilise la Year Difference formule pour évaluer la cellule, comme indiqué dans le tableau suivant.

Comme cette requête utilise la formule Year Difference avec les données Profit Margin , la formule de la cellule partagée ressemble au calcul suivant :

(($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  

Considérations supplémentaires

L'ordre de résolution peut s'avérer très délicat à gérer, notamment dans les cubes qui contiennent de très nombreuses dimensions impliquant des membres calculés, des formules de cumul personnalisé ou des cellules calculées. Lorsque SQL Server Analysis Services évalue une requête MDX, SQL Server Analysis Services prend en compte les valeurs d’ordre de résolution pour tout ce qui est impliqué dans une passe donnée, y compris les dimensions du cube spécifiées dans la requête MDX.

Voir aussi

CalculationCurrentPass (MDX)
CalculationPassValue (MDX)
Instruction CREATE MEMBER (MDX)
Manipulation de données (MDX)