Créer un modèle tabulaire avec Analysis Services (VertiPaq & DAX)

@joekakone

Joseph Konka @joekakone

03/03/2026 · 12 min

Big Data Analysis Services SSAS Tabular DAX VertiPaq

Maîtrisez la création d'un modèle décisionnel tabulaire moderne avec SQL Server Analysis Services : moteur in-memory VertiPaq, import de données, relations, hiérarchies et mesures DAX d'entreprise.

Créer un cube tabulaire

Dans l'écosystème moderne de Microsoft BI, le modèle tabulaire (Tabular Model) est devenu la référence absolue pour concevoir des bases analytiques d'entreprise. Propulsé par le moteur en mémoire VertiPaq — le même moteur sous-jacent qui fait la puissance de Power BI et d'Azure Analysis Services —, le modèle tabulaire allie simplicité de conception relationnelle, compression foudroyante et richesse expressive grâce au langage DAX (Data Analysis Expressions).

Ce tutoriel vous guide dans la conception complète d'un modèle tabulaire d'entreprise à partir du Data Warehouse AdventureWorksDW2022.

1. Principes architecturaux du moteur VertiPaq#

Contrairement aux bases relationnelles traditionnelles qui stockent les enregistrements ligne par ligne (Row-Oriented), le moteur VertiPaq est une base de données colonnaire en mémoire (Columnar In-Memory Database) :

flowchart TD
    subgraph RowStore [Stockage Ligne Classique SQL]
        R1["Ligne 1 : Date=2024-01-01 | Client=Dupont | Vente=150€"]
        R2["Ligne 2 : Date=2024-01-01 | Client=Martin | Vente=80€"]
        R3["Ligne 3 : Date=2024-01-02 | Client=Dupont | Vente=220€"]
    end

    subgraph ColumnarStore [Stockage Colonnaire VertiPaq]
        C1["Colonne Date\n[2024-01-01, 2024-01-01, 2024-01-02]"]
        C2["Colonne Client\n[Dupont, Martin, Dupont]"]
        C3["Colonne Vente\n[150, 80, 220]"]
    end

    ColumnarStore --> Comp["Techniques de Compression\n1. Encodage par dictionnaire\n2. Encodage RLE (Run-Length Encoding)\n3. Bit-Packing"]
    Comp --> RAM[Chargement direct en RAM\nAgrégations instantanées]

