Modélisation dimensionnelle dans Microsoft Fabric Warehouse : tables de dimension

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 meilleures pratiques pour la conception de tables de dimension 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.

Dans un modèle dimensionnel, une table de dimensions décrit une entité pertinente pour vos besoins métier et d’analytique. En général, les tables de dimension représentent les éléments que vous modélisez. Les choses peuvent être des produits, des personnes, des lieux ou tout autre concept, y compris la date et l’heure. Pour identifier facilement les tables de dimension, vous préfixez généralement leur nom avec d_ ou Dim_.

Structure de la table de dimension

Pour décrire la structure d’une table de dimension, considérez l’exemple suivant d’une table de dimension vendeur nommée d_Salesperson. Cet exemple applique les meilleures pratiques de conception. Chacun des groupes de colonnes est décrit dans les sections suivantes.

CREATE TABLE d_Salesperson
(
    --Surrogate key
    Salesperson_SK INT NOT NULL,
    
    --Natural key(s)
    EmployeeID VARCHAR(20) NOT NULL,
    
    --Dimension attributes
    FirstName VARCHAR(20) NOT NULL,
    <…>
    
    --Foreign key(s) to other dimensions
    SalesRegion_FK INT NOT NULL,
    <…>
    
    --Historical tracking attributes (SCD type 2)
    RecChangeDate_FK INT NOT NULL,
    RecValidFromKey INT NOT NULL,
    RecValidToKey INT NOT NULL,
    RecReason VARCHAR(15) NOT NULL,
    RecIsCurrent BIT NOT NULL,
    
    --Audit attributes
    AuditMissing BIT NOT NULL,
    AuditIsInferred BIT NOT NULL,
    AuditCreatedDate DATE NOT NULL,
    AuditCreatedBy VARCHAR(15) NOT NULL,
    AuditLastModifiedDate DATE NOT NULL,
    AuditLastModifiedBy VARCHAR(15) NOT NULL
);

Clé de substitution

L’exemple de table de dimension a une clé de substitution, nommée Salesperson_SK. Une clé de substitution est un identificateur unique à colonne unique généré et stocké dans la table de dimension. Il s’agit d’une colonne clé primaire utilisée pour établir une relation avec d’autres tables du modèle dimensionnel.

Les clés de substitution s’efforcent d’isoler l’entrepôt de données des modifications apportées aux données sources. Ils offrent également de nombreux autres avantages, ce qui vous permet de :

• Consolidez plusieurs sources de données (évitez les conflits d’identificateurs en double).
• Consolider des clés naturelles à plusieurs colonnes dans une clé à colonne unique plus efficace.
• Suivez l’historique des dimensions avec un type SCD 2 (Dimension à variation lente).
• Limitez la largeur des tables de faits pour l’optimisation du stockage (en sélectionnant le plus petit type de données entier possible).

Une colonne clé de substitution est une pratique recommandée, même lorsqu’une clé naturelle (décrite ensuite) semble un candidat acceptable. Vous devez également éviter de donner de la signification aux valeurs clé (à l’exception des clés de dimension de date et d’heure, comme décrit plus loin).

Clés naturelles

L’exemple de table de dimension a également une clé naturelle, nommée EmployeeID. Une clé naturelle est la clé stockée dans le système source. Il permet de relier les données de dimension à son système source, généralement effectué par un processus d’extraction, de chargement et de transformation (ETL) pour charger la table de dimension. Parfois, une clé naturelle est appelée clé métier, et ses valeurs peuvent être significatives pour les utilisateurs métier.

Parfois, les dimensions n’ont pas de clé naturelle. Cela peut être le cas pour votre dimension de date ou vos dimensions de recherche, ou lorsque vous générez des données de dimension en normalisant un fichier plat.

Attributs de dimensions

Un exemple de table de dimension a également des attributs de dimension, comme la colonneFirstName. Les attributs de dimension fournissent un contexte aux données numériques stockées dans des tables de faits associées. Ils sont généralement des colonnes de texte utilisées dans les requêtes analytiques pour filtrer et regrouper (segmenter et découper), mais pas pour être agrégées elles-mêmes. Certaines tables de dimension contiennent peu d’attributs, tandis que d’autres contiennent de nombreux attributs (autant qu’il faut pour prendre en charge les exigences de requête du modèle dimensionnel).

Conseil

Un bon moyen de déterminer les dimensions et attributs dont vous avez besoin est de trouver les bonnes personnes et de poser les bonnes questions. Plus précisément, restez alerte pour la mention du mot par. Par exemple, quand quelqu’un dit qu’il doit analyser les ventes par vendeur, par mois et par catégorie de produit, il vous dit qu’il a besoin de dimensions qui ont ces attributs.

