Conseils sur les relations plusieurs-à-plusieurs

Cet article vous cible en tant que modélisateur de données qui fonctionne avec Power BI Desktop. Il décrit trois scénarios différents de modélisation plusieurs à plusieurs. Il vous fournit également des conseils sur la façon de les concevoir correctement dans vos modèles.

Note

Une introduction aux relations de modèle n’est pas abordée dans cet article. Si vous n’êtes pas complètement familiarisé avec les relations, leurs propriétés ou comment les configurer, nous vous recommandons de lire d’abord les relations de modèle dans l’article Power BI Desktop.

Il est également important de comprendre la conception de schémas en étoile. Pour plus d'informations, consultez Comprendre le schéma en étoile et son importance pour Power BI.

Il existe trois différents scénarios plusieurs à plusieurs. Ils peuvent se produire lorsque vous êtes tenu de :

• Lier deux tables de dimensions
• Lier deux tables de faits
• Relier les tables de faits à niveau de détail plus élevé, lorsque la table de faits stocke des lignes à un niveau de détail supérieur à celui des lignes de la table de dimensions

Associer des dimensions plusieurs à plusieurs

Le scénario classique plusieurs-à-plusieurs concerne deux entités, par exemple les clients bancaires et les comptes bancaires. Considérez que les clients peuvent avoir plusieurs comptes et que les comptes peuvent avoir plusieurs clients. Lorsqu’un compte a plusieurs clients, ils sont couramment appelés titulaires d'un compte commun.

La modélisation de ces entités est immédiate. Une table de dimension stocke les comptes, et une autre table de dimension stocke les clients. Comme c'est caractéristique des tables de dimension, on trouve une colonne d’identificateur unique (ID) dans chaque table. Pour modéliser la relation entre les deux tables, une troisième table est requise. Cette table est généralement appelée table de pontage. Dans cet exemple, il s’agit de stocker une ligne pour chaque association de compte client. Il est intéressant de voir que lorsque cette table contient uniquement des colonnes d'identificateurs, elle est appelée table de faits sans faits.

Voici un diagramme simpliste des trois tables de modèle.

La première table est nommée Accountet contient deux colonnes : AccountID et Account. La deuxième table est nommée AccountCustomeret contient deux colonnes : AccountID et CustomerID. La troisième table est nommée Customeret contient deux colonnes : CustomerID et Customer. Les relations n’existent pas entre les tables.

Deux relations de type un-à-plusieurs sont ajoutées pour associer les tables. Voici un diagramme de modèle mis à jour des tables associées. Une table de faits nommée Transaction a été ajoutée. Il enregistre les transactions de compte. La table de liaison et toutes les colonnes d’identificateur ont été masquées.

Pour vous aider à décrire le fonctionnement de la propagation du filtre de relation, le diagramme de modèle a été modifié pour révéler les lignes de la table.

Les détails des lignes des quatre tables figurent dans la liste à puces suivante :

• La table Account comporte deux lignes :
  • AccountID 1 concerne Account-01
  • AccountID 2 concerne Account-02
• La table Customer comporte deux lignes :
  • CustomerID91 concerne Client-91
  • CustomerID 92 est pour Customer-92
• La table AccountCustomer comporte trois lignes :
  • AccountID1 est associé à CustomerID91
  • AccountID1 est associé à CustomerID92
  • AccountID2 est associé à CustomerID92
• La table Transaction comporte trois lignes :
  • Date 1er janvier 2019, AccountID1, Amount100
  • Date2 février 2019, AccountID2, Amount200
  • Date3 mars 2019, AccountID1, Amount-25

Voyons ce qui se passe quand le modèle est interrogé.

Dans l’image suivante, il existe deux visuels de tableau qui résument la colonne Amount de la table Transaction. Le premier visuel effectuant un regroupement par compte, la somme des colonnes Amount représente le solde du compte. Le deuxième visuel est groupé par client, et la somme des colonnes Amount représente le solde client .

Le premier visuel de table (Solde de compte) comporte deux colonnes : Account et Amount. Il affiche le résultat suivant :

• Le montant du solde pour Account-01 est égal à 75.
• Le montant du solde du compte Account-02 est 200.
• Le total est égal à 275.

Le deuxième visuel de table (Customer Balance) comporte deux colonnes : Customer et Amount. Il affiche le résultat suivant :

