Relations entre les tables dans un modèle de données

Votre navigateur ne prend pas en charge la vidéo. Installez Microsoft Silverlight, Adobe Flash Player ou Internet Explorer 9.
Vidéo : Relations dans Power View et PowerPivot

Pour donner encore plus de puissance à l’analyse de vos données, créez des relations entre les données de différentes tables. Une relation est une connexion entre deux tables de données, basée sur une colonne dans chaque table. Pour comprendre pourquoi les relations sont utiles, imaginez que vous effectuez le suivi des données des commandes client dans votre entreprise. Vous pouvez effectuer le suivi de toutes les données dans une table individuelle possédant une structure similaire à :

CustomerID

Nom

EMail

DiscountRate

OrderID

OrderDate

Product

Quantity

1

Ashton

chris.ashton@contoso.com

0,05

256

2010-01-07

Compact Digital

11

1

Ashton

chris.ashton@contoso.com

0,05

255

2010-01-03

SLR Camera

15

2

Jaworski

michal.jaworski@contoso.com

0,10

254

2010-01-03

Budget Movie-Maker

27

Cette approche peut fonctionner, mais elle implique le stockage de nombreuses données redondantes, telles que l’adresse de courrier du client pour chaque commande. Le stockage est bon marché, mais si l’adresse de courrier change, vous devez vous assurer de mettre à jour chaque ligne pour ce client. Une solution à ce problème consiste à fractionner les données en plusieurs tables et à définir des relations entre ces tables. Il s’agit de l’approche utilisée dans les bases de données relationnelles comme SQL Server. Par exemple, une base de données que vous importez peut représenter des données de commandes en utilisant trois tables associées :

Clients

[CustomerID]

Nom

Email

1

Ashton

chris.ashton@contoso.com

2

Jaworski

michal.jaworski@contoso.com

CustomerDiscounts

[CustomerID]

DiscountRate

1

0,05

2

0,10

Orders

[CustomerID]

OrderID

OrderDate

Product

Quantity

1

256

2010-01-07

Compact Digital

11

1

255

2010-01-03

SLR Camera

15

2

254

2010-01-03

Budget Movie-Maker

27

Des relations existent dans un modèle de données : vous pouvez les créer explicitement, ou bien Excel les crée pour vous lorsque vous importez plusieurs tables simultanément. Vous pouvez également utiliser le complément Power Pivot pour créer ou gérer le modèle. Pour en savoir plus, consultez Créer un modèle de données dans Excel.

Si vous utilisez le complément Power Pivot pour importer des tables à partir de la même base de données, Power Pivot peut détecter les relations entre les tables en fonction des colonnes qui sont entre [crochets] et reproduire ces relations dans un modèle de données qu’il génère en arrière-plan. Pour plus d’informations, consultez Détection automatique et inférence des relations dans cet article. Si vous importez des tables de plusieurs sources, vous pouvez créer manuellement des relations, comme décrit dans Créer une relation entre deux tables.

Haut de la page

Dans cet article

Colonnes et clés

Types de relations

Relations et performances

Plusieurs relations entre les tables

Conditions requises pour une relation entre tables

Non pris en charge dans une relation entre tables

Clés composites et colonnes de recherche

Relations plusieurs-à-plusieurs

Jointures réflexives et boucles

Détection automatique et inférence des relations dans PowerPivot

Détection automatique pour les jeux nommés

Inférence des relations

Colonnes et clés

Les relations sont basées sur les colonnes des tables qui contiennent les mêmes données. Par exemple, les tables Customers et Orders peuvent être associées l’une à l’autre car elles contiennent toutes les deux une colonne qui stocke un ID de client. Dans cet exemple, les noms des colonnes sont les mêmes, mais ce n’est pas nécessaire. L’un deux peut être CustomerID et un autre CustomerNumber, tant que toutes les lignes de la table Orders contiennent un ID qui est également stocké dans la table Customers.

Dans une base de données relationnelle, il existe plusieurs types de clés, qui sont en général simplement des colonnes avec des propriétés spéciales. Le fait de comprendre le rôle de chaque clé peut vous aider à gérer un modèle de données à plusieurs tables, qui fournit des données à un tableau croisé dynamique, un graphique croisé dynamique ou un rapport Power View.

Les clés suivantes sont particulièrement intéressantes en ce qui nous concerne :

  • Clé primaire : identifie de façon unique une ligne dans une table, telle que CustomerID dans la table Customers.

  • Clé secondaire (ou clé candidate) : une colonne autre que la clé primaire qui est unique. Par exemple, une table Employees peut stocker un ID d’employé et un numéro de sécurité sociale, qui sont tous les deux uniques.

  • Clé étrangère : une colonne qui fait référence à une colonne unique dans une autre table, telle que CustomerID dans la table Orders, laquelle fait référence à CustomerID dans la table Customers.

