Améliorations des performances pour MDX dans SQL Server 2008 Analysis Services
Pour cette version d'Analysis Services, des efforts particuliers ont été déployés pour améliorer les performances de l'exécution des calculs d'expressions multidimensionnelles (MDX, Multidimensional Expressions). Plusieurs modifications importantes ont été apportées à l'architecture du moteur pour atteindre ces gains de performance. Toutefois, pour tirer parti de ces améliorations des performances, il est nécessaire d'optimiser votre code MDX.
Ce document vous permet de comprendre à quels moments des problèmes peuvent se produire dans votre code MDX existant, vous empêchant de bénéficier des améliorations des performances, et d'obtenir des conseils sur la manière d'éviter ces problèmes dans votre nouveau codage MDX. Ce document inclut également la liste des fonctions qui bénéficient des améliorations des performances.
Examen de votre code pour obtenir des gains de performance maximaux dans les expressions MDX
Lors de l'examen de votre code, essayez d'éviter l'ensemble des scénarios ou situations de codage suivants car ils risquent d'empêcher vos instructions MDX d'atteindre les gains de performance attendus dans SQL Server 2008 Analysis Services (SSAS). Toutefois, si vous n'avez aucun moyen pratique de modifier votre code afin d'éviter les situations répertoriées, votre code MDX atteindra le même niveau de performance que dans SQL Server 2005 Analysis Services (SSAS).
Définitions utiles
Espace
Jeu de cellules sur lesquelles une expression est évaluée.
Forme arbitraire
Espace qui ne peut pas être exprimé sous la forme de la jointure croisée de deux jeux ou plus. Par exemple, l'espace {(Drink, USA), (Food, Canada)} représente une forme arbitraire parce qu'il s'agit d'un sous-ensemble de la jointure croisée entre {Drink, Food} * {USA, Canada} = {(Drink, USA), (Drink, Canada), (Food, USA), (Food, Canada)}.
Expression statique
Une expression est dite statique lorsqu'elle est invariante sur l'espace sur lequel elle est calculée.
Par exemple, sur l'espace de CrossJoin(Product.Members, Customer.Members), les expressions suivantes sont invariantes.
1, une expression constante
Product.Members.Count
Expression dynamique
Une expression est dite dynamique lorsqu'elle se résout dans une valeur différente pour chaque cellule comprise sur l'espace sur lequel elle est calculée.
Par exemple, sur l'espace de CrossJoin(Product.Members, Customer.Members), les expressions suivantes sont dynamiques.
- Sales, parce que Sales est une mesure dont la valeur est différente pour chaque cellule de l'espace.
Attribut variable
Un attribut variable régit le mode d'évaluation de l'expression et rend cette expression dépendante vis-à-vis de lui. Par exemple, l'expression Customer.Geography.CurrentMember dépend des attributs d'une hiérarchie géographique.
En général, les attributs variables réduisent l'espace sur lequel les expressions sont évaluées. Prenons l'expression suivante :
with member measures.x as Customers.Geography.currentmember.uniquename
Select Customers.Geography.City.members on 0,
Product.members on 1
From sales
Where measures.x
Dans cette expression, Customers.Geography est une expression statique. La fonction currentmember est un attribut variable parce qu'elle introduit une dépendance vis-à-vis de l'attribut City. Uniquename n'ajoute pas d'attributs variables parce qu'il est lié à currentmember dans une relation 1:1. Par conséquent, uniquename sera évalué une seule fois pour chaque client et ne sera pas répété pour chaque Product. Ainsi, l'espace entier de l'expression a été réduit efficacement sur l'attribut variable.
Utilisation d'expressions dans les propriétés d'une cellule qui ne sont pas des valeurs
Toute expression MDX utilisée pour assigner la valeur d'une propriété d'une cellule qui n'est pas une valeur ne bénéficiera pas des améliorations des performances. Les performances resteront au même niveau que celui d'SQL Server 2005 Analysis Services (SSAS).
Utilisation de fonctions non répertoriées
L'utilisation de toute fonction non répertoriée dans ce document dans votre code MDX ne bénéficiera pas des gains de performance attendus de cette version du produit. Consultez Functions with enhanced performance dans ce document.
Utilisation de la sécurité des cellules
L'évaluation d'une expression MDX sur un espace qui a défini la sécurité des cellules empêchera votre code d'atteindre les performances améliorées.
La relation entre la sécurité des cellules et les performances est présentée dans le tableau suivant.
Sécurité des cellules |
Performances attendues |
|---|---|
Aucune |
Les meilleures |
Lecture |
Intermédiaires |
Lecture du contingent |
Les plus faibles |
Consultez Utilisation d'expressions MDX pour définir les autorisations sur les données des cellules et Octroi d'accès personnalisés aux données des cellules.
Utilisation de la dimensionnalité dynamique
L'utilisation d'expressions de dimensionnalité dynamique dans votre code MDX empêchera votre code d'atteindre les performances améliorées. Par exemple, les expressions comme Sum( IIF( Sales > 10000, h1.Members, h2.Members)) n'en bénéficieront pas parce que votre code modifie les membres à additionner à mesure que l'expression Sales est évaluée. Un autre exemple implique un scénario dans lequel vous devez utiliser un membre des hiérarchies Année civile ou Année fiscale qui dépendent d'un attribut qui appartient au membre actuel de l'attribut Account pour faire une comparaison avec la valeur équivalente d'une période parallèle. L'expression MDX requise pour ce scénario ressemblerait alors à l'exemple de code suivant.
ParallelPeriod(Iif( Account.CurrentMember.Properties("UsesFiscalCalendar")="Y", FiscalTime, CalendarTime).CurrentMember)
De nouveau, les dimensions changent de manière dynamique à mesure que le membre actuel de la dimension Account change.
Utilisation de paramètres dynamiques
L'utilisation de paramètres dynamiques dans votre code MDX empêchera votre code d'atteindre les performances améliorées. Par exemple, une expression comme KpiGoal("Sales_" & [Fiscal Year].currentmember.UniqueName) varie sur les cellules sur lesquelles elle est calculée. Par opposition, l'expression KpiGoal("Sales_" & Cstr(Year(Now))) est invariante.
Important
Il peut arriver que l'expression KpiGoal("Sales_" & [Fiscal Year].currentmember.UniqueName) soit évaluée à la même valeur sur l'espace dans lequel elle est calculée. Toutefois, cela ne suffirait pas au moteur pour fournir les gains de performance attendus.
Références de membres dynamiques
L'utilisation de références de membres dynamiques dans votre code MDX empêchera votre code d'atteindre les performances améliorées. Par exemple, dans l'expression suivante
(IIF( e, mbr1, mbr2), Sales)
il n'existe aucun moyen de connaître le tuple obtenu tant que l'expression IIF() n'a pas été évaluée au moment de l'exécution. Toutefois, dans l'expression équivalente suivante
IIF( e, (mbr1, Sales), (mbr1, Sales))
l'un ou l'autre des tuples obtenus est connu avant l'évaluation de l'expression e.
Indicateurs de plan
Les indicateurs de plan sont une extension du langage MDX qui permet d'indiquer au moteur comment évaluer les expressions. Dans cette version d'Analysis Services, les indicateurs de plan sont introduits via la fonction IIF(,,) uniquement.
Les indicateurs de plan peuvent être spécifiés dans l'expression MDX ou configurés de manière globale dans les propriétés de configuration du serveur.
Indicateurs de plan dans les fonctions IIF()
Dans IIF(,,), les indicateurs des expressions sont spécifiés à l'aide de la syntaxe suivante :
IIF(<cond>, <expr>, <expr>) [HINT <hints>]
<expr> ::= <expr> [HINT <hints>]
<hints> ::= <hint> [<hints>]
<hint> ::= EAGER | STRICT | LAZY
EAGER entraîne l'évaluation de l'expression dans l'intégralité du sous-espace IIF.
STRICT entraîne l'évaluation de l'expression uniquement dans le sous-espace résultant en fonction des résultats de l'expression de condition.
LAZY entraîne l'évaluation de l'expression en mode cellule par cellule.
EAGER et STRICT s'excluent mutuellement dans l'indicateur ; ils peuvent être utilisés dans la même fonction IIF(,,) sur des expressions différentes.
Exemple de syntaxe :
IIF([Measures].[Internet Sales Amount]=0
, {([Date].[Calendar Year].CURRENTMEMBER, [Customer].[Country].[All Customers])} HINT EAGER
, {{[Date].[Calendar Year].CURRENTMEMBER} * [Customer].[Country].[Country].MEMBERS} STRICT LAZY
)
Indicateurs de plan dans les propriétés de configuration
Les propriétés de configuration suivantes sont introduites pour assurer la prise en charge des indicateurs de plan, sous le chemin d'accès OLAP\Query :
Nom de la propriété |
Valeurs acceptables |
Explication |
IIFThenMode |
0 | 1 | 2 |
0, aucun indicateur (valeur par défaut) 1, EAGER 2, STRICT |
IIFElseMode |
0 | 1 | 2 |
0, aucun indicateur (valeur par défaut) 1, EAGER 2, STRICT |
LazyEnabled |
0 | 1 |
0, désactivé (valeur par défaut) 1, activé |
Procédures stockées définies par l'utilisateur (COM ou .NET)
L'utilisation de procédures stockées définies par l'utilisateur dans votre code MDX empêchera votre code d'atteindre des performances optimales.
[!REMARQUE]
SQL Server 2008 Analysis Services (SSAS) fournit des procédures stockées optimisées pour les améliorations des performances.
Utilisation de jeux nommés ou d'alias de jeu dans les paramètres
Chaque fois qu'un jeu nommé ou un alias de jeu est utilisé en tant que premier paramètre dans les fonctions Sum, Min, Max, Avg ou Aggregate dans votre code MDX, votre code ne bénéficiera pas des améliorations des performances.
Par exemple, l'expression MDX suivante compte le nombre de membres qui ont plusieurs enfants.
Sum(h.members as S, Iif(S.Current.Children.Count > 1, 1, 0))
Étant donné que h.members se voit attribuer l'alias S et que la valeur de la fonction Current est tirée du jeu sous forme d'alias, il empêche l'amélioration attendue des performances.
Un autre exemple courant de cette situation est illustré dans le code suivant.
WITH
SET [Core Products] AS '{[Product].[Category].[Bikes]}'
MEMBER [Measures].[Core Products Sales] AS SUM([Core Products], [Measures].[Internet Average Unit Price] * [Measures].[Internet Order Quantity])
Select [Measures].[Core Products Sales] on 0
From [Adventure Works]
La fonction SUM dans la définition du membre n'atteint pas les performances améliorées attendues parce qu'elle se base sur un jeu nommé.
Utilisation de liaisons tardives dans les expressions de cumul personnalisé
Chaque fois qu'une expression de cumul personnalisé fait référence à un membre calculé ou à toute autre expression MDX évaluée au moment de l'exécution, l'expression de cumul personnalisé empêche d'atteindre les performances améliorées.
Utilisation de références anticipées dans les scripts
Chaque fois que vous créez des références de définition anticipées dans des instructions distinctes de votre code MDX, votre code ne bénéficie pas des améliorations des performances. Par exemple, dans l'extrait de script MDX suivant, une référence anticipée est créée sur Y pendant la définition de X.
Create Member X as Y * 2;
…
Create Member Y as ( Sales, [Date].[Calendar].[Month].PreviousMember);
Pour corriger cette situation, placez la définition de Y avant la définition de X, comme dans l'extrait suivant.
Create Member Y as ( Sales, [Date].[Calendar].[Month].PreviousMember);
Create Member X as Y * 2;
…
Fonctions avec performances améliorées
Fonctions scalaires
La liste suivante de fonctions scalaires inclut les fonctions sur lesquelles vous devriez constater de meilleures performances. La première colonne de la liste contient les opérateurs scalaires.
- |
OR |
KEY |
* |
XOR |
LEVELS.COUNT |
/ |
CALCULATIONPASSVALUE |
MEMBERTOSTR |
+ |
CASE |
MEMBERVALUE |
< |
COALESCEEMPTY |
NAME |
<= |
HIERARCHIES.COUNT |
ORDINAL |
<> |
ID |
PROPERTIES |
= |
IIF |
UNIQUENAME |
> |
IS |
USERNAME |
>= |
ISANCESTOR |
VALIDMEASURE |
unary minus |
ISEMPTY |
VALUE |
NOT |
ISLEAF |
|
AND |
ISSIBLING |
|
[!REMARQUE]
Toute procédure stockée définie par l'utilisateur, dans du code COM ou managé, ne bénéficiera pas d'une amélioration des performances par rapport à SQL Server 2005 Analysis Services (SSAS). Pour plus d'informations, consultez la section Procédures stockées définies par l'utilisateur (COM ou .NET) dans ce document. Les expressions constantes, qu'elles soient littérales ou numériques, bénéficieront des améliorations des performances.
Fonctions de membre
La liste suivante de fonctions de membre inclut les fonctions sur lesquelles vous devriez constater de meilleures performances.
.CurrentMember |
.FirstSibling |
.LastSibling |
.DataMember |
.Item |
.Lead |
.DefaultMember |
.Lag |
.Parent |
.FirstChild |
.LastChild |
.UnknownMember |
Ancestor |
KPIStatus |
NextMember |
Ancestors |
KPITrend |
OpeningPeriod |
Ascendants |
KPIValue |
ParallelPeriod |
ClosingPeriod |
KPIWeight |
PrevMember |
Cousin |
LastPeriods |
StrToMember(<String Expression>, CONSTRAINED) |
KPIGoal |
LinkMember |
|
[!REMARQUE]
StrToMember(<String Expression>, CONSTRAINED) obtient les meilleures performances possibles lorsque <String Expression> est une expression statique.
Fonctions de jeu
La liste suivante de fonctions de jeu inclut les fonctions sur lesquelles vous devriez constater de meilleures performances.
Aggregate |
Max |
Sum |
Avg |
Min |
|
Toutefois, lorsque vous utilisez l'une des fonctions répertoriées, le premier paramètre doit être une expression qui utilise une combinaison des fonctions suivantes.
- (opérateur except) |
.Children |
MTD |
(<set expression>,(<set expression>, …,(<set expression>) (opérateur cross join) |
.Members |
PeriodsToDate |
* (opérateur cross join) |
.Siblings |
QTD |
: (opérateur range) |
AddCalculatedMembers |
StrToSet(<String Expression>, CONSTRAINED) |
+ (opérateur union) |
Crossjoin(<set expression>,(<set expression>, …,(<set expression>) |
Tail |
|
Descendants |
Union |
|
Distinct |
Unorder |
|
Except |
WTD |
|
Hierarchize |
YTD |
|
Intersect |
|
[!REMARQUE]
Les jeux statiques, notamment le jeu vide, bénéficieront également des gains de performance attendus.
Fonctions VBA
La liste suivante de fonctions VBA inclut les fonctions sur lesquelles vous pouvez constater de meilleures performances.
Abs |
CLong |
NbCar |
CDate |
CChaîne |
Maintenant |
CDbl |
Int |
Droite |
CEnt |
Gauche |
Round |
Pour les fonctions VBA suivantes, vous pouvez vous attendre à obtenir de meilleures performances si la fonction est évaluée sur des attributs variables.
Asc |
Format |
Sgn |
AscW |
VC |
Sin |
Atn |
Hex |
AmorLin |
CBool |
Heure |
Espace |
COctet |
IntPer |
Racine |
CMonnaie |
Minuscule |
NumChaîne |
CDec |
Log |
CompChaîne |
Chr |
SupprGauche |
ConvChaîne |
ChrW |
Minute |
Chaîne |
Cos |
Mois |
SYD |
CSmpl |
NPM |
Tan |
CVar |
Oct |
Minuterie |
Date |
Partition |
SérieHeure |
AjDate |
VPM |
VHeure |
DiffDate |
PrincPer |
SupprEspace |
PartDate |
VA |
TypeName |
SérieDate |
RVBC |
Majuscule |
ValDate |
Taux |
Val |
Jour |
RBG |
JourSem |
DDB |
Aléat |
Année |
Exp |
RTrim |
|
Arrond |
Seconde |
|