Rapport Projet JO Entrepôt de données

Sommaire

1. Introduction

2. Analyse de la Métadonnée

2.1 Analyse de la Qualité des Données

2.2 Analyse des valeurs manquantes

2.3 Conclusion et Actions Correctives

2.4 Sélection et Préparation des Données

2.5 Analyse des différences de dénomination des champs de données et recommandations pour la standardisation

• gender : format des données ['Female', 'Male']

• team_gender : format des données ['M', 'W', 'X', 'O']
• M : probablement pour "Male" (homme)
• W : probablement pour "Female" (femme)
• X, O : pourraient représenter un genre mixte, une autre catégorie ou une valeur inconnue

• name

• name_short

• name_tv

• nickname

• birth_country

• code_team

• country

• country_code

• country_long

• nationality

• nationality_code

• nationality_long

• residence_country

• team

• team_gender (peut indiquer le genre de l’équipe)

• event

• event_medal

• event_type

• events

• url_event

• event_medal

• is_medallist

• medal_code

• medal_date

• medal_type

• Standardisation des données de genre : il est recommandé d’unifier les valeurs de team_gender, en remplaçant M, W par Male, Female, et en précisant la signification des catégories X, O.

• Uniformisation des champs liés aux pays et aux équipes : il serait pertinent de standardiser country_code en utilisant un format ISO et d’uniformiser team pour éviter toute confusion.

• Harmonisation des champs event et medal : ces champs peuvent être regroupés et standardisés afin d’éviter la redondance et d’améliorer la cohérence des analyses de données.

3. Architecture et Modélisation du Système de Données

3.1 Flux des Données dans le Processus de Modélisation du Data Warehouse

1. Extraction des données brutes (CSV → Database)

2. Nettoyage et transformation des données (Database → ODS)

3. Chargement des données optimisées pour l’analyse (ODS → Data Warehouse)

3.2 Base de Données Relationnelle (Database)

1. athletes : Contient les informations détaillées sur les athlètes (nom, pays, genre, discipline, date de naissance).

2. countries : Répertorie les pays participants avec leur identifiant unique.

3. sports & categories : Permettent de structurer les disciplines sportives et leurs catégories associées.

4. events : Stocke les événements olympiques, associés aux sports et aux lieux de compétition.

5. venues : Répertorie les infrastructures où se déroulent les compétitions.

6. medals & medallists : Contiennent les informations sur les médailles remportées, avec les détails des épreuves et des gagnants.

7. teams & team_athletes : Gèrent les équipes sportives et leurs membres.

8. schedules : Enregistre le calendrier des épreuves, y compris les dates de début et de fin.

• Clés primaires et étrangères : Maintiennent la cohérence et empêchent les incohérences relationnelles.

• Contraintes d’unicité : Évitent les doublons dans des entités critiques (ex : un id_athlete unique par athlète).

• Validation des types de données : Assure que chaque colonne respecte son format attendu (ex : medal_date en format DATE).

• Gestion des valeurs nulles : Certaines colonnes essentielles (comme id_country pour athletes) ne peuvent pas être vides pour éviter des problèmes d’analyse ultérieurs.

1. Nettoyage et Préparation : Les données brutes extraites des fichiers CSV peuvent contenir des erreurs, des incohérences ou des valeurs manquantes. En passant par une base relationnelle, nous pouvons nettoyer et structurer ces données avant leur transformation.

2. Stockage Structuré : Contrairement à un simple stockage de fichiers plats, une base relationnelle permet une organisation cohérente et optimisée des informations.

3. Contrôle des Modifications : Grâce aux contraintes relationnelles, nous pouvons assurer l’intégrité des données avant leur chargement dans l’entrepôt de données.

4. Flexibilité pour l’ETL : Les données étant stockées sous un format structuré, elles peuvent être plus facilement transformées et chargées dans l’ODS et le Data Warehouse via des requêtes SQL optimisées.

3.3 Modélisation du Data Warehouse (DWH)

1. Identification des besoins analytiques
Nous avons d’abord identifié les principales requêtes et indicateurs dont nous avions besoin, tels que :
• Analyse des âges des athlètes
• Répartition des médailles par pays et par sport
• Évolution chronologique des performances

2. Développement d’un modèle initial simplifié
Plutôt que de concevoir une grande table de faits unique, nous avons décidé de démarrer avec un modèle plus restreint, incluant seulement les dimensions et faits nécessaires pour répondre aux premières analyses.

