Partitionnement d`un data warehouse relationnel

Partitionnement d’un data warehouse relationnel
Les sections suivantes décrivent brièvement le concept de data warehouse relationnel, les avantages du
partitionnement de data warehouses relationnels, ainsi que les avantages de la migration vers le
partitionnement dans Microsoft SQL Server 2005.
À propos des data warehouses relationnels
Les data warehouses relationnels offrent une source de données et une infrastructure complètes pour le
développement de solutions d’analyse décisonnelle. En outre, les data warehouses relationnels sont
utilisés par les applications de création de rapports et les requêtes SQL ad hoc complexes.
Un data warehouse relationnel typique est constitué de tables de dimensions et de tables de faits. La
taille des tables de dimensions est généralement inférieure à celle des tables de faits ; les tables de
dimensions offrent des détails sur les attributs qui expliquent les faits. Article, magasin et heure sont des
exemples de dimensions. Les tables de faits représentent les enregistrements métier, tels que les
informations sur les ventes d’articles pour tous les magasins. Les tables de faits sont mises à jour
périodiquement avec les données collectées récemment.
Une implémentation réussie d’une solution de data warehouse relationnel implique une planification
soigneuse et à long terme. Voici quelques-uns des facteurs à prendre en considération lors du
développement d’un data warehouse relationnel :
•
Volume des données
•
Fenêtre de chargement des données
•
Fenêtre de maintenance des index
•
Caractéristiques de la charge de travail
•
Stratégie d’expiration des données
•
Stratégie d’archivage et de sauvegarde
•
Caractéristiques matérielles
Une étude détaillée de ces facteurs est fournie dans les sections suivantes de ce document.
Un data warehouse relationnel peut être implémenté à l’aide d’une approche par partition ou une
approche par table de faits monolithique (énorme). Le choix de conception entre ces deux approches
dépend en grande partie des facteurs détaillés plus haut dans ce document. Les data warehouses
relationnels peuvent tirer parti du partitionnement des données. La section suivante décrit les avantages
du partitionnement des data warehouses relationnels.
Avantages du partitionnement
À mesure que la taille des bases de données d’une organisation augmente, avec des volumes importants
de données, il est essentiel de maintenir la haute disponibilité tout en satisfaisant à la nécessité d'une
petite fenêtre de maintenance de la base de données. Cette exigence fait du partitionnement une
solution naturelle pour les bases de données très volumineuses. Le partitionnement répond à des
problèmes essentiels dans la prise en charge des tables très volumineuses en vous permettant de les
décomposer en partitions plus petites, ce qui facilite la gestion des facteurs tels que le chargement,
l’expiration et l’archivage des données. Microsoft SQL Server prend en charge le partitionnement des
données par l'intermédiaire de vues partitionnées dans SQL Server 7.0/2000, ainsi que la prise en charge
des tables partitionnées dans SQL Server 2005.
Partitionnement dans SQL Server 7.0/2000
SQL Server 7.0 a introduit la prise en charge du partitionnement par l'intermédiaire des vues
partitionnées. Dans SQL Server 2000, la fonctionnalité a été améliorée afin de prendre en charge les
vues partitionnées pouvant être mises à jour. Une vue partitionnée est particulièrement adaptée à un
data warehouse relationnel lorsque la table de faits peut être naturellement partitionnée ou divisée en
tables distinctes par plages de données. Les tables sous-jacentes de la vue partitionnée font l’objet d'une
UNION afin de présenter un ensemble de données unifié. Les vues partitionnées réduisent

considérablement la complexité des applications, car l’implémentation physique est déduite des
méthodes d’accès aux données par l’application.
Dans SQL Server 2000, les vues partitionnées peuvent être étendues afin d’inclure des vues
partitionnées distribuées, ce qui permet la fédération des bases de données sur plusieurs
serveurs/instances. L’étude des vues partitionnées distribuées sort du cadre de ce document. Pour une
étude détaillée, reportez-vous au document « Vues partitionnées distribuées » sur MSDN (Microsoft
Developer Network) à l’adresse http://www.microsoft.com/sql/evaluation/features/distpart.asp .
Partitionnement dans SQL Server 2005
Le partitionnement de tables et d’index dans SQL Server 2005 limite la complexité liée à la gestion de
bases de données très volumineuses par l'intermédiaire de vues partitionnées. SQL Server 2005 permet
le partitionnement horizontal par plage avec la ligne de données comme plus petite unité de
partitionnement. Les objets pouvant être partitionnés sont les suivants :
•
Tables de base
•
Index (en cluster ou non)
•
Vues indexées
Les partitions par plage sont des partitions de table définies par des plages personnalisables de données.
L’utilisateur définit la fonction de partition avec des valeurs limites, un schéma de partition avec mise en
correspondance des groupes de fichiers, ainsi que des tables mises en correspondance avec le schéma de
partition. Une fonction de partition détermine à quelle partition appartient une ligne particulière d’une
table ou d’un index. Chaque partition définie par une fonction de partition est mise en correspondance
avec un emplacement de stockage (groupe de fichiers) via un schéma de partition. Pour une étude
complète de l’implémentation du partitionnement avec SQL Server 2005, reportez-vous à l’article
« Tables et index partitionnés dans SQL Server 2005 » sur MSDN.
La section suivante explique les avantages du partitionnement dans SQL Server 2005 et offre une
stratégie pour la migration vers les tables partitionnées SQL Server 2005.
Avantages du partitionnement dans SQL Server 2005
Le partitionnement de tables et d’index dans SQL Server 2005 facilite la gestion des bases de données
très volumineuses via la gestion étape par étape des partitions. Cette section décrit quelques-uns des
avantages des tables partitionnées par rapport aux vues partitionnées, s’agissant des data warehouses
relationnels.
Gestion
L’un des inconvénients de l’utilisation de vues partitionnées est que les opérations de base de données
doivent être effectuées sur des objets individuels plutôt que sur la vue proprement dite. Par exemple, si
l’index existant doit être supprimé et qu’un nouvel index doit être créé, l'opération doit être effectuée sur
chaque table sous-jacente.
Dans SQL Server 2005, les opérations de base de données telles que la maintenance des index sont
effectuées sur la table partitionnée proprement dite et non sur les partitions sous-jacentes, ce qui permet
de simplifier considérablement la gestion des index.
Mécanisme de parallélisme plus performant
Dans SQL Server 2000, les opérations sont effectuées sur des tables individuelles et les données sont
agrégées à un niveau de vue partitionnée. Les lignes des tables sous-jacentes sont collectées à l’aide de
l’opérateur de concaténation afin de représenter la vue. Des agrégations sont ensuite effectuées sur les
données résultantes.
Dans SQL Server 2005, les requêtes exécutées sur les tables partitionnées peuvent utiliser un nouvel
opérateur appelé parallélisme de demande. Le parallélisme de demande est influencé par les ressources
système et les paramètres MAXDOP.
Les requêtes qui utilisent des tables partitionnées sont compilées beaucoup plus rapidement que les
requêtes équivalentes utilisant des vues partitionnées. La durée de compilation des requêtes est

proportionnelle au nombre de partitions lors de l’utilisation de vues partitionnées, tandis que la
compilation n’est pas affectée par le nombre de partitions lors de l’utilisation de tables partitionnées.
Les requêtes sur des vues partitionnées peuvent présenter de meilleures performances dans certains cas,
par exemple :
•
La plus petite unité de parallélisme, lorsque le parallélisme de demande est choisi par l’optimiseur, est
une partition. Les performances des requêtes sur une partition unique dans une table partitionnée SQL
Server 2005 peuvent être dégradées en raison du fait que le degré de parallélisme est limité à un. La
même requête sur une vue partitionnée peut offrir de meilleures performances en raison d’un meilleur
parallélisme dans une partition.
•
Les vues partitionnées peuvent offrir de meilleures performances lorsque le nombre de partitions est
inférieur au nombre de processeurs, en raison d’une meilleure utilisation des processeurs disponibles
via le parallélisme. Lorsque le nombre de partitions est supérieur au nombre de processeurs, les
performances des requêtes sur les tables partitionnées peuvent s’en trouver dégradées si les données
ne sont pas réparties uniformément sur les partitions.
•
Les requêtes sur les vues partitionnées peuvent offrir de meilleures performances en cas de non-
uniformité de la distribution des données sur les partitions.
Identification du parallélisme de demande dans un plan de requête
Voici un exemple de plan de requête généré à partir d'une requête d'agrégation additive.
La section encerclée en rouge indique la présence du parallélisme de demande dans le plan de requête.
L’enfant de gauche de l’opérateur de boucle imbriquée est représenté par les ID de partition. L’enfant de
droite de l’opérateur de boucle imbriquée est représenté par la table partitionnée proprement dite. Dans
cette illustration, pour chaque ID de partition renvoyé par l’enfant de gauche, un opérateur de recherche
d'index en parallèle génère une itération sur les lignes de la partition appropriée. Toutes les opérations
au-dessus de l’opérateur de boucle imbriquée sont également influencées par le nombre de threads
parallèles défini par le parallélisme de demande. L’enfant de gauche représente uniquement les ID de
partition affectés par la requête lorsque la sélection de partitions est effective par exemple lorsque la
requête filtre les résultats par partition.

Figure 1. Identification du parallélisme de demande
Voir l’image en taille réelle
Migration vers des tables/index partitionnés SQL Server 2005 à partir de vues
partitionnées SQL Server 2000
Une application existante basée sur des vues de tables monolithiques ou des vues partitionnées peut être
restructurée ou migrée vers une solution SQL Server 2005 basée sur le partitionnement. La décision de
restructurer ou de migrer l’application implique une analyse détaillée des exigences en termes de
performances, de gestion et de disponibilité.
Une procédure simple pour la migration de vues partitionnées SQL Server 2000 vers des tables
partitionnées SQL Server 2005 comporte les étapes suivantes :
•
Créez une fonction et un schéma de partition afin de déterminer les points limites et l’emplacement de
stockage physique de chaque partition. Les points limites doivent correspondre à ceux des tables
sous-jacentes de la vue partitionnée.
•
Créez une table partitionnée sur le schéma de partition nouvellement créé. La table doit spécifier la
même structure physique, y compris les index, que les tables sous-jacentes de la vue partitionnée.
•
Basculez les tables individuelles sous-jacentes des vues partitionnées dans chaque partition de la table
de faits partitionnée nouvellement créée. Les groupes de fichiers référencés dans le schéma de

partition doivent correspondre au groupe de fichiers de la table vers laquelle le basculement a lieu. En
outre, les tables à migrer doivent satisfaire aux exigences de l’instruction de basculement. Par
exemple, la table cible ne peut pas être un composant d’une vue avec liaison de schéma. Pour
connaître la liste des exigences de l’instruction de basculement, reportez-vous au document
« Transfert efficace de données avec le changement de partition » dans la documentation en ligne de
SQL Server 2005.
Haut de page
Facteurs qui influencent le partitionnement de data warehouses
relationnels
L’implémentation réussie d’un data warehouse relationnel partitionné implique la planification de la
croissance de la base de données et la simplicité de gestion. Les sections qui suivent expliquent les
facteurs qui influencent le partitionnement pour les data warehouses relationnels et apportent des détails
sur les implémentations de fenêtres glissantes.
Volume des données
Une approche partitionnée rend la gestion plus complexe sans apporter de réelle plus-value lorsque les
tables de faits sont plus petites. Cette taille est basée sur les caractéristiques de l’application et doit être
déterminée pour chaque implémentation. À titre d’information, plusieurs clients ont demandé que la taille
des tables de faits soit au moins égale à 100 Go avant toute implémentation du partitionnement.
Chargement des données
Le chargement des données fait partie intégrante d’un data warehouse. Presque tous les data
warehouses sont périodiquement alimentés avec les données collectées récemment. La gestion réussie
d’un data warehouse dépend de l’efficacité du processus de chargement en masse, au cours duquel les
données existantes doivent continuer d’être disponibles.
Il existe deux options pour la création de votre table de faits :
•
Créez une table monolithique unique, ou
•
Utilisez une approche par partition
L’approche par table monolithique entraîne une disponibilité inférieure par rapport à l’approche par
partition, en raison du chargement incrémentiel en masse effectué dans les environnements de data
warehouse typiques. Par exemple, le chargement incrémentiel en masse peut largement tirer parti d'un
verrou de table sur la table cible. Avec une table unique, aucun utilisateur ne pourra plus accéder à la
table au cours du chargement des données. La solution optimale consiste à planifier la fenêtre de
maintenance pour le chargement incrémentiel des données. Pour une étude complète du chargement en
masse avec l'approche par table monolithique, consultez l'article « Étude de cas d’un chargement en
masse incrémentiel dans SQL Server 2000 » à l’adresse
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/incbulkload.mspx .
L’approche par partition implique le chargement en masse des données dans des tables intermédiaires
individuelles, chacune représentant une plage de partitions déterminée. La table intermédiaire est
ensuite ajoutée à la vue partitionnée ou basculée dans la table partitionnée en tant que nouvelle
partition. Étant donné que chaque partition est logiquement représentée par une table intermédiaire
individuelle, les chargements en masse incrémentiels n’affectent pas la disponibilité et les performances
des requêtes sur les données existantes.
Une solution de data warehouse typique inclut la transformation des données en parallèle de leur
chargement en masse. La transformation inclut le nettoyage et/ou l'agrégation des données source afin
d'alimenter le data warehouse de destination.
Une transformation est généralement effectuée à l’aide d’outils tels que Microsoft System Integration
Services. Les clients peuvent choisir d’utiliser SELECT/INTO pour exécuter cette transformation si le
processus ne nécessite pas de workflow complexe.