Posté par Maxime Barsamian le 18 Février 2021

L’une des principales raisons de créer des cubes Analysis Services dans le cadre d’une solution de BI est de pouvoir obtenir des requêtes plus performantes qu’en interrogeant directement la base de données relationnelle. Bien qu’Analysis Services soit très rapide, il serait naïf de penser que toutes les requêtes, aussi complexes soient-elles, s’effectueront en quelques secondes sans qu’aucun réglage ne soit nécessaire. L’optimisation des performances du moteur de stockage est relativement simple. Il est possible de diagnostiquer facilement les problèmes de performances afin de pouvoir résoudre ces problèmes et revoir la modélisation du cube, et ce grâce à deux options : le partitionnement et l’agrégation. L’optimisation des performances du Formula Engine est beaucoup plus compliquée car elle nécessite une connaissance du MDX. Le diagnostic des problèmes de performances est difficile car le fonctionnement interne du Formula Engine est compliqué à suivre. La résolution du problème dépend de l’utilisation de certaines fonctions qui peuvent tout changer dans la rapidité d’exécution de la requête MDX.

Dans cet article, je vais dans un premier temps présenter les différentes méthodes pour diagnostiquer les performances de calcul MDX, puis dans un second temps nous verrons un cas concret et analyserons les avantages et les inconvénients afin de comprendre certaines fonctions.

Partie 1 : Réglage de la performance des requêtes dans SSAS

Comment Analysis Services traite les requêtes

Les deux principales parties du moteur Analysis Services sont :

Le moteur de formule : il traite les requêtes MDX, détermine les données nécessaires pour y répondre, demande ces données au moteur de stockage, puis effectue tous les calculs nécessaires à la requête.
Le moteur de stockage : il gère toutes les lectures et écritures de données : il récupère les données demandées par le moteur de formule lorsqu’une requête est exécutée et les agrège avec la granularité requise.

Lorsqu’une requête MDX est exécutée, cette requête est d’abord envoyée au moteur de formule où elle est analysée : le moteur de formule demande ensuite toutes les données nécessaires pour répondre à la requête du moteur de stockage, effectue tous les calculs nécessaires sur ces données, puis renvoie les résultats dans un ensemble de cellules à l’utilisateur. Il existe plusieurs sortes de réglages des performances à toutes les étapes de ce processus.

Les événements détaillés du sous-cube de requête représentent des demandes individuelles de données du moteur de formule vers le moteur de stockage, auxquelles il est possible de répondre à partir du cache, d’une agrégation ou de données de partition de base. Chacune de ces demandes a une granularité unique, ce qui signifie que toutes les données de la demande proviennent d’une seule combinaison d’attributs ; ces granularités sont appelées des « sous-cubes ».

Diagnostiquer les problèmes de performances de Formula Engine

Si une requête est peu performante et si l’on peut exclure le moteur de stockage comme cause, le problème vient du moteur de formule. Le Profiler permet de vérifier les performances avec des événements tel que « Query Subcube », pour voir quelles partitions sont touchées et pour vérifier si des agrégations sont utilisées. Si l’on soustrait la somme des durées de tous les événements du sous-cube de requête de la durée de la requête dans son ensemble, alors on obtiendra le temps passé dans le moteur de formule.

L’étape suivante consiste à essayer d’ajuster la requête. Dans certains cas, on pourra obtenir des gains de performances importants (parfois de plusieurs centaines de pourcents) simplement en réécrivant une requête et les calculs dont elle dépend. Le problème est de savoir comment réécrire le MDX et de déterminer quels calculs contribuent le plus à la durée globale de la requête. Malheureusement, Analysis Services ne donne pas beaucoup d’informations à utiliser pour résoudre ce problème et il existe très peu d’outils qui peuvent aider.

Il existe trois façons principales d’améliorer les performances du moteur de formule :

Modifier la structure du cube sur lequel il s’exécute
Modifier les algorithmes utilisés dans le MDX
Le contexte de mise en cache

Modification de la structure

Les performances d’une requête sont intimement liées à la conception du cube sur lequel elle s’exécute. Par exemple, la conception de dimension, en particulier l’optimisation des relations d’attributs, peut avoir un effet significatif sur les performances de toutes les requêtes, au moins autant que l’une des optimisations décrites dans cet article. Par conséquent, si une requête est peu performante, la première chose à faire est de revoir la conception du cube pour voir si certaines choses peuvent être faites différemment. Il est possible de faire un compromis entre l’utilisabilité, le temps de développement, « l’élégance » globale de la conception et les performances des requêtes, mais comme les performances des requêtes sont généralement la considération la plus importante pour les utilisateurs, elles auront la priorité.

