====== Conception de cube et ETL ======
Les données à utiliser avec ce TP sont disponibles ici: [[http://liris.cnrs.fr/~ecoquery/files/data-edo.zip|data-edo.zip]].
Elles ont été extraites des sources suivantes (utiles pour la documentation):
* [[http://www.data.gouv.fr/donnees/view/Informations-sur-la-localisation-des-accidents-corporels--de-la-circulation-sur-5-ann%C3%A9es---France-M%C3%A9-30379821?xtmc=accidents&xtcr=2|Informations sur la localisation des accidents corporels de la circulation sur 5 années]], c.f. la note descriptive.
* [[http://www.insee.fr/fr/methodes/nomenclatures/cog/|Code Officiel Géographique]], lien [[http://www.insee.fr/fr/methodes/nomenclatures/cog/documentation.asp|Documentation]]
L'objectif de ce TP est:
- de concevoir un cube de données permettant d'analyser les données ci-dessus et créer le schéma relationnel correspondant
- de mettre en place un processus de chargement des données via Talend
- 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.csv''((attention la table de fait n'est peut-être pas un copier/coller direct des données CSV)).
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 catalogue((i.e. une nouvelle base de donnée dans le jargon MySQL)) ''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 [[http://fr.talend.com/index.php|Talend]] permet, entre autres, et définir des processus de chargement de données. Ces processus, dits ETL((Extract, Transform, Load)), 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:
- connaitre les 10 points les plus dangereux (les emplacements où il y a eu la plus grosse somme de "gravité" d'accidents)
- quelles sont les 10 routes ayant le plus de morts
- le classement des régions les plus sûres (pour lesquelles il y a le moins d'accidents)
- le classement des services selon la gravité moyenne des accidents
- le type de voie le moins sûr (en termes de gravité des accidents)
- la départementale la plus dangereuse de Corse
- vos requêtes ici :)
===== Rendu =====
Un rapport est à rendre pour le 29/02/2012 par mail à [[emmanuel.coquery@liris.cnrs.fr?subject=[TI4] Rendu TP2|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