Dans un modèle de données, la clé primaire ou la clé secondaire est dite colonne associée. Si une table possède à la fois une clé primaire et une clé secondaire, vous pouvez utiliser l’une ou l’autre comme base pour une relation entre tables. La clé étrangère est connue sous le nom de colonne source ou plus simplement colonne. Dans notre exemple, une relation est définie entre CustomerID dans la table Orders (la colonne) et CustomerID (la colonne de recherche) dans la table Customers. Si vous importez des données à partir d’une base de données relationnelle, Excel choisit par défaut la clé étrangère dans une table et la clé primaire correspondante dans l’autre table. Toutefois, vous pouvez utiliser toute colonne possédant des valeurs uniques comme colonne de recherche.

Types de relations

La relation entre les clients et les commandes est une relation un-à-plusieurs. Chaque client peut avoir plusieurs commandes, mais une commande ne peut pas avoir plusieurs clients. Les autres types de relations sont un-à-un et plusieurs à plusieurs. La table CustomerDiscounts, qui définit un taux d’escompte unique pour chaque client, a une relation un-à-un avec la table Customers.

Le tableau ci-dessous indique les relations entre les trois tables :

Relation

Type

Colonne de recherche

Colonne

Customers-CustomerDiscounts

un-à-un

Customers.CustomerID

CustomerDiscounts.CustomerID

Customers-Orders

un-à-plusieurs

Customers.CustomerID

Orders.CustomerID

Remarque :  Les relations plusieurs-à-plusieurs ne sont pas prises en charge dans un modèle de données. Un exemple de relation plusieurs à plusieurs est une relation directe entre Products et Customers, dans laquelle un client peut acheter plusieurs produits et un même produit peut être acheté par plusieurs clients.

Relations et performances

Après la création de toute relation, Excel doit normalement recalculer toutes les formules qui utilisent des colonnes des tables de la relation nouvellement créée. Le traitement peut prendre du temps, selon la quantité de données et la complexité des relations. Pour plus d’informations, consultez Recalculer des formules.

Plusieurs relations entre les tables

Un modèle de données peut avoir plusieurs relations entre deux tables. Pour créer des calculs exacts, Excel a besoin d’un chemin d’accès unique entre une table et la table suivante. Par conséquent, il ne peut y avoir qu’une seule relation active à un moment donné entre chaque paire de tables. Les autres sont inactives, mais vous pouvez spécifier une relation inactive dans les formules et les requêtes. Dans la vue de diagramme, la relation active est représentée par un trait continu et celles inactives par des lignes en pointillés. Par exemple, dans AdventureWorksDW2012, la table DimDate contient une colonne DateKey qui est associée à trois colonnes différentes dans la table FactInternetSales : OrderDate, DueDate et ShipDate. Si la relation active se situe entre DateKey et OrderDate, il s’agit de la relation par défaut dans les formules, sauf spécification contraire de votre part.

Haut de la page

Conditions requises pour une relation entre tables

Une relation peut être créée lorsque les conditions suivantes sont remplies :

Critères

Description

Identificateur unique pour chaque table

Chaque table doit avoir une colonne unique qui identifie de façon unique chaque ligne dans cette table. Cette colonne est communément appelée clé primaire.

Colonnes de recherche uniques

Les valeurs des données dans la colonne de recherche doivent être uniques. En d’autres termes, la colonne ne doit pas contenir de doublons. Dans un modèle de données, les valeurs Null et les chaînes vides sont équivalentes à un espace, qui est une valeur de donnée distincte. Cela signifie que vous ne pouvez pas avoir plusieurs valeurs Null dans la colonne de recherche.

Types de données compatibles

Les types de données dans la colonne source et la colonne de recherche doivent être compatibles. Pour plus d’informations sur les types de données, consultez Types de données pris en charge dans lesmodèles de données.

Non pris en charge dans une relation entre tables

Dans un modèle de données, vous ne pouvez pas créer de relations entre les tables si la clé est une clé composite. Des limitations existent également en ce qui concerne les relations un-à-un et un-à-plusieurs. Les autres types de relations ne sont pas pris en charge.

Clés composites et colonnes de recherche

Une clé composite est composée de plusieurs colonnes. Les modèles de données ne peuvent pas utiliser de clés composites ; une table doit toujours avoir exactement une colonne qui identifie de façon unique chaque ligne dans la table. Si vous importez des tables qui ont une relation existante basée sur une clé composite, l’Assistant Importation de table dans Power Pivot ignore cette relation, car elle ne peut pas être créée dans le modèle.

Pour créer une relation entre deux tables qui ont plusieurs colonnes définissant les clés étrangère et primaire, commencez par associer les valeurs afin de créer une colonne clé unique avant de créer la relation. Vous pouvez le faire avant d’importer les données, ou en créant une colonne calculée dans le modèle de données à l’aide du complément Power Pivot.

Relations plusieurs-à-plusieurs

Un modèle de données ne peut pas avoir de relations plusieurs à plusieurs. Vous ne pouvez pas simplement ajouter des tables de jointure dans le modèle. Toutefois, vous pouvez utiliser les fonctions DAX pour modéliser des relations plusieurs à plusieurs.

Jointures réflexives et boucles

