[SSAS] Cubes OLAP : généralités sur les agrégations

Ce billet présente les agrégations au sein d’un cube OLAP.

Pour en savoir plus sur comment concevoir un cube SSAS, vous pouvez aller ici. Vous pouvez également aller ici, pour connaître quelques concepts associés à l’univers OLAP/SSAS.

 

Qu’est-ce qu’une agrégation ?

Par analogie à T-SQL, les agrégations peuvent être vues comme des valeurs calculées par GROUP BY avec quelques attributs de table(s). Il s’agit, en fait, de structure de données pré-calculées qui peuvent aussi être vues comme un sous-ensemble d’une partition de mesures.

La création d’agrégations au sein d’un cube contribue à l’amélioration des performances des requêtes, grâce au pré-calcul des mesures. En revanche, comme tout objet qui se respecte, les agrégations possèdent également des inconvénients. Elles prennent non seulement de la volumétrie, mais peuvent potentiellement prolonger les temps de traitement d’un cube. D’où l’importance de ne pas créer un nombre illimité d’agrégations.

Il existe 2 types d’agrégations :

  • Rigide : il s’agit d’agrégations ne nécessitant pas de recalcul, en cas de mise-à-jour des attributs des dimensions, lors du traitement incrémental du cube.
  • Flexible : il s’agit d’agrégations recalculées à chaque mise-à-jour des attributs des dimensions, lors d’un traitement incrémental du cube.

A noter que les agrégations sont limitées à une seule partition – étant donné qu’il s’agit de structure de données stockées sur disque -, mais peuvent être partagées par différentes partitions.

 

Création et utilisation d’agrégations

Dans nos différents exemples, nous reprendrons le cube créé dans cet article. Et on supposera que l’on souhaite créer une agrégation au sein du groupe de mesures Fact Internet Sales.

    Avant de commencer : activation de l’archivage de requêtes OLAP via le QueryLog

Avant de commencer, et afin de montrer tout l’intérêt des agrégations, activons l’archivage d’informations associées aux traitements OLAP. Pour ce faire :

  • Dans l’explorateur d’objets de SSMS, après s’être connecté à l’instance SSAS, allez dans les propriétés de cette dernière :


  • Activez l’utilisation de la table d’archivage d’informations (QueryLog) :

Dans l’onglet General, changez les propriétés suivantes :

  • Paramétrez à true, l’option Log\QueryLog\QueryLogTable, afin d’activer la création et utilisation de la table d’archivage.
  • Spécifiez la chaîne de connexion de la base de données au sein de laquelle la table d’archivage sera stockée : Log\QueryLog\QueryLogConnectionString. Dans notre exemple, il s’agit de la base de données AdventureWorksDW, située au sein d’une instance SQL Server locale :

  • Paramétrez éventuellement l’option Log\QueryLog\QueryLogSampling. Par défaut, elle est à 10, ce qui signifie que jusqu’à 10 requêtes sur le cube sont archivées. Dans notre exemple, elle est paramétrée à 1, afin d’archiver toutes les requêtes lancées sur le cube.
  • Spécifiez un nom à la table, si vous le souhaitez. Par défaut, la table s’appellera OlapQueryLog, dont le nom sera laissé inchangé dans notre exemple.

Voici un aperçu du paramétrage de l’ensemble des options importantes :


La requête ci-dessous permet de vérifier si la table d’archivage a été correctement créée (assurez-vous que le compte d’exécution de l’instance SSAS puisse accéder et écrire dans la base de données ciblée pour l’archivage) ou son contenu :

USE AdventureWorksDW
GO
SELECT
*
FROM dbo.OlapQueryLog
GO

Le SQL Server Profiler peut également être utilisé pour auditer toute activité au sein de l’instance SSAS, y compris les requêtes MDX lancées.

    Modus operandi de création d’agrégations via l’assistant

La conception d’une agrégation est élémentaire. Au sein de la solution SSDT :

  • Ouvrez le cube, dans l’explorateur de la solution :


  • Allez dans l’onglet Aggregations (ou Agrégations, en version française) :


  • Double-cliquez sur Unsassigned Aggregation Design, juste en-dessous du groupe de mesures au sein duquel l’on souhaite créer des agrégations (ici, Fact Internet Sales), et sélectionnez Design Aggregations… :


Cela aura pour effet d’ouvrir l’assistant de création d’agrégations :


Cliquez sur Next >.

  • Spécifiez le mode d’usage des agrégations pour chaque attribut.

Dans cette étape, il est possible d’exclure des attributs des agrégations à créer, surtout si on les estime inutiles. Ou sélectionner leur usage, qui est constitué de 4 types :

  • Default : usage par défaut. Le moteur SSAS appliquera une règle interne par défaut basée sur le type d’attribut et les relations associées à sa dimension, et tentera de déterminer s’il doit ou non l’inclure dans une agrégation donnée.
  • Full : usage complet. Le moteur SSAS incluera l’attribut dans toutes les agrégations créées.
  • None : aucun usage. Le moteur SSAS n’incluera pas l’attribut dans une agrégation.
  • Unrestricted : usage sans restriction. Le moteur SSAS traitera l’attribut indépendamment des règles internes.