Si vous envisagez de créer un modèle sémantique Direct Lake, vous devez inclure toutes les colonnes possibles requises pour le filtrage et le regroupement en tant qu’attributs de dimension. Cela est dû au fait que les modèles sémantiques Direct Lake ne prennent pas en charge les colonnes calculées.

Clés étrangères

L’exemple de table de dimension a également une clé étrangère, nommée SalesRegion_FK. D’autres tables de dimension peuvent référencer une clé étrangère et leur présence dans une table de dimension est un cas spécial. Elle indique que la table est liée à une autre table de dimension, ce qui signifie qu’elle peut faire partie d’une dimension Snowflake ou qu’elle est liée à une sous-dimension.

Fabric Warehouse prend en charge les contraintes de clé étrangère, mais elles ne peuvent pas être appliquées. Par conséquent, il est important que votre processus ETL teste l’intégrité entre les tables associées lorsque les données sont chargées.

Il est toujours judicieux de créer des clés étrangères. Une des bonnes raisons de créer des clés étrangères non appliquées consiste à autoriser les outils de modélisation, tels que Power BI Desktop, à détecter et créer automatiquement des relations entre des tables dans le modèle sémantique.

Attributs de suivi historique

L’exemple de table de dimension a également différents attributs de suivi historique. Les attributs de suivi historique sont facultatifs en fonction de votre besoin de suivre des modifications spécifiques au fur et à mesure qu’elles se produisent dans le système source. Ils permettent de stocker des valeurs pour prendre en charge le rôle principal d’un entrepôt de données, qui consiste à décrire le passé avec précision. Plus précisément, ces attributs stockent le contexte historique lorsque le processus ETL charge des données nouvelles ou modifiées dans la dimension.

Pour plus d’informations, consultez Gérer une modification historique plus loin dans cet article.

Attributs d’audit

L’exemple de table de dimension a également différents attributs d’audit. Les attributs d’audit sont facultatifs mais recommandés. Ils vous permettent de suivre quand et comment les enregistrements de dimension ont été créés ou modifiés, et peuvent inclure des informations de diagnostic ou de résolution des problèmes générées pendant les processus ETL. Par exemple, vous souhaiterez suivre qui (ou quel processus) a mis à jour une ligne et quand. Les attributs d’audit peuvent également aider à diagnostiquer un problème difficile, comme lorsqu’un processus ETL s’arrête de façon inattendue. Ils peuvent également marquer des membres de dimension comme des erreurs ou des membres déduits.

Taille de la table de dimension

Souvent, les dimensions les plus utiles et polyvalentes d’un modèle dimensionnel sont de grandes dimensions larges. Ils sont volumineux en termes de lignes (en plus de millions) et larges en termes de nombre d’attributs de dimension (potentiellement des centaines). La taille n’est pas si importante (bien que vous deviez concevoir et optimiser pour la plus petite taille possible). Ce qui importe, c’est que la dimension prend en charge le filtrage, le regroupement et l’analyse historique précise des données de faits.

Les dimensions volumineuses peuvent provenir de plusieurs systèmes sources. Dans ce cas, le traitement de dimension doit combiner, fusionner, dédupliquer et normaliser les données ; et affecter des clés de substitution.

Par comparaison, certaines dimensions sont minuscules. Ils peuvent représenter des tables de choix qui contiennent uniquement plusieurs enregistrements et attributs. Souvent, ces petites dimensions stockent les valeurs de catégorie liées aux transactions dans les tables de faits, et elles sont implémentées en tant que dimensions avec des clés de substitution pour se rapporter aux enregistrements de faits.

Conseil

Lorsque vous avez de nombreuses petites dimensions, envisagez de les consolider dans une dimension de courrier indésirable.

Concepts de conception de dimension

Cette section décrit différents concepts de conception de dimension.

Dénormalisation et normalisation

C’est presque toujours le cas où les tables de dimension doivent être dénormalisées. Bien que la normalisation soit le terme utilisé pour décrire les données stockées d’une manière qui réduit les données répétitives, la dénormalisation est le terme utilisé pour définir où existent des données redondantes précomputées. Les données redondantes existent généralement en raison du stockage des hiérarchies (décrites plus loin), ce qui signifie que les hiérarchies sont aplaties. Par exemple, une dimension de produit peut stocker la sous-catégorie (et ses attributs connexes) et sa catégorie (et ses attributs associés).

