Partager via

Modélisation dimensionnelle dans Microsoft Fabric Warehouse : Charger des tables

  • Article

S’applique à :✅ point de terminaison d’analytique SQL et entrepôt dans Microsoft Fabric

Remarque

Cet article fait partie de la série de modélisation dimensionnelle d’articles. Cette série se concentre sur les conseils et les meilleures pratiques de conception liées à la modélisation dimensionnelle dans Microsoft Fabric Warehouse.

Cet article vous fournit des conseils et des bonnes pratiques pour charger des tables de dimension et de faits dans un modèle dimensionnel. Il fournit des conseils pratiques pour Warehouse dans Microsoft Fabric, qui est une expérience qui prend en charge de nombreuses fonctionnalités T-SQL, telles que la création de tables et la gestion des données dans les tables. Par conséquent, vous contrôlez complètement la création de vos tables de modèles dimensionnels et leur chargement avec des données.

Remarque

Dans cet article, le terme entrepôt de données fait référence à un entrepôt de données d’entreprise, qui fournit une intégration complète des données critiques au sein de l’organisation. En revanche, le terme autonome entrepôt fait référence à Fabric Warehouse, qui est une offre de base de données relationnelle SaaS (Software as a Service) que vous pouvez utiliser pour implémenter un entrepôt de données. Pour plus de clarté, dans cet article, ce dernier est mentionné en tant que Fabric Warehouse.

Conseil

Si vous êtes inexpérimenté avec la modélisation dimensionnelle, tenez compte de cette série d’articles de votre première étape. Il n’est pas destiné à fournir une discussion complète sur la conception de modélisation dimensionnelle. Pour plus d’informations, reportez-vous directement au contenu publié et largement adopté, comme The Data Warehouse Toolkit : The Definitive Guide to Dimensional Modeling (3ème édition, 2013) par Ralph Kimball et d’autres auteurs.

Charger un modèle dimensionnel

Le chargement d’un modèle dimensionnel implique l’exécution périodique d’un processus d’extraction, de transformation et de chargement (ETL). Un processus ETL orchestre l’exécution d’autres processus, qui sont généralement concernés par les données sources intermédiaires, la synchronisation des données de dimension, l’insertion de lignes dans des tables de faits et l’enregistrement des données et des erreurs d’audit.

Pour une solution Fabric Warehouse, vous pouvez utiliser Data Factory pour développer et exécuter votre processus ETL. Le processus peut mettre en scène, transformer et charger des données sources dans vos tables de modèles dimensionnels.

Plus précisément, vous pouvez :

  • Utilisez des pipelines de données pour générer des flux de travail pour orchestrer le processus ETL. Les pipelines de données peuvent exécuter des scripts SQL, des procédures stockées, etc.
  • Utilisez des dataflows pour développer un low-code pour ingérer des données à partir de centaines de sources de données. Les dataflows prennent en charge la combinaison de données à partir de plusieurs sources, la transformation des données, puis le chargement vers une destination, comme une table de modèle dimensionnel. Les flux de données sont créés à l’aide de l’expérience Power Query familière disponible aujourd’hui dans de nombreux produits Microsoft, notamment Microsoft Excel et Power BI Desktop.

Remarque

Le développement ETL peut être complexe et le développement peut être difficile. On estime que 60 à 80 % d’un effort de développement d’entrepôt de données sont dédiés au processus ETL.

Orchestration

Le flux de travail général d’un processus ETL est le suivant :

  1. De manière facultative, chargez des tables de mise en lots.
  2. Traiter les tables de dimension.
  3. Traiter des tables de faits.
  4. De manière facultative, effectuez des tâches post-traitement, comme déclencher l’actualisation du contenu Fabric dépendant (comme un modèle sémantique).

Le diagramme montre les quatre étapes du processus ETL, comme décrit dans le paragraphe précédent.

Les tables de dimension doivent d’abord être traitées pour s’assurer qu’elles stockent tous les membres de dimension, y compris ceux ajoutés aux systèmes sources depuis le dernier processus ETL. Lorsqu’il existe des dépendances entre les dimensions, comme c’est le cas avec les sous-dimensions, les tables de dimension doivent être traitées dans l’ordre de dépendance. Par exemple, une dimension géographique utilisée par une dimension client et une dimension fournisseur doit être traitée avant les deux autres dimensions.

