Conception de cube et ETL

Les données à utiliser avec ce TP sont disponibles ici: data-edo.zip.

Elles ont été extraites des sources suivantes (utiles pour la documentation):

L'objectif de ce TP est:

  1. de concevoir un cube de données permettant d'analyser les données ci-dessus et créer le schéma relationnel correspondant
  2. de mettre en place un processus de chargement des données via Talend
  3. de configurer et d'interroger le cube via Mondrian

Conception du cube

Parcourir les notices explicatives (cf liens ci-dessus) concernant les données à utiliser.

La table de faits portera sur les données des accidents contenus dans le fichier etalabv3met.csv1). On identifiera les différentes dimensions utiles (a priori 3), dont la dimension localisation. La hiérarchie dans cette dernière s'articule principalement autour des communes:

  • les informations de localisation à plus gros grain (départements, régions) peuvent être déduites des couples communes/département via les données additionnelles (fichiers .txt)
  • les informations à grain plus fin sont dans le fichier de faits. On distinguera le type de localisation (c.f. notice), puis les localisations elles-mêmes.

Créer le schéma relationnel dans la base mysql. On prendra un nouveau catalogue2) accidents. Vous disposez de MySQL Administrator et de MySQL Query Browser accessibles depuis le sous-menu “Développement” du menu des applications de la machine virtuelle.

Il peut être utile d'utiliser ici le workbench Mondrian (dans apps/).

Pour utiliser la connection JDBC dans le workbench, il faut:

  • Editer workbench.sh et remplacer drivers/$i par $i
  • Configurer le workbench (menu Tools → Preferences):
    • Driver class name: com.mysql.jdbc.Driver
    • Connection url: jdbc:mysql:///foodmart
    • User: root
    • Password: etudiant

Prise en main de Talend

Le logiciel Talend permet, entre autres, et définir des processus de chargement de données. Ces processus, dits ETL3), peuvent être édités graphiquement dans l'IDE Talend, cette dernière générant alors du code Java pour effectuer en pratique la transformation et le chargement des données dans une base, par exemple dans un entrepôt de données. Un processus consiste en un ensemble de composants, chacun chargé d'un tâche précise: lire un certain fichier, filtrer des lignes, effectuer un mapping de colonnes, faire une transformation de valeurs, fusionner des données, ou encore écrire le résultat dans un fichier ou l'insérer dans un table d'un SGBD.

Documentation

La documentation de talend est disponible dans la machine virtuelle (répertoire apps/talend, fichier TalendOpenStudio_DI_UG_50a_FR.pdf). Vous pouvez survoler l'introduction et le chapitre 4 sur la conception de jobs d'intégration de données. Le manuel de référence est TalendOpenStudio_Components_RG_50a_FR.pdf.

Mini tutoriel

Lancer talend (répertoire apps/talend/TOS_DI-r74687-V5.0.1, lancer ./TOS_DI-linux-gtk-x86.sh). Importer puis lancer le projet Demo de Talend.

L'arborescence Jobs contient un ensemble de processus illustrant l'utilisation des différents composants disponibles.

Créer un nouveau Job. Dans la palette à droite, sélectionner le composant Fichier → Lecture → tFileInputDelimited, puis glisser déposer le composant dans l'éditeur. Sélectionner le composant tFileInputDelimited_xx dans l'éditeur, puis choisissez l'onglet Component dans la zone du bas. Changer le nom du fichier CSV pour indiquer l'emplacement du fichier reg2011.txt.

Regarder le contenu de ce fichier dans un éditeur de texte. Revenez dans Talend et chagez le caractère séparateur de champ. Indiquer le bon nombre de lignes d'entête, puis éditez le schéma (bouton …) et ajoutez pour chaque colonne du fichier reg2011.txt la description de son schéma.

Depuis la palette, ajouter un composant Logs & Erreurs → tLogRow. Cliquez-droit sur le tFileInputDelimited_xx → Ligne → Main, puis sur cliquez sur le tLogRow_xx pour établir une connexion entre les deux. En bas, passez dans l'onglet “Exécuter (Job toto)”, lancer l'exécution et vérifiez rapidement la cohérence de ce qui s'affiche.