• Le montant du solde pour Customer-91 est égal à 275.
• Le montant du solde pour Customer-92 est égal à 275.
• Le total est égal à 275.

Un coup d’œil rapide sur les lignes de la table et le visuel Solde du compte révèle que le résultat est correct, pour chaque compte et le montant total. Cela est dû au fait que chaque regroupement de comptes entraîne une propagation de filtre dans la table Transaction pour ce compte.

Toutefois, quelque chose ne semble pas correct avec le visuel du solde client. Chaque client de ce visuel a le même solde que le solde total. Ce résultat ne pouvait être correct que si chaque client était un titulaire conjoint de tous les comptes. Ce n’est pas le cas dans cet exemple. Il y a un problème, et il est lié à la propagation du filtre. Les filtres ne parviennent pas jusqu’à la table Transaction.

Si vous suivez les instructions de filtre de relation de la table Customer vers la table Transaction, vous pouvez déterminer que la relation entre les tables Account et AccountCustomer se propage dans une direction incorrecte. La direction du filtre pour cette relation doit être définie sur Both.

Comme prévu, aucun changement n’a été apporté au visuel Solde du compte.

Toutefois, le visuel Solde du client affiche maintenant le résultat suivant :

• Le montant du solde pour Customer-91 est égal à 75.
• Le montant du solde pour Customer-92 est égal à 275.
• Le total est égal à 275.

Le visuel Solde du client affiche désormais un résultat correct. Suivez les instructions de filtre pour vous-même et découvrez comment les soldes des clients ont été calculés. En outre, sachez que la valeur totale affichée englobe tous les clients.

Quelqu’un qui n’est pas familiarisé avec les relations de modèle peut conclure que le résultat est incorrect. Ils peuvent demander : Pourquoi n’est-ce pas le solde total de Customer-91 et Customer-92 égal à 350 (75 + 275) ?

La réponse à leur question réside dans la compréhension de la relation plusieurs-à-plusieurs. Chaque solde client peut représenter l’ajout de plusieurs soldes de compte, et les soldes clients sont donc non additifs.

Conseils sur l’association des dimensions plusieurs à plusieurs

Lorsque vous avez une relation plusieurs à plusieurs entre les tables de dimension, suivez ces instructions :

• Ajoutez chaque entité associée à une relation plusieurs-à-plusieurs en tant que table de type modèle, en vous assurant qu'elle a une colonne ID.
• Ajoutez une table de pontage pour stocker les entités associées.
• Créez des relationsun-à-plusieurs entre les trois tables.
• Définissez une relation bidirectionnelle pour permettre la propagation des filtres jusqu'à la table de faits.
• Lorsqu’il n’est pas approprié d’avoir des valeurs d’ID manquantes, désactivez la propriété Is Nullable : l’actualisation des données échoue lorsque les valeurs manquantes sont sources.
• Masquez la table de pontage (sauf si elle contient d’autres colonnes ou mesures requises pour la création de rapports).
• Masquez les colonnes d’ID qui ne conviennent pas à la création de rapports (par exemple, lorsque les colonnes stockent les valeurs de clé de substitution).
• S’il est judicieux de conserver une colonne ID visible, vérifiez qu’elle se trouve sur le côté « un » de la relation, et masquez toujours la colonne côté « plusieurs ». Cela est dû au fait que les filtres appliqués à une diapositive spécifique entraînent une meilleure performance des filtres.
• Pour éviter toute confusion ou mauvaise interprétation, communiquez les explications aux utilisateurs de votre rapport : vous pouvez ajouter des descriptions avec des zones de texte ou des info-bulles d’en-tête de visuel.

Nous vous déconseillons de lier directement des tables de dimension plusieurs à plusieurs. Cette approche de conception nécessite la configuration d’une relation avec une cardinalité plusieurs à plusieurs. Conceptuellement, elle peut être obtenue, mais implique que les colonnes associées peuvent contenir des valeurs en double. Toutefois, il s’agit d’une pratique de conception bien acceptée, que les tables de dimension ont une colonne ID. Les tables de dimension doivent toujours utiliser la colonne ID comme côté « un » d’une relation.

Associer des faits plusieurs à plusieurs