Les tables de faits peuvent être traitées une fois que toutes les tables de dimension sont traitées.

Lorsque toutes les tables de modèles dimensionnels sont traitées, vous pouvez déclencher l’actualisation des modèles sémantiques dépendants. Il est également judicieux d’envoyer une notification au personnel concerné pour les informer du résultat du processus ETL.

Données mises en lots

La mise en lot les données sources peut aider à prendre en charge les exigences de chargement et de transformation des données. Elle implique l’extraction des données système sources et le chargement dans des tables de mise en lots, que vous créez pour prendre en charge le processus ETL. Nous vous recommandons de mettre en scène des données sources, car elles peuvent :

  • Réduire l’impact sur les systèmes opérationnels.
  • Être utilisées pour faciliter et optimiser le traitement ETL.
  • Fournir la possibilité de redémarrer le processus ETL, sans avoir à recharger les données à partir de systèmes sources.

Les données des tables intermédiaires ne doivent jamais être mises à la disposition des utilisateurs professionnels. Il s’agit uniquement du processus ETL.

Remarque

Lorsque vos données sont stockées dans un Fabric Lakehouse, il se peut qu’il ne soit pas nécessaire de mettre en lots ses données dans l’entrepôt de données. Si ceci implémente une architecture de médaillon, vous pouvez sourcer ses données à partir de la couche bronze, argent ou or.

Nous vous recommandons de créer un schéma dans l’entrepôt, éventuellement nommé staging. Les tables de mise en lots doivent ressembler aux tables sources aussi étroitement que possible en termes de noms de colonnes et de types de données. Le contenu de chaque table doit être supprimé au début du processus ETL. Toutefois, notez que les tables Fabric Warehouse ne peuvent pas être tronquées. Au lieu de cela, vous pouvez supprimer et recréer chaque table de mise en lots avant de la charger avec des données.

Vous pouvez également envisager des alternatives de virtualisation des données dans le cadre de votre stratégie de mise en lots. Vous pouvez utiliser :

Transformer les données

La structure de vos données sources peut ne pas ressembler aux structures de destination de vos tables de modèles dimensionnelles. Par conséquent, votre processus ETL doit remodeler les données sources pour s’aligner sur la structure des tables de modèles dimensionnels.

En outre, l’entrepôt de données doit fournir des données nettoyées et conformes. Les données sources peuvent donc être transformées pour garantir la qualité et la cohérence.

Remarque

Le concept de garbage in, garbage out s’applique certainement à l’entreposage de données. Par conséquent, évitez de charger des données garbage (de faible qualité) dans vos tables de modèles dimensionnels.

Voici quelques transformations que votre processus ETL peut effectuer.

  • Combiner des données : les données provenant de différentes sources peuvent être intégrées (fusionnées) en fonction des clés correspondantes. Par exemple, les données de produit sont stockées sur différents systèmes (comme la fabrication et le marketing), mais elles utilisent toutes une unité de gestion de stock (SKU) commune. Les données peuvent également être ajoutées lorsqu’elles partagent une structure commune. Par exemple, les données de vente sont stockées dans plusieurs systèmes. Une union des ventes de chaque système peut produire un super-ensemble de toutes les données de vente.
  • Convertir des types de données : les types de données peuvent être convertis en ceux définis dans les tables de modèle dimensionnel.
  • Calculs : les calculs peuvent être effectués pour produire des valeurs pour les tables de modèles dimensionnels. Par exemple, pour une table de dimension employé, vous pouvez concaténer des prénoms et des noms pour produire le nom complet. Comme autre exemple, pour votre table de faits de ventes, vous pouvez calculer le chiffre d’affaires brut, qui est le produit du prix unitaire et de la quantité.
  • Détecter et gérer les modifications historiques : les modifications peuvent être détectées et stockées de manière appropriée dans les tables de dimension. Pour plus d’informations, consultez Gérer une modification historique plus loin dans cet article.
  • Données agrégées : l’agrégation peut être utilisée pour réduire la dimensionnalité des tables de faits et/ou pour augmenter la granularité des faits. Par exemple, la table de faits vente n’a pas besoin de stocker les numéros de commande vente. Par conséquent, un résultat agrégé qui regroupe toutes les clés de dimension peut être utilisé pour stocker les données de la table de faits.

