Transformer les données en connaissances : analyse d’un restaurant avec SQL et Power BI

Récemment, j’ai participé à un entretien d’embauche pour rejoindre l’équipe Dataviz – BI d’une grande entreprise. Au cours de la conversation, on m’a présenté le cas d’utilisation des données opérationnelles d’un restaurant, comment les structurer, les analyser et les visualiser. Je pense que ma solution était trop pratique et pas assez technique, heureusement mon interlocuteur m’a guidé pour que l’explication s’oriente vers une solution qui contribuerait à l’activité de l’entreprise. J’ai donc décidé de créer un projet qui implique la création d’un Data Warehouse en utilisant le modèle de schéma en étoile et l’analyse des données en utilisant SQL et Power BI. Je vous raconte ici les étapes que j’ai suivies pour mener à bien ce projet, de la conception du schéma en étoile à la création d’un tableau de bord interactif dans Power BI.

Introduction

Le projet vise à concevoir un entrepôt de données pour un restaurant en utilisant le schéma en étoile de la modélisation dimensionnelle. Ce schéma facilitera la manipulation et l’analyse des données opérationnelles du restaurant, permettant la génération de rapports et l’analyse des performances à l’aide de requêtes SQL dans un premier temps. Nous intégrerons ensuite les données dans Power BI afin de créer un tableau de bord pour l’analyse et la distribution à d’autres parties prenantes.

Objectifs du projet

Concevoir un schéma en étoile pour l’entrepôt de données du restaurant.
Implémenter les tables nécessaires dans une base de données SQL.
Remplir les tables avec des données simulées.
Effectuer des requêtes SQL pour analyser divers aspects de l’entreprise.
Exporter la base de données au format .csv pour l’importer dans Power BI.
Créer un tableau de bord dans Power BI pour l’analyse.
Conclusions.

Conception d’un schéma en étoile

Le schéma en étoile est une technique de modélisation dimensionnelle utilisée dans la construction d’entrepôts de données. C’est l’une des méthodes les plus populaires en raison de sa simplicité et de son efficacité dans l’organisation des données pour l’analyse et les requêtes.

La première étape du projet a consisté à concevoir un schéma en étoile pour l’entrepôt de données du restaurant. Un schéma en étoile se caractérise par la présence d’une table centrale appelée « table des faits« , qui contient les données transactionnelles, et de plusieurs « tables de dimensions« , qui contiennent les attributs descriptifs connexes.

Pour ce projet, j’ai conçu le système suivant :

Table des faits : Sales_Fact
- Sales_ID (clé primaire)
- Customer_ID (clé étrangère)
- Product_ID (clé étrangère)
- Location_ID (clé étrangère)
- Date_ID (clé étrangère)
- Quantity_Sold
- Total_Amount
- Cost
Tableaux de dimensions :
- Customer_Dim
  - Customer_ID (clé primaire)
  - Customer_Name
  - Customer_Email
- Product_Dim
  - Product_ID (clé primaire)
  - Product_Name
  - Product_Category
- Location_Dim
  - Location_ID (clé primaire)
  - Location_Name
  - Location_City
- Date_Dim (clé primaire)
  - Date_ID (clé primaire)
  - Date
  - Day
  - Month
  - Year

Avantages du système de l’étoile

Simplicité : la structure est facile à comprendre et à parcourir, tant pour les développeurs que pour les utilisateurs finaux.
Performance des requêtes : les requêtes dans un schéma en étoile sont souvent rapides car les relations entre la table des faits et les tables des dimensions sont simplifiées.
Évolutivité : il est facile de se développer en ajoutant des dimensions ou des faits supplémentaires sans réorganiser l’ensemble de la structure.

Inconvénients du système Star

Redondance des données : Les tables dimensionnelles peuvent contenir des données redondantes, ce qui peut augmenter l’espace de stockage.
Maintenance : la redondance et la duplication des données peuvent compliquer la maintenance.

Mise en œuvre des tables SQL

Après avoir conçu le schéma en étoile, j’ai procédé à l’implémentation des tables dans une base de données SQL. J’ai utilisé PostgreSQL à cette fin. Voici le code SQL pour créer les tables :

-- Créer une table de faits
CREATE TABLE Sales_Fact (
    Sales_ID SERIAL PRIMARY KEY,
    Customer_ID INT,
    Product_ID INT,
    Location_ID INT,
    Date_ID INT,
    Quantity_Sold INT,
    Total_Amount DECIMAL(10, 2),
    Cost DECIMAL(10, 2)
);

