Le Post Infeeny

Les articles des consultants et experts Infeeny

Indexation DataWarehouse

L’indexation d’un entrepôt de données est délicate :

  • si vous avez trop peu d’indexes les temps de chargement de votre entrepôt seront optimum mais les temps de requêtage seront déplorables
  • au contraire, si vous avez trop d’indexes, les temps de chargement vont exploser mais les performances de requêtage seront excellentes.

Afin de prévoir un plan d’indexation optimum vous devez prendre en compte certains critères :

  • le type d’entrepôt (archive vs temps (quasi) réel)
  • la taille des dimensions et des tables de faits
  • le nombre d’utilisateurs ayant accès à l’entrepôt (le nombre d’accès concurrents max à gérer)
  • le type d’accès aux données (ad-hoc ou via des interfaces d’applications structurées)
  • le mode d’alimentation (upsert, annule/remplace …)
  • etc …

Voici néanmoins quelques règles basiques à respecter lors du design de votre entrepôt.

Indexation des dimensions

Sur toutes vos dimensions, utilisez une clé auto-incrémentée de type [int] ou [bigint] (en fonction de la taille estimée de vos dimensions) comme clé primaire non-cluster (contrairement au comportement par défaut du designer). Que les données soit ordonnées physiquement selon la clé de substitution n’a aucun intérêt (exception faite de la dimension Temps dont la clé technique ne doit pas être auto-incrémentée mais devant être la représentation de la date au format AAAAMMJJ stocké dans un entier).

Chaque dimension doit contenir une clé naturelle ou transactionnelle aussi appelée clé métier (Business Key). Bien que cette clé peut ne pas être unique, comme dans le cas d’une dimension à variation lente de type 2, créer un index cluster sur la clé métier permet d’optimiser les temps de requête surtout lorsque cette clé est utilisée dans la clause WHERE. Ce qui est le cas dans 99% des requêtes analytiques avec recherche dimensionnelle. Notez qu’il doit s’agir d’un index unique dans le cas des dimensions en SCD de type 0 ou 1.

Dans le cas des dimensions à variation lentes de forte volumétrie  en SCD de type 2, ajoutez un index non-cluster sur la clé métier et la date de début et étendez la couverture de l’index à la date de fin et la clé de substitution. Exemple :


CREATE INDEX IDX_DimensionSCD2_CoveringIndex NONCLUSTER
 ON (BusinessKEY, StartDate) INCLUDE (EndDate, SurrogateKEY);

Si d’autres colonnes de la dimension sont utilisées en permanence pour la recherche, le tri ou le regroupement, créez des index non-cluster sur les colonnes comme vous le feriez dans une base de données transactionnelle.

Si la dimension contient une hiérarchie intégrée, envisagez d’indexer les colonnes intervenant dans la hiérarchie. Cela permettra d’améliorer les performances des requêtes sans trop alourdir les temps de chargement.

Indexation des tables de faits

Parce que toutes les analyses sont Time dépendantes, il est primordial de créer un index cluster sur la clé temporelle. Par ailleurs, vous serez à même d’utiliser cette clé cluster comme clé de partitionnement si la table de faits est volumineuse. Cela facilite et optimise grandement les requêtes sur des plages de dates spécifiques, et permet d’améliorer les temps de traitement des cubes d’Analysis Services si vous avez des groupes de mesures partitionnés.

Toutefois, si la table de faits contient plusieurs clés temporelles, définissez l’index cluster sur la clé la plus utilisée (date d’analyse par défaut)

Créez, ensuite, un index non-cluster sur chacune des clés étrangères de la table de faits, et envisagez de combiner la clé étrangère et la clé temporelle (dans cet ordre uniquement). Par exemple : ProductKey et DateKey. La création d’une clé non-cluster sur les clés étrangères fonctionne particulièrement bien si l’une ou plusieurs des dimensions associées est une dimension à variation lente de type 2.

Notez par ailleurs qu’il est préférable de conserver l’intégrité référentielle et donc de définir les foreign key sur les tables de faits. Je rappelle que l’optimiseur SQL Server utilise les contraintes d’intégrité référentielles lorsqu’elles sont Trusted pour optimiser le plan d’exécution.