Algorithmes de réglage utilisés dans MDX

Le réglage d’un algorithme dans MDX se fait de manière très traditionnelle : il s’agit de comprendre votre problème et d’élaborer la logique qui fournit la solution la plus efficace. Il est possible d’écrire la même requête ou le même calcul en utilisant le même algorithme mais en utilisant différentes fonctions MDX et voir une grande différence de performances. Par conséquent, il faut savoir quelles sont les fonctions à utiliser et celles à éviter. Voici quelques recommandations pour améliorer les performances MDX :

Utilisation des ensembles nommés « Named set » pour éviter de recalculer les expressions d’ensemble

Utilisation des membres calculés pour mettre en cache des valeurs numériques

Ne jamais utiliser de fonctions de liaison tardive telles que LookupCube , ou StrToMember ou StrToSet sans l’ indicateur Constrained dans les calculs, car elles ont un impact très important sur les performances. Il est presque toujours possible de réécrire les calculs afin qu’ils n’aient pas besoin d’être utilisés. En fait, la seule utilisation valide pour StrToMember ou StrToSet dans le code de production est lors de l’utilisation de paramètres MDX
Utiliser la fonction NonEmpty autant que possible. Cela peut être beaucoup plus efficace que d’utiliser la fonction Filter() ou d’autres méthodes. Ne jamais utiliser NonEmptyCrossjoin non plus :elle est obsolète et à remplacer par NonEmpty, plus fiable et plus facile d’utilisation
Essayer d’écrire dans un premier temps le code MDX le plus simple possible, et ne le modifier que lorsque les performances ne sont pas acceptables

Mise en cache

L’utilisation du cache est un facteur très important dans les performances globales d’un cube. Bien qu’Analysis Services puisse partager le contenu du cache entre les utilisateurs, il existe plusieurs situations dans lesquelles il ne peut le faire. Il est donc important de comprendre les différents contextes de cache et les éléments qui empêche son utilisation optimale.

Étendues du cache de formule

Il existe trois contextes de cache différents dans le moteur de formule, qui concernent la durée de stockage des données dans le cache et la manière dont ces données peuvent être partagées entre les utilisateurs :

Contexte de requête : Les résultats des calculs ne peuvent être mis en cache que pendant la durée de vie d’une seule requête et ne peuvent donc pas être réutilisés par des requêtes ultérieures ou par d’autres utilisateurs.
Contexte de session : Les résultats des calculs sont mis en cache pendant la durée de vie d’une session et peuvent être réutilisés par des requêtes ultérieures dans la même session par le même utilisateur.
Contexte global : Les résultats des calculs sont mis en cache jusqu’à ce que le cache doive être supprimé car les données du cube ont changé (généralement lorsqu’une forme de traitement a lieu sur le serveur). Ces valeurs mises en cache peuvent être réutilisées par des requêtes ultérieures exécutées par d’autres utilisateurs ainsi que par l’utilisateur qui a exécuté la requête d’origine.

Il est clair que le contexte global est le meilleur du point de vue des performances, suivi du contexte de session puis du contexte de requête. Analysis Services essaiera toujours d’utiliser le contexte global dans la mesure du possible. Cependant certaines requêtes ou calculs forcent l’utilisation du contexte de session ou du contexte de requête. Voici une liste des situations les plus courantes :

Si on définit des calculs (sans inclure les ensembles nommés) dans la clause WITH d’une requête, même si on ne les utilise pas, Analysis Services ne peut utiliser que le contexte de requête
Si on définit des calculs à l’échelle de la session mais qu’on ne définit pas de calculs dans la clause WITH, le contexte de session doit être utilisé.
L’utilisation d’une sous-sélection dans une requête forcera l’utilisation du contexte de requête
L’utilisation de l’instruction CREATE SUBCUBE forcera l’utilisation du contexte de session
Lorsqu’un utilisateur se connecte à un cube à l’aide d’un rôle qui utilise la sécurité de cellule, le contexte de requête est utilisé.
Lorsque des calculs qui contiennent des fonctions non déterministes sont utilisés (fonctions qui pourraient renvoyer des résultats différents à chaque fois qu’elles sont appelées), par exemple la fonction Now () qui renvoie la date et l’heure du système, la fonction Username () ou toute procédure stockée Analysis Services, alors cela force l’utilisation du contexte de requête.