Charger les données

Vous pouvez charger des tables dans Fabric Warehouse à l’aide des options d’ingestion de données suivantes.

  • COPY INTO (T-SQL) : cette option est utile lorsque les données sources comprennent des fichiers Parquet ou CSV stockés dans un compte de stockage Azure externe, comme ADLS Gen2 ou Stockage Blob Azure.
  • Pipelines de données : en plus d’orchestrer le processus ETL, les pipelines de données peuvent inclure des activités qui exécutent des instructions T-SQL, effectuent des recherches ou copient des données d’une source de données vers une destination.
  • Flux de données : en tant qu’alternative aux pipelines de données, les flux de données fournissent une expérience sans code pour transformer et nettoyer les données.
  • Ingestion inter-entrepôt : lorsque les données sont stockées dans le même espace de travail, l’ingestion entre entrepôts permet de joindre différentes tables d’entrepôt ou de lakehouse. Il prend en charge les commandes T-SQL telles que INSERT…SELECT, SELECT INTO et CREATE TABLE AS SELECT (CTAS). Ces commandes sont particulièrement utiles lorsque vous souhaitez transformer et charger des données à partir de tables de mise en lots dans le même espace de travail. Ils sont également des opérations basées sur des ensembles, qui sont susceptibles d’être le moyen le plus efficace et le plus rapide de charger des tables de modèles dimensionnelles.

Conseil

Pour obtenir une explication complète de ces options d’ingestion de données, notamment les meilleures pratiques, consultez Ingestion de données dans l’entrepôt.

Logging

Les processus ETL nécessitent généralement une surveillance et une maintenance dédiées. Pour ces raisons, nous vous recommandons de consigner les résultats du processus ETL sur des tables de modèles non dimensionnels dans votre entrepôt. Vous devez générer un ID unique pour chaque processus ETL et l’utiliser pour journaliser les détails de chaque opération.

Envisagez la journalisation :

  • Le processus ETL :
    • ID unique pour chaque exécution ETL
    • Heure de début et heure de fin
    • État (réussite ou échec)
    • Toutes les erreurs rencontrées
  • Chaque table de modèle intermédiaire et dimensionnel :
    • Heure de début et heure de fin
    • État (réussite ou échec)
    • Lignes insérées, mises à jour et supprimées
    • Nombre de lignes de la table finale
    • Toutes les erreurs rencontrées
  • Autres opérations :
    • Heure de début et heure de fin des opérations d’actualisation du modèle sémantique

Conseil

Vous pouvez créer un modèle sémantique dédié à la surveillance et à l’analyse de vos processus ETL. Les durées de processus peuvent vous aider à identifier les goulots d’étranglement susceptibles de tirer parti de la révision et de l’optimisation. Le nombre de lignes peut vous permettre de comprendre la taille de la charge incrémentielle chaque fois que l’ETL s’exécute, et également de prédire la taille future de l’entrepôt de données (et quand effectuer une mise à l’échelle de la capacité Fabric, le cas échéant).

Traiter les tables de dimension

Le traitement d’une table de dimension implique la synchronisation des données de l’entrepôt de données avec les systèmes sources. Les données sources sont d’abord transformées et préparées pour le chargement dans sa table de dimension. Ces données sont ensuite mises en correspondance avec les données de table de dimension existantes en se joignant aux clés métier. Il est ensuite possible de déterminer si les données sources représentent des données nouvelles ou modifiées. Lorsque la table de dimensions applique le type de dimension 1 (SCD) à variation lente, les modifications sont apportées en mettant à jour les lignes de la table de dimensions existantes. Lorsque la table applique les modifications du type SCD 2, la version existante a expiré et une nouvelle version est insérée.

Le diagramme suivant illustre la logique utilisée pour traiter une table de dimension.

Le diagramme montre un flux qui décrit la façon dont les lignes sources nouvelles et modifiées sont chargées dans une table de dimensions, comme décrit dans le paragraphe suivant.