Étant donné que les dimensions sont généralement petites (par rapport aux tables de faits), le coût de stockage des données redondantes est presque toujours dépassé par les performances de requête améliorées et la facilité d’utilisation.

Dimensions en flocon

Une exception à la dénormalisation consiste à concevoir une dimension Snowflake. Une dimension Snowflake est normalisée et stocke les données de dimension sur plusieurs tables associées.

Le diagramme suivant illustre une dimension Snowflake qui comprend trois tables de dimension connexes : Product, Subcategory et Category.

Envisagez d’implémenter une dimension Snowflake quand :

• La dimension est extrêmement volumineuse et les coûts de stockage l’emportent sur la nécessité de hautes performances de requête. (Toutefois, réévaluez régulièrement que cela reste toujours le cas.)
• Vous avez besoin de clés pour lier la dimension aux faits à grain plus élevé. Par exemple, la table de faits sales stocke les lignes au niveau du produit, mais la table de faits cible des ventes stocke les lignes au niveau de la sous-catégorie.
• Vous devez suivre les modifications historiques à des niveaux de granularité plus élevés.

Remarque

N’oubliez pas qu’une hiérarchie dans un modèle sémantique Power BI ne peut être basée que sur des colonnes d’une seule table de modèle sémantique. Par conséquent, une dimension Snowflake doit fournir un résultat dénormalisé à l’aide d’une vue qui joint les tables Snowflake ensemble.

Hiérarchies

Généralement, les colonnes de dimension produisent des hiérarchies. Les hiérarchies permettent d’explorer les données à des niveaux distincts de résumé. Par exemple, la vue initiale d’un visuel de matrice peut afficher des ventes annuelles, et le consommateur de rapports peut choisir de descendre dans la hiérarchie pour révéler les ventes trimestrielles et mensuelles.

Il existe trois façons de stocker une hiérarchie dans une dimension. Vous pouvez utiliser :

• Colonnes d’une dimension unique dénormalisée.
• Une dimension Snowflake qui comprend plusieurs tables associées.
• Relation parent-enfant (auto-référencement) dans une dimension.

Les hiérarchies peuvent être équilibrées ou déséquilibrées. Il est également important de comprendre que certaines hiérarchies sont déséquilibrées.

Hiérarchies équilibrées

Les hiérarchies équilibrées sont le type de hiérarchie le plus courant. Une hiérarchie équilibrée a le même nombre de niveaux. Un exemple courant d’une hiérarchie équilibrée est une hiérarchie de calendrier dans une dimension de date qui comprend des niveaux pour l’année, le trimestre, le mois et la date.

Le diagramme suivant illustre une hiérarchie équilibrée de régions de vente. Il comprend deux niveaux, qui sont le groupe de régions de vente et la région des ventes.

Les niveaux d’une hiérarchie équilibrée sont basés sur des colonnes d’une dimension unique, dénormalisée ou à partir de tables qui forment une dimension Snowflake. En fonction d’une dimension unique dénormalisée, les colonnes qui représentent les niveaux supérieurs contiennent des données redondantes.

Pour les hiérarchies équilibrées, les faits sont toujours liés à un niveau unique de la hiérarchie, qui est généralement le niveau le plus bas. De cette façon, les faits peuvent être agrégés (regroupés) au niveau le plus élevé de la hiérarchie. Les faits peuvent être liés à n’importe quel niveau, qui est déterminé par le grain de la table de faits. Par exemple, la table de faits vente peut être stockée au niveau de la date, tandis que la table de faits cible des ventes peut être stockée au niveau du trimestre.

Hiérarchies déséquilibrées

Les hiérarchies déséquilibrées sont un type de hiérarchie moins courant. Une hiérarchie déséquilibré a des niveaux basés sur une relation parent-enfant. Pour cette raison, le nombre de niveaux dans une hiérarchie déséquilibré est déterminé par les lignes de dimension, et non par des colonnes de table de dimension spécifiques.

Un exemple courant d’une hiérarchie déséquilibrée est une hiérarchie d’employés où chaque ligne d’une dimension employé est liée à une ligne du gestionnaire de rapports dans la même table. Dans ce cas, n’importe quel employé peut être un responsable disposant de rapports d’employés. Naturellement, certaines branches de la hiérarchie auront plus de niveaux que d’autres.

Le diagramme suivant illustre une hiérarchie déséquilibrée. Il comprend quatre niveaux, et chaque membre de la hiérarchie est un vendeur. Notez que les vendeurs ont un nombre différent d’ancêtres dans la hiérarchie selon qui ils signalent.

D’autres exemples courants de hiérarchies déséquilibrées incluent la facture de matériaux, les modèles de propriété de l’entreprise et le grand livre.

