Dans le vaste univers de l'informatique décisionnelle (Business Intelligence), SQL Server Analysis Services (SSAS) est la technologie maîtresse développée par Microsoft pour concevoir, héberger et interroger des modèles analytiques à très haute performance. Qu'il s'agisse d'analyser des millions de transactions de vente en quelques fractions de seconde ou de fournir une source de vérité unique aux analystes financiers, SSAS s'est imposé comme le standard de l'écosystème d'entreprise Microsoft.

Ce module vous guide pas à pas dans la compréhension, la conception, le déploiement et l'exploitation de modèles décisionnels d'entreprise.

---

1. Objectifs pédagogiques du module

À l'issue de cette formation pratique sur Analysis Services, vous serez capable de :

• Distinguer avec rigueur les systèmes transactionnels (OLTP) des systèmes analytiques (OLAP).
• Maîtriser la modélisation dimensionnelle : tables de faits, tables de dimensions, granularité et schémas en étoile (Star Schema).
• Comprendre la dualité technique de SSAS : le mode Multidimensionnel historique (cubes MOLAP, langage MDX) et le mode Tabulaire moderne (moteur colonnaire VertiPaq, langage DAX).
• Préparer et restaurer un Data Warehouse relationnel sous SQL Server avec la base de référence AdventureWorksDW2022.
• Développer, déployer et actualiser un modèle analytique à l'aide de Visual Studio, SSMS et SQL Server Agent.
• Connecter Microsoft Excel au cube analytique pour construire des tableaux de bord interactifs et dynamiques.

---

2. Fondations : OLTP vs OLAP

Pour bien saisir la raison d'être d'Analysis Services, il faut comprendre pourquoi les bases de données relationnelles classiques ne sont pas adaptées au reporting analytique à grande échelle.

flowchart LR
    subgraph Transactionnel [Systèmes OLTP]
        ERP[ERP / CRM / E-Commerce] -->|Écritures / Transactions rapides| RDBMS[(Bases SQL Relationnelles\n3NF - Lignes)]
    end

    subgraph ETL_Pipeline [Pipeline de Données]
        RDBMS -->|Extraction / Transformation / Chargement| ETL[ETL / ELT]
    end

    subgraph Decisionnel [Systèmes OLAP]
        ETL -->|Chargement par lots| DW[(Data Warehouse / Data Mart\nSchéma en Étoile)]
        DW -->|Modélisation & Pré-agrégation| SSAS[Moteur SSAS\nCubes & In-Memory]
    end

    subgraph Restitution [Consommation BI]
        SSAS --> Excel[Microsoft Excel / TCD]
        SSAS --> PBI[Power BI / SSRS]
    end

Comprendre la distinction avec Martin Keen

Dans cette vidéo explicative, l'inventeur et vulgarisateur technique Martin Keen détaille les différences structurelles majeures entre un système transactionnel et un système analytique :

Critère	OLTP (Online Transaction Processing)	OLAP (Online Analytical Processing)
Objectif principal	Enregistrer les opérations métier quotidiennes au fil de l'eau.	Analyser les performances historiques pour guider la décision.
Type d'opérations	Écritures fréquentes, lectures ciblées (insertions, mises à jour, suppressions).	Lectures massives, agrégations complexes (SUM, AVG), pas d'écriture utilisateur.
Modélisation	Hautement normalisée (3e forme normale, 3NF) pour éviter la redondance.	Dénormalisée (schéma en étoile ou en flocon) pour accélérer les jointures.
Structure de stockage	Lignes (row-oriented), optimisé pour les transactions atomiques (ACID).	Multidimensionnelle ou colonnaire en mémoire (column-oriented VertiPaq).
Temps de réponse	Millisecondes par transaction individuelle.	Secondes ou sous-seconde pour agréger des millions de lignes.

---

3. Le schéma en étoile (Star Schema)

Au cœur de tout projet Analysis Services se trouve la modélisation dimensionnelle formalisée par Ralph Kimball. La structure reine est le schéma en étoile, où une table centrale de faits est entourée de dimensions d'analyse.

Un modèle en étoile se compose de deux grandes familles de tables :

1. Les tables de faits (Fact Tables) :
2. Elles contiennent les métriques numériques quantifiables (ventes, quantités, coûts, marges, taxes).
3. Chaque ligne correspond à un événement métier précis (une ligne de commande, un retrait bancaire).

4. Elles intègrent des clés étrangères pointant vers chaque dimension d'analyse.

5. Les tables de dimensions (Dimension Tables) :

6. Elles contiennent le contexte textuel et descriptif (qui, quand, où, quoi).
7. Exemples : le client (DimCustomer), le produit (DimProduct), le temps (DimDate), le territoire géographique (DimGeography).

[!TIP] Dans un schéma en étoile pur, chaque dimension est reliée directement à la table de faits par une relation un-à-plusieurs (\(1 \rightarrow \infty\)). Lorsque les dimensions sont elles-mêmes normalisées en sous-tables (par exemple, Produit \(\rightarrow\) SousCatégorie \(\rightarrow\) Catégorie), on parle de schéma en flocon (Snowflake Schema).

---

4. Les opérations du cube OLAP

Historiquement, le concept de cube décisionnel a été imaginé pour permettre aux décideurs d'examiner les données sous tous les angles sans avoir à rédiger des requêtes SQL complexes :

Un cube OLAP permet d'exécuter nativement cinq opérations analytiques majeures :