Utilisez le mode :
  • Full, si un attribut est utilisé dans un très grand nombre de requêtes. Et surtout si ledit attribut ne possède pas trop de membres (le cas échéant, l’assistant prendra beaucoup de temps à concevoir les agrégations associées).
  • Unrestricted, si attributs sont fréquemment utilisés. Il est recommandé de n’affecter qu’entre 5 à 10 unrestricted attributes, tout au plus, par dimension.

 

Dans notre exemple, le mode Default est laissé pour tous les attributs. Cliquez sur Next >.

  • Spécifiez le nombre de valeurs pour chaque objet (dimensions et mesures) à agréger :

Cette étape est importante, car elle permet à l’assistant de determiner l’espace de stockage requis pour les agrégations.

Il existe 2 possibilités de comptage :

  • Laisser l’assistant determiner le nombre de valeurs (clic sur le bouton Count). Cette option est utile pour déterminer le nombre exact de valeurs.
  • Entrer le nombre de valeurs manuellement. Cette option est utile s’il y a un trop grand nombre de lignes à compter, et que le comptage est susceptible de perdurer avec l’assistant.

Dans notre cas, le comptage automatique a été choisi :


Cliquez sur Next>.

  • Spécifiez les options d’agrégations :


Cette étape permet de concevoir les agrégations en tenant compte des performances et/ou de stockage. En règle général, l’idée est de trouver un juste équilibre entre ces deux notions, sachant qu’il existe 3 approches :

  • Estimate storage reaches (par défaut, à 100 Mo) : cette approche permet au moteur SSAS de déterminer quelles agrégations stocker en fonction de la volumétrie maximale allouée. Elle est plus adaptée pour les cas d’espace(s) de stockage limité(s), et le paramètre peut être modifié (aussi bien Mo qu’en Go).
  • Performance gain reaches (par défaut, à 30%) : cette approche permet au moteur SSAS de partir du principe (via un algorithme interne basé sur le calcul de la formule suivante : (TempsRequêteMaximal – TempsRequêteIdéal)x100/(TempsRequêteMaximal-TempsRequêteMinimal)) qu’au-delà de 30%, il n’y pas de gain significatif en matière de performances. Le pourcentage peut être augmenté ou réduit, mais celui par défaut (30%) est un juste milieu par rapport à la volumétrie, notamment.
  • I click Stop : cette approche permet de déterminer le meilleur équilibre entre les performances et le stockage (en se basant sur le graphe).
  • Do not design agregation (0%) : cette approche (qui n’en est, certes, pas une…) permet de pas créer d’agrégation.

Le choix s’est porté sur la performance. Il n’y a plus qu’à cliquer sur Start :


Avec un niveau d’optimisation de l’ordre de 30%, 5 agrégations ont donc été créées. Cliquez sur Next>.

  • Déployez et traitez le cube avec les agrégations nouvellement crées :


Il est possible de juste sauvegarder les agrégations créées pour ne déployer le cube avec elles que plus tard.

Dans notre exemple, outre le déploiement et le traitement choisis, un nom (arbitraire) a été affecté à l’ensemble des agrégations créées au sein du groupe de mesures Fact Internet Sales : FactInternetSalesAggregations.

Cliquez sur Finish pour terminer le travail, et déployer/traiter le cube par la suite.

Dans l’onglet Aggregations, nous pouvons apercevoir le résultat de notre travail :


Il est possible d’avoir une vue détaillée des agrégations crées par attributs du groupe de mesure, dont voici un aperçu, par exemple (en cliquant sur l’icône encadrée en rouge) :


 

Modus operandi de création d’agrégations via l’optimisation basée sur l’usage

Avec cette approche de conception d’agrégations, il est possible de compléter éventuellement le travail de l’assistant en agrégeant les attributs manquants (en se basant sur les informations archivées dans le QueryLog concernant les requêtes OLAP). En effet, et la dernière figure de la vue avancée de la sous-section précédente le montre, l’assistant n’effectue pas toujours un travail d’agrégation adéquat.

De plus, en cas de présence d’autres groupes de mesures (dans notre exemple, un seul est utilisé), l’optimisation basée sur l’usage peut être utile dans la mesure où l’assistant part du principe que toutes les requêtes fonctionnent de la même manière, ce qui l’amène, là aussi, à ne pas toujours agréger des attributs devant l’être.

Pour lancer la création d’agrégations basées sur l’usage (également possible via SQL Server Management Studio), il suffit de faire un clic-droit sur Unassigned Aggregation Design (l’agrégation existante) du groupe de mesures ciblé, puis sélectionner Usage Based Optimization…


Cela aura pour effet d’ouvrir une fenêtre de présentation :


L’étape suivante concerne la spécification de critères d’analyse des requêtes OLAP (date de début, date de fin, utilisateurs les ayant exécutés, requêtes les plus fréquemment lancées…). Il s’agit de requêtes dont les informations d’exécution ont été notamment stockées au sein du QueryLog durant leur lancement :


Il est possible de ne rien filtrer, et d’analyser toutes les requêtes sans exception.

Les autres étapes suivent la même logique que celle qui a permis de créer les agrégations via l’assistant, dans la sous-section précédente, avec cette fois-ci la prise en compte spécifique de l’usage des attributs et mesures en fonction des requêtes archivées dans le QueryLog.

 

Pour aller plus loin…

Vous pouvez garder un œil ici, pour tout sujet relatif à SSAS.

Publicités

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