Contexte

L'objectif de ce projet est de concevoir une plateforme pour l'analyse des données, de la récupération à la présentation sous forme de tableau de bord.

Les technologies sélectionnées sont MySQL comme base de données OLTP pour les données de préparation et PostgreSQL pour l'entrepôt de stockage des données de production. 

Les différentes étapes du projet comprennent, après extraction des données :

• la mise en place d'une base de données type OLTP;
• la conception et mise en œuvre d'un entrepôt de données ;
• la conception d'un tableau de bord ou dashboard ;
• la mise en place d'une pipeline de mise à jour.

Conception d'une base de données OLTP avec MySQL

La première étape consiste à concevoir la base de données type OLTP, puis à remplir la base de données OLTP avec les données fournies.

La base de données OLTP est généralement utilisée pour gérer les transactions commerciales quotidiennes d'une organisation comme une banque ou une chaîne de supermarchés. Les bases de données OLTP peuvent être lourdes en écriture ou avoir une charge de lecture/écriture équilibrée.

Il devrait gérer un grand nombre de transactions par seconde. Chaque transaction implique généralement l'accès (lecture/écriture) à une petite partie de la base de données, en d'autres termes la charge utile par transaction est faible.

Le temps nécessaire à l’exécution d’une transaction, généralement appelé latence, doit être très réduit.

Le schéma d'une base de données OLTP est hautement normalisé afin d'obtenir une latence très faible. Pour améliorer encore la latence, une base de données OLTP stocke uniquement les données récentes comme les données de la semaine dernière. Ils fonctionnent généralement sur un stockage très rapide comme le SSD.

Conception et construction d'un entrepôt de données avec PostgreSQL

La conception de l'entrepôt de données commence par un schéma, puis l'application de ce schéma par la mise en place des tables de faits et de dimensions.  Il est ensuite possible d'automatiser l'exportation des données incrémentielles quotidiennes de la base de données précédemment créée vers l'entrepôt de données nouvellement créé.

Pour l'exemple, nous prenons les données non réelles d'une société vendant uniquement des articles téléchargés tels que des livres électroniques, des films, des chansons, etc. Elle a une présence internationale et des clients du monde entier et souhaite créer un entrepôt de données afin de pouvoir créer des rapports tels que

• ventes totales par an et par pays
• ventes totales par mois par catégorie
• ventes totales par trimestre par pays
• ventes totales par catégorie et par pays

L'outil de conception ERD de pgAdmin de PostgreSQL est utilisé.

Le script SQL est ensuite généré à partir de l'outil de conception ERD pour obtenir le schéma de l'entrepôt de données.

Après avoir chargé les données dans les différentes tables, les requêtes d'agrégation sont créées, ainsi que des MQTs (Materialized Query Table) pour faciliter la création de rapports.

• Requête cumulative utilisant les colonnes pays, catégorie, ventes totales, pour obtenir les ventes totales par pays et par catégorie.

• Requête cumulative utilisant les colonnes année, pays et ventes totales, pour obtenir les ventes totales par an et par pays

Conception d'un tableau de bord

Les données de l'entrepôt de données sont ensuite récupérées, afin de créer un tableau de bord, avec plusieurs graphiques soulignant l'évolution des données nécessitant un suivi.

Reprenons l'exemple des données de la société de vente.

Conception d'une pipeline de mise à jour

Il est important de maintenir les données synchronisées entre différentes bases de données/entrepôts de données. Une tâche régulièrement effectuée est la synchronisation de la base de données de préparation et de l’entrepôt de données de production. L'automatisation de cette synchronisation permet de gagner beaucoup de temps et standardiser ce processus.

Plusieurs scripts Python sont écrits pour effectuer un chargement de données incrémentiel et programmé pour synchroniser les données entre la base de données et l'entrepôt.

Le script Python doit :

• se connecter à l'entrepôt de données PostgreSQL et identifiez la dernière ligne.
• se connecter à l'entrepôt de données intermédiaire MySQL et recherchez toutes les lignes postérieures à la dernière ligne de l'entrepôt de données.
• s'insérer les nouvelles données de l'entrepôt de données de préparation MySQL dans l'entrepôt de données de production PostgreSQL.