-- Créer des tableaux de dimensions
CREATE TABLE Customer_Dim (
    Customer_ID SERIAL PRIMARY KEY,
    Customer_Name VARCHAR(100),
    Customer_Email VARCHAR(100)
);

CREATE TABLE Product_Dim (
    Product_ID SERIAL PRIMARY KEY,
    Product_Name VARCHAR(100),
    Product_Category VARCHAR(100)
);

CREATE TABLE Location_Dim (
    Location_ID SERIAL PRIMARY KEY,
    Location_Name VARCHAR(100),
    Location_City VARCHAR(100)
);

CREATE TABLE Date_Dim (
    Date_ID SERIAL PRIMARY KEY,
    Date DATE,
    Day INT,
    Month INT,
    Year INT
);

Remplir les tableaux avec des données simulées

Pour simuler des données dans les tables, j’ai utilisé la fonction RANDOM() de PostgreSQL. Vous trouverez ci-dessous un exemple de la manière dont j’ai inséré les données simulées dans la table des faits :

-- Insérer des données dans Date_Dim
INSERT INTO Date_Dim (Date_ID, Date, Day, Month, Quarter, Year) VALUES
(1, '2023-01-01', 1, 1, 1, 2023),
(2, '2023-01-02', 2, 1, 1, 2023);

-- Insérer des données dans Product_Dim
INSERT INTO Product_Dim (Product_ID, Product_Name, Category, Price) VALUES
(1, 'Burger', 'Food', 5.99),
(2, 'Fries', 'Food', 2.99);

-- Insérer des données dans Customer_Dim
INSERT INTO Customer_Dim (Customer_ID, Customer_Name, Gender, Age, City, Country) VALUES
(1, 'John Doe', 'M', 30, 'New York', 'USA'),
(2, 'Jane Smith', 'F', 25, 'Los Angeles', 'USA');

-- Insérer des données dans Location_Dim
INSERT INTO Location_Dim (Location_ID, Store_Name, City, Region, Country) VALUES
(1, 'Main Street', 'New York', 'Northeast', 'USA'),
(2, 'Second Avenue', 'Los Angeles', 'West', 'USA');

-- Insérer des données dans Sales_Fact
INSERT INTO Sales_Fact (Sale_ID, Date_ID, Product_ID, Customer_ID, Location_ID, Quantity_Sold, Total_Amount) VALUES
(1, 1, 1, 1, 1, 2, 11.98),
(2, 2, 2, 2, 2, 3, 8.97);

Effectuer des requêtes SQL pour l’analyse

Une fois les tableaux remplis, j’ai exécuté plusieurs requêtes SQL pour analyser différents aspects de l’entreprise. Par exemple :

-- Analyse des ventes par client
SELECT C.Customer_Name, SUM(S.Total_Amount) AS Total_Spent
FROM Sales_Fact S
JOIN Customer_Dim C ON S.Customer_ID = C.Customer_ID
GROUP BY C.Customer_Name
ORDER BY Total_Spent DESC;

-- Ventes totales par produit
SELECT P.Product_Name, SUM(S.Quantity_Sold) AS Total_Quantity, SUM(S.Total_Amount) AS Total_Sales FROM Sales_Fact S JOIN Product_Dim P ON S.Product_ID = P.Product_ID GROUP BY P.Product_Name;

-- Ventes totales par mois
SELECT D.Month, D.Year, SUM(S.Total_Amount) AS Total_Sales
FROM Sales_Fact S
JOIN Date_Dim D ON S.Date_ID = D.Date_ID
GROUP BY D.Month, D.Year
ORDER BY D.Year, D.Month;

-- Ventes par lieu
SELECT L.Store_Name, L.City, SUM(S.Total_Amount) AS Total_Sales
FROM Sales_Fact S
JOIN Location_Dim L ON S.Location_ID = L.Location_ID
GROUP BY L.Store_Name, L.City;

L’utilisation du schéma en étoile facilite l’organisation et l’analyse des données dans un entrepôt de données, ce qui permet de générer des requêtes SQL efficaces. Cela montre comment les données d’un restaurant peuvent être structurées et manipulées pour obtenir des informations utiles à la prise de décision et à l’amélioration des performances de l’entreprise.

Exporter la base de données au format .csv

Pour importer les données dans Power BI, j’ai d’abord exporté les tables de la base de données vers des fichiers .csv. J’ai utilisé les instructions suivantes dans PostgreSQL :

