Avant de concevoir un cube ou un modèle tabulaire dans Analysis Services, nous devons disposer d'un magasin de données relationnel de type Data Warehouse ou Data Mart. Un cube ne génère pas de données par lui-même : il lit, agrège, compresse et structure des données préalablement stockées dans une base de données relationnelle.

Dans ce cours pratique, nous utiliserons la base décisionnelle de référence de Microsoft : AdventureWorksDW2022 (DW signifiant Data Warehouse).

---

1. Anatomie du Data Warehouse AdventureWorksDW

Contrairement à la base opérationnelle transactionnelle (AdventureWorks2022), la base décisionnelle AdventureWorksDW2022 est spécialement modélisée selon les principes du schéma en étoile (Star Schema) :

erDiagram
    FactInternetSales }o--|| DimDate : "OrderDateKey"
    FactInternetSales }o--|| DimCustomer : "CustomerKey"
    FactInternetSales }o--|| DimProduct : "ProductKey"
    DimCustomer }o--|| DimGeography : "GeographyKey"
    DimProduct }o--|| DimProductSubcategory : "ProductSubcategoryKey"
    DimProductSubcategory }o--|| DimProductCategory : "ProductCategoryKey"

    FactInternetSales {
        int ProductKey FK
        int OrderDateKey FK
        int CustomerKey FK
        int OrderQuantity
        decimal UnitPrice
        decimal SalesAmount
        decimal TotalProductCost
    }

    DimCustomer {
        int CustomerKey PK
        string FirstName
        string LastName
        string EmailAddress
        int GeographyKey FK
    }

    DimDate {
        int DateKey PK
        date FullDateAlternateKey
        string EnglishMonthName
        int CalendarQuarter
        int CalendarYear
    }

    DimProduct {
        int ProductKey PK
        string EnglishProductName
        string Color
        decimal StandardCost
    }

Tables clés du modèle

1. dbo.FactInternetSales (Table de faits) :
2. Contient l'historique détaillé des transactions de vente en ligne.
3. Fournit les indicateurs chiffrés (mesures) : montant des ventes (SalesAmount), coût de revient (TotalProductCost), quantité commandée (OrderQuantity), taxe (TaxAmt) et fret (Freight).

4. Relie chaque vente à ses dimensions d'analyse via des clés de substitution numériques (Surrogate Keys).

5. dbo.DimDate (Dimension Temps) :

6. Contient une ligne par jour sur plusieurs années.

7. Expose les hiérarchies temporelles : Année \(\rightarrow\) Semestre \(\rightarrow\) Trimestre \(\rightarrow\) Mois \(\rightarrow\) Jour.

8. dbo.DimCustomer (Dimension Client) :

9. Profil démographique des acheteurs (nom, prénom, niveau d'études, revenu annuel, nombre d'enfants).

10. dbo.DimProduct (Dimension Produit) :

11. Catalogue des articles commercialisés (vélos, composants, accessoires, vêtements), structuré en sous-catégories et catégories.

12. dbo.DimGeography (Dimension Territoire) :

13. Répartition spatiale des clients (Ville, État/Province, Pays, Code postal).

---

2. Télécharger la sauvegarde AdventureWorksDW2022

Microsoft met à disposition les fichiers de sauvegarde officiels (.bak) de ses bases d'apprentissage :

1. Rendez-vous sur la page officielle : AdventureWorks sample databases - Microsoft Learn.
2. Dans le tableau, repérez la section Data Warehouse et téléchargez le fichier de sauvegarde le plus récent : AdventureWorksDW2022.bak :

1. Une fois le téléchargement terminé, déplacez le fichier AdventureWorksDW2022.bak dans le dossier de sauvegarde par défaut de votre instance SQL Server, généralement : C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup :

[!NOTE] Placer le fichier .bak dans le sous-dossier Backup de SQL Server évite les erreurs de droits d'accès (Access Denied / Operating system error 5) fréquentes lorsque le service SQL Server tente de lire un fichier situé dans le dossier personnel de téléchargement de l'utilisateur.

---

3. Restaurer la base dans SSMS