Pour les hiérarchies déséquilibrées, les faits sont toujours liés au grain de dimension. Par exemple, les faits de vente concernent différents vendeurs, qui ont des structures de création de rapports différentes. La table de dimension aurait une clé de substitution (nommée Salesperson_SK) et une colonne ReportsTo_Salesperson_FK de clé étrangère, qui fait référence à la colonne de clé primaire. Chaque vendeur sans personne à gérer n’est pas nécessairement au niveau le plus bas de toute branche de la hiérarchie. Lorsqu’ils ne sont pas au niveau le plus bas, un vendeur peut vendre des produits et signaler des vendeurs qui vendent également des produits. Ainsi, le rollup des données de faits doit tenir compte du vendeur individuel et de tous ses descendants.

L’interrogation des hiérarchies parent-enfant peut être complexe et lente, en particulier pour les dimensions volumineuses. Bien que le système source puisse stocker des relations en tant qu’enfant parent, nous vous recommandons de naturaliser la hiérarchie. Dans cette instance, naturaliser signifie transformer et stocker les niveaux de hiérarchie dans la dimension sous forme de colonnes.

Conseil

Si vous choisissez de ne pas naturaliser la hiérarchie, vous pouvez toujours créer une hiérarchie basée sur une relation parent-enfant dans un modèle sémantique Power BI. Toutefois, cette approche n’est pas recommandée pour les dimensions volumineuses. Pour plus d’informations, consultez Compréhension des fonctions pour des hiérarchies parent-enfant dans DAX.

Hiérarchies déséquilibrées

Parfois, une hiérarchie est déséquilibrée, car le parent d’un membre de la hiérarchie existe à un niveau qui n’est pas immédiatement au-dessus de celui-ci. Dans ces cas, les valeurs de niveau manquantes répètent la valeur du parent.

Prenons un exemple de hiérarchie géographique équilibrée. Une hiérarchie déséquilibrée existe lorsqu’un pays/une région n’a pas d’états ou de provinces. Par exemple, la Nouvelle-Zélande n’a ni états ni provinces. Par conséquent, lorsque vous insérez la ligne Nouvelle-Zélande, vous devez également stocker la valeur pays/région dans la colonne StateProvince.

Le diagramme suivant illustre une hiérarchie déséquilibrée des régions géographiques.

Gérer les modifications historiques

Si nécessaire, le changement historique peut être géré en implémentant une dimension à variation lente (SCD). Un SCD gère le contexte historique en tant que données nouvelles ou modifiées, est chargé dans celui-ci.

Voici les types de requêtes SCD les plus communs.

• Type 1 : remplacer le membre de dimension existant.
• Type 2 : insérer un nouveau membre de dimension versionné basé sur le temps.
• Type 3 : suivre l’historique limité avec des attributs.

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

Le type SCD 3 n’est pas couramment utilisé, en partie en raison du fait qu’il est difficile d’utiliser dans un modèle sémantique. Déterminez soigneusement si une approche SCD type 2 serait plus adaptée.

Conseil

Si vous prévoyez une dimension à variation rapide, qui est une dimension qui a un attribut qui change fréquemment, envisagez plutôt d’ajouter cet attribut à la table de faits. Si l’attribut est numérique, comme le prix du produit, vous pouvez l’ajouter en tant que mesure dans la table de faits. Si l’attribut est une valeur de texte, vous pouvez créer une dimension basée sur toutes les valeurs de texte et ajouter sa clé de dimension à la table de faits.

SCD de type 1

Les modifications du type SCD 1 remplacent la ligne de dimension existante, car il n’est pas nécessaire de suivre les modifications. Ce type SCD peut également être utilisé pour corriger les erreurs. Il s’agit d’un type de SCD courant et doit être utilisé pour la plupart des attributs changeants, tels que le nom du client, l’adresse e-mail et d’autres données.

Le diagramme suivant illustre l’état avant et après d’un membre de dimension vendeur dont le numéro de téléphone a changé.

Ce type SCD ne conserve pas la perspective historique, car la ligne existante est mise à jour. Cela signifie que les modifications de type SCD 1 peuvent entraîner des agrégations de niveau supérieur différentes. Par exemple, si un vendeur est affecté à une autre région de vente, une modification de type SCD 1 remplace la ligne de dimension. Le rollup des résultats historiques des ventes des vendeurs dans la région produirait ensuite un résultat différent, car il utilise désormais la nouvelle région de ventes actuelle. C’est comme si ce vendeur était toujours affecté à la nouvelle région de vente.