3. Ajout progressif de nouvelles dimensions et faits
Une fois le premier modèle en place et testé avec succès, nous avons ajouté progressivement d’autres dimensions et faits selon les besoins émergents, rendant le DWH flexible et évolutif.

4. Défis et Solutions avec Talend

4.1 Problème d’identification de la colonne code dans la table medal

4.2 Problème de suppression de colonnes dans Edit Schema

4.3 Configuration des paramètres d’importation (File Delimited)

• Encodage : UTF-8

• Format : UNIX

• Séparateur de champ : ","

• Séparateur de ligne : "\n"

• Délimiteur de texte : '"'

4.4 Expressions utilisées en Talend

• Vider une table de la base de données :

TRUNCATE TABLE categories CASCADE;

• Filtrer les données non nulles :

row1.id_venue != null && !row1.id_venue.trim().isEmpty()

• Créer une séquence incrémentale :

Numeric.sequence("seq",1,1).toString()

• Récupérer la valeur actuelle d’une boucle :

((Integer)globalMap.get("tLoop_1_CURRENT_VALUE")).toString()

• Formatage des mois sous forme de chaîne :

(0+month.month).substring((0+month.month).length()-2)

• Conversion du genre :

row10.gender.equals("Male")?"M":"W"

• Vérifier si une chaîne contient des lettres :

row23.code.matches(".*[A-Za-z].*")

4.5 Problème d’incomplétude des catégories sportives

4.6 Contraintes de base de données et erreurs de parsing

• Valeur trop longue (value too long), empêchant l’insertion correcte des données.

• Violation de clé étrangère (foreign key violation), empêchant certaines données d’être correctement associées aux tables principales.

• Violation de contrainte d’unicité (violate constraint), provoquant l’échec de l’insertion.

4.7 Erreurs de parsing des données

• Erreur de parsing sur birth_date : Couldn't parse value for column 'birth_date' in 'row10', value is 'Athlete'. Cela indique que Talend a tenté d’interpréter une valeur textuelle comme une date, ce qui a entraîné une erreur de format.

• Taille de champ dépassée : value too long for type character varying(10), indiquant que la valeur d’un champ dépasse la longueur définie dans la base. Pour corriger cela, nous avons soit modifié la structure de la base, soit tronqué les valeurs trop longues.

5. Visualisation des Données avec Power BI

5.1 Pyramide des âges des athlètes par sexe

5.2 Répartition des athlètes par pays et par sexe

5.3 Ratio des médaillés par rapport aux participants par pays

5.4 Répartition des médailles par pays et type de médaille

5.5 Évolution des médailles au fil du temps

• Certains pays ont un départ rapide, gagnant de nombreuses médailles dès le début des Jeux, tandis que d’autres progressent plus lentement.

• Il est possible d’identifier des pics de performance, correspondant à des jours où de nombreuses finales sont organisées.

• Cette visualisation permet aussi d’analyser l’impact de la programmation des épreuves sur les résultats des différentes nations.

5.6 Répartition des médailles par discipline sportive

5.7 Répartition des médailles par catégorie de sport

5.8 Répartition des athlètes par pays sur une carte géographique

5.9 Répartition des médailles par pays sur une carte géographique

6. Annale

CREATE TABLE athletes (
id_athlete VARCHAR(20) NOT NULL,
id_country VARCHAR(20) NOT NULL,
name VARCHAR(50),
gender VARCHAR(5),
sport VARCHAR(50),
event VARCHAR(50),
birth_date DATE,
PRIMARY KEY (id_athlete)
);

CREATE TABLE categories (
id_category VARCHAR(20) NOT NULL,
category VARCHAR(50),
PRIMARY KEY (id_category)
);

CREATE TABLE countries (
id_country VARCHAR(20) NOT NULL,
country VARCHAR(50),
PRIMARY KEY (id_country)
);

CREATE TABLE events (
id_event VARCHAR(20) NOT NULL,
id_sport VARCHAR(20) NOT NULL,
event VARCHAR(50),
PRIMARY KEY (id_event)
);

CREATE TABLE medals (
id_medal VARCHAR(20) NOT NULL,
id_country VARCHAR(20) NOT NULL,
id_event VARCHAR(20) NOT NULL,
id_team VARCHAR(20),
id_athlete VARCHAR(20),
medal_date DATE,
medal_type VARCHAR(50),
gender VARCHAR(5),
CONSTRAINT medals_PK PRIMARY KEY (id_medal),
CONSTRAINT chk_medal CHECK (
(id_athlete IS NOT NULL AND id_team IS NULL) OR
(id_athlete IS NULL AND id_team IS NOT NULL)
)
);