Considérez le processus de la table de dimension Product.

  • Lorsque de nouveaux produits sont ajoutés au système source, les lignes sont insérées dans la table de dimension Product.
  • Lorsque les produits sont modifiés, les lignes existantes de la table de dimension sont mises à jour ou insérées.
    • Lorsque le type SCD 1 s’applique, les mises à jour sont apportées aux lignes existantes.
    • Lorsque le type SCD 2 s’applique, les mises à jour sont effectuées pour expirer les versions de lignes actuelles et de nouvelles lignes qui représentent la version actuelle sont insérées.
    • Lorsque le type SCD 3 s’applique, un processus similaire au type SCD 1 se produit, mettant à jour les lignes existantes sans insérer de nouvelles lignes.

Clés de substitution

Nous vous recommandons d’utiliser une clé de substitution pour chaque table de dimension, qui doit utiliser le plus petit type de données entier possible. Dans les environnements SQL Server, cela se fait généralement en créant une colonne d’identité. Cette fonctionnalité n’est toutefois pas prise en charge dans Fabric Warehouse. Au lieu de cela, vous devez utiliser une technique de contournement qui génère des identificateurs uniques.

Important

Lorsqu’une table de dimension inclut des clés de substitution générées automatiquement, vous ne devez jamais effectuer de tronquer et de recharger complètement. Cela est dû au fait qu’elle invaliderait les données chargées dans des tables de faits qui utilisent la dimension. En outre, si la table de dimension prend en charge les modifications de type SCD 2, il est possible de ne pas pouvoir régénérer les versions historiques.

Gérer les modifications historiques

Lorsqu’une table de dimension doit stocker les modifications historiques, vous devez implémenter une dimension variable lente (SCD).

Remarque

Si la ligne de la table de dimensions est un membre déduit (inséré par un processus de chargement de faits), vous devez traiter les modifications comme des détails de dimension arrivant en retard au lieu d’une modification SCD. Dans ce cas, tous les attributs modifiés doivent être mis à jour et la colonne d’indicateur membre déduite définie sur FALSE.

Il est possible qu’une dimension puisse prendre en charge les modifications de type SCD 1 et/ou SCD type 2.

SCD de type 1

Lorsque des modifications de type SCD 1 sont détectées, utilisez la logique suivante.

  1. Mettez à jour les attributs modifiés.
  2. Si la table inclut la date de dernière modification et la dernière modification par colonnes, définissez la date et le processus actuels qui ont apporté les modifications.

SCD de type 2

Lorsque les modifications du type SCD 2 sont détectées, utilisez la logique suivante.

  1. Expirez la version actuelle en définissant la colonne de validité de la date de fin sur la date de traitement ETL (ou un horodatage approprié dans le système source) et l’indicateur actuel sur FALSE.
  2. Si la table inclut la date de dernière modification et la dernière modification par colonnes, définissez la date et le processus actuels qui ont apporté les modifications.
  3. Insérez de nouveaux membres dont la colonne de validité de la date de début est définie sur la valeur de colonne de validité de la date de fin (utilisée pour mettre à jour la version précédente) et dont l’indicateur de version actuelle est défini sur TRUE.
  4. Si la table inclut les colonnes date de création et créée par, définissez la date et le processus actuels qui ont effectué les insertions.

SCD de type 3

Lorsque des modifications de type SCD 3 sont détectées, mettez à jour les attributs à l’aide d’une logique similaire au traitement du type SCD 1.

Suppressions de membres de dimension

Veillez à ce que les données sources indiquent que les membres de dimension ont été supprimés (soit parce qu’ils ne sont pas récupérés à partir du système source, soit qu’ils ont été marqués comme supprimés). Vous ne devez pas synchroniser les suppressions avec la table de dimension, sauf si les membres de dimension ont été créés par erreur et qu’aucun enregistrement de faits ne leur est associé.