SCD de type 2

Les modifications du type SCD 2 entraînent la création de nouvelles lignes qui représentent une version basée sur le temps d’un membre de dimension. Il existe toujours une ligne de version actuelle et elle reflète l’état du membre de dimension dans le système source. Attributs de suivi historique dans les valeurs du magasin de tables de dimension qui permettent d’identifier la version actuelle (indicateur actuel TRUE) et sa période de validité. Une clé de substitution est requise, car il y aura des clés naturelles dupliquées lorsque plusieurs versions sont stockées.

Il s’agit d’un type commun de SCD, mais il doit être réservé aux attributs qui doivent conserver la perspective historique.

Par exemple, si un vendeur est affecté à une autre région de vente, une modification de type SCD 2 implique une opération de mise à jour et une opération d’insertion.

1. L’opération de mise à jour remplace la version actuelle pour définir les attributs de suivi historiques. Plus précisément, la colonne de validité de fin est définie sur la date de traitement ETL (ou un horodatage approprié dans le système source) et l’indicateur actuel est défini sur FALSE.
2. L’opération d’insertion ajoute une nouvelle version actuelle, en définissant la colonne de validité de début sur la valeur de colonne de validité de fin (utilisée pour mettre à jour la version précédente) et l’indicateur actuel sur TRUE.

Il est important de comprendre que la granularité des tables de faits connexes n’est pas au niveau du vendeur, mais plutôt au niveau de la version du vendeur. Le rollup de leurs résultats de ventes historiques dans la région produira des résultats corrects, mais il y aura deux versions membres du vendeur (ou plus) à analyser.

Le diagramme suivant illustre l’état avant et après d’un membre de dimension vendeur dont la région de vente a changé. Étant donné que l’organisation souhaite analyser les efforts des vendeurs par la région à laquelle elles sont affectées, elle déclenche une modification de type SCD 2.

Conseil

Lorsqu’une table de dimensions prend en charge les modifications de type SCD 2, vous devez inclure un attribut d’étiquette qui décrit le membre et la version. Prenons un exemple lorsque le vendeur Lynn Tsoflias d’Adventure Works est affecté de la région de vente d’Australie à la région de vente du Royaume-Uni. L’attribut d’étiquette pour la première version peut lire « Lynn Tsoflias (Australie) » et l’attribut d’étiquette pour la nouvelle version actuelle pourrait lire « Lynn Tsoflias (Royaume-Uni) ». Si cela est utile, vous pouvez également inclure les dates de validité dans l’étiquette.

Vous devez équilibrer la nécessité d’une précision historique par rapport à la facilité d’utilisation et à l’efficacité. Essayez d’éviter d’avoir trop de modifications de type SCD 2 sur une table de dimension, car cela peut entraîner un nombre écrasant de versions qui peuvent rendre difficile la compréhension par les analystes.

En outre, trop de versions peuvent indiquer qu’un attribut changeant peut être mieux stocké dans la table de faits. Extension de l’exemple précédent, si les modifications de la région de vente étaient fréquentes, la région des ventes peut être stockée en tant que clé de dimension dans la table de faits plutôt que d’implémenter un type SCD 2.

Tenez compte des attributs de suivi historique du type SCD 2 suivants.

CREATE TABLE d_Salesperson
(
    <…>

    --Historical tracking attributes (SCD type 2)
    RecChangeDate_FK INT NOT NULL,
    RecValidFromKey INT NOT NULL,
    RecValidToKey INT NOT NULL,
    RecReason VARCHAR(15) NOT NULL,
    RecIsCurrent BIT NOT NULL,

    <…>
);

Voici les objectifs des attributs de suivi historiques.

• La colonne RecChangeDate_FK stocke la date à laquelle la modification est entrée en vigueur. Il vous permet d’interroger quand des modifications ont eu lieu.
• Les colonnes RecValidFromKey et RecValidToKey stockent les dates effectives de validité de la ligne. Envisagez de stocker la date la plus ancienne trouvée dans la dimension de date pour RecValidFromKey pour représenter la version initiale et de stocker 01/01/9999 pour le RecValidToKey des versions actuelles.
• La colonne RecReason est facultative. Il permet de documenter la raison pour laquelle la version a été insérée. Il peut encoder les attributs modifiés, ou il peut s’agir d’un code du système source qui indique une raison métier particulière.
• La colonne RecIsCurrent permet de récupérer uniquement les versions actuelles. Il est utilisé lorsque le processus ETL recherche des clés de dimension lors du chargement de tables de faits.

Remarque