Les jointures réflexives ne sont pas autorisées dans un modèle de données. Une jointure réflexive est une relation récursive entre une table et elle-même. Les jointures réflexives sont souvent utilisées pour définir des hiérarchies de type parent-enfant. Par exemple, vous pouvez joindre une table Employees à elle-même pour produire une hiérarchie qui indique la chaîne de gestion dans une entreprise.

Excel n’autorise pas la création de boucles entre les relations dans un classeur. En d’autres termes, l’ensemble suivant de relations est interdit.

  • Table 1, colonne a   à   Table 2, colonne f

  • Table 2, colonne f   à   Table 3, colonne n

  • Table 3, colonne n   à   Table 1, colonne a

Si vous essayez de créer une relation qui entraînerait la création d’une boucle, une erreur est générée.

Haut de la page

Détection automatique et inférence des relations dans Power Pivot

L’un des avantages d’importer des données à l’aide du complément Power Pivot est que Power Pivot peut parfois détecter des relations et les créer dans le modèle de données qu’il crée dans Excel.

Lorsque vous importez plusieurs tables, Power Pivot détecte automatiquement toutes les relations existantes entre ces tables. En outre, lorsque vous créez un tableau croisé dynamique, Power Pivot analyse les données dans les tables. Il détecte les relations possibles qui n’ont pas été définies et suggère les colonnes appropriées à inclure dans ces relations.

L’algorithme de détection utilise des données statistiques concernant les valeurs et les métadonnées des colonnes afin de faire des inférences sur la probabilité des relations.

  • Les types de données dans toutes les colonnes associées doivent être compatibles. Pour la détection automatique, seuls les types de nombres entiers et les types de données texte sont pris en charge. Pour plus d’informations sur les types de données, consultez Types de données pris en charge dans les modèles de données.

  • Pour que la relation soit correctement détectée, le nombre de clés uniques dans la colonne de recherche doit être supérieur aux valeurs de la table du côté « plusieurs ». En d’autres termes, la colonne clé du côté « plusieurs » de la relation ne doit pas contenir de valeurs qui ne se trouvent pas dans la colonne clé de la table de recherche. Par exemple, vous disposez d’une table qui répertorie les produits avec leurs ID (table de recherche) et d’une table des ventes qui répertorie les ventes de chaque produit (côté « plusieurs » de la relation). Si vos enregistrements de ventes contiennent l’ID d’un produit qui n’a pas d’ID correspondant dans la table Products, la relation ne peut pas être créée automatiquement, mais vous pouvez éventuellement la créer manuellement. Pour permettre à Excel de détecter la relation, vous devez d’abord mettre à jour la table de recherche Product à l’aide des ID des produits manquants.

  • Vérifiez que le nom de la colonne clé du côté « plusieurs » est semblable au nom de la colonne clé dans la table de recherche. Les noms n’ont pas besoin d’être identiques. Par exemple, il arrive souvent que les noms des colonnes qui contiennent globalement les mêmes données varient selon les paramètres d’entreprise : N° employé, Numéro employé, ID employé, ID_EMP, etc. L’algorithme détecte les noms semblables et affecte une probabilité plus élevée aux colonnes qui ont des noms semblables ou strictement identiques. Par conséquent, pour accroître la probabilité de créer une relation, vous pouvez essayer de renommer les colonnes dans les données que vous importez avec un intitulé semblable aux colonnes de vos tables existantes. Si Excel trouve plusieurs relations possibles, il ne crée pas de relation.

Ces informations peuvent vous aider à comprendre pourquoi les relations ne sont pas toutes détectées, ou comment les changements apportés à des métadonnées, telles que le nom d’un champ et les types de données, peuvent améliorer les résultats de la détection automatique des relations. Pour plus d’informations, consultez Résoudre les problèmes liés aux relations.

Détection automatique pour les jeux nommés

Les relations ne sont pas détectées automatiquement entre les jeux nommés et les champs connexes dans un tableau croisé dynamique. Vous pouvez créer ces relations manuellement. Si vous souhaitez utiliser la détection automatique des relations, supprimez chaque jeu nommé et ajoutez directement les champs individuels du jeu nommé dans le tableau croisé dynamique.

Inférence des relations

Dans certains cas, les relations entre les tables sont automatiquement chaînées. Par exemple, si vous créez une relation entre les deux premiers ensembles de tables ci-dessous, il est déduit qu’une relation existe entre les deux autres tables et une relation est établie automatiquement.

  • Products et Category -- relation créée manuellement

  • Category et SubCategory -- relation créée manuellement

  • Products et SubCategory -- relation inférée

Pour que des relations soient chaînées automatiquement, elles doivent avoir une même direction, comme dans l’exemple ci-dessus. Si les relations initiales sont, par exemple, entre les ventes et les produits et entre les ventes et les clients, aucune relation n’est inférée. En effet, la relation entre les produits et les clients est une relation plusieurs à plusieurs.

Haut de la page

Développez vos compétences
Découvrez des formations
Accédez aux nouvelles fonctionnalités en avant-première
Rejoignez le programme Office Insider

Ces informations vous ont-elles été utiles ?

Nous vous remercions pour vos commentaires.

Merci pour vos commentaires. Il serait vraisemblablement utile pour vous de contacter l’un de nos agents du support Office.

×