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

Les relations sont basées sur les colonnes des tables qui contiennent les mêmes données. Par exemple, vous pouvez associer une table Customers à une table Orders si chacune contient une colonne qui stocke un ID 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. Une clé est généralement une colonne 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.

Bien qu’il existe de nombreux types de clés, celles-ci sont les plus importantes pour notre objectif ici :

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

• Clé alternative (ou clé candidate) : colonne autre que la clé primaire 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 : colonne qui fait référence à une colonne unique dans une autre table, telle que CustomerID dans la table Commandes , qui 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 dans la table Customers (colonne de recherche). 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.

La relation entre un client et une commande est une relation un-à-plusieurs. Chaque client peut avoir plusieurs commandes, mais une commande ne peut pas avoir plusieurs clients. Une autre relation de table importante est un-à-un. Dans notre exemple, la table CustomerDiscounts , qui définit un taux de remise unique pour chaque client, a une relation un-à-un avec la table Customers.

Ce tableau montre les relations entre les trois tables (Customers, CustomerDiscounts et Orders) :

Relation	Type	Colonne de recherche	Colonne
Customers-CustomerDiscounts	un-à-un	Customers.CustomerID	CustomerDiscounts.CustomerID
Customers-Orders	un-à-plusieurs	Customers.CustomerID	Orders.CustomerID

Une fois qu’une relation a été créée, Excel doit généralement recalculer toutes les formules qui utilisent des colonnes de tables dans 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.

Un modèle de données peut avoir plusieurs relations entre deux tables. Pour générer des calculs précis, Excel a besoin d’un seul chemin d’accès d’un tableau à l’autre. Par conséquent, il ne peut y avoir qu’une seule relation active à un moment donné entre chaque paire de tables. Bien que les autres soient inactives, vous pouvez spécifier une relation inactive dans les formules et les requêtes.

En mode Diagramme, la relation active est une ligne pleine et les relations inactives sont des traits en pointillés. Par exemple, dans AdventureWorksDW2012, la table DimDate contient une colonne, DateKey, qui est lié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.

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 les modèles de données.

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 manière unique chaque ligne de 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.

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, dans un environnement métier, vous avez souvent des variantes sur les noms des colonnes qui contiennent essentiellement les mêmes données : Emp ID, EmployeeID, Employee ID, Employee ID, EMP_ID, 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.