Partie 2 : Cas concret

Lors de la création d’une base de données multidimensionnelle (SSAS), il faut parfois utiliser la mesure de comptage distincte. Par exemple pour connaître le nombre d’utilisateurs uniques qui achètent des produits. Dans le cas où un client achète de nombreux produits, la mesure de comptage distincte est très utile. De plus, il arrive souvent que le calcul des valeurs distinctes doit être dans un périmètre restreint. Ces mesures contiennent des informations très utiles pour nos rapports, mais malheureusement c’est une ressource coûteuse et si la quantité de données à traiter se compte en dizaine de Go ou de To, l’exécution des requêtes peut prendre beaucoup de temps.

Considérons le modèle en étoile suivant :

Ce modèle est constitué d’une table de fait contenant toutes les ventes sur les 10 dernières années ainsi que de 5 dimensions :

Dim Vente : 30 millions de ligne
Dim Produit : 3000 lignes
Dim Client : 1000 lignes
Dim Date : 3650 lignes
Dim Organisme : 100 lignes
Fait Vente : 400 millions de ligne

Dans notre exemple, nous allons calculer le nombre de ventes annulées par les clients sur un périmètre restreint (Produit informatique et livres, Région Île de France, Annee 2019). Pour cela, nous utiliserons la fonction Filter afin de filtrer les ventes annulées via l’indicateur EstAnnule dans la table de fait. Cet indicateur contient 2 valeurs : 0 pour faux et 1 pour vrai

Formule 1 :

Temps d’exécution : 2 minutes 51 sec

La requête a mis presque 3 minutes pour s’exécuter car la dimension vente contient plus de 30 millions de lignes, et le produit croisé de cette dimension avec d’autres dimensions génère plusieurs centaines de millions de combinaisons. De plus l’espace cubique est clairsemé, par conséquent bon nombre de ces combinaisons sont vides. L’opération filter() n’est pas optimisée pour fonctionner en mode bloc, ce qui signifie que beaucoup de calculs devront être effectués par le moteur pour évaluer l’ensemble des lignes, que les combinaisons soient vides ou non. En effet la fonction Filter() doit parcourir l’ensemble complet de données dans chaque cellule afin d’isoler une seule cellule. Pour cette raison, la fonction Filter() peut être lente lors de l’utilisation de grandes dimensions ou de résultats de jointure croisée avec une dimension de taille moyenne ou petite.

Afin de remédier à ce problème, nous allons ajouter des fonctions d’optimisation qui vont éliminer ces ensembles vides

Formule 2 : Ajout des fonctions NonEmpty() et Existing()

La fonction NonEmpty()retourne l’ensemble des tuples qui ne sont pas vides d’un jeu spécifié sur la base du produit croisé du jeu spécifié avec un deuxième jeu. Il réduit les ensembles vides de manière efficace et rapide.
La fonction existing() force le jeu spécifié à être évalué dans le contexte actuel (le slicer : where ou sous cube s’il y a plusieurs membres)

Il existe certaines règles sur l’emplacement de NonEmpty() dans les calculs effectués par la composition des fonctions MDX. Si nous essayons de détecter la multi-sélection, c’est à dire plusieurs membres dans le slicer, NonEmpty() doit entrer avec la fonction EXISTING à l’extérieur. La raison est qu’ils réduisent tous les deux efficacement les ensembles. NonEmpty() fonctionne très bien si l’ensemble est intact. Existing n’est pas affecté par l’ordre des membres. Par conséquent NonEmpty() doit être appliqué plus tôt.

Indépendamment des avantages qui ont été montrés ici, NonEmpty() doit être utilisé avec prudence. Voici quelques bonnes pratiques concernant cette fonction :

L’utiliser avec des ensembles, tels que des ensembles nommés et des axes.
L’utiliser dans les fonctions qui ne sont pas optimisées pour fonctionner en mode bloc (ex Filter)
Éviter de l’utiliser dans des fonctions agrégées telles que Sum().
Éviter de l’utiliser dans d’autres fonctions MDX qui sont optimisées pour fonctionner en mode bloc car il les empêchera d’évaluer l’ensemble en mode bloc. La raison est que l’ensemble ne sera pas compact une fois qu’il aura passé la fonction NonEmpty(). La fonction le divisera en plusieurs morceaux non vides, et chacun de ces morceaux devra être évalué séparément. Cela augmentera inévitablement la durée de la requête.

NonEmpty() vs NON_EMPTY

