Microsoft 365 - Conseils pour la structuration d'une base de données -

[SERIEUXETCOOL]

Bonjour à tous,

J'utilise Excel 365 FR. Je rencontre un problème concernant la structure d'une base de données (i.e. la manière de présenter les données pour permettre leur analyse plus tard). J'espère trouver ici des conseils de personnes bien plus expérimentées que moi pour m'aider à y voir plus clair.

Je mets en pièce jointe un fichier Excel pour illustrer mon propos. Pour faire simple, supposons que j'étudie l'exemple d'une base de données, dans laquelle j'ai des cours (Maths, Méca, Physique, etc.), des durées (2h, 4h, etc.), des étudiants (Groupe1, Groupe2, etc.), des profs (Marc, Julie, Jean, etc.), des salles (Salle1, Salle2, Salle3, etc.). Intuitivement, je propose une base de données avec une colonne cours, étudiants, prof, salle. Jusque-là tout va bien et je peux analyser mes données via Tableau Croisé Dynamique (TCD) comme je l'entends. Par exemple, je peux connaître le nombre heures qui est assuré par un prof, le taux de charge d'une salle, vérifier si les groupes d'étudiants ont eu le même nombre d'heures de cours, présenter une maquette des cours avec les heures associées, etc.

Le problème se pose lorsque pour un même cours (de Maths par exemple), j'ai plusieurs données associées (comme plusieurs groupes d'étudiants qui assistent à ce cours, ou bien plusieurs profs qui donnent le cours, ou encore plusieurs salles nécessaires pour assurer ce cours). Dans ce cas de figure, mon TCD ne dissocie pas les données (ce qui me semble normal). Par conséquent, je n'arrive pas avoir la bonne granulométrie dans mon analyse. Je n'arrive plus à connaitre le nombre d'heures qui est assuré par un prof, puisque je vais avoir les heures réalisées par le groupe de profs. Même chose pour les autres colonnes. Du coup, je suis perdu.

La solution que je retiens à ce stade est de dupliquer ma base de données initiale autant de fois qu'il y a de colonnes qui présentent des données liées. Dans mon exemple précédent, les colonnes Elève, Prof et Salle contiennent toutes les 3 des données liées. Je vais donc dupliquer ma base initiale 3 fois. Ce qui donne les feuilles Excel "Table Elève", "Table Prof" et "Table Salle". Pour chaque nouvelle base de données, je m'occupe uniquement d'une seule colonne contenant des données liées. Les autres colonnes contenant des données liées ne changent pas dans cette feuille, mais dans les autres. Et donc je vais ajouter autant de nouvelles lignes qu'il y a de données liées.

Par exemple, la feuille "Table Elève" s'occupe de la colonne Elève. Le cours de physique est réalisé par le groupe1 et le groupe 2 (Groupe1, Groupe2). Je vais donc copier coller cette ligne. Une ligne contiendra Groupe1, l'autre ligne contiendra Groupe2. Je pourrais ainsi analyser cette colonne Elève finement. Je fais pareil pour les autres feuilles. C'est une macro VBA qui s'occupera de créer les bases de données "Slave" contenant des données non-liées, à partir de la base de donnée initiale "Master" contenant toutes les données éventuellement liées.

Cette méthode est loin d'être parfaite mais elle me permet de répondre à mon besoin : analyser finement des données qui se retrouvent liées dans un tableau Excel.