Un autre type de scénario plusieurs à plusieurs implique la liaison de deux tables de faits. Deux tables de faits peuvent être liées directement. Cette technique de conception peut être utile pour l’exploration rapide et simple des données. Toutefois, et pour être clair, nous ne recommandons généralement pas cette approche de conception. Nous expliquerons pourquoi plus loin dans cette section.

Prenons un exemple qui implique deux tables de faits : Order et Fulfillment. La table Order contient une ligne par ligne de commande et la table Fulfillment peut contenir zéro ou plusieurs lignes par ligne de commande. Les lignes de la table Order représentent les commandes de vente. Les lignes de la table Fulfillment représentent les articles de commande qui ont été expédiés. Une relation plusieurs-à-plusieurs relie les colonnes OrderID de chaque table, avec une transmission du filtre uniquement à partir de la table Order (ce qui signifie que la table Order filtre la table Fulfillment).

La cardinalité de relation est définie sur Many-to-many pour prendre en charge le stockage des valeurs de colonnes dupliquées OrderID dans les deux tables. Dans la table Order, les valeurs d’ID dupliquées peuvent exister, car un ordre peut avoir plusieurs lignes. Dans la table Fulfillment, les valeurs d’ID dupliquées peuvent exister, car les commandes peuvent avoir plusieurs lignes et les lignes de commande peuvent être remplies par de nombreuses expéditions.

Examinons maintenant les lignes de la table. Dans le tableau Fulfillment, notez que les lignes de commande peuvent être remplies par plusieurs expéditions. (L’absence d’une ligne de commande signifie que l’ordre n’est pas encore rempli.)

Les détails des lignes pour les deux tables sont décrits dans la liste à puces suivante :

• La table Order comporte cinq lignes :
  • OrderDate1er janvier 2019, OrderID1, OrderLine1, ProductIDProd-A, OrderQuantity5, Sales50
  • OrderDate1er janvier 2019, OrderID1, OrderLine2, ProductIDProd-B, OrderQuantity10, Sales80
  • OrderDate2 février 2019, OrderID2, OrderLine1, ProductIDProd-B, OrderQuantity5, Sales40
  • OrderDate 2 février 2019, OrderID2, OrderLine2, ProductIDProd-C, OrderQuantity1, Sales20
  • OrderDate3 mars 2019, OrderID3, OrderLine1, ProductIDProd-C, OrderQuantity5, Sales100
• La table Fulfillment comporte quatre lignes :
  • FulfillmentDate1er janvier 2019, FulfillmentID50, OrderID1, OrderLine1, FulfillmentQuantity2
  • FulfillmentDate2 février 2019, FulfillmentID51, OrderID2, OrderLine1, FulfillmentQuantity5
  • FulfillmentDate2 février 2019, FulfillmentID52, OrderID1, OrderLine1, FulfillmentQuantity3
  • FulfillmentDate1er janvier 2019, FulfillmentID53, OrderID1, OrderLine2, FulfillmentQuantity10

Voyons ce qui se passe quand le modèle est interrogé. Voici un visuel de table qui compare les quantités commandées et traitées selon la colonne OrderID de la table Order.

Le visuel présente un résultat précis. Toutefois, l’utilité du modèle est limitée, car vous ne pouvez filtrer ou regrouper que par la table OrderOrderID colonne.

Conseils sur l’association des faits plusieurs à plusieurs

En règle générale, nous vous déconseillons de lier deux tables de faits directement à l’aide de la cardinalité plusieurs à plusieurs. La raison principale est que le modèle ne fournit pas de flexibilité dans la façon dont vos visuels de rapport filtrent ou regroupent. Dans l'exemple, les visuels ne peuvent être filtrés ou regroupés que par la colonne OrderID de la table Order. Une autre raison concerne la qualité de vos données. Si vos données ont des problèmes d'intégrité, il est possible que certaines lignes soient omises lors de l'interrogation en raison de la cardinalité plusieurs à plusieurs et des relations limitées.

Au lieu de relier directement des tables de faits, nous vous recommandons de mettre en œuvre une conception de schéma en étoile. Cela signifie que vous ajoutez des tables de dimension. Ces tables de dimension sont ensuite liées aux tables de faits à l’aide de relations un-à-plusieurs. Cette approche de conception est robuste, car elle offre efficacement des options de création de rapports flexibles. Il vous permet de filtrer ou de regrouper à l’aide de l’une des colonnes de la table de dimension et de synthétiser les colonnes d’une table de faits associée.

