Le mode multidimensionnel (Multidimensional Mode) est l'architecture historique et éprouvée de SQL Server Analysis Services (SSAS). Il repose sur le concept de cube OLAP (MOLAP - Multidimensional OLAP) et le modèle dimensionnel unifié (Unified Dimensional Model - UDM).
Dans ce modèle, les données sont pré-agrégées et stockées dans des structures multidimensionnelles optimisées sur disque. Il s'interroge avec le langage puissant MDX (MultiDimensional eXpressions).
Dans ce tutoriel complet, nous allons concevoir pas à pas un cube de vente d'entreprise basé sur le Data Warehouse AdventureWorksDW2022, en analysant les performances commerciales selon quatre axes majeurs : Temps (Date), Client (Customer), Produit (Product) et Territoire (Geography).
1. Créer le projet multidimensionnel dans Visual Studio#
Étape 1 : Nouveau projet#
Lancez Visual Studio 2022 depuis le menu Démarrer, puis cliquez sur le bouton Créer un projet :
Étape 2 : Sélectionner le modèle#
Dans la liste déroulante des modèles de projet, recherchez ou faites défiler jusqu'à Projet Multidimensionnel Analysis Services (Analysis Services Multidimensional and Data Mining Project) :
Cliquez sur Suivant pour passer à la configuration du projet :
Étape 3 : Nommer et créer la solution#
Nommez votre projet AdventureWorks_SSAS_Cube et cliquez sur le bouton Créer :
L'Explorateur de solutions affiche l'arborescence standard d'un projet décisionnel multidimensionnel : - Sources de données (Data Sources) : connexions aux bases SQL sous-jacentes. - Vues des sources de données (Data Source Views - DSV) : sous-ensemble logique des tables et relations. - Cubes : définitions des tables de faits, mesures et groupes de mesures. - Dimensions : axes d'analyse et hiérarchies d'attributs. - Rôles : sécurité et droits d'accès.
2. Configurer la source de données (Data Source)#
La source de données contient la chaîne de connexion et les informations d'authentification requises pour lire le Data Warehouse SQL Server.
Étape 1 : Lancer l'assistant#
Dans l'Explorateur de solutions, faites un clic droit sur le dossier Sources de données, puis sélectionnez Nouvelle source de données... :
L'assistant de création s'ouvre. Cliquez sur Suivant (Next) :
Étape 2 : Définir la connexion OLE DB#
Sélectionnez l'option Créer une source de données basée sur une connexion existante ou nouvelle puis cliquez sur le bouton Nouveau... :
Si un message d'avertissement relatif au fournisseur s'affiche, validez en cliquant sur OK :
Dans la fenêtre du Gestionnaire de connexions, configurez les paramètres de liaison :
- Fournisseur : sélectionnez Fournisseur OLE DB Microsoft pour SQL Server ou Microsoft OLE DB Driver for SQL Server.
- Nom du serveur : saisissez localhost ou . (votre instance locale SQL Server).
- Authentification : conservez Authentification Windows.
- Sélectionner ou entrer un nom de base de données : choisissez AdventureWorksDW2022.
Cliquez sur le bouton Tester la connexion. Dès que le message "La connexion de test a réussi" apparaît, cliquez sur OK :
La nouvelle connexion apparaît dans la liste. Cliquez sur Suivant (Next) :
Étape 3 : Informations d'emprunt d'identité (Impersonation)#
[!IMPORTANT] Les informations d'emprunt d'identité déterminent quel compte Windows sera utilisé par le service SSAS lors du traitement (Process) du cube pour interroger SQL Server. Sélectionnez Utiliser le compte de service (Use the service account).
Conservez le nom par défaut Adventure Works DW2022 et cliquez sur Terminer (Finish) :
3. Créer la vue de source de données (Data Source View - DSV)#
La Vue de source de données (DSV) est une couche d'abstraction essentielle dans SSAS Multidimensionnel. Elle isole le modèle décisionnel des modifications physiques de la base SQL et permet de renommer des colonnes, de créer des requêtes nommées ou d'ajouter des relations logiques sans modifier la base SQL Server.
Étape 1 : Lancer l'assistant DSV#
Dans l'Explorateur de solutions, faites un clic droit sur Vues des sources de données \(\rightarrow\) Nouvelle vue des sources de données... :
Cliquez sur Suivant (Next) :
Sélectionnez la source de données Adventure Works DW2022 créée précédemment, puis cliquez sur Suivant :
Étape 2 : Sélectionner les tables du schéma#
La liste de gauche affiche toutes les tables et vues disponibles dans la base relationnelle :
- Cliquez sur la table
DimCustomeret cliquez sur le bouton fléché>pour la faire basculer dans la colonne Objets inclus à droite :
- Maintenez la touche Ctrl enfoncée et sélectionnez simultanément les tables :
DimDateDimGeographyDimProductFactInternetSales
Cliquez sur le bouton > pour toutes les inclure :
Toutes les tables nécessaires étant incluses dans le volet droit, cliquez sur Suivant :
Validez en cliquant sur Terminer :
Le concepteur graphique s'ouvre, affichant le schéma avec la table de faits au centre reliée aux dimensions par leurs clés étrangères :
4. Concevoir la dimension Temps (DimDate)#
Une dimension dans SSAS représente un axe d'analyse métier. Commençons par structurer la dimension de référence pour toute analyse temporelle : DimDate.
Étape 1 : Assistant de création de dimension#
Faites un clic droit sur le dossier Dimensions \(\rightarrow\) Nouvelle dimension... :
Cliquez sur Suivant :
Choisissez l'option Utiliser une table existante (Use an existing table) puis cliquez sur Suivant :
Étape 2 : Définir la table source et la clé primaire#
- Table principale :
DimDate - Colonnes clés :
DateKey - Colonne de nom :
FullDateAlternateKey(permet d'afficher une vraie date lisible au lieu d'un identifiant numérique).
Cliquez sur Suivant :
Étape 3 : Sélectionner les attributs temporels#
Cochez les attributs d'analyse pertinents :
- DateKey
- FullDateAlternateKey
- DayNumberOfWeek
- EnglishDayNameOfWeek
- DayNumberOfMonth
- DayNumberOfYear
- WeekNumberOfYear
- EnglishMonthName
- MonthNumberOfYear
- CalendarQuarter
- CalendarYear
- CalendarSemester
Cliquez sur Suivant.
Étape 4 : Définir le type sémantique des attributs#
Pour permettre au moteur SSAS de reconnaître automatiquement les granularités temporelles et d'activer les fonctions MDX de séries chronologiques, spécifiez le type d'attribut :
- FullDateAlternateKey : Date > Calendrier > Date
- EnglishMonthName : Date > Calendrier > Mois
- CalendarQuarter : Date > Calendrier > Trimestre
- CalendarYear : Date > Calendrier > Année
- CalendarSemester : Date > Calendrier > Semestre
Validez la configuration des types sémantiques en cliquant sur Suivant :
Nommez la dimension Dim Date et cliquez sur Terminer :
Le concepteur de dimension s'ouvre dans Visual Studio, présentant l'arborescence des attributs et la structure de la table :
5. Créer le cube avec l'Assistant Cube#
Maintenant que la dimension temps est modélisée, assemblons le cube autour de la table de faits.
Étape 1 : Lancer l'Assistant Cube#
Faites un clic droit sur le dossier Cubes \(\rightarrow\) Nouveau cube... :
Cliquez sur Suivant :
Sélectionnez Utiliser des tables existantes (Use existing tables) puis cliquez sur Suivant :
Étape 2 : Identifier la table de faits centrale#
Cliquez sur le bouton Suggérer (Suggest). L'assistant analyse les relations de la DSV et détecte automatiquement que FactInternetSales est la table de faits :
Assurez-vous que la case FactInternetSales est cochée, puis cliquez sur Suivant :
Étape 3 : Sélectionner les mesures numériques#
L'assistant sélectionne par défaut toutes les colonnes numériques de la table de faits.
[!WARNING] Décochez impérativement les clés étrangères et identifiants techniques qui ne sont pas des grandeurs sommables : - Décochez
Promotion Key- DécochezCurrency Key- DécochezSales Territory Key- DécochezRevision NumberConservez les vraies mesures d'affaires :
Sales Amount,Total Product Cost,Order Quantity,Unit Price,Tax Amt,Freight.
Cliquez sur Suivant :
Étape 4 : Associer les dimensions#
Cochez la dimension existante Dim Date créée précédemment :
L'assistant propose ensuite de générer automatiquement des dimensions pour les autres tables reliées (Dim Customer, Dim Product). Décochez FactInternetSales si elle est listée dans les dimensions, puis cliquez sur Suivant :
Donnez le nom AdventureWorks_Cube et cliquez sur Terminer :
Le concepteur de cube s'affiche avec le schéma en étoile : le groupe de mesures Fact Internet Sales au centre, entouré de ses dimensions :
6. Enrichir les dimensions et créer des membres calculés MDX#
Dans l'Explorateur de solutions, les dimensions Dim Customer.dim et Dim Product.dim ont été créées :
Création d'une mesure calculée en MDX#
Dans le concepteur de cube, basculez sur l'onglet Calculs (Calculations) pour ajouter des métriques métier via le langage MDX :
/* Calcul de la Marge Brute */
CREATE MEMBER CURRENTCUBE.[Measures].[Marge Brute]
AS [Measures].[Sales Amount] - [Measures].[Total Product Cost],
FORMAT_STRING = "Currency",
VISIBLE = 1,
ASSOCIATED_MEASURE_GROUP = 'Fact Internet Sales';
/* Calcul du Taux de Marge Brute en Pourcentage */
CREATE MEMBER CURRENTCUBE.[Measures].[Taux de Marge]
AS IIF(
[Measures].[Sales Amount] = 0,
NULL,
[Measures].[Marge Brute] / [Measures].[Sales Amount]
),
FORMAT_STRING = "Percent",
VISIBLE = 1,
ASSOCIATED_MEASURE_GROUP = 'Fact Internet Sales';
7. Déployer et traiter le cube sur le serveur SSAS#
Pour rendre notre cube interrogeable par les outils clients (SSMS, Excel, Power BI), nous devons le déployer sur l'instance SQL Server Analysis Services.
Étape 1 : Paramétrer le serveur cible#
- Dans l'Explorateur de solutions, faites un clic droit sur le nom du projet
AdventureWorks_SSAS_Cube\(\rightarrow\) Propriétés. - Sous l'onglet Déploiement :
- Serveur : indiquez le nom de votre instance SSAS Multidimensionnelle (par exemple
localhostou.oulocalhost\MULTIDIM). - Base de données :
AdventureWorks_SSAS_Cube. - Option de traitement :
Par défaut(Default).
Étape 2 : Déployer la solution#
Faites un clic droit sur le projet \(\rightarrow\) Déployer (Deploy).
Visual Studio effectue les étapes suivantes : 1. Compilation des fichiers de métadonnées XMLA (.dwproj). 2. Contrôle de validité des hiérarchies et des relations. 3. Envoi du script de création de la base de données au serveur SSAS. 4. Traitement (Processing) des tables de faits et des dimensions : lecture des enregistrements SQL Server, calcul des agrégations et construction des index MOLAP sur disque.
L'opération de génération a démarré...
------ Début de la génération : Projet : AdventureWorks_SSAS_Cube, Configuration : Development ------
La construction du projet Analysis Services a commencé : Incremental ....
Dimension [Dim Date] : Vérification des hiérarchies temporelles et des types d'attributs.
Dimension [Dim Product] : Indexation des clés de substitution.
Dimension [Dim Customer] : Validation des relations d'attributs.
Fin de la génération -- 0 erreurs, 0 avertissements
------ Début du déploiement : Projet : AdventureWorks_SSAS_Cube, Configuration : Development ------
Exécution d'un déploiement incrémentiel de la base de données « AdventureWorks_SSAS_Cube » sur le serveur « localhost ».
Génération du script de déploiement XMLA...
Ajouter Database AdventureWorks_SSAS_Cube
Processus Database AdventureWorks_SSAS_Cube
Terminé
Envoi du script de déploiement au serveur...
Traitement de la partition FactInternetSales... [60 398 lignes agrégées]
Terminé
Fin du déploiement -- 0 erreurs, 0 avertissements
=========== Build : 1 réussi ou à jour, 0 échoué, 0 ignoré =========
========== Déploiement : 1 réussi, 0 a échoué, 0 ignoré ==========
8. Vérifier le cube déployé dans SSMS#
Pour vérifier que le cube est prêt à être consommé :
- Ouvrez SQL Server Management Studio (SSMS).
- Dans la boîte de dialogue de connexion, sélectionnez Type de serveur : Analysis Services.
- Renseignez
localhostet connectez-vous avec l'authentification Windows. - Développez Bases de données \(\rightarrow\)
AdventureWorks_SSAS_Cube\(\rightarrow\) Cubes \(\rightarrow\)AdventureWorks_Cube. - Faites un clic droit sur le cube \(\rightarrow\) Parcourir (Browse) : vous pouvez glisser-déposer des mesures (
Sales Amount,Marge Brute) et des dimensions (Calendar Year,English Product Name) pour tester instantanément la réactivité du cube !
Bravo ! Vous venez de concevoir, enrichir et déployer avec succès votre premier cube multidimensionnel MOLAP. Dans la leçon suivante, nous allons aborder une problématique critique en production : l'actualisation et l'automatisation du traitement des cubes.
Commentaires