LLI Wiki — My Digital Knowledge Base

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.

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.

Schéma en étoile : une table de faits reliée aux dimensions date, produit, client, magasin, vendeur et heure

Indexation et compression

Type de table Index recommandé
Dimension index classique (rowstore, B-tree)
Faits index columnstore

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

Éditeur de stratégie de groupe locale, section Système

ETL avec SSIS

Propriétés d'un gestionnaire de connexions OLE DB dans SSIS

Propriétés d'exécution d'une tâche SSIS : FailParentOnFailure et MaximumErrorCount

Suivi des changements : CDC et tables temporelles

Mastodon LLIMastodon SocialFosstodon