Les 3 mécanismes de compression de VertiPaq#

  1. Encodage par dictionnaire (Dictionary Encoding) : Les chaînes de texte répétitives (ex: noms de pays, catégories de produits) sont remplacées par des entiers courts.
  2. Encodage RLE (Run-Length Encoding) : Les valeurs consécutives identiques sont compressées sous forme de paires (valeur, nombre d'occurrences).
  3. Compactage de bits (Bit-Packing) : Si une colonne d'entiers ne contient que des valeurs entre 0 et 3, seuls 2 bits sont alloués au lieu de 32 bits par enregistrement.

Résultat : un Data Warehouse de 100 Go sur disque se compresse fréquemment en moins de 10 à 15 Go de mémoire RAM, permettant d'exécuter des requêtes d'agrégation (SUM, AVERAGE) sur des dizaines de millions de lignes en moins de 100 millisecondes.

2. Créer un projet tabulaire dans Visual Studio#

Étape 1 : Initialiser le projet#

  1. Lancez Visual Studio 2022.
  2. Sur la page de démarrage, cliquez sur Créer un projet.
  3. Dans la liste déroulante des modèles, sélectionnez Projet Tabulaire Analysis Services (Analysis Services Tabular Project).
  4. Nommez votre projet : AdventureWorks_Tabular et cliquez sur Créer.

Étape 2 : Choisir le niveau de compatibilité et l'espace de travail#

Une boîte de dialogue vous invite à configurer deux paramètres cruciaux :

  1. Espace de travail intégré (Integrated Workspace) :
  2. Cochez Espace de travail intégré. Visual Studio démarrera une instance SSAS locale éphémère en arrière-plan pour vous permettre de manipuler et tester vos données directement pendant la conception sans impacter un serveur de production.
  3. Niveau de compatibilité (Compatibility Level) :
  4. Choisissez SQL Server 2022 / Azure Analysis Services (1600) (ou 1500 pour SQL Server 2019). Ce niveau débloque les fonctions DAX les plus récentes et la prise en charge des colonnes de métadonnées modernes.

Cliquez sur OK pour ouvrir l'espace de conception du fichier central Model.bim.

3. Importer les données depuis SQL Server#

Le modèle tabulaire repose sur des tables importées en mémoire.

Étape 1 : Ouvrir l'Assistant d'importation#

  1. Dans la barre de menus supérieure de Visual Studio, cliquez sur Modèle \(\rightarrow\) Sources de données existantes (ou Extensions \(\rightarrow\) Modèle \(\rightarrow\) Importer à partir de la source de données).
  2. Choisissez la source Microsoft SQL Server.
  3. Renseignez les paramètres de connexion :
  4. Nom du serveur : localhost (ou . ou le nom de votre machine).
  5. Nom de la base de données : AdventureWorksDW2022.
  6. Mode d'authentification : Authentification Windows.

Étape 2 : Sélectionner les tables du schéma en étoile#

Dans la liste des tables disponibles, cochez les tables suivantes :

  • dbo.FactInternetSales (Faits)
  • dbo.DimDate (Dimension Temps)
  • dbo.DimCustomer (Dimension Client)
  • dbo.DimProduct (Dimension Produit)
  • dbo.DimProductSubcategory (Dimension Sous-Catégorie)
  • dbo.DimProductCategory (Dimension Catégorie)
  • dbo.DimGeography (Dimension Territoire)

Cliquez sur Charger (Load). VertiPaq lit les tables, applique ses algorithmes de compression et charge les enregistrements dans l'espace de travail intégré.

4. Définir les relations dans la vue Diagramme#

Une fois les données chargées, basculez en bas à droite de l'éditeur sur l'icône Vue Diagramme (Diagram View) pour visualiser votre modèle relationnel.

Vérifiez et tracez les relations un-à-plusieurs (\(1 \rightarrow \infty\)) entre vos tables :

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

[!NOTE] Dans la table FactInternetSales, il existe plusieurs colonnes de date : OrderDateKey, DueDateKey et ShipDateKey. Une seule relation peut être active à la fois (par convention, OrderDateKey \(\rightarrow\) DateKey). Les autres relations restent inactives et pourront être sollicitées dans les calculs DAX via la fonction USERELATIONSHIP().

5. Créer des mesures d'entreprise avec le langage DAX#

Dans un modèle tabulaire, les indicateurs analytiques sont définis sous forme de mesures explicites dans la zone de calcul située sous la grille de chaque table.

Basculez en Vue Grille (Data View), sélectionnez la table FactInternetSales et placez-vous sur une cellule vide de la zone de calcul inférieure.

Saisissez les formules DAX professionnelles suivantes :

Mesures DAX de base et financières
-- 1. Chiffre d'affaires total
Total Sales := SUM(FactInternetSales[SalesAmount])

-- 2. Coût total de revient des produits vendus
Total Cost := SUM(FactInternetSales[TotalProductCost])

-- 3. Marge brute réalisée
Gross Profit := [Total Sales] - [Total Cost]

-- 4. Pourcentage de marge brute avec gestion de division par zéro
Profit Margin % := DIVIDE([Gross Profit], [Total Sales], 0)

-- 5. Nombre total de transactions uniques
Total Orders := DISTINCTCOUNT(FactInternetSales[SalesOrderNumber])

-- 6. Panier moyen par commande
Average Order Value := DIVIDE([Total Sales], [Total Orders], 0)

Mesures de Time Intelligence (Intelligence Temporelle)#

L'un des immenses avantages du modèle tabulaire et du langage DAX est la facilité avec laquelle on implémente des comparaisons temporelles :

Mesures Time Intelligence
-- Ventes cumulées depuis le début de l'année (Year-To-Date)
Sales YTD := TOTALYTD(
    [Total Sales], 
    DimDate[FullDateAlternateKey]
)

-- Ventes de la même période l'année précédente (Same Period Last Year)
Sales SPLY := CALCULATE(
    [Total Sales], 
    SAMEPERIODLASTYEAR(DimDate[FullDateAlternateKey])
)

-- Croissance annuelle du chiffre d'affaires (Year-over-Year %)
Sales YoY Growth % := DIVIDE(
    [Total Sales] - [Sales SPLY], 
    [Sales SPLY], 
    0
)

[!TIP] Veillez à formater vos mesures dans la fenêtre Propriétés à droite : - Total Sales, Total Cost, Gross Profit : format Devise (Currency) avec 2 décimales. - Profit Margin %, Sales YoY Growth % : format Pourcentage (Percentage) avec 1 ou 2 décimales.

6. Construire des hiérarchies de navigation#

Les hiérarchies permettent aux utilisateurs finaux de descendre (Drill-Down) intuitivement dans les données depuis Excel ou Power BI.

Hiérarchie Calendrier (DimDate)#

Dans la table DimDate, faites un clic droit sur la colonne CalendarYear \(\rightarrow\) Créer une hiérarchie. Nommez-la Calendrier et ajoutez-y successivement les niveaux :

\[\text{CalendarYear} \longrightarrow \text{CalendarQuarter} \longrightarrow \text{EnglishMonthName} \longrightarrow \text{FullDateAlternateKey}\]

Hiérarchie Produits (DimProduct)#

Pour structurer la navigation dans le catalogue, reliez les niveaux descriptifs :

\[\text{ProductCategoryName} \longrightarrow \text{ProductSubcategoryName} \longrightarrow \text{EnglishProductName}\]

7. Configurer et déployer le modèle sur l'instance SSAS#

Une fois le modèle conçu dans Visual Studio, nous devons le publier sur le serveur SQL Server Analysis Services.

Étape 1 : Paramétrer le déploiement#

  1. Dans l'Explorateur de solutions, faites un clic droit sur le nom du projet AdventureWorks_Tabular \(\rightarrow\) Propriétés.
  2. Dans la section Déploiement :
  3. Serveur : indiquez le nom de votre instance SSAS en mode Tabulaire (ex: localhost ou localhost\TABULAR).
  4. Base de données : spécifiez le nom de la base finale qui sera créée sur le serveur (ex: AdventureWorks_Tabular_DB).
  5. Option de traitement : Par défaut (Default) ou Complète (Full).

Étape 2 : Exécuter le déploiement#

  1. Faites un clic droit sur le projet \(\rightarrow\) Déployer (Deploy).
  2. Visual Studio génère les métadonnées TMSL (Tabular Model Scripting Language), compile le projet, crée la base de données sur l'instance SSAS cible, et déclenche le chargement des données.
Journal de déploiement Visual Studio (Sortie)
------ Début du déploiement : Projet : AdventureWorks_Tabular, Configuration : Development ------
Connexion au serveur Analysis Services 'localhost'...
Création de la base de données 'AdventureWorks_Tabular_DB'...
Application des métadonnées du modèle (TMSL 1600)...
Traitement de la table 'DimDate'... [Terminé - 3 652 lignes chargées]
Traitement de la table 'DimProduct'... [Terminé - 606 lignes chargées]
Traitement de la table 'DimCustomer'... [Terminé - 18 484 lignes chargées]
Traitement de la table 'FactInternetSales'... [Terminé - 60 398 lignes chargées]
Recalcul des dictionnaires et des index de colonnes VertiPaq...
Déploiement terminé avec succès. 0 erreurs, 0 avertissements.
========== Déploiement : 1 réussi, 0 a échoué, 0 ignoré ==========

8. Modèle Tabulaire vs Modèle Multidimensionnel : Le comparatif#

Fonctionnalité Modèle Tabulaire (VertiPaq) Modèle Multidimensionnel (MOLAP)
Moteur d'exécution Colonnaire en mémoire (In-Memory RAM). Fichiers disques pré-agrégés (MOLAP/ROLAP).
Langage de calcul DAX (très accessible, identique à Power BI). MDX (puissant mais courbe d'apprentissage abrupte).
Vitesse sur les grands volumes Exceptionnelle sur des agrégations dynamiques. Excellente si les agrégations sont pré-calculées.
Modélisation Tables et relations (proche du relationnel). Dimensions, cubes, groupes de mesures, UDM.
Hiérarchies Parent-Enfant Gérées via des fonctions DAX spécifiques (PATH). Gérées nativement au niveau de la dimension.
Calculs de devises multiples Nécessite une modélisation explicite en DAX. Prise en charge native avancée (Currency Conversion).
Avenir technologique Standard Microsoft (Azure AS, Power BI, Fabric). En maintenance, réservé aux projets historiques.

Vous disposez à présent d'un modèle tabulaire performant et opérationnel. Dans le chapitre suivant, nous allons découvrir la méthode historique de création de cubes multidimensionnels MOLAP avec Visual Studio.

Commentaires

Inscrivez-vous à une formation complète

Voir le catalogue