Vue d'ensemble des relations
Cette rubrique vise à vous présenter les relations que vous pouvez définir entre des tables dans PowerPivot pour Excel. Cette rubrique comprend les sections suivantes :
Définition d'une relation
Conditions requises pour les relations
Détection automatique et inférence des relations
Après avoir lu cette rubrique, vous pourrez comprendre ce qu'est une relation, quelles conditions doivent être remplies pour définir une relation et comment PowerPivot pour Excel peut détecter automatiquement les relations à votre place. Avant cela, vous apprendrez une partie de la terminologie que les professionnels des bases de données utilisent pour décrire des relations.
Définition d'une relation
Une relation est une connexion entre deux tables de données, basée sur une ou plusieurs colonnes dans chaque table (exactement une colonne dans chaque table pour PowerPivot). 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 |
DiscountRate |
OrderID |
OrderDate |
Product |
Quantity |
|
|---|---|---|---|---|---|---|---|
1 |
Ashton |
chris.ashton@contoso.com |
.05 |
256 |
2010-01-07 |
Compact Digital |
11 |
1 |
Ashton |
chris.ashton@contoso.com |
.05 |
255 |
2010-01-03 |
SLR Camera |
15 |
2 |
Jaworski |
michal.jaworski@contoso.com |
.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 messagerie du client pour chaque commande. Le stockage est bon marché, mais vous devez vous assurer de mettre à jour chaque ligne pour ce client si l'adresse de messagerie change. Une solution à ce problème consiste à fractionner les données en plusieurs tables et à définir des relations entre ces tables. C'est l'approche utilisée dans les bases de données relationnelles comme SQL Server. Par exemple, une base de données que vous importez dans PowerPivot pour Excel peut représenter les données des commandes en utilisant trois tables associées :
Customers
[CustomerID] |
Nom |
|
|---|---|---|
1 |
Ashton |
chris.ashton@contoso.com |
2 |
Jaworski |
michal.jaworski@contoso.com |
CustomerDiscounts
[CustomerID] |
DiscountRate |
|---|---|
1 |
.05 |
2 |
.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 |
Si vous importez ces tables à partir d'une même base de données, PowerPivot peut détecter les relations entre les tables en fonction des colonnes qui sont entre [crochets] et reproduire ces relations dans la fenêtre PowerPivot. Pour plus d'informations, consultez « Détection automatique et inférence des relations » dans cette rubrique. Si vous importez des tables à partir de plusieurs sources, vous pouvez créer manuellement des relations, comme cela est décrit dans Créer une relation entre deux tables.
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. Les quatre types de clés suivants sont les plus intéressants 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, qui fait référence à CustomerID dans la table Customers.
Clé composite : une clé composée de plusieurs colonnes. Les clés composites ne sont pas prises en charge dans PowerPivot pour Excel. Pour plus d'informations, consultez « Clés composites et colonnes de recherche » dans cette rubrique.
Dans PowerPivot pour Excel, la clé primaire ou la clé secondaire est dite colonne de recherche associée ou simplement colonne de recherche. Si une table possède à la fois une clé primaire et une clé secondaire, vous pouvez utiliser l'une ou l'autre comme colonne de recherche. 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, PowerPivot pour 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 Customers et Orders 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. 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. PowerPivot pour Excel ne prend pas en charge les relations plusieurs à plusieurs dans l'interface utilisateur. Pour plus d'informations, consultez « Relations plusieurs à plusieurs » dans cette rubrique.
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 |
Relations et performances
Après la création de toute relation, PowerPivot pour 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.
Conditions requises pour les relations
PowerPivot pour Excel présente plusieurs conditions qui doivent être remplies lors de la création de relations :
Relation unique entre des tables
Plusieurs relations pourraient générer des dépendances ambiguës entre des tables. Pour créer des calculs exacts, vous avez 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 entre chaque paire de tables. 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 vous tentez d'importer ces tables, la première relation est créée avec succès, mais vous recevrez l'erreur suivante sur les relations consécutives qui impliquent la même colonne :
* Relation : table[colonne 1]-> table[colonne 2] - État : erreur - Raison : Impossible de créer une relation entre les tables <table 1> et <table 2>. Une seule relation directe ou indirecte peut exister entre deux tables.
Si vous avez deux tables et plusieurs relations entre elles, vous devez importer plusieurs copies de la table qui contient la colonne de recherche et créer une relation entre chaque paire de tables.
Une relation pour chaque colonne source
Une colonne source ne peut pas participer à plusieurs relations. Si vous avez déjà utilisé une colonne comme colonne source dans une relation, mais souhaitez utiliser cette colonne pour la connexion à une autre colonne de recherche connexe dans une table différente, vous pouvez créer une copie de la colonne et utiliser cette colonne pour la nouvelle relation.
Il est facile de créer une copie d'une colonne qui a exactement les mêmes valeurs, en utilisant une formule DAX dans une colonne calculée. Pour plus d'informations, consultez Colonnes calculées.
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 PowerPivot pour Excel, les valeurs Null et 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 prises en charge dans les classeurs PowerPivot.
Clés composites et colonnes de recherche
Vous ne pouvez pas utiliser de clés composites dans un classeur PowerPivot ; vous devez toujours avoir exactement une colonne qui identifie de façon unique chaque ligne dans la table. Si vous essayez d'importer des tables qui ont une relation existante basée sur une clé composite, l'Assistant Importation de table ignore cette relation, car elle ne peut pas être créée dans PowerPivot.
Si vous souhaitez créer une relation entre deux tables dans PowerPivot et que plusieurs colonnes définissent les clés étrangère et primaire, vous devez associer les valeurs pour créer une colonne clé unique avant de créer la relation. Vous pouvez le faire avant d'importer les données, ou dans PowerPivot en créant une colonne calculée.
Relations plusieurs-à-plusieurs
PowerPivot pour Excel ne prend pas en charge les relations plusieurs à plusieurs et vous ne pouvez pas ajouter simplement des tables de jointure dans PowerPivot. 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 les tables PowerPivot. 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.
PowerPivot pour 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.
Détection automatique et inférence des relations
Lorsque vous importez des données dans la fenêtre PowerPivot, l'Assistant Importation de table détecte automatiquement toutes les relations existantes entre les tables. En outre, lorsque vous créez un tableau croisé dynamique, PowerPivot pour Excel 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 prises en charge dans les classeurs PowerPivot.
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 à PowerPivot pour 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 : Emp ID, EmployeeID, 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 PowerPivot pour 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 et l'article Dans les coulisses de la détection automatique de la relation PowerPivot.
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 -- création manuelle
Category et SubCategory -- création manuelle
Products et SubCategory -- la relation est 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 Sales et Products, et entre Sales et Customers, aucune relation n'est inférée. En effet, la relation entre Products et Customers est une relation plusieurs à plusieurs.
Voir aussi
Concepts
Créer une relation entre deux tables