Optimisation Many-to-Many SSAS

Agenda :

Tour d’horizon des axes d’optimisation des M2M

  • Partitionnement
  • Agrégation

Les matrices de compression en détails

  • Présentation du fonctionnement
  • Utilisation de BIDS Helper
  • Démonstrations

Rappel des M2M:

Exemple:  manymany

  • Des raisons d’achat et des achats
  • Des personnes et des comptes bancaires (une personne peut avoir plusieurs comptes bancaires, et un compte peut être associé à plusieurs personnes)

Gestions SSAS:

  • Ssas gère nativement le multidim, par contre ce n’est pas le cas pour le tabulaire

Un exemple d’un modèle de données :

Modele M2M

Ce qui nous intéresse c’est le rectangle en pointié.

C’est un forum où des utilisateurs sont inscrits et qui font des activités, l’activité de notre exemple est de faire des commentaires sur des articles, en fonction de leurs activités ils reçoivent des badges, par exemple poster un article sur sqlserver donne droit au badge sqlserver.

Où est la M2M ?: Comment on peut compter le nombre de commentaires en fonction des badges ?

Ce que l’on souhaite de faire :

Croiser badge et commentaire : Connaitre le score et nombre de commentaires par badge.

Gérer les relations : Faire attention à ne pas sommer n’importe comment.

Performance et optimisation : On souhaite que tout cela fonctionne avec un minimum de temps de réponse.

Les partitions:

Processing et requête plus rapides

  • Parallélisme
  • Scanne moins de données

Optimisation au niveau de SSAS:

Une partition est un découpage de beaucoup de groupes de mesures. Dans SSAS par défaut : un groupe de mesure = une partition.

Dans 90% des cas on découpe les partitions par année, pays ..

Le gain est au niveau de requêtage et processing, SSAS process les partitions en parallèle.

Le requêtage  est rapide car SSAS scanne moins de données.

Les partitions « Bonnes pratiques »:

Le fait de diviser les groupes de mesures cela nous donne plus de flexibilité.

Il faut pensez aux scripts XMLA pour créer les partitions, c’est pratique pour gagner du temps, en l’occurrence on peut générer le code xmla pour une partition et l’exécuter pour les autres en changeant les variables.

Définir une agrégation sur une partition :

Pour les très grandes volumétries, il vaut mieux une grande partition que plusieurs petites (le cas où les données d’une année représente une très grande volumétrie).

partitions

Les aggregations:

Un autre grand point sur lequel on peut faire des optimisations est les agrégations. En créant des pré-calculs sur les partitions, on demande à SSAS de préparer les résultats en avance, donc il commence à agréger les données en fonction de différents attributs.

Trois façons de faire :

La Méthode classique qui est l’assistant qui permet soit de sélectionner manuellement les attributs, soit semi-manuel c-à-d des fonctions qui appliquent des algorithmes sur les groupes de mesures et les différents axes pour choisir lui-même les meilleurs agrégations.

Et sinon on a l’optimisation parfaite qui est le « usage based optimization » qui est une optimisation automatique (après activation), ça historise toutes les requêtes jouées sur le cube et à partir de ça il crée des agrégation basé sur le comportement de ces requêtes (très performant mais demande beaucoup de ressources).

Les agrégations : Bonnes pratiques:

  • Méthode : faite du semi manuel Unrestricted fonctionne plutôt bien sur de faible volumétrie (avec l’option “gain de perf”).
  • SSDT : Gérer 20% à 30 % des agrégations possibles.
  • Pourquoi pas 100% ?: parce que si on demande à SSAS de faire 100% des agrégations, il va faire toutes les combinaison possible et donc le temps de process et l’espace disque vont exploser.

Cela marche plutôt bien sur des petites volumétries.

Récapitulatif:

recap

Quand on fait une requête sur un cube via excel par exemple, c’est du code mdx qui est balancé au niveau du cube, et là on a 3 niveaux d’interprétations:

Soit la requête a déjà été exécutée dans le cache donc le résultat est dans le cache, ça renvoi la réponse directement, sinon ça va chercher le résultat dans les agrégations, et si ces derniers ne suffisent pas il va taper dans les partitions.

Il existe une autre option que l’on appelle le cache warming, c’est préparer le cache avec les requêtes de type “prepare cache” .

Les Matrices de compression : la solution lorsque le partitionnement et les agrégations ne suffisent plus ?

aproche sql

Dans ce modèle :

  • Un utilisateur a un ou plusieurs badges, un badge est attaché à un ou plusieurs utilisateurs.
  • Un utilisateur poste un ou plusieurs commentaires, un commentaire appartient à un utilisateur.