La façon appropriée de gérer les suppressions sources consiste à les enregistrer en tant que suppression réversible. Une suppression réversible marque un membre de dimension comme n’étant plus actif ou valide. Pour prendre en charge ce cas, votre table de dimension doit inclure un attribut booléen avec le type de données bit, comme IsDeleted. Mettez à jour cette colonne pour tous les membres de dimension supprimés sur TRUE (1). La dernière version actuelle d’un membre de dimension peut également être marquée avec une valeur booléenne (bit) dans les colonnes IsCurrent ou IsActive. Toutes les requêtes de création de rapports et les modèles sémantiques Power BI doivent filtrer les enregistrements qui sont des suppressions réversibles.

Dimension de date

Les dimensions calendrier et horaires sont des cas spéciaux, car elles n’ont généralement pas de données sources. Au lieu de cela, elles sont générées à l’aide d’une logique fixe.

Vous devez charger la table de dimension de date au début de chaque nouvelle année pour étendre ses lignes à un nombre spécifique d’années à venir. Il peut y avoir d’autres données commerciales, par exemple des données d’exercice comptable, des jours fériés et des numéros de semaine à mettre à jour régulièrement.

Lorsque la table de dimension de date inclut des attributs de décalage relatifs, le processus ETL doit être exécuté quotidiennement pour mettre à jour les valeurs d’attribut de décalage en fonction de la date actuelle (aujourd’hui).

Nous vous recommandons d’étendre ou de mettre à jour la table de dimension de date en T-SQL et encapsulée dans une procédure stockée.

Traiter les tables de faits

Le traitement d’une table de faits implique la synchronisation des données de l’entrepôt de données avec les faits du système source. Les données sources sont d’abord transformées et préparées pour le chargement dans sa table de faits. Ensuite, pour chaque clé de dimension, une recherche détermine la valeur de clé de substitution à stocker dans la ligne de faits. Lorsqu’une dimension prend en charge le type SCD 2, la clé de substitution pour la version actuelle du membre de dimension doit être récupérée.

Remarque

Généralement, la clé de substitution peut être calculée pour les dimensions de date et d’heure, car elles doivent utiliser le format YYYYMMDD ou HHMM. Pour plus d’informations, voir Calendrier et horaires.

Si une recherche de clé de dimension échoue, elle peut indiquer un problème d’intégrité avec le système source. Dans ce cas, la ligne de faits doit toujours être insérée dans la table de faits. Une clé de dimension valide doit toujours être stockée. Une approche consiste à stocker un membre de dimension spécial (comme Inconnu). Cette approche nécessite une mise à jour ultérieure pour affecter correctement la valeur de clé de dimension true, lorsqu’elle est connue.

Important

Étant donné que Fabric Warehouse n’applique pas de clés étrangères, il est essentiel que le processus ETL vérifie l’intégrité lorsqu’il charge des données dans des tables de faits.

Une autre approche, pertinente lorsqu’il y a confiance que la clé naturelle est valide, consiste à insérer un nouveau membre de dimension, puis à stocker sa valeur de clé de substitution. Pour plus d’informations, consultez Membres de dimension déduits plus loin dans cette section.

Le diagramme suivant illustre la logique utilisée pour traiter une table de faits.

Le diagramme montre un flux qui décrit la façon dont les nouvelles lignes sources sont chargées dans une table de faits, comme décrit dans les paragraphes précédents.

Dans la mesure du possible, une table de faits doit être chargée de manière incrémentielle, ce qui signifie que de nouveaux faits sont détectés et insérés. Une stratégie de charge incrémentielle est plus évolutive et réduit la charge de travail pour les systèmes sources et les systèmes de destination.

Important

Surtout pour une grande table de faits, il doit s’agir d’un dernier recours à tronquer et recharger une table de faits. Cette approche est coûteuse en termes de temps de processus, de ressources de calcul et d’interruption possible des systèmes sources. Elle implique également une complexité lorsque les dimensions de la table de faits appliquent le type SCD 2. Cela est dû au fait que les recherches de clés de dimension devront être effectuées au cours de la période de validité des versions des membres de dimension.

Espérons que vous pouvez détecter efficacement de nouveaux faits en s’appuyant sur des identificateurs de système source ou des horodateurs. Par exemple, lorsqu’un système source enregistre de manière fiable les commandes de vente qui sont dans l’ordre, vous pouvez stocker le dernier numéro de commande de vente récupéré (appelé filigrane élevé). Le processus suivant peut utiliser ce numéro de commande de vente pour récupérer les commandes de vente nouvellement créées, puis à nouveau stocker le dernier numéro de commande de vente récupéré pour une utilisation par le processus suivant. Il peut également être possible qu’une colonne de date de création puisse être utilisée pour détecter de manière fiable les nouvelles commandes.