Prenons une meilleure solution.

Notez les modifications de conception suivantes :

• Le modèle dispose désormais de quatre tables supplémentaires : OrderLine, OrderDate, Productet FulfillmentDate.
• Les quatre tables supplémentaires sont toutes des tables de dimension reliées par des relations un-à-plusieurs aux tables de faits.
• La table OrderLine contient la colonne OrderLineID, qui stocke la valeur de OrderID multipliée par 100, ainsi que la valeur de colonne OrderLine , un ID pour chaque ligne de commande.
• Les tables Order et Fulfillment contiennent désormais chacune une colonne OrderLineID, et elles ne contiennent plus les colonnes OrderID et OrderLine.
• La table Fulfillment contient désormais des colonnes OrderDate et ProductID.
• La table FulfillmentDate a une relation uniquement avec la table Fulfillment.
• Toutes les colonnes d’ID sont masquées.

Prendre le temps d’adopter une conception de schéma en étoile offre les avantages suivants :

• Vos visuels de rapport peuvent être filtrés ou regroupés par n’importe quelle colonne visible des tables de dimension.
• Vos visuels de rapport peuvent résumer n’importe quelle colonne visible des tables de fait.
• Les filtres appliqués aux tables OrderLine, OrderDateou Product se propagent aux deux tables de faits.
• Toutes les relations sont de type un-à-plusieurs, et chaque relation est une relation régulière . Les problèmes d’intégrité des données ne seront pas masqués. Pour plus d’informations sur l’évaluation des relations, consultez Relations de modèle dans Power BI Desktop.

Relier les faits à niveau de détail plus élevé

Ce scénario plusieurs-à-plusieurs est très différent des deux autres déjà décrits dans cet article.

Prenons un exemple impliquant quatre tables : Date, Sales, Productet Target. Les tables Date et Product sont des tables de dimension, et chaque relation un-à-plusieurs relie chacune d'elles à la table de faits Sales. Jusqu’à présent, il s’agit d’une bonne conception de schéma en étoile. Toutefois, la table Target n’est pas encore liée aux autres tables.

La table Target contient trois colonnes : Category, TargetQuantityet TargetYear. Les lignes du tableau révèlent une granularité de l’année et de la catégorie de produit. En d’autres termes, les cibles utilisées pour mesurer les performances des ventes sont définies chaque année pour chaque catégorie de produit.

Étant donné que la table Target stocke des données à un niveau supérieur aux tables de dimension, une relation un-à-plusieurs ne peut pas être créée. Eh bien, c’est vrai pour une seule des relations. Examinons comment la table Target peut être liée aux tables de dimension.

Relier les périodes de temps à niveau de détail plus élevé

Une relation entre les tables Date et Target devrait être de type un-à-plusieurs. Cela est dû au fait que les valeurs de colonne TargetYear sont des dates. Dans cet exemple, chaque colonne TargetYear stocke la première date de l’année cible.

Conseil

Lorsque vous stockez des faits à une granularité de temps supérieure à la journée, définissez le type de données de colonne sur Date (ou nombre entier si vous utilisez des clés de date). Dans la colonne, stockez une valeur représentant le premier jour de la période. Par exemple, une période d’année est enregistrée comme le 1er janvier de l’année et une période de mois est enregistrée comme premier jour de ce mois.

Toutefois, vous devez veiller à ce que les filtres de niveau de mois ou de date produisent un résultat significatif. Sans logique de calcul spéciale, les visuels de rapport peuvent signaler que les dates cibles sont littéralement le premier jour de chaque année. Tous les autres jours, et tous les mois sauf janvier, récapitulent la quantité cible comme VIDE.

Le visuel de matrice suivant montre ce qui se passe lorsque l’utilisateur du rapport explore les données d’une année jusqu’au niveau des mois. Le visuel récapitule la colonne TargetQuantity. (L’option Afficher les éléments sans données a été activée pour les lignes de la matrice.)

Pour éviter ce comportement, nous vous recommandons de contrôler la synthèse de vos données de faits à l’aide de mesures. Une façon de contrôler le résumé consiste à retourner BLANK lorsque des périodes de niveau inférieur sont interrogées. Une autre façon, définie avec un DAX sophistiqué, consiste à répartir les valeurs entre les périodes de bas niveau.