Modification du schéma d’indexation

Au fil du temps, votre entrepôt de données va évoluer en fonction des besoins d’analyse et vous devrez modifier votre schéma d’indexation.

Si votre entrepôt de données relationnelles est utilisé uniquement pour monter des cubes SSAS en MOLAP, il se peut que vous n’ayez pas besoin d’indexer votre entrepôt comme indiquer auparavant; car Analysis Services va toujours exécuter les mêmes requêtes pour traiter les données. Par conséquent vous pouvez  exécuter Le Database Engine Tuning Advisor pour optimiser précisément vos indexes afin d’améliorer les temps de traitement de vos cubes.

Notez tout de même que même si vous n’avez que des cubes SSAS en frontal, les utilisateurs demanderont toujours des analyses au niveau feuille (le niveau le plus fin), et qu’il sera parfois plus performant de requêter directement l’entrepôt plutôt que d’interroger le cube.

Et n’oubliez surtout pas d’établir un plan de maintenance de vos index. Créer des indexes ne suffit pas, il faut aussi les maintenir, je rappelle au passage quelques règles de maintenance des indexes :

  • si le taux de fragmentation est >5% et <30% : reorganizer l’index
ALTER INDEX MyIndex ON MyTable REORGANIZE
  • si le taux de fragmentation est >= 30% : reconstruire l’index
ALTER INDEX MyIndex ON MyTable REBUILD

Quelques petits liens utiles :

Pour la maintenance de vos indexes, le mieux reste d’intégrer ces opérations dans vos flux ETL car ils sont inhérents au chargement de vos données. Par exemple, si vous avez à faire des traitements en DELETE/INSERT, il est intéressant de profiter des indexes pour effectuer les suppressions, puis de les désactiver voir supprimer avant toute insertion en masse et enfin de les réactiver (réorganiser ou reconstruire en fonction du taux de fragmentation) ou bien de le recréer.

Conclusion

Commencez simple, et évaluez/révisez soigneusement chaque construction d’index, en prenant en compte :

  • Les temps de chargement de l’entrepôt via l’ETL
  • La maintenance de votre entrepôt
  • Les temps de requêtage pour les utilisateurs et/ou rapports analytiques

4 réponses à “Indexation DataWarehouse

  1. Frédéric BO 13 mars 2013 à 14 02 33 03333

    Bravo pour ton article.

    J’ai quelques requêtes permettant d’aider un peu sur la gestion des index sur une base :

    – Liste de tous les index sur une base
    – Les index en doublons ou inclus
    – Liste des index non utilisés sur la base
    – L’utilisation des différents index sur la base

  2. Ludo 14 mars 2013 à 13 01 30 03303

    Bon article Fred. Concernant le choix de reorganiser ou reconstruire l’index comme tu précise, voila une procedure permettant de faire le choix suivant des parametres défini (% frag., FillFactor ..) que tu peux trouver sur codeplex:
    http://olahallengren.codeplex.com/

    • fbrossard 14 mars 2013 à 14 02 37 03373

      Merci Ludo pour la référence bien pratique permettant de mettre rapidement et à moindre coût un plan de maintenance des index.
      En règle générale, j’ai plutôt tendance à gèrer les index dans les lots SSIS, de façon à les utiliser tant que j’en ai besoin, de les supprimer ou desactiver avant les bulk insert, et de les reconstruire/reorganiser ensuite. Cela me permet de maitriser les temps de chargements de l’entrepôt et d’avoir une indexation optimum.
      Le seul problème avec un plan de maintenance planifié ce sont les temps de (re)traitement des index sur les tables de faits. Cela peut rapidement devenir critique sur les tables de faits bien volumineuses, alors que si ces dernières sont correctement partitionnées et alimentées en partition switching via l’ETL, on peut aisément maintenir les index avec 0 fragmentation sans pour autant pénaliser les temps de chargement de l’entrepôt.

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

%d blogueurs aiment cette page :