Certains systèmes sources ne stockent pas les modifications historiques. Il est donc important que la dimension soit traitée régulièrement pour détecter les modifications et implémenter de nouvelles versions. De cette façon, vous pouvez détecter les modifications peu de temps après qu’elles se produisent, et leurs dates de validité seront exactes.

SCD de type 3

Les modifications du type SCD 3 suivent l’historique limité avec des attributs. Cette approche peut être utile lorsqu’il est nécessaire d’enregistrer la dernière modification ou un certain nombre des dernières modifications.

Ce type SCD conserve une perspective historique limitée. Il peut être utile lorsque seules les valeurs initiales et actuelles doivent être stockées. Dans ce cas, les modifications intermédiaires ne seraient pas requises.

Par exemple, si un vendeur est affecté à une autre région de vente, un changement de type SCD 3 remplace la ligne de dimension. Une colonne qui stocke spécifiquement la région de ventes précédente est définie comme région de ventes précédente, et la nouvelle région de vente est définie comme région de ventes actuelle.

Le diagramme suivant illustre l’état avant et après d’un membre de dimension vendeur dont la région de vente a changé. Étant donné que l’organisation souhaite déterminer toute affectation de région de vente précédente, elle déclenche une modification de type SCD 3.

Membres de dimension spéciaux

Vous pouvez insérer des lignes dans une dimension qui représentent des états manquants, inconnus, N/A ou d’erreur. Par exemple, vous pouvez utiliser les valeurs de clé de substitution suivantes.

Valeur de clé	Objectif
0	Manquant (non disponible dans le système source)
-1	Inconnu (échec de recherche lors du chargement d’une table de faits)
-2	N/A (non applicable)
-3	Error

Calendrier et horaires

Presque sans exception, les tables de faits stockent les mesures à des moments spécifiques dans le temps. Pour prendre en charge l’analyse par date (et éventuellement l’heure), il doit y avoir des dimensions de calendrier (date et heure).

Il est rare qu’un système source ait des données de dimension de calendrier. Il doit donc être généré dans l’entrepôt de données. En règle générale, il est généré une fois et, s’il s’agit d’une dimension de calendrier, il est étendu avec les dates futures si nécessaire.

Dimension de date

La dimension de date (ou de calendrier) est la dimension la plus courante utilisée pour l’analyse. Il stocke une ligne par date et prend en charge l’exigence courante de filtrer ou de regrouper des périodes spécifiques de dates, telles que des années, des trimestres ou des mois.

Important

Une dimension de date ne doit pas inclure de fragment qui s’étend à l’heure de la journée. Si l’analyse de l’heure de jour est requise, vous devez avoir à la fois une dimension de date et une dimension de temps (décrite ci-dessous). Les tables de faits qui stockent les faits de jour doivent avoir deux clés étrangères, une à chacune de ces dimensions.

La clé naturelle de la dimension de date doit utiliser le type de données date. La clé de substitution doit stocker la date à l’aide du format YYYYMMDD et du type de données int. Cette pratique acceptée doit être la seule exception (en même temps que la dimension de temps) lorsque la valeur de clé de substitution a une signification et est lisible par l’homme. Le stockage de YYYYMMDD en tant que type de données int n’est pas seulement efficace et trié numériquement, mais il est également conforme au format de date non ambiguë ISO 8601 (International Standards Organization).

Voici quelques attributs courants à inclure dans une dimension de date.

• Year, Quarter, Month, Day
• QuarterNumberInYear, MonthNumberInYear – qui peut être nécessaire pour trier les étiquettes de texte.
• FiscalYear, FiscalQuarter – certaines planifications comptables d’entreprise commencent à la mi-année, afin que le début/la fin de l’année civile et l’année fiscale soient différents.
• FiscalQuarterNumberInYear, FiscalMonthNumberInYear – qui peut être nécessaire pour trier les étiquettes de texte.
• WeekOfYear : il existe plusieurs façons d’étiqueter la semaine de l’année, y compris une norme ISO qui a 52 ou 53 semaines.
• IsHoliday, HolidayText – si votre organisation opère dans plusieurs zones géographiques, vous devez conserver plusieurs ensembles de listes de congés que chaque zone géographique observe comme une dimension distincte ou naturalisée dans plusieurs attributs dans la dimension de date. L’ajout d’un attribut HolidayText peut aider à identifier les jours fériés pour la création de rapports.
• IsWeekday – de même, dans certaines zones géographiques, la semaine de travail standard n’est pas du lundi à vendredi. Par exemple, la semaine de travail va de dimanche à jeudi dans de nombreuses régions du Moyen-Orient, tandis que d’autres régions emploient une semaine de travail de quatre jours ou six jours.
• LastDayOfMonth
• RelativeYearOffset, RelativeQuarterOffset, RelativeMonthOffset, RelativeDayOffset – qui peut être nécessaire pour prendre en charge le filtrage de date relative (par ex. le mois précédent). Les périodes actuelles utilisent un décalage de zéro (0) ; les périodes précédentes stockent les décalages de -1, -2, -3... ; les futures périodes stockent les décalages de 1, 2, 3....