Créer une nouvelle table regions_test(numero,nom) dans votre base accidents. Dans Talend, ajouter un composant Bases de données → MySQL → tMySQLOutput. Ouvrez l'arborescence Métadonnées sur la gauche et créez une nouvelle connexion. Renseignez les champs de façon à vous connecter au schéma accidents créé précédement dans la base MySQL. Puis faites un clic droit sur la connexion → Récupérer le schéma. Revenir sur votre composant tMySQLOutput. Indiquez Référenciel comme type de propriété et choisissez Votre nouvelle connexion (bouton …), puis indiquez la table regions_test. Pour le schéma, indiquez également Référenciel et choisissez à nouveau regions_test.

Ajouter un composant Transformation → tMap. Supprimez la connexion tFileInputDelimited → tLogRow et remplacez la par un connexion vers le tMap. Ajoutez une autre connexion, entre le tMap et le tMySQLOutput. Si vous n'avez pas eu de proposition pour récupérer le schéma du composant cible, éditez le schéma du tMySQLOutput et cliquez sur la double flèche vers la gauche pour copier le schéma de la BD vers le schéma d'entrée. Sélectionnez le tMap et lancer l'éditeur de mapping (bouton …) depuis l'onglet Component. Faire un glissez déposer du champ REGION vers le champ numero et du champ NCC vers le champs nom. Ajoutez enfin un connexion vers le tLogRow, que vous aurez configuré préalablement pour utiliser le schéma de la table regions_test. Effectuer un mapping identique à celui vers le tMySQLOutput. Lancez le job et vérifiez qu'il a correctement fonctionné.

Créer un nouveau composant pour lire le fichier depts2011.txt. Créer une table departements_test(numero,nom,nom_region). Créer un composant pour écrire dans cette table (il peut être utile de mettre à jour les schémas dans les métadonnées connexions Talend). Ajouter un composant Transformation → tJoin, puis une connexion depuis le composant qui lit dans depts2011.txt vers ce tJoin et une connexion depuis le tMap vers ce tJoin. Connecter ensuite le tJoin vers un nouveau tMap, ce dernier étant connecté à la sortie dans la table departements_test. Vérifier les différents schémas (e.g. cohérence du schéma de sortie du premier tMap avec le schéma d'entrée lookup du tJoin). Dans les propriété du tJoin, spécifier l'attribut de jointure (region).

Tester.

Définition du processus ETL pour le cube

Créer un nouveau projet Talend et définir un/des job(s) pour charger les données des fichiers texte (.txt et .csv).

Configuration du cube dans Mondrian

Aller dans le répertoire ~/apps/apache-tomcat-7.0.23/webapps/mondrian.

Créer un fichier WEB-INF/queries/Accidents.xml en copiant puis modifiant le fichier WEB-INF/queries/FoodMart.xml. Modifier les fichier faisant référence à FoodMart pour faire maintenant référence à Accidents. Penser à modifier les requêtes MDX et les urls JDBC. Liste des fichiers:

grep -rli foodmart . | grep -v 'lib\|/xmla/\|\.class'

Quelques requêtes pour finir

Requêter votre cube via JPivot pour:

  1. connaitre les 10 points les plus dangereux (les emplacements où il y a eu la plus grosse somme de “gravité” d'accidents)
  2. quelles sont les 10 routes ayant le plus de morts
  3. le classement des régions les plus sûres (pour lesquelles il y a le moins d'accidents)
  4. le classement des services selon la gravité moyenne des accidents
  5. le type de voie le moins sûr (en termes de gravité des accidents)
  6. la départementale la plus dangereuse de Corse
  7. vos requêtes ici :)

Rendu

Un rapport est à rendre pour le 29/02/2012 par mail à Emmanuel Coquery avec comme sujet “[TI4] Rendu TP2”. Ce rapport comprendra:

  • Le schéma de votre cube (avec justification des choix, en particulier sur le type des attributs et les contraintes not NULL) et le schéma relationnel correspondant. On détaillera la/les représentation(s) de la localisation.
  • Le diagramme Talend décrivant le processus de chargement des données, avec une description des composants utilisés.
  • Les requêtes MDX utilisées dans JPivot pour les questions de la section précédente.
  • Les erreurs/problèmes rencontrés dans les données et les moyen de les traiter. On fournira en annexe:
    • des statistiques pour les tables (nombre de tuples, nombre de valeurs pour les différents attributs)
    • le fichier xml de configuration de Mondrian
1)
attention la table de fait n'est peut-être pas un copier/coller direct des données CSV
2)
i.e. une nouvelle base de donnée dans le jargon MySQL
3)
Extract, Transform, Load