Cas SQL / BI
Imaginons un exemple dans lequel une société aurait des employés ou prestataires qui ont un coût journalier, coût qui pour diverses raisons vient à varier au fil du jour. En pratique ces informations sont stockées dans une base de données d'un logiciel et devraient être reprises dans un "datawarehouse" afin d'avoir un historique et de permettre à la société d'avoir un aperçu clair et simple de la situation.
L'exercice suivant consiste donc à récupérer et stocker ces informations, puis mettre en place un système de dashboard grâce à " Power BI ". Une raison supplémentaire de reprendre en détails les méthodes utilisées, est que je me suis rendu compte que pas mal de personnes et professionnels en " Data Engineering " auraient fait cela d'une manière, certes fonctionnelle, en utilisant une méthode comme : Script externe tel que " PHP " ou " Python ", lancé par une tâche " Cron " qui dépend du système d'exploitation pour ensuite stocker les informations ligne par ligne et jour par jour ... Or il est fondamental de pouvoir être pratique et performant, en limitant les ressources et le temps d'exécution qui ont tous deux des impacts considérables, d'où ma proposition d'une alternative beaucoup plus directe et efficace !
Passons à la pratique !
1. La source de données
Comme on peut le voir sur l'image suivante, il s'agit d'une base de donnée contenant le nom de personnes avec un montant susceptible de varier dans la journée, généralement toutes les 6 heures.
2. Création du " Datawarehouse "
Pour les données à conserver et utiliser, le manager des ressources humaines dispose d'une petite machine locale qu'il voudrait utiliser, ce matériel fonctionnant sous Linux je vais donc installer une bases de données utilisant " MariaDB " , similaire à " MySQL ", générique et aussi afin de rester dans l'open-source pur et la légèreté. Installation des packages nécessaires :
sudo apt-get update && sudo apt-get upgrade
sudo apt-get install mariadb-server -y
sudo apt-get install mariadb-plugin-connect -y
Comme vous avez pu le voir sur le point précédent, j'ai installé un plug-in " Maria DB : Connect ", qu'il faudra également activer via une simple commande SQL :
INSTALL SONAME 'ha_connect';
Une fois le système en place, je crée une base de données et une table spécifique utilisant un système d'historisation interne : " System versioning " qui va garder une trace des changements effectués sur les enregistrements selon les périodes afin de permettre de retrouver les valeurs précédentes, cela en les stockant de manière spécifique. Ce dispositif est également disponible sous " Microsoft SQL Server " via les " Temporal tables " (Lien info).
USE Salary_Tracking;
CREATE TABLE Salary_History (
ID INT PRIMARY KEY,
LastName VARCHAR(255),
FirstName VARCHAR(255),
Salary DECIMAL(20, 2),
start_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
end_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME(start_timestamp, end_timestamp)
) WITH SYSTEM VERSIONING;
3. Récupération des données distantes
Un script dans un language de programmation tiers, lié à une tâche qui l'exécute, générant une bonne dose de code, en espérant que tout fonctionne ? Non Merci ! Je préfère utiliser un seul et unique système pour effectuer la totalité du processus et je reste donc dans le système de base de données choisi !
Pour cela, un script permet de créer une table distincte en local, tout cela grâce au plugin " Connect " qui va se connecter à une table distante afin de reprendre à l'identique sa structure et son contenu (possibilité de modifier les colonnes si besoin), et qui permettra lors d'une requête de sélection effectuée sur cette table locale, d'avoir une mise à jour transparente des données.
CREATE TABLE `Remote_source`
engine=CONNECT table_type=MYSQL
connection='mysql://user:pass@host/database/table';
Finalement il ne reste plus qu'à insérer ces données distantes dans la table contenant l'historique, en effectuant une insertion basée sur la sélection de ma table liée précédemment :
INSERT INTO `Salary_History` (ID, LastName, FirstName, Salary) (SELECT * FROM `Remote_source`)
ON DUPLICATE KEY UPDATE Salary = `Remote_source`.`Salary`;
4. Mise à jour des données
Bien évidement, exécuter manuellement une requête d'insertion des données n'est pas viable à long terme. De ce fait, je vais utiliser une autre possibilité du moteur de base de données : l'exécution de tâches automatiques qui peuvent être définies selon les besoins, ici une exécution toutes les six heures et une minute à partir d'une certaine date, afin de récupérer et stocker les données qui ont été modifiées dans la table distante.
CREATE EVENT IF NOT EXISTS `Update_History`
ON SCHEDULE EVERY 6 HOUR STARTS '2020-03-01 00:01:00'
DO
INSERT INTO `Salary_History` (ID, LastName, FirstName, Salary) (SELECT * FROM `Remote_source`)
ON DUPLICATE KEY UPDATE Salary = `Remote_source`.`Salary`;
Une fois que cette tâche récurrente est définie, ne surtout pas oublier de l'activer, ou même de vérifier si elle et les évènements sont bien en place :
SET GLOBAL event_scheduler = ON; # Start Scheduler
SHOW EVENTS; # Give complete information on a process
SHOW PROCESSLIST; # Show the processes that are registered
Une requête , SELECT * FROM Salary_History; , permet donc de consulter les données de chaque utilisateur à l'instant présent :
Tandis qu'une autre , SELECT * FROM Salary_History FOR SYSTEM_TIME ALL; , permet de consulter l'historique :
5. Mise à disposition de l'historique
En pensant déjà à l'étape suivante sous " Power BI " pour les visualisations voulues, les données complètes et brutes de l'historisation telles qu'actuellement présentes ne seront pas suffisamment bien présentées et demandent donc quelques améliorations. Plutôt que de le faire dans un outil tiers, il m'apparaît utile d'effectuer une dernière tâche sous SQL afin de continuer à maximiser les performances et d'éviter des manipulations ultérieures qui seront ici très simples. Afin de délivrer ces données je vais donc demander au système de gestion de base de données (SGDB) de créer une vue qui contiendra mon "dataset" complet avec : le jour de l'année, la date, et une moyenne du salaire sur la journée.
CREATE VIEW `Salary_View` AS
SELECT ID, LastName, FirstName, DAYOFYEAR(start_timestamp) AS `DayOfYear`, DATE(start_timestamp) as `DayDate`, AVG(Salary) AS `DayAverageSalary`
FROM `Salary_History` FOR SYSTEM_TIME ALL GROUP BY DAYOFYEAR(start_timestamp), ID;
6. Données visualisables
Passons à l'outil de visualisation qui permettra à l'utilisateur de pouvoir consulter facilement ses données. Je ne vais pas décrire tout le processus qui consiste à créer une source de données effectuant une requête directe sur la base de données (d'où l'existence de la vue définie au point précédent), ni la vérification du dataset ou la manière de créer chaque élément, par contre je vous laisse découvrir le résultat final attendu.