Si certains ont des conseils pour faire mieux (car dupliquer des bases de données, multiplier des données, augmenter le stockage, augmenter le risque d'erreurs, complexifier la structure de données, augmenter les temps de calculs, complexifier la maintenance, etc.). Autant d'arguments pour ne pas utiliser cette approche.


Un grand merci d'avance à ceux que ça inspire !

 

[SERIEUXETCOOL]

Bonsoir CroCroCro,

Merci pour ces premiers retours. C'est super intéressant.

Je pense avoir compris les limites du modèle initial (celui qui s'occupait uniquement des heures lorsque des champs multivalués pouvaient être présents). La réponse que tu apportes, basée sur le dégroupage de certains champs multivalués avec le bon filtre activé résous parfaitement la problématique - même si ce n'est pas "propre" du point de vue de la méthodologie, ça fonctionne

Tu as entrouvert la porte des tables, avec des champs qui détaillent ces tables, et la possibilité d’analyser précisément l'ensemble des données - cette fois-ci de manière "propre". Je ne peux pas passer à côté de cette possibiité (plus de données, plus d'analyse, plus de pertinance pour tout le monde). C'est ce que tu avais évoqué au tout début de nos échanges : il faut passer par des tables de jointures pour toutes les relations n, n. Je n'ai pas voulu aborder cette voie car ces fameuses tables m'effrayaient du fait de mon ignorance. Mais force est de constater que c'est probablement la clé de succès. Je travaille dessus justement. J'essaie de voir ce que ça changera pour moi de "bien faire les choses" en passant par ces tables de jointures. J'essaie de produire le même fichier que toi, mais en passant par les tables supplémentaires pour bien comprendre les écarts. Si tu peux produire un fichier exemple avec cette méthode "propre", je pourrais comparer par rapport à ce que j'ai fait. A tous les coups, je vais m'y prendre à l'envers encore

Concernant les userforms, je n'ai pas d'expertise pour juger. J'ai le sentiment que ce n'est pas ce qui conviendra le mieux (en particulier pour laisser de la liberté aux utilisateurs de pouvoir croiser les données selon leur choix). Comme par exemple ajouter les heures de plusieurs profs, en fonction de X paramètres, etc. Je crains que ce soit plus rigide que de proposer une base de données à analyser plus librement. Mais je me trompe probablement.

Bonne nuit à toi CroCroCro.
Je poursuis l'investigation sur les tables de jointures de ton modèle initial^^

 

[crocrocro]

J'ai apporté des modifications dans le post précédent #30.
ce qui est supprimé
ce qui est ajouté

Si tu t'en sens capable, tu peux rapidement créer les 3 tables de lien (dans la même feuille) en utilisant un fichier Cours d'une dizaine de lignes et tester les différents TCD.

En pj un fichier où j'ai :
- ajouter les tables de lien pour les tables groupes, professeurs et Salles (Feuille Tables de Lien)
- modifier la structure de la table professeur (identifiant semi fonctionnel composé du Nom suivi de l'initiale du Prénom) ajout des infos Nom, Prénom, ajout de l'info N - P (concaténation du nom et du prénom par formule).
- ajouter une feuille Nouveau TCD qui contient 2 TCD utilisant les tables de lien (et créer les relations nécessaire (regarde dans relations comment elle sont créées). Le 2ème TCD utilise l'info N - P, une piste pour contourner la contrainte "pas de champ calculé si relation". Mais cela imposera de rajouter des infos à chaque nouveau besoin (je veux aussi l'âge du capitaine, la longueur de sa moustache .... Ceci dit, très rapide à mettre en œuvre, la formule est propagée sur toute la colonne de la table, juste à ajouter l'info dans le TCD (ne pas oublier d'actualiser les Données pour que l'info apparaissent dans le modèle de données)
- le code VBA pour regénérer les tables de lien. Nouveau bouton dans la feuille Cours.

En fonction de ce que tu veux faire apparaitre au niveau des TCDs, il te faudra soit utiliser la durée de la table Cours soit celle de la table de lien concernée (regarde les 2 TCDs en exemple).


Je reviens (très tardivement) sur la conception du modèle de données et des questions qui conditionnent son élaboration.
Qu'est-ce qu'un Cours ou plus exactement, quel est le niveau le plus fin pour lequel on voudra connaitre les informations d'un cours ?
Un matière enseignée à une date (jour - heure), une durée , une salle donnés ?
Un matière enseignée à une date (jour - heure), une durée , une salle, un groupe donnés ?
Un matière enseignée à une date (jour - heure), une durée , une salle, un groupe, un professeur donnés ?
Dans une même salle peut-on avoir en même temps pour un cours, plusieurs professeurs ?
Dans une même salle peut-on avoir en même temps pour un cours, plusieurs groupes ?
Pour un cours, un professeur peut-il avoir en même temps plusieurs groupes ?
Si tout est possible et si l'on souhaite pouvoir restituer toutes ces informations pour la consultation par TCD, un cours (au sens Table) serait alors une matière enseignée à une date horaire pour une durée dans 1 salle par 1 professeur à 1 groupe.
Par le jeu des relations entre tables, on pourrait alors retrouver, par exemple :
les 2 professeurs qui ont donné un même cours (qui ont été groupés) dans la feuille Cours actuelle ou
les 3 groupes (qui ont été groupés) qui ont reçu le même cours dans la même salle dans la feuille Cours actuelle.
Un peu tard peut-être pour un rétro-pédalage sur ce qui a déjà été fait, ce qui aurait un impact :
- éventuellement sur la saisie de la feuille Cours, mais on peut ne rien toucher.
- le code vba pour, à partir de la feuille Cours générer la table Cours du modèle.
à voir aussi, si au niveau des TCD, on arrive à obtenir tout ce que l'on souhaite.

Je crois comprendre ta demande de conserver l'ordre de sélection pour les informations groupées : pour une même ligne de cours , Prof1, Prof2, Prof3 et Groupe2, Groupe1, Groupe3, Salle3, Salle2, Salle1 signifie Prof1 donne le cours au Groupe2 en Salle3, en même temps Prof2 donne le cours au Groupe1 en Salle2 et Prof3 donne le cours au Groupe3 en Salle1.
Cette interprétation, si elle est bonne, impose, (si on veut la traduire au niveau d'un dégroupage) d'avoir 1 prof pour 1 salle pour 1 groupe.

 

[SERIEUXETCOOL]

Bonjour CroCroCro, les amis du Forum qui passeraient par-là,

@crocrocro : je suis venu sur le forum avec une présentation initiales de mes données au format non-structuré. Nous sommes partis sur pleins de pistes (avec des avantages et inconvénients). Je suis très intéressé par la piste relative à la structure des données et aux relations entre tables. Depuis ton dernier post, j'ai ressenti le besoin de repartir de 0 sur mon projet grâce à tous les enseignements acquis ici. Je ne peux plus continuer sur mon approche initiale. Trop de choses ont changé au niveau des concepts. Je ne peux pas non plus continuer avec le modèle simpliste que l'on utilise actuellement (Matière, Groupe, Professeur, Salle). Je dois faire le point sur la vision d'ensemble et proposer une version réaliste de mon projet sous la forme de données structurées. C'est seulement là que j'y verrai plus clair. Donc désolé pour le délai de réponse mais depuis quelques jours je planche sur ce modèle de données qui décrit le mieux ma situation. Je pensais être plus rapide, mais comme je l'ai déjà dit...je suis un vrai dinosaure ! Je ne lâche rien, mais que c'est difficile piouf.

En fonction de ce que tu veux faire apparaitre au niveau des TCDs...

Voilà pourquoi je repars de 0 afin de mieux connaitre la finalité de mes TCD. Grâce au nouveau modèle structurée des données, je pourrais proposer les types de TCD que je souhaite analyser. Ça va arriver donc

Qu'est-ce qu'un Cours ou plus exactement, quel est le niveau le plus fin pour lequel on voudra connaitre les informations d'un cours ?

Là aussi, j'ai besoin de proposer la nouvelle version de mon fichier pour s'appuyer dessus. C'est en faisant cet exercice que je pourrais précisément dire "voilà ce que je souhaite, voilà où je rencontre une difficulté". Je dois être le plus précis pour demander un coup de main et rester le plus efficace possible, et surtout le plus correct possible vis-à-vis des personnes comme toi qui acceptent d'aider.

Je crois comprendre ta demande de conserver l'ordre de sélection pour les informations groupées : pour une même ligne de cours , Prof1, Prof2, Prof3 et Groupe2, Groupe1, Groupe3, Salle3, Salle2, Salle1 signifie Prof1 donne le cours au Groupe2 en Salle3, en même temps Prof2 donne le cours au Groupe1 en Salle2 et Prof3 donne le cours au Groupe3 en Salle1.
Cette interprétation, si elle est bonne, impose, (si on veut la traduire au niveau d'un dégroupage) d'avoir 1 prof pour 1 salle pour 1 groupe.

Cette interprétation n'est pas la bonne (désolé de ne pas être suffisamment clair). Je reviendrai sur ce point plus en détail plus tard. En synthèse pour comprendre tout de même :

1. Dans un premier temps, je cherche à connaitre le détail des heures réalisées par la matière, ou bien la salle, ou bien le groupe, ou bien le professeur. Ces heures me permettront de vérifier que tout est ok pour tout le monde, mais aussi de calculer le taux de charge d'une salle (est-elle occupée ?) et le taux de charge d'un professeur (est-il occupé ?) entre autres. C'est tout l'objet de nos discussions depuis le début ici.
2. Dans un second temps, j'aimerais essayer de trouver le couple (Date; Heure de début, Heure de fin) d'un cours complet, et cela pour tous les cours inscrits à l'agenda. Pourquoi je demande la liste ordonnée des professeurs pour 1 cours ? Pour me permettre de commencer par le professeur idéal de la liste, de voir ses disponibilités, puis de passer au professeur n°2, de voir ses disponibilités, etc. jusqu'à identifier le premier professeur disponible au plus tôt pour donner le cours. Même chose pour la liste des salles (quelle est la première salle de la liste disponible pour accueillir le cours ?). L'objectif : placer les cours dès que c'est possible en assouplissant les données d'entrées (en proposant plusieurs ressources pour ne pas pénaliser le placement des cours). En général, je vais avoir 1 à 5 professeurs pouvant donner le cours et 1 à 20 salles pouvant accueillir le cours.

Je reviens ici dans le courant de la semaine prochaine avec un fichier basé sur des tables relationnelles, des identifiants techniques, et un modèle le plus proche de la réalité que je puisse faire. Je verrai bien jusqu'où j'arrive à avancer. Pour le moment je suis bien parti, mais je vais attaquer le fameux problème des heures...aie aie aie^^ De toute façon il nous faut ce fichier actualisé pour poursuivre les échanges selon moi.

Passe un bon weekend CroCroCro

 

[crocrocro]

Bonjour Sérieux et Cool,
toujours dans tes Liaisons Dangereuses ?
Bon courage

 

[SERIEUXETCOOL]

Bonjour CroCroCro,

Plus que jamais embourbé de la tête aux pieds sur ce sujet

J'y ai passé la semaine entière (nuit et jour !) et je n'ai pas le sentiment d'avoir grandement avancé. En tout cas, pas à la hauteur de mes espérances. J'ai avancé un peu tout de même, mais force est de constater que c'est bien difficile pour moi. En tout cas, j'apprends des choses et ça c'est cool.

Où j'en suis à l'heure actuelle ?
Je suis venu sur le Forum avec un exemple simple qui illustrait mon problème initial de structure de données (les fameux champs Groupe, Professeur et Salle contenant plusieurs valeurs et qui ne respectent pas les règles de la normalisation - ça je ne le savais pas au début). J'ai fini par comprendre qu'il me fallait passer par une structure que je ne connaissais pas : la base de données relationnelle. J'ai également compris qu'il me fallait respecter les conventions de ce système si je voulais exploiter tout son potentiel : à savoir respecter la normalisation (uniquement des relations 1 à plusieurs, sinon passer par des tables de jointures lorsque j'ai une relation plusieurs à plusieurs). J'ai donc revu mon problème de fond en comble. J'ai modélisé la structure des données la plus réaliste possible. Cette fois-ci, les données sont là, structurées du mieux que j'ai pu. Ce modèle me semble être une bonne base de départ. En tout cas, une meilleure base de départ que mon modèle simpliste du début.

Où je bloque à l'heure actuelle ?
J'ai renseigné quelques données pour éprouver mon modèle. Malheureusement, je n'arrive pas à exploiter le quart de mes données !

Je parviens à produire un rapport (UE, EC, Séance, Format vs la durée d'une séquence). Il s'agit du TCD en fin de classeur.

Je ne parviens pas à produire d'autre rapports pertinents. Par exemple, accéder aux données du Groupe, du Professeur ou de la Salle vs la durée d'une séquence. Je sens que la démarche est bonne, bien que j'obtienne encore moins de résultats qu'avant. Je dois probablement mal m'y prendre au niveau des relations car elles ne me permettent pas de relier/rallier les données entre elles comme je le veux.


Conséquence ?
Je ne peux pas m'attaquer au problème de fond : compter les heures pour UE, EC, Seance, Sequence, Groupe, Professeur, Salle et vérifier que tout fonctionne bien. J'ai l'intuition que mon modèle ne dois pas être loin de la vérité mais un truc m’échappe encore (sans doute plusieurs trucs même ).

@crocrocro
Verrais-tu des erreurs de modélisation évidentes ? De toute façon, je suis obligé d'en passer par ce modèle pour avancer.

Image de la modélisation :

Quelques explications sur certaines tables :

• Table_Formation = la liste des formations ouvertes aux étudiants. 1 étudiant est inscrit dans 1 seule et unique formation du début à la fin de sa période dans l'université (i.e. pendant 5 pour la formation Ingénieur par exemple).
• Table_Semestre = une année universitaire est divisée en 2 semestres (de septembre à janvier, puis de février à juin).
• Table_Groupe et Table_Groupe_Cible = les étudiants sont divisés dans des groupes pendant chaque semestre. 1 étudiant doit appartenir à un groupe (par exemple Groupe A au début, puis groupe C plus tard). Un groupe est précisément identifié par sa formation + son année + son semestre + son groupe = Table_Groupe_Cible
• Table_UE et Table_EC = Les enseignements sont distribués en UE (Unités d'Enseignement) ; chaque UE comporte un ou plusieurs EC (Eléments Constitutifs). À chaque UE et/ou EC est affecté un nombre donné de crédits ECTS (European Credit Transfer and Accumulation System - Système européen de transfert et d’accumulation de crédits, facilitent la reconnaissance des diplômes dans l’espace européen et fluidifient la mobilité étudiante). 1 ECTS = 25 à 30h (tout type d'activité confondu comme par exemple travail en classe, à la maison, DS, etc.).
• Table_Format = liste les différents types d'activité qu'un étudiant peut suivre. Ce peut être une activité comme un cours magistral, un travail en laboratoire, des exercises sur table, un projet, un travail à la maison, un devoir sur table, etc.
• Table_Seance_Cible et Table_Sequence = précise le contenu d'une séance de cours. Par exemple, la séance peut démarrer par un cours, puis une partie pratique, puis un examen. Je cherche à obtenir le détail des heures de ces séquence (pas des séances, mais des séquences).
• Table_Pole et Table_Professeur = les professeurs appartiennent à des Pôles. C'est comme un département. On regroupe les professeurs experts en mécanique dans un Pôle, les professeurs experts en Langue dans un autre Pôle, etc.
• 3 Table_Inactivite = les étudiants ne travaillent pas le jeudi après-midi par exemple, les professeurs peuvent être en télétravail le lundi matin par exemple, la séance de cours ne peut pas être planifiée le mercredi matin par exemple. Ces tables fixent les indisponibilités des ressources dans la semaine.

Code couleur dans Excel :

• Les cellules de couleur verte = identifient une clé primaire (la cellule à une validation de données + mise en forme conditionnelle pour limiter certaines erreurs)
• Les cellules de couleur bleu = identifient une clé étrangère (la cellule à une validation de données + mise en forme conditionnelle pour limiter certaines erreurs). La cellule a également un commentaire (appliqué par VBA) pour aider à lire la clé étrangère (ce qui se cache derrière la valeur numérique)
• Les feuilles de couleur verte = des tables dont le contenu n'est pas voué à beaucoup évoluer.
• Les feuilles de couleur rouge = des tables dont le contenu est voué à évoluer régulièrement.
• Les feuilles de couleurs jaune = des tables sur lesquelles je travaille encore et dont je ne suis pas certain de moi. Il n'y pas de validation des données, ni MFC ici.

Pour le reste, CroCroCro, tu connais déjà bien le contexte me semble-t-il. Je répondrai avec plaisir à tes questions si tu en as.

Aller, je retourne à mes tests sur les relations inter-tables pour comprendre pourquoi ça ne fonctionne pas comme je veux...

 

[crocrocro]

Un premier retour sur ton modèle de données.

Beaucoup de travail .

Une question importante :
Le projet a-t-il pour fonctionnalités de gérer des plannings de professeurs, d'occupation de salles, comme semble le suggérer ce que tu décris des tables d'inactivité ?
Si c'est le cas, l'application s'avère beaucoup plus complexe.
Il ne serait pas raisonnable de faire les saisies directement dans des tableaux même si tu es le seul à effectuer les mises à jour. De plus tu peux être amené à déléguer.
Il faudrait établir un véritable cahier des charges à établir (et à faire valider) ainsi qu'un gros travail de réalisation, des cycles de validation – correction … plus tout à fait un travail d'amateur, malgré son talent et sa volonté.
Sinon, l'application ne sert donc qu'à faire de l'analyse à postériori et dans ce cas, on peut simplifier le modèle de données. D'autant plus si tu es le seul à effectuer les mises à jour.

Comme je te l'ai déjà dit dans un post précédent, il est important de savoir assez précisément ce que tu souhaites analyser dans les TCDs. Il faudrait que tu fasses une liste :

• Connaitre pour un Professeur, la durée des cours d'une matière par groupe par semestre d'une année
• Connaitre pour un Professeur, la durée des cours d'une matière par groupe pour une période au choix
• Connaitre par Professeur, la durée des cours par matière par groupe pour une période au choix
• Connaitre par Salle, la durée d'occupation pour chaque mois de l'année scolaire
• Etc…

Je te ferai part de mes remarques sur le modèle de données après que tu aies répondu à la 1ère question.

 

[crocrocro]

Un complément

Quelques remarques sur les TCD et Graphiques
Les TCD produisent des données agrégées (somme ou nombre de valeurs, Moyenne, Maximum …)
Pour un TCD où tu veux par exemple connaitre par Professeur, Groupe, Salle, la durée (le cumul des cours), il faut que dans la Table finale où se trouve la durée, il y ait l'identifiant du Professeur, du Groupe et de la Salle ou bien, si on prend le problème à l'envers, que dans la table où il y a l'identifiant du Professeur, du Groupe et de la Salle, il y ait également la durée. Si tu ne cherches pas à avoir l'information pour 1 groupe particulier (donc tous groupes confondus), il n'est pas nécessaire d'avoir l'identifiant du Groupe dans la Table. Avec la table contenant l'identifiant du Professeur, du Groupe et de la Salle (qui peut le plus, peut le moins) tu peux créer des TCDs où dans le même TCD, en filtrant du pourras sélectionner le(s) professeur(s), le(s) groupe(s) de ton choix.

Toujours avec le même exemple, supposons qu'un professeur enseigne 1 seule matière et que tu aies donc une relation entra la table Matière et Professeur. En partant de la même table, tu peux créer un TCD pour connaitre par Matière, Professeur, Groupe, Salle, la durée.

Avec une présentation classique des TCDs en colonne (Option – Affichage – Disposition classique du tableau), tu peux filtrer sur toutes les colonnes.

Sur le Graphique Croisé Dynamique, quelque soit le type de présentation du TCD, tu peux filtrer sur tous les niveaux.

Quand créer une table ?
Si la table ne contient qu'une seule information, qui sera donc la clé primaire, on ne créé pas de table, l'information est mise directement dans la table "liée".
Par exemple, le semestre. Quelle information autre que le fait de savoir qu'on est au 1er ou au 2ème semestre est-elle nécessaire ? Si le 1er semestre correspond à la période entre le 1er janvier et le 30 juin et du 1er juillet au 31 décembre, c'est inutile. Si c'est juste pour mettre un libellé ("1er semestre"), c'est inutile.
Autre exemple, dans une table on a une donnée qui doit être paire. Doit-on créer une table des nombres pairs et faire une relation entre les tables ? là, il parait évident que non. Oui mais, il faut que ma donnée soit paire et en faisant un lien avec la table des nombres pairs, c'était réglé. Oui mais non : dans ce type de cas, on passe par une règle de contrôle (hors modèle de données). Dans Excel, on pourra passer par La validation de données.
Le problème particulier des périodes de validité d'une valeur de l'information, le Statut que tu as inclus dans pas mal de table.
C'est parfait pour autoriser / interdire la saisie d'une valeur si elle est périmée.
Par exemple avec une Table des inscriptions universitaires qui donne par année universitaire, l'adresse la salle … et un statut que tu positionneras à ouvert ou clos. Table que tu voudras peut-être rattacher à la table des étudiants.
Dans ton application d'inscription, tu ne pourras pas inscrire un étudiant si le statut est clos.
Mais quand tu regarderas les année précédentes, tu verras évidemment des étudiants inscrits alors que pour cette année-là, le statut est clos. Encore une fois, c'est parfait pour contrôler la saisie. Ici, la table est utile mais il n'y a pas vraiment d'intérêt à la rattacher à la table des étudiants. Sauf si tu veux connaitre le lieu, la salle d'inscription de l'étudiant. Pour la date d'inscription (qui ne peut pas être dans la table des inscriptions), elle sera certainement dans la table étudiant.
Mais, tout ce que je dis là et dans les posts précédents sur les BDR, TCD ... tu peux certainement le trouver ailleurs, présenté bien plus clairement, et j'ai sans doute écrit quelques c... ici ou là.

 

[SERIEUXETCOOL]

Bonjour CroCroCro,

J'espère que tu vas bien et que tu as passé un bon weekend.

Je n'ai pas pu te répondre plus tôt. Voici les réponses à tes précédentes questions. J'espère que ça aidera. Je reste bien à disposition si ce n'est pas assez clair :

Le projet a-t-il pour fonctionnalités de gérer des plannings de professeurs, d'occupation de salles, comme semble le suggérer ce que tu décris des tables d'inactivité ?

J'ai 2 objectifs successifs avec ce projet :

1. [Indispensable]Je cherche à mesurer puis analyser les taux d'occupation de certaines ressources comme les groupes d'étudiants, les professeurs, les salles. C'est vraiment mon objectif premier.
   • Voici quelques questions sous-jacentes à ce premier objectif :
     • Le responsable des maquettes voudra savoir la répartition des heures de la maquette, le nombre d'heure total dans la maquette. Cela lui permettra de savoir à quel endroit il y a trop d'heures, et à quel endroit il faut en rajouter pour respecter les quotas. Cela permettra de générer un rapport complet de la maquette qui sera très utile pour avoir une vue d'ensemble des cours assurés (Cf. feuille Excel nommée TCD Maquette).
     • Un professeur voudra savoir le nombre d'heures qu'il donne, et connaitre la répartition précise de ce nombre d'heures. Ce sera pour lui un rapport complet de ses interventions. (Cf. feuille Excel nommée TCD Professeur).
     • Un responsable de Pôle voudra connaitre le nombre d'heures assurées par les enseignants de son Pôle. Il pourra comparer le nombre d'heures avec un autre Pôle pour vérifier que chaque Pôle assure une performance comparable. (Cf. feuille Excel nommée TCD Pole).
     • La direction sera intéressée pour connaitre la répartition des heures pour chaque Pôle et pour chaque professeur. Cela lui permettra d'intervenir si besoin d'équilibrer les charges générales, savoir si un Pôle peut embaucher un professeur ou bien s'il doit puiser dans ses ressources internes aux vues des disponibilités des professeurs. (Cf. feuille Excel nommée TCD Direction).
     • La scolarité sera intéressée pour vérifier que tous les groupes d'étudiants, devant suivre une même EC (une matière), ont bien tous eu le même nombre d'heures. N'a-t-on oublié personne ? Un professeur pourra aussi surveiller cet indicateur. (Cf. feuille Excel nommée TCD Groupe).
     • Un responsable du patrimoine sera intéressé pour connaitre la répartition des heures pour chaque Bâtiment et pour chaque Salle. Celui lui permettra de surveiller le taux d'utilisation des bâtiments, connaître les salles les plus demandées et celle inutilisées, intervenir pour renforcer l'offre des salles si besoin, transformer des salles inutilisées en d'autre type de salle, veiller à proposer une disponibilité suffisante pour permettre les rotations des cours, etc. (Cf. feuilles Excel nommées TCD Bâtiment et TCD Salle).
2. [La cerise sur le gâteau si j'y arrive]Une fois tous les cours saisis sur Excel, je me dis qu'une étape logique et super intéressante serait de déterminer pour chaque séance de cours prévue : 1 date, 1 heure de début, 1 heure de fin. Ayant rassemblé l'ensemble des informations, comme les disponibilités/indisponibilités des ressources, les professeurs capables de donner le cours, les salles capables d'accueillir le cours, etc., alors par VBA il "suffirait" de boucler sur l'ensemble des données pour trouver la correspondance la plus tôt possible. Quelque chose comme :
   • Je prends une séance d'un cours,
   • Je cherche la première disponibilité de ce cours dans une liste donnée,
   • Je prends le premier professeur de la liste des professeurs capables de donner ce cours,
     • Si indisponible, je prends le professeur numéro n°2, etc.,
   • Je prends la première salle de la liste des salles capables d'accueillir ce cours,
     • Si indisponible, je prends la salle numéro n°2, etc.
   • Je détermine la date, l'heure de début et l'heure de fin compatible avec toutes les contraintes
   • Je passe à la prochaine séance à planifier

Le code VBA sera probablement long à parcourir toutes les possibilités (peut-être 5 à 10h de calcul) mais qu'importe, il finira obligatoirement par converger et placer 100% des cours. (Sauf si je force des cas impossibles à résoudre comme par exemple de dire qu'un professeur n'est jamais disponible = on ne trouvera jamais un créneau). Mais mis à part ces effets de bord, le seul risque c'est de planifier des cours de manière "peu intelligente dont la conséquence est qu'ils sont planifiés sur une durée n'étant pas bonne" comme par exemple, planifier la séance d'un cours dans 2 ans. Ok, tout le monde sera disponible à ce moment-là...mais il faut que tous les cours rentrent semestre par semestre. Donc trouver les triplets 1 date, 1 heure de début, 1 heure de fin qui sont compatibles. Je pense que c'est à ma portée, mais je sous-estime probablement la tâche. En tout cas, je rêverai de travailler sur cet axe...mais pas sans avoir atteint mon objectif n°1 avant toute chose.​

En gros, comment je vois les choses personnellement :
L'objectif n°1 servira à collecter la donnée de base pour satisfaire les professeurs, les responsables des Pôles, la direction des études, les responsables des formations, le responsable du patrimoine et le responsable des maquettes. Il gère le Qui ?, Par Qui ?, Pour Qui ?, Quoi ?, Où ?, Comment ?, Combien ?, (Pourquoi ?)
L'objectif n°2 servira à satisfaire le responsable de la scolarité, et indirectement toutes les autres parties prenantes. Il gère essentiellement le Quand ?

Comme je te l'ai déjà dit dans un post précédent, il est important de savoir assez précisément ce que tu souhaites analyser dans les TCDs. Il faudrait que tu fasses une liste :

• Connaitre pour un Professeur, la durée des cours d'une matière par groupe par semestre d'une année
• Connaitre pour un Professeur, la durée des cours d'une matière par groupe pour une période au choix
• Connaitre par Professeur, la durée des cours par matière par groupe pour une période au choix
• Connaitre par Salle, la durée d'occupation pour chaque mois de l'année scolaire
• Etc…

J'ai mis en pièce jointe les 7 principaux TCD que j'aimerais obtenir à la fin. Je n'avais pas réalisé ce travail avec l'approche par base de données. Ta question a eu le mérite de me forcer à proposer un exemple de ce que je souhaitais obtenir à la fin. Ce n'était si simple de savoir ce que je souhaitais vraiment à la fin. Donc encore plus difficile pour des personnes extérieures au projet comme toi CroCroCro. Très utile d'avoir cet objectif sous les yeux maintenant en effet

Si j'anticipe bien, ces objectifs de TCD devraient permettre de faire le lien avec le modèle de données relationnel que j'ai proposé...et de m'expliquer pourquoi il est logique que je ne puisse pas croiser les données comme je le pensais.

Je vais prendre le temps de lire ton dernier message pour voir si je peux en tirer des leçons à appliquer directement pour corriger ma trajectoire.

J'en profite à nouveau pour te remercier d'essayer de m'aider à progresser sur cette problématique. Je fais de mon mieux, et tes conseils me sont précieux. Merci à toi sincèrement.

 

[SERIEUXETCOOL]

J'ai essayé de comprendre le problème des relations entre les tables en faisant pleins de tests simples. Je cherche à produire un graphique indiquant les "Duree_Seance" pour chaque "Prenom_Nom_Professeur" depuis le fichier donné en pièce jointe, très simplifié pour comprendre les choses.

Le problème se pose lorsque je rencontre une relation plusieurs à plusieurs, qui implique l'usage d'une table de jointure. Ici, la table "Table_Professeur_Seance_Cible" par exemple. Dès que j'ai ce type de relation, je ne parviens plus à croiser la donnée des durées avec les champs de ces tables jointes.

J'ai fini par observer, sans forcément comprendre pourquoi, que je pouvais remonter le croisement des informations uniquement dans un sens particulier : dans le sens opposé à la flèche présente dans Power Query (i.e. le petit symbole de forme carré, comportant une flèche orientée à l'intérieur).

Voici une illustration :

Dès lors que je demande une information qui va dans le sens de la flèche, je rencontre une erreur.

Voici une illustration (alors que le graphique est correct pourtant) :

J'ai reproduit le même exemple simpliste sur Access...et je n'ai eu aucun mal à obtenir les données (tabulaires + graphiques).

Un truc m'échappe visiblement sur Excel. Avec Access, j'obtiens le bon résultat, sans erreur apparente. Je verrai demain si je trouve une astuce pour remonter les données sur toutes les tables Excel

 

[crocrocro]

Bonjour à tous,
Modification du post qui est parti "à l'insu de mon plein gré"
un élément de réponse pour comprendre le pourquoi mais, pour le moment au moins, sans proposer de solution :
S&C, j'ai modifié la table Professeur (ajouté un nouveau professeur ID 30) et la table de liens (remplacé sur la ligne ID40, l'id Professeur 20 par 30).
La raison : dans le TCD que tu montres, le résultat est (en fait parait) cohérent avec les données en tables;
Avec le nouveau professeur, on constate que ce n'est pas le cas.
Pourquoi le TCD n'est pas bon :
Excel, en fonction des relations (actives) et des informations du TCD essaie de "cheminer" entre les tables au travers des relations.
Dans le TCD, Excel constate qu'il manque à priori une relation et te le signale.
Ici c'est comme faire le lien entre l'id professeur (le 1er niveau de ton TCD est Nom-prénom de la table Professeur) et la table Séance-Cible, où tu affiches le nom de la séance.
Rien pour faire le lien entre les 2 tables. Il fait ce qu'on appelle un produit cartésien entre les 2 tables, ce qui se traduit dans le TCD par, pour chaque professeur, il affiche les mêmes résultats, ceux que tu obtiens si tu enlèves le prénom -nom du professeur dans le TCD.
Même si tu ajoutes une information de la table de liens, par exemple l'id-professeur et ou l'id-séance cible, cela ne résout pas le problème.
Le hic, c'est qu'on se retrouve dans une hiérarchie qui s'inverse :
Pour 1 professeur (1 ligne dans la table professeurs) on a n lignes dans la table de lien (table Professeur séance cible)
Pour 1 Séance Cible (1 ligne dans la table Séance Cible) on a n lignes dans la table de lien (table Professeur séance cible)
Et quand on parcourt dans le sens Table Professeur -> Table Professeur séance cible -> table Séance Cible (ou l'inverse) on passe de 1 -> N à N -> 1.
Ce qui fonctionnerait par exemple pour un TCD basé sur des Tables Régions – Départements - Communes où on cherche à connaitre le nombre de communes par Département par Région.
Et malgré la présence de toutes les informations nécessaires pour afficher les bons résultats dans ton TCD, Excel ne sait pas faire (en tous cas, je ne vois pas comment, mais quelqu'un a peut-être la solution) dans ce cas.
Sans passer par les TCD, ce qui est dommage, il y a des solutions avec du code VBA, mais ce sera beaucoup plus lourd et à faire pour chaque TCD de cas similaire.
Peut-être existe-t-il des solutions utilisant Power Query avec des requêtes qui remontent toutes les infos nécessaires (Nom-Prénom, Id Séance cible – Nom séance) dans une table et tu baserais alors ton TCD sur cette table et la Table Séquence.

 

[crocrocro]

En pj, une solution avec Power Query.
En résumé, une requête (connexion seulement) est créée sur les 4 tables concernées (professeur, Professeur - séance cible, Séance cible, séquence.
Une requête (connexion seulement suffit même si la table est créée) correspondant à la fusion des 4 requêtes
Sur la feuille TEST TCD Professeur
Le TCD avant

Le TCD avec Power Query

 

[SERIEUXETCOOL]

Bonjour le Forum,

Merci CroCroCro pour tes explications et pour avoir pris le temps à nouveau pour me répondre

Bon ben j'ai le sentiment d'avoir pris tout ce temps à construire un modèle de données précis, basé sur une structure de type base de données relationnelles...pour me retrouver bloqué au final par Excel

Malheureusement, je ne suis pas assez calé ni sur Excel, ni sur Power Query pour franchir cet obstacle. A mon niveau, il ne me reste plus que la piste Microsoft Access a explorer à court terme. Tout comme toi CroCroCro, j'ai modifié la table Professeur sur Access (ajouté un nouveau professeur ID 30) et la table de liens (remplacé sur la ligne ID40, l'ID Professeur 20 par 30). Access produit toujours le bon rapport :

Je vais reproduire l’ensemble de mon modèle sur Access et je verrai bien si ça coince toujours.

Merci CroCroCro pour m'avoir orienté sur ce schéma de SGBDR qui me semble bien plus pertinent que mon approche initiale. Je vais poursuivre jusqu'au bout de mes limites. Un grand merci encore à toi. J'ai appris pleins de nouvelles choses et suis monté en compétences

En espérant que ce fil aide les débutants à relativiser avec l'usage souvent abusif du mot "Base de données".

Ps : je viens de voir ton dernier message CroCroCro. Merci de m'avoir montré que c'était visiblement possible avec Power Query. Je vais passer un peu de temps avec Access et Power Query (2 outils que je ne connais pas bien).

 

[crocrocro]

Power Query pas compliqué pour ce que tu as à faire et comme je t'ai déjà dit, je débute complet sur PQ, il y a donc peut-être plus simple :
T'impose (seulement !) pour chaque TCD de ce cas de créer une fusion des tables concernées.
Dès lors qu'une connexion est créér dans PQ pour toutes les tables potentielles, çà prend 10 - 20 minutes pour créer la requête sur laquelle tu vas baser le TCD (ici la fusion des 4 tables).
Par simple actualisation, tout se rafraichit avec les données actualisées.
Et, oui, on aurait pu espérer qu'Excel sache gérer correctement les relations pour les TCD
Peut-être que quelqu'un aura la solution sans passer par ma proposition PQ.
Vu ton modèle de données et ce que tu souhaites en tirer, Access me parait plus adapté.
A voir en multi-consultation (et ou mise à jour) en réseau.

 

[SERIEUXETCOOL]

Bonjour le forum, bonjour CroCroCro qui ne doit pas être bien loin je pense ,

Je viens clôturer ce sujet ouvert depuis quelques semaines déjà. Bonne nouvelle : je considère mon problème initial résolu !

Grâce aux conseils du forum, et en particulier à CroCroCro qui a été formidable (incroyable je dirais même !), j'ai pu expérimenter, apprendre et comprendre par moi-même tout un tas d'approches que je ne connaissais pas. Je suis parti d'une demande initiale, formulée comme je "savais" à l'époque, et qui a bien évolué tout au long des posts ici. Mais finalement, le forum m'a aidé à répondre correctement à ma question !

Aujourd’hui :

• Je suis capable de saisir mes données dans un modèle structuré, de type relationnel,
• Je suis capable de saisir plusieurs Groupes, Professeurs, Salles, etc. (des relations plusieurs à plusieurs) sans souci,
• Je suis capable de produire des TCD me permettant d'atteindre mes objectifs de consultation et d'analyse, selon le niveau que je souhaite (le fameux "croiser les données dans tous les sens"),
• Plus de frein, toutes les portes sont ouvertes à ce stade du projet.

Voilà, j'ai appris à structurer des données, les relier et les agréger pour les analyser en profondeur. Cela répond totalement à ma question initiale. Je suis bien plus serein maintenant et enclin à collecter des centaines de données car je sais que je pourrai les faire parler d'une manière assez propre

Pour ceux que ça intéresserait, voici comment j'ai surmonté le dernier obstacle :

• Excel est peu adapté à la production d'une table unique comportant un grand ensemble de colonnes dont les données sont répétées. C'est juste impraticable.
• On peut créer des tableaux de données sous Excel, puis utiliser Power Pivot pour créer un modèle de données relationnelles. Une fois les tables liées, nous l'avons vu, il n'est pas toujours possible de remonter les informations dans tous les sens via TCD ce qui limite l'usage de cette approche. En revanche, utiliser Excel + Power Pivot pour modéliser le schéma relationnel de départ, est une bonne approche. Moi, ça m'a aidé à visualiser les choses, les imprimer si besoin, etc. (mieux que dans Access où c'est plus archaïque).
• On peut alors créer des tableaux de données sous Excel, puis utiliser Power Query pour ajouter les tableaux au modèle. On a plus besoin de Power Pivot ici, ni des relations entre les tables. En appliquant les conseils de CroCroCro, on peut produire une table fusionnée de l'ensemble des données. C'est déconcertant de facilité et ce n'est pas très long. Ce qui permet de produire un TCD passe partout sans effort et qui fonctionne (à l'inverse du blocage rencontré avec Power Pivot).
  • Cette vidéo m'a d’ailleurs fortement inspirée (Cliquez-ici pour la vidéo YouTube)
• Finalement, utiliser Excel + Power Pivot + Power Query (+ Power BI) peut donner des résultats comparables à Access tout seul. Je peux le faire avec ces 2 approches (ouf, j'ai matière à essayer et comparer^^).
• La suite ? A court terme, travailler la partie IHM (Interface Homme Machine) permettant d'alimenter la base de données de manière simple et intègre (UserForm Excel vs Formulaire Access). Le reste on verra bien, petit à petit, solution après solution^^

J'ai hâte d'arpenter cette nouvelle étape pour comparer les 3 approches [Excel + Power Pivot + Power Query + Power BI] VS [Access tout seul] VS [Access + Excel + Power Pivot + Power Query + Power BI pourquoi pas !!!] et utiliser celle qui me conviendra le mieux. Cela va me permettre de découvrir Access que je ne connais que depuis quelques semaines et qui me semble être l'outil le plus adapté à cette application.

Merci à toi encore @crocrocro car tu m'as toujours poussé dans la bonne direction avec des exemples simples ce qui m'a permis d'essayer pour comprendre. Vraiment au top, merci

Sujet clos pour cette partie concernant : - Conseils pour la structuration d'une base de données -

 

[crocrocro]

Bonjour S&C,
la discussion étant close, voir mon message...