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