Comme pour n’importe quelle dimension, il est important de contenir des attributs qui prennent en charge les exigences de filtrage, de regroupement et de hiérarchie connues. Il peut également y avoir des attributs qui stockent des traductions d’étiquettes dans d’autres langues.

Lorsque la dimension est utilisée pour se rapporter à des faits plus élevés, la table de faits peut utiliser la première date de la période de date. Par exemple, une table de faits cible des ventes qui stocke les cibles des vendeurs trimestriels stocke la première date du trimestre dans la dimension de date. Une autre approche consiste à créer des colonnes clés dans la table de dates. Par exemple, une clé de trimestre peut stocker la clé de trimestre à l’aide du format YYYYQ et du type de données smallint.

La dimension doit être remplie avec la plage connue de dates utilisées par toutes les tables de faits. Il doit également inclure des dates futures lorsque l’entrepôt de données stocke des faits sur les cibles, les budgets ou les prévisions. Comme avec d’autres dimensions, vous pouvez inclure des lignes qui représentent des situations manquantes, inconnues, N/A ou d’erreur.

Conseil

Recherchez sur Internet « générateur de dimension de date » pour rechercher des scripts et des feuilles de calcul qui génèrent des données de date.

En règle générale, au début de l’année suivante, le processus ETL doit étendre les lignes de dimension de date à un nombre spécifique d’années à venir. Lorsque la dimension 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).

Dimension de temps

Parfois, les faits doivent être stockés à un moment donné (comme dans l’heure de la journée). Dans ce cas, créez une dimension d’heure (ou d’horloge). Il peut avoir un grain en minutes (24 x 60 = 1 440 lignes) ou même en secondes (24 x 60 x 60 = 86 400 lignes). Les autres grains possibles incluent la demi-heure ou l’heure.

La clé naturelle d’une dimension de temps doit utiliser le type de données time. La clé de substitution peut utiliser un format approprié et stocker des valeurs qui ont une signification et sont lisibles par l’homme, par exemple, à l’aide du format HHMM ou HHMMSS.

Voici quelques attributs courants à inclure dans une dimension de temps.

• Hour, HalfHour, QuarterHour, Minute
• Étiquettes de période (matin, après-midi, soir, nuit)
• Noms des postes de travail
• Indicateurs de pointe ou hors pointe

Dimensions conformes

Certaines dimensions peuvent être des dimensions conformes. Les dimensions conformes sont liées à de nombreuses tables de faits et sont donc partagées par plusieurs étoiles dans un modèle dimensionnel. Elles offrent une cohérence et peuvent vous aider à réduire le développement et la maintenance en cours.

Par exemple, il est courant que les tables de faits stockent au moins une clé de dimension de date (car l’activité est presque toujours enregistrée par date et/ou heure). Pour cette raison, une dimension de date est une dimension conforme commune. Vous devez donc vous assurer que votre dimension de date inclut des attributs pertinents pour l’analyse de toutes les tables de faits.

Le diagramme suivant montre la table de faits Sales et la table de faits Inventory. Chaque table de faits est liée à la dimension Date et à la dimension Product, qui sont conformes aux dimensions.

En guise d’autre exemple, votre employé et vos utilisateurs peuvent être le même ensemble de personnes. Dans ce cas, il peut être judicieux de combiner les attributs de chaque entité pour produire une dimension conforme.

Dimensions de rôle actif

Lorsqu’une dimension est référencée plusieurs fois dans une table de faits, elle est appelée dimension de rôle actif.

Par exemple, lorsqu’une table de faits de vente a des clés de dimension de date de commande, de date d’expédition et de date de livraison, la dimension de date est liée de trois manières. Chaque manière représente un rôle distinct, mais il n’existe qu’une seule dimension de date physique.

Le diagramme suivant illustre une table de faits Flight. La dimension Airport est une dimension de rôle actif, car elle est liée deux fois à la table de faits en tant que dimension Departure Airport et dimension Arrival Airport.

Dimensions fourre-tout