COPY Sales_Fact TO '/path/to/sales_fact.csv' DELIMITER ',' CSV HEADER;
COPY Customer_Dim TO '/path/to/customer_dim.csv' DELIMITER ',' CSV HEADER;
COPY Product_Dim TO '/path/to/product_dim.csv' DELIMITER ',' CSV HEADER;
COPY Location_Dim TO '/path/to/location_dim.csv' DELIMITER ',' CSV HEADER;
COPY Date_Dim TO '/path/to/date_dim.csv' DELIMITER ',' CSV HEADER;

Si votre PostgreSQL a des problèmes de version ou de permissions, une solution est de le faire par Console. Pour cela, vous devez installer PSQL :

➜ psql -h localhost -d restaurants -U postgres

Saisissez votre nom d’utilisateur et votre mot de passe et, une fois dans la base de données « Restaurants », effectuez la requête pour chaque table en SQL :

restaurants=# \copy customer_dim TO '/Users/javierladino/Documents/Javier/sql/customer_dim.csv' DELIMITER ',' CSV HEADER;

Création de tableaux de bord dans Power BI

Pour un restaurant, l’objectif du tableau de bord est de fournir des informations essentielles pour aider à prendre des décisions stratégiques, optimiser les opérations et améliorer la satisfaction des clients. Sur la base du modèle en étoile et des données simulées, nous avons défini les mesures et les visualisations qu’il serait le plus important d’inclure dans un tableau de bord Power BI.

Nous avons importé les fichiers .csv et configuré les relations entre les tables selon le schéma en étoile. Ensuite, nous avons créé plusieurs visualisations pour vérifier l’utilisation des données, telles que :

Ventes totales par client : Un diagramme à barres montrant les ventes totales par client.
Ventes par catégorie de produits : diagramme circulaire montrant la répartition des ventes par catégorie de produits.
Ventes par emplacement : une carte montrant les ventes dans les différents emplacements.
Tendances des ventes par mois : un graphique linéaire montrant la tendance des ventes au fil du temps.

Mais quelles questions le gérant du restaurant pourrait-il poser à l’analyste de données pour connaître les principales mesures de son activité ? (Ce point est peut-être au début, mais c’est là que je pense qu’il devient valable).

Le gérant du restaurant peut poser une série de questions pour mieux comprendre les mesures clés de son entreprise et prendre des décisions éclairées. Quelques questions clés que le propriétaire pourrait poser, suivies de la requête en DAX (Power BI Data Analysis Expressions) pour créer les mesures ou les colonnes respectivement :

Questions sur les ventes et les performances

Quelles sont les ventes totales du restaurant au cours d’une période donnée (mois, trimestre, année) ?
Mesure : Ventes totales (SUM(Sales_Fact[Total_Amount]))
Quel est le bénéfice net du restaurant après soustraction des coûts ?
Mesure : bénéfice (SUM(Sales_Fact[Total_Amount]) – SUM(Sales_Fact[Cost]))
Comment les ventes varient-elles dans le temps (jour, semaine, mois) ?
Visualisation : Graphique linéaire des ventes totales par mois
Quelle est la marge bénéficiaire du restaurant ?
Mesure : Marge bénéficiaire (DIVIDE([Profit], [Total_Sales], 0))

Questions sur les produits et les catégories

Quels sont les produits les plus vendus ?
Métrique : Ventes par produit (SUM(Sales_Fact[Total_Amount]) groupé par Product_Dim[Product_Name])
Quelles sont les catégories de produits qui génèrent le plus de revenus ?
Métrique : Ventes par catégorie (SUM(Sales_Fact[Total_Amount]) groupé par Product_Dim[Category])
Combien de produits sont vendus en moyenne par client ?
Métrique : Nombre moyen de produits vendus par client (MOYENNE(Fait_Vente[Quantité_Vendue]))

Questions sur les clients

Quelle est la consommation totale de chaque client ?
Métrique/colonne : Consommation totale par client (SUM(Sales_Fact[Total_Amount]) filtré par Customer_ID)
Quel est le ticket moyen par client ?
Métrique : Ventes moyennes par client (AVERAGE(Sales_Fact[Total_Amount]))
Quels sont les clients qui dépensent le plus ?
Visualisation : Tableau ou diagramme à barres avec Nom_du_client et Dépenses_totales_par_client

Questions sur les opérations et l’efficacité

