On trouve des exemples de réalisation de tableaux croisés dynamiques allant "piocher" des données à partir de plusieurs tableaux (modèle de données).
Mais quand on tente de faire de même sur d'autres exemples, on obtient parfois des tableaux erronés et Excel nous indique qu'il manque des liaisons !
Je vais donc tenter d'expliquer une petite règle qu'il faut avoir en tête pour savoir ce qui est faisable ou non !
Tableaux de départs
Je vais utiliser les 4 mini tableaux suivant.
(Erratum : Lire Nom_Region_R et pas _B)
Rôles des tableaux
- On a des livraisons (Tab_Livraison) liées à des clients (Tab_Clients).
- Les clients sont répartis par régions (Tab_Regions).
- Les produits livrés viennent de différents sites de production (Tab_Sites_Prod).
Ces 4 tableaux sont réduits au maximum afin de faciliter la compréhension et le contrôle des résultats obtenus.
Préparation des tableaux
Dans Excel
Pour chaque plage/tableau on va :
- Convertir la plage en tableau (Ruban Insertion > Tableau),
- Renommer le tableau (Ruban Création de tableau > zone Nom du tableau),
- Importer le tableau dans le modèle de données (Ruban Power Pivot > Ajouter au modèle de données).
Dans Power Pivot
On ajoute les liaisons entre les tableaux.
On passe en affichage Vue de diagramme (ruban Accueil) puis on glisse les champs "clé primaire" (côté 1) d'une table vers le champ "clé externe" correspondant de l'autre table (côté *, étoile signifie ici "plusieurs").
Rappels :
- Dans PowerPivot, on ne dispose que de liaisons "un à plusieurs" (1>*), l'étoile représentant le coté plusieurs (les relations "un à un" sont remplacées par des "un à plusieurs" et les "plusieurs à plusieurs" sont "simulées" via des mesures DAX).
- On ne peut pas avoir plusieurs liaisons entre 2 même tables.
Remarque :
L'ordre/sens du glisser n'est pas important dans ce cas.
Excel va déterminer le sens de la liaison à partir des données des tables mais ce n'est pas toujours si simple.
Imaginons que nous ayons une table nécessitant une liaison "1 à 1", dans ce cas Excel aura le choix du sens, les 2 étant valables .
Une liaison "1>*" ou "*>1" (sens inverse) pouvant fonctionner toutes les deux (1 étant compris dans *).
On va voir plus loin que le sens de liaison est important donc dans ce cas il faudra choisir soigneusement le sens de la liaison !
Pouvant faire le glissé dans un sens comme dans l'autre, il faut savoir que dans cette situation Excel utilisera le champ glissé comme étant le côté * (plusieurs) !
La règle à connaitre pour les TCD
Avec le modèle de données :
- Tous les champs utilisés/posés en étiquettes de lignes ou de colonnes doivent être du côté 1 par rapports aux champs placés en zone Valeurs !
- La liaison n'est prise en compte seulement si au moins un champ se trouve en zone ∑Valeurs du TCD .
(Voir https://learn.microsoft.com/fr-fr/power-bi/guidance/star-schema)
Rappel : Pour réaliser nos tableaux croisés dynamiques on utilise/pose des champs :
- En zone Lignes ou Colonnes pour les utiliser comme étiquettes de ligne ou de colonne.
- En zone ∑Valeurs pour les utiliser pour réaliser les calculs de synthèses.
Exemples
Légende :
- Cadres Rouges : Champs placés en zone Valeurs.
- Cadres Verts : Champs placés en étiquettes de lignes ou de colonnes.
Nombre de livraisons par région clients
Interprétation : Pour chaque région, on va récupérer une liste d'adresses sur laquelle on peut appliquer une fonction de synthèse comme un décompte.
Remarque : On n'est pas obligé d'utiliser un champ de Tab_Clients.
Nombre de livraisons par région clients et par site de production
Interprétation : Pour chaque région, on va récupérer une liste d'adresses sur laquelle on peut appliquer une fonction de synthèse comme un décompte. Chaque adresse étant accompagnée du nom de la ville de production.
Remarque : Ville_S et Nom_Region_R sont bien du côté 1.
Nombre de livraisons et nombre de clients par région clients
Interprétation : Pour chaque région, on va récupérer une liste de clients et une liste d'adresses sur laquelle on peut appliquer une fonction de synthèse comme un décompte.
Remarque : Si on utilise Ville_S en étiquettes de ligne ou colonne.
- Le calcul Nombre de Adresse_L sera toujours juste (2+1, 1, 4) car Ville_S est du côté 1 par rapport à Adresse_L (Excel ne trouve qu'une valeur en "suivant" la relation/liaison).
- Le calcul Nombre de NomClient_C sera "faux" (répété) car Ville_S est du côté * (plusieurs) par rapport à NomClient_C (Excel trouve plusieurs valeurs en "suivant" la relation/liaison).
- Excel nous alerte alors avec ce message "Des relations entre les tables peuvent être nécessaires" dans le volet Liste de champs.
Nombre de livraisons par région clients et par année de livraison
Remarque : Pas de problème si un champ étiquette se trouve dans la même table qu'un champ Valeur.
Outrepasser cette règle
Exemple : Nombre de clients par site de production
Interprétation : Ne pouvant "remonter" la liaison, Excel fait un "produit" entre la table Clients et Sites. Pour chaque ville on retrouve les 3 clients (double cliquer sur l'un des 3 pour s'en rendre compte).
Nous nous retrouvons dans la cas d'une liaison plusieurs à plusieurs (un clients correspond à plusieurs sites de production, un site de production correspond à plusieurs clients). Un type de relation non autorisé.
Une solution avec les mesures Powerpivot
On va créer 2 mesures.
mNbreNomC : =COUNTA(Tab_Clients[NomClient_C])
mNbNom_Ville : =CALCULATE([mNbreNomC];Tab_Livraisons)On utilise la mesure mNbNom_Ville entant que champ Valeur.
Cette méthode permet donc de simuler une liaison plusieurs à plusieurs.
Merci pour votre attention bienveillante.