La fonction NonEmpty () et le mot clé NON EMPTY réduisent les ensembles, mais ils le font d’une manière différente. Le mot clé NON EMPTY supprime les lignes vides, les colonnes ou les deux, selon l’axe sur lequel ce mot clé est utilisé dans la requête. Par conséquent, l’opérateur NON EMPTY essaie de pousser l’évaluation des cellules à un stade précoce lorsque cela est possible. De cette façon, l’ensemble sur axe est déjà réduit et le résultat final est plus rapide. L’astuce est que l’opérateur NON EMPTY utilise l’ensemble sur l’axe opposé, les colonnes, pour réduire l’ensemble sur les lignes. Par conséquent, on peut dire que NON EMPTY dépend fortement des membres sur les axes et de leurs valeurs dans les colonnes et les lignes. Contrairement à l’opérateur NON EMPTY trouvé uniquement sur les axes, la fonction NonEmpty () peut être utilisée n’importe où dans la requête. La fonction NonEmpty () supprime tous les membres de son premier ensemble où la valeur d’une ou plusieurs mesures du deuxième ensemble est vide. Si aucune mesure n’est spécifiée, la fonction est évaluée dans le contexte du membre actuel. En d’autres termes, la fonction NonEmpty () dépend fortement des membres du deuxième ensemble, du segment ou de la coordonnée actuelle en général.

Temps d’exécution : 39 secondes

La requête a mis 39 secondes à s’exécuter. Nous voyons au travers du profiler que la requête a tourné 99% dans le moteur de formule. La lenteur de cette requête est dû à l’utilisation Filter() qui est très consommatrice dans la requête

Formule 3 : La fonction iif

Il existe plusieurs moyens d’éviter d’utiliser la fonction Filter() qui peuvent améliorer les performances :

Quand il faut filtrer sur des valeurs non numériques (tuple, except(), exists(), …)
Quand il faut filtrer sur des valeurs numériques : IIF()

Pour cet exemple, il faut filtrer sur des valeurs numériques, puis compter les membres. Pour cela, on utilise des fonctions d’agrégation telles que SUM () et la fonction IIF () dans son expression. Voici la formule :

Temps d’exécution : 1 minutes 24 sec

Malheureusement dans ce cas, la requête a mis plus de temps que prévu (1mn 24 secondes) à s’exécuter. On voit au travers du profiler que la requête a tourné 99% dans le moteur de formule.

On voit ici, que cette solution n’est pas meilleure que la précédente (fonction Filter). En effet, la fonction IIF doit être exécutée pour chaque cellule ce qui est très coûteux.

Formule 4 : Remodélisation du cube

Comme on peut le constater, il est difficile d’optimiser les requêtes en manipulant les formules. Une autre approche peut sensiblement améliorer les performances, mais nécessite de revoir la conception du cube. Cette méthode peut-être envisager à condition que cela n’impacte pas le résultat de toutes les formules mais également sur le modèle actuel pour l’utilisateur final.

On va ajouter une table de fait qui contiendra uniquement la liste des ventes annulées par les clients. Cette nouvelle table sera nommée FaitVenteAnnule (cf image). Afin de calculer le nombre de ces ventes, on définira une nouvelle mesure sous la forme Count non empty.

On définit ensuite une relation many to many entre les dimensions et la nouvelle table de fait FaitVenteAnnule.

Voici la nouvelle formule à partir de cette nouvelle modélisation pour calculer les ventes annulées.

A la différence des formules précédentes, nous n’utilisons plus les fonctions MDX « Count » et « Filter » qui sont très consommatrices. Le comptage des ventes a été pré-agrégé dans le moteur de stockage.

Temps d’exécution : 1 seconde

Avec la nouvelle modélisation, la requête a mis uniquement 1 seconde à s’exécuter ! Au travers du profiler, nous voyons que la requête a durée 120 ms, dont :

118 ms (52+66) sur le moteur de stockage (Query subcube verbose NonCache ) : 100% du temps total
2 ms (120 – Somme durée moteur stockage) sur le moteur de formule (0-MDX Query) : 0% du temps total

Conclusion

Dans cet article, nous avons couvert les performances de calcul et la mise en cache MDX, ainsi que la façon d’écrire MDX pour garantir que le moteur de formule fonctionne aussi efficacement que possible. Nous avons également vu à quel point la manipulation des fonctions peuvent avoir un impact sur le temps d’exécution des requêtes. Et qu’une bonne modélisation du cube permet d’améliorer et de simplifier considérablement nos requêtes.