Une dimension fourre-tout est utile quand il existe de nombreuses dimensions indépendantes, en particulier composées de quelques attributs (peut-être un seul) et lorsque ces attributs ont une faible cardinalité (peu de valeurs). L’objectif d’une dimension de courrier indésirable est de consolider de nombreuses petites dimensions en une seule dimension. Cette approche de conception peut réduire le nombre de dimensions, et diminuer le nombre de clés de table de faits et ainsi la taille de stockage de table de faits. Elle aide également à réduire l’encombrement du volet Données, car elle présente moins de tables aux utilisateurs.

Une table de dimension de courrier indésirable est généralement le produit cartésien de toutes les valeurs d’attribut de dimension, avec une colonne de clé de substitution.

Les bons candidats incluent les indicateurs, l’état des commandes et les états démographiques des clients (sexe, groupe d’âge et autres).

Le diagramme suivant illustre une dimension de courrier indésirable nommée Sales Status qui combine les valeurs de statut de commande et les valeurs d’état de remise.

Dimensions dégénérées

Une dimension dégénérée peut se produire lorsque la dimension est au même fragment que les faits connexes. Un exemple courant d’une dimension dégénérée est une dimension de numéro de commande client qui se rapporte à une table de faits de ventes. En règle générale, le numéro de facture est un attribut unique et non hiérarchique dans la table de faits. Par conséquent, il est recommandé de ne pas copier ces données pour créer une table de dimension distincte.

Le diagramme suivant illustre une dimension Sales Order qui est une dimension dégénérée basée sur la colonne SalesOrderNumber d’une table de faits de ventes. Cette dimension est implémentée en tant qu’affichage qui récupère les valeurs de numéro de commande vente distinctes.

Conseil

Il est possible de créer une vue dans Fabric Warehouse qui présente la dimension dégénérée en tant que dimension à des fins de requête.

Du point de vue de la modélisation sémantique Power BI, une dimension dégénérée peut être créée en tant que table distincte à l’aide de Power Query. De cette façon, le modèle sémantique est conforme aux meilleures pratiques indiquant que les champs utilisés pour filtrer ou regrouper sont générés à partir de tables de dimension, et que les champs utilisés pour synthétiser les faits sont générés à partir de tables de faits.

Sous-dimensions

Lorsqu’une table de dimension est liée à d’autres tables de dimension, elle est appelée sous-dimension. Une sous-dimension peut contribuer à la conformité et à la réutilisation des définitions dans le modèle dimensionnel.

Par exemple, vous pouvez créer une dimension géographique qui stocke des emplacements géographiques pour chaque code postal. Cette dimension peut ensuite être référencée par la dimension client et la dimension vendeur, qui stockerait la clé de substitution de la dimension géographique. De cette façon, les clients et les vendeurs peuvent ensuite être analysés à l’aide d’emplacements géographiques cohérents.

Le diagramme suivant illustre une dimension Geography qui est une sous-dimension. Elle n’est pas directement liée à la table de faits Sales. Au lieu de cela, elle est liée indirectement par le biais de la dimension Customer et de la dimension Salesperson.

Considérez que la dimension de date peut être utilisée comme sous-dimension lorsque d’autres attributs de table de dimension stockent des dates. Par exemple, la date de naissance dans une dimension client peut être stockée à l’aide de la clé de substitution de la table de dimension de date.

Dimensions à valeurs multiples

Lorsqu’un attribut de dimension doit stocker plusieurs valeurs, vous devez concevoir une dimension à valeurs multiples. Vous implémentez une dimension à valeurs multiples en créant une table de pont (parfois appelée table de jointure). Une table de pont stocke une relation plusieurs-à-plusieurs entre les entités.

Par exemple, considérez qu’il existe une dimension vendeur et que chaque vendeur est affecté à une ou éventuellement plusieurs régions de vente. Dans ce cas, il est judicieux de créer une dimension de région de vente. Cette dimension stocke chaque région de vente une seule fois. Une table distincte, appelée table de pont, stocke une ligne pour chaque vendeur et chaque relation de région de vente. Physiquement, il existe une relation un-à-plusieurs de la dimension vendeur vers la table de pont, et une autre relation un-à-plusieurs de la dimension région de vente à la table de pont. Il y a une relation plusieurs-à-plusieurs entre le vendeur et la région.

Dans le diagramme suivant, la table de dimensions Account est liée à la table de faits Transaction. Étant donné que les clients peuvent avoir plusieurs comptes et que les comptes peuvent avoir plusieurs clients, la table de dimension Customer est liée via la table de pont Customer Account.

Contenu connexe

Dans l’article suivant de cette série, découvrez les conseils et les meilleures pratiques de conception pour les tables de faits.