Étape 1 : Ouvrir l'assistant de restauration

Ouvrez SQL Server Management Studio (SSMS) et connectez-vous au moteur de base de données relationnelle (localhost ou . sous authentification Windows).

Dans l'Explorateur d'objets, faites un clic droit sur le dossier Bases de données, puis sélectionnez Restaurer la base de données... (Restore Database) :

Étape 2 : Sélectionner le fichier de sauvegarde

Dans la fenêtre de restauration, cochez l'option Support (Device), puis cliquez sur le bouton de navigation avec les trois points ... à droite :

Dans la boîte de dialogue contextuelle, assurez-vous que le type de support est bien Fichier (File), puis cliquez sur Ajouter (Add) :

Naviguez jusqu'au dossier C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup et sélectionnez le fichier AdventureWorksDW2022.bak :

Validez la sélection du fichier en cliquant sur OK :

Confirmez à nouveau en cliquant sur OK :

Étape 3 : Exécuter la restauration

Vérifiez que la case à cocher de la sauvegarde est active dans la grille, puis cliquez sur le bouton OK en bas à droite pour démarrer le processus de restauration.

Quelques secondes plus tard, un message de confirmation apparaît : "La restauration de la base de données 'AdventureWorksDW2022' a réussi" :

Cliquez sur OK. Dans l'Explorateur d'objets de SSMS, développez le nœud Bases de données : vous constatez que la base AdventureWorksDW2022 est prête et accessible avec toutes ses tables dbo.Dim* et dbo.Fact* :

---

4. Vérification et validation des données en T-SQL

Pour nous assurer que le Data Warehouse est sain avant de concevoir nos cubes, exécutons une requête de contrôle dans SSMS.

Ouvrez un nouvel éditeur de requête (Ctrl + N) et collez le script T-SQL suivant :

Contrôle des volumes et des métriques de vente

USE AdventureWorksDW2022;
GO

-- 1. Vérification du volume de lignes de la table de faits centrale
SELECT 
    COUNT(*) AS [Total Lignes Ventes],
    MIN(OrderDate) AS [Première Commande],
    MAX(OrderDate) AS [Dernière Commande],
    ROUND(SUM(SalesAmount), 2) AS [Chiffre d'Affaires Global],
    ROUND(SUM(TotalProductCost), 2) AS [Coût de Revient Total]
FROM dbo.FactInternetSales;
GO

-- 2. Aperçu analytique : Chiffre d'affaires par Année et Catégorie de Produit
SELECT 
    d.CalendarYear AS [Année],
    pc.EnglishProductCategoryName AS [Catégorie],
    COUNT(f.SalesOrderNumber) AS [Nombre Commandes],
    ROUND(SUM(f.SalesAmount), 2) AS [Chiffre Affaires],
    ROUND(SUM(f.SalesAmount) - SUM(f.TotalProductCost), 2) AS [Marge Brute]
FROM dbo.FactInternetSales f
INNER JOIN dbo.DimDate d ON f.OrderDateKey = d.DateKey
INNER JOIN dbo.DimProduct p ON f.ProductKey = p.ProductKey
INNER JOIN dbo.DimProductSubcategory ps ON p.ProductSubcategoryKey = ps.ProductSubcategoryKey
INNER JOIN dbo.DimProductCategory pc ON ps.ProductCategoryKey = pc.ProductCategoryKey
GROUP BY 
    d.CalendarYear,
    pc.EnglishProductCategoryName
ORDER BY 
    d.CalendarYear ASC, 
    [Chiffre Affaires] DESC;
GO

[!TIP] Conservez ces chiffres de référence sous les yeux ! Une fois notre cube déployé dans les chapitres suivants, nous devrons retrouver exactement les mêmes montants d'agrégation dans Excel et dans SSAS. C'est le principe fondamental de non-régression décisionnelle.

---

Notre Data Warehouse relationnel est prêt et validé ! Dans la section suivante, nous allons aborder la création de notre premier modèle avec le moteur tabulaire VertiPaq.

Partagez sur les réseaux sociaux

Commentaires