CREATE TABLE metallists (
id_medalliste VARCHAR(20) NOT NULL,
id_country VARCHAR(20) NOT NULL,
id_event VARCHAR(20) NOT NULL,
id_athlete VARCHAR(20) NOT NULL,
id_team VARCHAR(20),
medal_type VARCHAR(10),
gender VARCHAR(5),
medal_date DATE,
is_medallist CHAR(1),
PRIMARY KEY (id_medalliste)
);

CREATE TABLE schedules (
id_schedule VARCHAR(20) NOT NULL,
id_event VARCHAR(20) NOT NULL,
id_venue VARCHAR(20) NOT NULL,
"date" DATE,
start_date TIMESTAMPTZ,
end_date TIMESTAMPTZ,
event_medal NUMERIC,
phase VARCHAR(50),
PRIMARY KEY (id_schedule)
);

CREATE TABLE sports (
id_sport VARCHAR(20) NOT NULL,
id_category VARCHAR(20) NOT NULL,
sport VARCHAR(100),
PRIMARY KEY (id_sport)
);

CREATE TABLE team_athletes (
athletes_id_athlete VARCHAR(20) NOT NULL,
teams_id_team VARCHAR(20) NOT NULL,
PRIMARY KEY (athletes_id_athlete, teams_id_team)
);

CREATE TABLE teams (
id_team VARCHAR(20) NOT NULL,
id_country VARCHAR(20) NOT NULL,
team VARCHAR(50),
team_gender VARCHAR(5),
PRIMARY KEY (id_team)
);

CREATE TABLE venues (
id_venue VARCHAR(20) NOT NULL,
venue VARCHAR(100),
PRIMARY KEY (id_venue)
);

- Foreign Keys

ALTER TABLE athletes ADD CONSTRAINT athletes_countries_FK FOREIGN KEY (id_country) REFERENCES countries (id_country);
ALTER TABLE events ADD CONSTRAINT events_sports_FK FOREIGN KEY (id_sport) REFERENCES sports (id_sport);
ALTER TABLE medals ADD CONSTRAINT medals_athletes_FK FOREIGN KEY (id_athlete) REFERENCES athletes (id_athlete);
ALTER TABLE medals ADD CONSTRAINT medals_countries_FK FOREIGN KEY (id_country) REFERENCES countries (id_country);
ALTER TABLE medals ADD CONSTRAINT medals_events_FK FOREIGN KEY (id_event) REFERENCES events (id_event);
ALTER TABLE medals ADD CONSTRAINT medals_teams_FK FOREIGN KEY (id_team) REFERENCES teams (id_team);
ALTER TABLE metallists ADD CONSTRAINT metallists_athletes_FK FOREIGN KEY (id_athlete) REFERENCES athletes (id_athlete);
ALTER TABLE metallists ADD CONSTRAINT metallists_countries_FK FOREIGN KEY (id_country) REFERENCES countries (id_country);
ALTER TABLE metallists ADD CONSTRAINT metallists_events_FK FOREIGN KEY (id_event) REFERENCES events (id_event);
ALTER TABLE metallists ADD CONSTRAINT metallists_teams_FK FOREIGN KEY (id_team) REFERENCES teams (id_team);
ALTER TABLE schedules ADD CONSTRAINT Schedule_events_FK FOREIGN KEY (id_event) REFERENCES events (id_event);
ALTER TABLE schedules ADD CONSTRAINT Schedule_venues_FK FOREIGN KEY (id_venue) REFERENCES venues (id_venue);
ALTER TABLE sports ADD CONSTRAINT sports_categories_FK FOREIGN KEY (id_category) REFERENCES categories (id_category);
ALTER TABLE team_athletes ADD CONSTRAINT team_athletes_athletes_FK FOREIGN KEY (athletes_id_athlete) REFERENCES athletes (id_athlete);
ALTER TABLE team_athletes ADD CONSTRAINT team_athletes_teams_FK FOREIGN KEY (teams_id_team) REFERENCES teams (id_team);
ALTER TABLE teams ADD CONSTRAINT teams_countries_FK FOREIGN KEY (id_country) REFERENCES countries (id_country);