• Roll-up (Agrégation) : remonter d'un niveau fin vers un niveau plus global (passer des ventes par Jour aux ventes par Trimestre ou par Année).
• Drill-down (Zoom avant) : naviguer d'un niveau résumé vers le détail (passer de la vue Continent à la vue Pays, puis Ville).
• Slice (Découpage en tranche) : figer une dimension sur une valeur unique (par exemple, filtrer exclusivement sur l'année 2024).
• Dice (Sous-cube) : filtrer simultanément plusieurs dimensions pour créer une intersection spécifique (par exemple, les ventes de Vélos en Europe au T1 2024).
• Pivot (Rotation) : faire pivoter les axes du tableau (intervertir les lignes et les colonnes pour changer d'angle d'analyse).

---

5. La notion cruciale de granularité

La granularité définit le niveau de finesse unitaire d'un enregistrement dans une table de faits :

\[\text{Année} \longrightarrow \text{Mois} \longrightarrow \text{Date} \longrightarrow \text{Boutique} \longrightarrow \text{Ticket} \longrightarrow \text{Ligne de ticket}\]

Plus la granularité est fine, plus la liberté d'analyse est totale, mais plus le volume de données et la consommation mémoire explosent. Inversement, une granularité trop agrégée économise du stockage mais prive le décideur de détails opérationnels cruciaux.

[!NOTE] Le rôle moteur de SSAS est de concilier ces deux impératifs : stocker les données à un niveau de granularité très fin tout en calculant des agrégations instantanées grâce à ses structures d'indexation pré-calculées ou son stockage colonnaire compressé.

---

6. Les deux moteurs de SSAS : Multidimensionnel vs Tabulaire

Microsoft Analysis Services propose deux approches architecturales distinctes. Lors de l'installation de l'instance SSAS, vous devez obligatoirement choisir le mode opératoire de votre serveur :

graph TD
    SSAS[SQL Server Analysis Services]
    SSAS --> Multi[Mode Multidimensionnel\nUDM / MOLAP]
    SSAS --> Tabular[Mode Tabulaire\nMoteur VertiPaq]

    Multi --> M_Storage[Stockage disque pré-agrégé\nFichiers .cube, .dim]
    Multi --> M_Lang[Langage MDX\nMultiDimensional eXpressions]

    Tabular --> T_Storage[Stockage colonnaire in-memory\nCompression VertiPaq]
    Tabular --> T_Lang[Langage DAX\nData Analysis Expressions]

1. Le mode Multidimensionnel (MOLAP / UDM) :
2. Présent depuis SQL Server 2000/2005.
3. Repose sur le Unified Dimensional Model (UDM) et calcule les agrégations à l'avance sur disque.
4. S'interroge avec le langage MDX (MultiDimensional eXpressions).

5. Idéal pour les modèles hautement complexes comportant des hiérarchies parent-enfant, des relations plusieurs-à-plusieurs complexes et des règles financières sophistiquées.

6. Le mode Tabulaire (VertiPaq / DAX) :

7. Introduit avec SQL Server 2012, c'est l'architecture moderne préconisée par Microsoft.
8. Repose sur le moteur colonnaire en mémoire VertiPaq (le même moteur qui alimente Power BI et Power Pivot).
9. S'interroge et se manipule avec le langage DAX (Data Analysis Expressions).
10. Offre des performances d'analyse foudroyantes grâce à des taux de compression colonnaire atteignant 80 à 90 %.

---

7. Cloud et extensions : Azure Analysis Services

Pour les architectures cloud modernes, Microsoft a transposé le moteur tabulaire de SSAS sous forme de service managé (PaaS) nommé Azure Analysis Services (AAS) :

Azure Analysis Services permet d'isoler la couche de modélisation sémantique au plus près de vos sources cloud (Azure SQL Database, Snowflake, Azure Synapse) et d'alimenter vos outils de restitution sans administrer de serveurs Windows physiques. Aujourd'hui, cette technologie converge naturellement vers les modèles sémantiques de Microsoft Fabric.

---

8. Le flux de travail de bout en bout

La mise en place d'une solution décisionnelle avec Analysis Services s'articule autour de trois phases incontournables :

1. Phase 1 : Alimentation du Data Warehouse : Extraction des données sources (fichiers CSV, bases transactionnelles, API), nettoyage et chargement dans une base relationnelle SQL Server (ex: AdventureWorksDW2022).
2. Phase 2 : Modélisation et Déploiement du Cube : Connexion à la source, définition des dimensions, des tables de faits, des relations, des mesures calculées et déploiement sur le serveur SSAS.
3. Phase 3 : Exploitation décisionnelle : Requêtage en temps réel par les utilisateurs finaux via Microsoft Excel (Tableaux Croisés Dynamiques) ou Power BI.

---

9. Le projet fil conducteur

Tout au long des leçons de ce module, nous allons concevoir et déployer un modèle décisionnel complet pour analyser le chiffre d'affaires, les marges et les profils clients de la société multinationale Adventure Works :

---

Sommaire de la formation

1. Installer SQL Server Analysis Services
2. Préparer les données dans SQL Server
3. Créer un cube tabulaire (VertiPaq & DAX)
4. Créer un cube multidimensionnel (MOLAP & MDX)
5. Actualiser un cube Analysis Services
6. Manipuler un cube dans Excel
7. Aller plus loin & Quiz de validation

---

Prêt à déployer votre premier serveur OLAP d'entreprise ? Rendez-vous dans la section suivante pour mettre en place l'environnement de développement complet.

Partagez sur les réseaux sociaux

Commentaires