Si vous ne pouvez pas vous appuyer sur les données du système source pour détecter efficacement les nouveaux faits, vous pouvez peut-être vous appuyer sur une capacité du système source pour effectuer une charge incrémentielle. Par exemple, SQL Server et Azure SQL Managed Instance ont une fonctionnalité appelée capture de données modifiées (CDC), qui peut suivre les modifications apportées à chaque ligne d’une table. En outre, SQL Server, Azure SQL Managed Instance et Azure SQL Database ont une fonctionnalité appelée suivi des modifications, qui peut identifier les lignes qui ont changé. Lorsqu’elle est activée, elle peut vous aider à détecter efficacement les données nouvelles ou modifiées dans n’importe quelle table de base de données. Vous pouvez également ajouter des déclencheurs à des tables relationnelles qui stockent les clés des enregistrements de table insérés, mis à jour ou supprimés.

Enfin, vous pouvez mettre en corrélation les données sources à la table de faits à l’aide d’attributs. Par exemple, le numéro de la commande de vente et le numéro de ligne de commande de vente. Toutefois, pour les tables de faits volumineuses, il peut s’agir d’une opération très coûteuse pour détecter les faits nouveaux, modifiés ou supprimés. Cela peut également être problématique lorsque le système source archive les données opérationnelles.

Membres de la dimension déduits

Lorsqu’un processus de chargement de faits insère un nouveau membre de dimension, il est appelé membre déduit. Par exemple, lorsqu’un invité de l’hôtel se connecte, il est invité à rejoindre la chaîne d’hôtel en tant que membre de fidélité. Un numéro d’abonnement est émis immédiatement, mais les détails de l’invité peuvent ne pas suivre tant que les documents ne sont pas soumis par l’invité (le cas échéant).

Tout ce qui est connu sur le membre de dimension est sa clé naturelle. Le processus de chargement de faits doit créer un membre de dimension à l’aide de valeurs d’attribut Inconnu. Il est important de définir l’ IsInferredMember attribut d’audit sur TRUE. Ainsi, lorsque les détails disponibles plus retard sont générés, le processus de chargement de dimension peut apporter les mises à jour nécessaires à la ligne de dimension. Pour plus d’informations, consultez Gérer une modification historique dans cet article.

Mises à jour ou suppressions de faits

Vous devrez peut-être mettre à jour ou supprimer des données de faits. Par exemple, lorsqu’une commande est annulée ou qu’une quantité de commandes est modifiée. Comme décrit précédemment pour charger des tables de faits, vous devez détecter efficacement les modifications et effectuer les modifications appropriées pour les données de faits. Dans cet exemple pour la commande annulée, l’état de la commande vente passe probablement de Ouvert à Annulé. Cette modification nécessiterait une mise à jour des données de faits, et non la suppression d’une ligne. Pour la modification de la quantité, une mise à jour de la mesure de quantité de lignes de faits serait nécessaire. Cette stratégie d’utilisation de suppressions réversibles conserve l’historique. Une suppression réversible marque une ligne comme n’étant plus active ou valide, et toutes les requêtes de création de rapports et les modèles sémantiques Power BI doivent filtrer les enregistrements qui sont des suppressions réversibles.

Lorsque vous prévoyez des mises à jour ou suppressions de faits, vous devez inclure des attributs (comme un numéro de commande de vente et son numéro de ligne de commande de vente) dans la table de faits pour vous aider à identifier les lignes de faits à modifier. Veillez à indexer ces colonnes pour prendre en charge des opérations de modification efficaces.

Enfin, si des données de faits ont été insérées à l’aide d’un membre de dimension spécial (comme Inconnu), vous devez exécuter un processus périodique qui récupère les données sources actuelles pour ces lignes de faits et met à jour les clés de dimension en valeurs valides.

Contenu connexe

Pour plus d’informations sur le chargement de données dans Fabric Warehouse, consultez :