Quelles sont les heures de la journée où les ventes sont les plus importantes ?
Mesure : ventes par heure (SUM(Sales_Fact[Total_Amount]) groupé par Date_Dim[Hour])
Quelle est la performance des ventes par emplacement de restaurant (s’il y a plusieurs emplacements) ?
Métrique : Ventes par emplacement (SUM(Sales_Fact[Total_Amount]) groupé par Location_Dim[Location_Name])
Quels sont les jours de la semaine où les ventes sont les plus importantes ?
Métrique : Ventes par jour de la semaine (SUM(Sales_Fact[Total_Amount]) groupé par Date_Dim[DayOfWeek])

Questions sur les promotions et les réductions

Comment les promotions affectent-elles les ventes ?
Mesure : Comparez les ventes pendant les périodes promotionnelles et les périodes non promotionnelles.
Quelles remises génèrent le plus de ventes ?
Mesure : Ventes par type de remise (SUM(Sales_Fact[Total_Amount]) groupé par Promotion_Dim[Promotion_Type])

Exemples de questions détaillées

« Quel a été le produit le plus vendu au cours du dernier trimestre et quel a été son chiffre d’affaires ?
« Quelle est la marge bénéficiaire moyenne de nos plats principaux ?
« Qui sont nos clients les plus précieux et combien ont-ils dépensé au total ?
« Quels jours de la semaine devrions-nous mettre l’accent sur les promotions pour augmenter les ventes ?
« Comment nos ventes ont-elles varié au cours des différentes saisons de l’année ?
« Quel restaurant (s’il y en a plusieurs) a les revenus les plus élevés et quel restaurant a les revenus les plus bas ?
« Quel est l’impact de nos promotions sur les ventes totales et la marge bénéficiaire ?
« Quelles sont les heures de pointe des ventes au cours de la journée ?
« Quel pourcentage de nos ventes totales provient de nos produits les plus vendus ?
« Quelles sont les dépenses moyennes par client et comment varient-elles entre les clients réguliers et les nouveaux clients ?

En incluant ces mesures et ces visualisations dans votre tableau de bord Power BI, vous obtiendrez une vue d’ensemble des performances de votre restaurant, couvrant des aspects tels que les ventes, les bénéfices, les marges bénéficiaires et les tendances au fil du temps. Ils vous permettront d’identifier les possibilités d’amélioration, de suivre les performances et de prendre des décisions éclairées pour développer l’activité.

J’ai créé un tableau de bord composé de cinq sections : Accueil (tableau de bord), Ventes, Produits, Clients et Opérations. Certaines mesures ou valeurs sont répétées, mais je l’ai fait intentionnellement pour améliorer l’expérience de l’utilisateur en filtrant les éléments dans Power BI.

restaurants Telecharger

Vous pourrez interagir avec le tableau de bord en vous connectant à votre compte Power BI :

Conclusions

Les réponses à ces questions permettront au propriétaire d’avoir une vision claire des domaines clés de l’entreprise, ce qui l’aidera à prendre des décisions stratégiques pour améliorer les opérations, optimiser le menu, cibler les promotions et augmenter la rentabilité. Ces questions permettent également d’identifier les tendances et les modèles de comportement des clients et de performance du restaurant.

Personnellement, ce projet m’a permis de démontrer mes compétences en matière de conception et d’implémentation d’un schéma en étoile, d’utilisation de SQL pour manipuler et analyser les données, et de création de visualisations efficaces dans Power BI. Les principales conclusions sont, par exemple, les suivantes:

Identification des clients clés : les données ont montré quels clients contribuent le plus aux ventes totales.
Produits les plus vendus : l’identification des catégories de produits les plus populaires a permis de prendre des décisions éclairées en matière de stocks.
Performances par site : l’analyse des ventes par site a révélé les zones les plus performantes.

En bref, ce projet n’a pas seulement démontré mes capacités techniques, mais a également fourni des informations précieuses à la direction du restaurant. Réaliser ce type de projet en mélangeant plusieurs outils, en faisant des erreurs, en faisant des recherches et en s’appuyant sur toutes les ressources est la base pour continuer à aller de l’avant.

J’espère que ce projet vous plaira et que vous pourrez le reproduire pour la pratique, et n’hésitez pas à me faire savoir ce que vous en pensez, s’il y a des erreurs ou si vous découvrez de nouvelles façons d’analyser et de visualiser les données. Bonjour à tous !