Principes de conception d'un data warehouse sur SQL Server
Schéma en étoile, clés de substitution, index columnstore, compression, bonnes pratiques SSIS, CDC et tables temporelles.
Cet article rassemble des principes généraux de conception d'un data warehouse sur SQL Server : modélisation dimensionnelle, indexation, compression, ETL avec SSIS et suivi des changements.
Schéma en étoile : faits et dimensions
Un modèle dimensionnel sépare les faits des dimensions.
- Une table de faits stocke les mesures (quantités, montants) des événements métier, au centre du modèle. De nombreuses lignes de faits pointent vers une ligne de dimension.
- Une table de dimension stocke les attributs descriptifs servant à filtrer et regrouper les mesures (date, produit, client, magasin, vendeur, heure).
- Une table de pont (bridge, aussi appelée table de faits sans fait, factless) ne contient que des clés, aucune mesure et aucune clé primaire. Elle sert à modéliser les relations plusieurs-à-plusieurs entre un fait et une dimension.
Dans un schéma en étoile typique, la table de faits porte les mesures (quantité, montant total) et une clé étrangère vers chaque dimension. Chaque dimension a sa propre clé et ses attributs, dont une dimension date riche en attributs calendaires et fiscaux et une dimension heure.
Toujours utiliser des clés de substitution (surrogate keys). Une clé de substitution est un entier sans signification métier généré par l'entrepôt, utilisé à la place de la clé métier du système source. Elle isole l'entrepôt des changements de clés de la source, garde des jointures étroites et rapides, et permet l'historisation. La clé métier reste dans la dimension comme clé alternative.
Indexation et compression
| Type de table | Index recommandé |
|---|---|
| Dimension | index classique (rowstore, B-tree) |
| Faits | index columnstore |
- Un index columnstore stocke les données par colonne dans des segments compressés. Il convient aux gros balayages et agrégations typiques des tables de faits. Ses métadonnées sont visibles via
sys.column_store_row_groupsetsys.column_store_segments. - Le mode d'exécution batch (traitement des lignes par lots plutôt qu'une à une) s'applique aux requêtes sur des index columnstore.
- La compression est plus efficace sur des données en lecture seule ou à dominante lecture. Elle convient bien aux dimensions.
- Une vue indexée matérialise le résultat d'une vue. Elle s'utilise surtout en passant par la vue ; son utilisation automatique par l'optimiseur sans référencer la vue nécessite l'édition Enterprise.
Maintenance des index
Pour les index rowstore, la règle usuelle basée sur la fragmentation est : entre 5 % et 30 %, réorganiser l'index ; au-delà de 30 %, le reconstruire.
Dimensionnement et environnement de test
- Penser au débit dès la conception. Une question de conception typique : comment charger 1 To de données en moins de 30 minutes ; mesurer le stockage avec un outil de test de lecture aléatoire comme SQLIO avant de choisir l'architecture.
- SqlLocalDB est un mode d'exécution léger de SQL Server Express, pratique pour le développement local.
ETL avec SSIS
- Installer les BI Developer Extensions (projets Business Intelligence pour Visual Studio) : bideveloperextensions.github.io.
- Un gestionnaire de connexion peut utiliser plusieurs fournisseurs : ADO.NET, ODBC ou OLE DB. Il expose des propriétés comme le nom du serveur, le catalogue initial et
RetainSameConnection. - Une erreur de package n'est pas la même chose qu'un job en échec : une tâche peut échouer sans que le package soit forcément en échec, selon les propriétés des tâches.
MaximumErrorCountdéfinit le nombre d'erreurs tolérées avant l'échec du conteneur (valeur 1 dans l'exemple).FailParentOnFailureàTruefait échouer le conteneur parent quand la tâche échoue. - Les points de contrôle (checkpoints) permettent à un package en échec de redémarrer à la tâche en échec plutôt que depuis le début. Ils exigent que les tâches fassent échouer le package en cas d'échec (
FailPackageOnFailure). - Pour tester l'envoi de mails sans vrai serveur de messagerie, on peut utiliser un serveur SMTP de test local comme Papercut SMTP.
Suivi des changements : CDC et tables temporelles
- Change Data Capture (CDC) enregistre les insertions, mises à jour et suppressions à partir du journal de transactions. Deux fonctions sont utiles pour le lire :
sys.fn_cdc_map_time_to_lsnfait correspondre une heure de début et de fin à des numéros de séquence de journal (LSN) ;- la fonction
cdc.fn_cdc_get_all_changes_<capture_instance>renvoie les changements exacts entre deux LSN.
- Les tables temporelles (versionnées par le système) conservent automatiquement tout l'historique des lignes. Elles nécessitent deux colonnes de période supplémentaires (début et fin de validité) dans la table.