Ce que l’on souhaite calculer :

  • Sommer les scores des commentaires qui ont un lien avec le badge.

Résultat :

  • Le score ici est 9 par badge.

Illustration :
Afin de calculer le nombre de commentaires par badge, on a utilisé les relations entre les 2 tables de faits «FactComment » et « FactBadge ». Cela a renvoyé 90 lignes.

On a ici un problème de volumétrie, par exemple pour un utilisateur qui a posté un commentaire et qui a 90 badges, la requête renvoi 90 lignes. Alors que la seule chose qui nous intéresse est que cet utilisateur a posté un commentaire et que ce commentaire à un score de 9.

Si on concatène les « BadgeId » et on les met en une seul ligne, on compresse 90 fois la table.

matrice de compression

L’idée est de revoir ce modèle pour passer par une autre table de Fait que l’on va créer « FactBadge – matrix » qui va être une table de fait modifiée avec d’une part la concaténation de l’ensemble de mes badges et d’autre part une clé qui sera une référence vers une autre table de Dimension que l’on va créer également « DimBadge – matrix ».

Un processus en 4 étapes :

  1. Estimer les gains
  2. Modification du DataWarehouse (Ajout de 2 tables, une fact et une dimension)
  3. Modification du Cube
  4. Implémentation dans l’ETL

estimation gain

Estimation des gains avec bids helper, on click sur le petit bouton (qui apparait en haut) , et il va scanner toutes les many-to-many, et puis nous donner la taille originale de la table de fait , la taille compressée et la taille de la Dim matrix qui va être à créer, et il nous calcule la réduction en pourcentage (ici 42%).

Ou bien avec du code !! Mais l’assistant est plus simple quand même 😉

modification du dataware

On a rajouté du temps d’alimentation:

  • Alimenter les 2 tables qu’on vient de créer
  • Faire un update sur la FactComment qui peut être couteux vu la volumétrie.

modification du dataware 2

Voilà un exemple d’illustration:

Dans la « FactBadge » on a des user qui sont attachés à des badges, le user 174601 a 3 badges, et le user 480508 aussi. On remarque que les 2 user ont le même ensemble de badge, donc ce qu’on va faire c’est concaténer l’ensemble de ces 3 badge et les mettre dans la table « DimBadgeMatrix »  avec comme clé ‘’BadgeMatrixId = 1’’.

On génère la 1ere table de la manière suivante :
À partir de cette table « DimBadgeMatrix »  on remplis la table de fait « FactBadgeMatrix », ça va nous permettre de garder le lien entre la table  « FactBadge » et « FactBadgeMatrix »

Enfin on met dans la table « FactComment » un lien directe vers la table de fait.

La taille de la colonne ‘’BadgeMatrixKey’’ va devenir un problème à gérer.

Il n’est pas envisageable de passer ‘’BadgeMatrixKey’’ en VARBINARY(MAX) or nous allons très vite dépasser les 8000 caractères maximum d’un VARCHAR(max).

Solution : Les fonctions de hachage sont là pour nous aider.

On nomme fonction de hachage une fonction particulière qui, à partir d’une donnée fournie en entrée, calcule une empreinte servant à identifier rapidement, bien qu’incomplètement, la donnée initiale. Les fonctions de hachage sont utilisées en informatique et en cryptographie.

A noter : il faut bien choisir sa fonction de hachage la plus adapté au cas afin d’éviter tout risque de tomber en collision.

modif cube

Dans SSAS, la nouvelle table de Fait « FactBadge-Matrix » est en Relation de type ‘’Regular’’ avec les dimensions « DimBadge » & « DimBadge Matrix ».

L’autre table de fait « Fact Comment » quant à elle est liée à la dimension « DimBadge » avec une Relation de type Many-to-Many en passant par la table de fait « FactBadge-Matrix ».

Modification ETL (4/4)

L’ETL doit :

  • Alimenter DimBadgeMatrix
  • Alimenter FactBadgeMatrix
  • Mettre à jour la table FactComment
  • Si votre liste d’identifiants dépasse 8000 caractères, l’ETL doit gérer le Hachage de la colonne.

Conclusion :

Conclusion

On remarque sur le graphique ci-dessus que la méthode ‘’M2M Matrix+Aggreg’’ est la plus rapide en termes de temps de requêtage. En revanche, le temps d’alimentation dans l’ETL est considérablement plus long par rapport à la méthode ‘’M2M+Aggreg’’. Le temps du process cube quant à lui est quasiment le même.

 

 

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s