Considérez la définition de mesure suivante qui utilise la fonction DAX ISFILTERED. Elle retourne uniquement une valeur lorsque les colonnes Date et Month ne sont pas filtrées.

Target Quantity =
IF(
    NOT ISFILTERED('Date'[Date])
        && NOT ISFILTERED('Date'[Month]),
    SUM(Target[TargetQuantity])
)

Le visuel de matrice suivant utilise la mesure Target Quantity. Elle indique que toutes les quantités cibles mensuelles sont VIDES.

Relier à niveau de détail plus élevé (non daté)

Une approche de conception différente est requise lorsqu'il s'agit de relier une colonne non date d'une table de dimension à une table de faits (et qu'elle est à un niveau de granularité supérieur à celui de la table de dimension).

Les colonnes Category (des tables Product et Target) contiennent des valeurs en double. Il n’y a donc pas de côté « un » pour une relation de type un-à-plusieurs. Dans ce cas, vous devez créer une relation plusieurs à plusieurs. La relation doit propager des filtres dans une direction unique, de la table de dimension à la table de faits.

Examinons maintenant les lignes de la table.

Dans le tableau Target, il existe quatre lignes : deux lignes pour chaque année cible (2019 et 2020) et deux catégories (vêtements et accessoires). Dans la table Product, il existe trois produits. Deux appartiennent à la catégorie vêtements, et l’autre appartient à la catégorie accessoires. L’une des couleurs de vêtements est verte, et les deux restantes sont bleues.

Un visuel de table groupé par la colonne Category de la table Product produit le résultat suivant. Toutefois, ce visuel produit le résultat correct. Prenons maintenant en compte ce qui se passe lorsque la colonne Color de la table Product est utilisée pour regrouper la quantité cible.

Le visuel produit une fausse représentation des données. Qu’est-ce qui se passe ici ?

Un filtre sur la colonne Color de la table Product génère deux lignes. L’une des lignes est pour la catégorie Vêtements, et l’autre pour la catégorie Accessoires. Ces deux valeurs de catégorie sont propagées en tant que filtres dans la table Target. En d’autres termes, étant donné que la couleur bleue est utilisée par les produits de deux catégories, ces catégories sont utilisées pour filtrer les cibles.

Pour éviter ce comportement, comme décrit précédemment, nous vous recommandons de contrôler la synthèse de vos données de faits à l’aide de mesures.

Considérez la définition de mesure suivante. Notez que toutes les colonnes de table Product qui se trouvent sous le niveau de catégorie sont testées pour les filtres.

Target Quantity =
IF(
    NOT ISFILTERED('Product'[ProductID])
        && NOT ISFILTERED('Product'[Product])
        && NOT ISFILTERED('Product'[Color]),
    SUM(Target[TargetQuantity])
)

Le visuel de table suivant utilise la mesure Target Quantity. Il indique que toutes les quantités cibles de couleur sont VIDES.

La conception finale du modèle ressemble à ce qui suit.

Conseils pour relier les faits à niveau de détail plus élevé

Lorsque vous devez associer une table de dimension à une table de faits et que la table de faits stocke les lignes à une granularité plus fine que celle des lignes de la table de dimension, suivez ces instructions :

• Pour les dates de faits à niveau de détail plus élevé
  • Dans la table des faits, stockez la première date de la période.
  • Créez une relation un-à-plusieurs entre la table de dates et la table de faits.
• Pour les autres faits à niveau de détail plus élevé
  • Créez une relation plusieurs à plusieurs entre la table de dimension et la table de faits.
• Pour les deux types
  • Contrôlez la synthèse avec la logique de mesure : retournez BLANK lorsque des colonnes de dimension de niveau inférieur sont utilisées pour filtrer ou regrouper.
  • Masquez les colonnes de table de faits récapitulables, ce qui garantit que seules les mesures peuvent être utilisées pour résumer la table de faits.

Contenu connexe

Pour plus d’informations sur cet article, consultez les ressources suivantes :

• Relations de modèle dans Power BI Desktop
• Comprendre le schéma en étoile et l’importance de Power BI
• guide de résolution des problèmes liés aux relations
• Questions ? Essayez de demander à la communauté Fabric
• Avez-vous des suggestions ? Envoyez-nous vos idées pour améliorer Fabric