Introduction
Dans tout projet d’entreprise, nous allons nous connecter à nos bases de données internes (et éventuellement des sources externes pour augmenter les nôtres), mais celles-ci comportent toutes des particularités et sont généralement privées. Afin que vous puissiez suivre le projet « a la lettre », nous allons utiliser une base de données publique, mise à disposition par Microsoft pour faire la démonstration de SQL Serveur.
Nous utiliserons la base Wide Word Importer qui est la base d’exemple la plus récente de Microsoft. Nous utiliserons la base Wide Word Importer qui est la base d’exemple la plus récente de Microsoft. La documentation est disponible ici : Wide World Importers – Exemple de base de données pour SQL – SQL Server | Microsoft Learn
Nous allons dans cet article « installer » notre base d’exemple, la préparer et l’inspecter un minimum pour pouvoir débuter nos travaux de datalake.
Les mains dedans !
Je pars du principe que vous avez déjà accès à un serveur SQL (au moins 2016), que vous pouvez trouver directement sur le site de Microsoft en utilisant la licence « développeur » qui vous permettra de l’installer en toute légalité –> Téléchargements SQL Server | Microsoft. J’utilise personnellement la version 2022 (à l’heure de l’écriture de ce post). Si vous choisissez d’installer un nouveau serveur, je vous conseille de l’installer en langue anglaise.
Installation / restauration du backup de la base WWI
Nous allons commencer par télécharger le backup mis à disposition par Microsoft : WideWorldImporters-Full.bak (github.com)
Nous allons dans notre exemple utiliser SSMS, l’outil standard pour travailler avec un serveur SQL Serveur. Une fois connectées à notre instance SQL, nous allons procéder à la restauration du backup précédemment téléchargé. Pour ce faire, il suffit de faire un clic droit dans l’arborescence de notre serveur au niveau « Base de données » / « Databases » :
Nous allons maintenant devoir choisir le backup à utiliser. Globalement, nous allons dire dans les différentes fenêtres que notre source de backup est un fichier, et qu’il se trouve à un endroit précis sur notre disque local.
Attention : c’est l’utilisateur qui fait tourner SQL Server qui va effectuer l’action et qu’il faut donc que cet utilisateur ait accès à ce répertoire. Si vous n’avez rien configuré spécifiquement sur votre serveur, il est probable qu’il n’ait pas accès à votre répertoire « Téléchargements ». Pour des raisons de facilité, vous pouvez créer un répertoire dans « C: » et y déposer le fichier .bak téléchargé.
Une fois validée, la partie « Destination » du wizard est maintenant configurée, et il suffit de valider en cliquant sur « OK » :
Notre base de données est maintenant restaurée et est accessible sur notre serveur :
Exploration de Wide World Importers
En explorant les tables, nous pouvons rapidement voir que notre base contient différents schémas de données qui regroupent les données de différentes parties de notre entreprise, ainsi que certaines tables sont marquées comme « System-Versioned »
Ces tables « versionnées » utilisent une fonctionnalité SQL Server très intéressante que l’on tentera de recréer dans notre Datalake. Mais ce que cela nous indique c’est que cette base de données contient un historique et cela sera très intéressant et utile pour notre projet de Datalake. Nous allons pouvoir travailler à la création d’historiques et l’utilisation de celui-ci !
Données d’historique
Prenons l’exemple des clients. Une donnée qui nécessite souvent d’être traité en « SCD » (pour Slow Changing Dimension, et si vous n’êtes pas familier avec ce terme je vous invite sur cette page : Dimension à évolution lente — Wikipédia (wikipedia.org)). Dans notre base d’exemple qui utilise la fonctionnalité dédiée de SQL Serveur, nous pouvons voir que notre table est en faite constituée de deux tables :
Il est possible de requêter ces deux tables de façon totalement indépendante :
SELECT TOP (1000) *
FROM [WideWorldImporters].[Sales].[Customers]
SELECT TOP (1000) *
FROM [WideWorldImporters].[Sales].[Customers_Archive]
Si cette fonctionnalité propre à SQL Server peut être intéressante, elle ne nous intéressera pas particulièrement dans nos travaux. Elle nous permettra cependant de travailler sur deux cas de figure différents que l’on pourrait rencontrer dans nos projets de « Prod » :
- Une table non historisée qui présente simplement la valeur courante des données
- Une table historisée en mode SCD type 2
Données mortes ?
Cependant, si nous voyons qu’il existe déjà un historique. A l’instant nous avons une base de données « morte ». C’est à dire que nous avons restauré un backup mais que celui-ci est figé à l’instant du backup.
Il suffit d’une simple requête pour comprendre qu’en l’état pour travailler sur un datalake qui lui vivra au jour le jour avec des alimentations incrémentales, nous allons avoir un problème !
SELECT max([OrderDate]) FROM [WideWorldImporters].[Sales].[Orders];
Heureusement, Microsoft a pensé à nous et en continuant notre observation, nous allons trouver des procédures stockées très intéressantes :
Grace à la procédure « DataLoadSimulation.PopulateDataToCurrentDate », nous allons pouvoir charger de nouvelles données dans notre base et la rendre vivante ! La documentation est disponible ici : Générer des données dans les exemples SQL WideWorldImporters – SQL Server | Microsoft Learn
Nous allons donc tout de suite combler le vide dans nos données en utilisant cette procédure. En utilisant le code ci-dessous, vous allez vérifier la différence de date de la commande la plus récente avant et après la procédure :
SELECT max([OrderDate]) FROM [WideWorldImporters].[Sales].[Orders];
EXECUTE DataLoadSimulation.PopulateDataToCurrentDate
@AverageNumberOfCustomerOrdersPerDay = 60,
@SaturdayPercentageOfNormalWorkDay = 50,
@SundayPercentageOfNormalWorkDay = 0,
@IsSilentMode = 1,
@AreDatesPrinted = 1;
SELECT max([OrderDate]) FROM [WideWorldImporters].[Sales].[Orders];
Attention, cette instruction peut être particulièrement longue !!! Chez moi, cela à duré 6h45 pour charger de juin 2016 à aout 2024 !!!
Cela est dû au fait que cette procédure effectue une boucle pour chaque jour et génère la vie d’une journée dans notre entreprise. Si l’on change les derniers paramètres par « @IsSilentMode = 0, @AreDatesPrinted = 0 », nous pourrons observer les « messages » d’exécution de notre procédure et nous voyons ce qu’il se passe :
Nous pouvons sauvegarder ce fichier de log et faire le compte rendu à nos clients dès maintenant ! Cependant, nous allons plutôt charger tout cela dans notre Datalake et l’exploiter dans PowerBI ! Nous pourrons cependant aller vérifier si notre historique de nombre de commandes aperçu dans PowerBI est bien le même que celui généré lors de cette procédure.
Une fois ce grand chargement, ce qui nous intéressera sera plutôt de générer « une journée » de donnée et cela se fera très rapidement en réutilisant la même procédure. A savoir que la procédure génère les données « depuis la dernière date de nos données, jusqu’à hier ». Nous n’aurons donc jamais de données « du jour » en utilisant cette procédure.
Conclusion
Nous avons donc maintenant à notre disposition une base de données opérationnelle / transactionnelle (OLTP) d’entreprise avec des données d’exemple cohérente que nous pouvons faire vivre au quotidien.
De plus, Microsoft fournit d’autres packages de développements et de bases de données pour montrer l’ensemble des fonctionnalités SQL Serveur. En utilisant notamment SSIS pour créer un Data Warehouse. La documentation liée est disponible ici : WideWorldImportersDW – Workflow ETL – SQL Server | Microsoft Learn
Nous pourrons donc nous inspirer de ce qui est fait dans ce workflow « à l’ancienne / OnPrem », pour créer notre version dans Synapse en utilisant les principes BigData / Cloud / DataLakeHouse