Dans le monde de la finance, du contrôle de gestion et de la prise de décision stratégique, Microsoft Excel est sans conteste l'outil de restitution analytique le plus répandu au monde. Ce que beaucoup ignorent, c'est qu'Excel est nativement conçu comme un client OLAP de premier ordre.
Lorsqu'Excel se connecte à un cube ou un modèle tabulaire SQL Server Analysis Services (SSAS), il ne rapatrie pas les millions de lignes brutes dans la feuille de calcul : il transmet des requêtes d'agrégation légères au serveur et affiche les résultats instantanément dans un Tableau Croisé Dynamique (TCD).
Dans cette leçon, nous apprenons à établir une connexion d'entreprise sécurisée, à naviguer dans les hiérarchies dimensionnelles et à exploiter la puissance des formules OLAP.
1. Créer la connexion au serveur Analysis Services#
Étape 1 : Nouveau classeur Excel#
Ouvrez Microsoft Excel et créez un Nouveau classeur vide :
Vous arrivez sur une grille de calcul standard :
Étape 2 : Lancer l'Assistant de connexion de données#
Dans le ruban supérieur d'Excel, rendez-vous dans l'onglet Données \(\rightarrow\) cliquez sur Obtenir des données \(\rightarrow\) À partir d'une base de données \(\rightarrow\) À partir de SQL Server Analysis Services (ou À partir d'autres sources \(\rightarrow\) À partir de SQL Server Analysis Services) :
L'Assistant Connexion de données s'ouvre :
Étape 3 : Spécifier le serveur et l'authentification#
- Nom du serveur : renseignez le nom de votre instance SSAS locale :
localhostou.(ou le nom réseau du serveur en entreprise). - Informations de connexion : sélectionnez Utiliser l'authentification Windows.
- Cliquez sur Suivant :
Étape 4 : Sélectionner la base de données et le cube#
- Dans la liste déroulante supérieure, sélectionnez votre base de données décisionnelle (ex:
AdventureWorks_SSAS_CubeouAdventureWorks_Tabular_DB). - Sélectionnez le cube ou le modèle à interroger (ex:
AdventureWorks_Cube). - Cliquez sur Suivant :
Étape 5 : Enregistrer le fichier de connexion (.odc)#
Excel propose d'enregistrer la configuration dans un fichier de connexion bureautique de données (.odc - Office Data Connection), stocké dans le dossier Mes sources de données. Cliquez sur Terminer :
2. Insérer le Tableau Croisé Dynamique (TCD)#
Une boîte de dialogue contextuelle vous demande comment visualiser les données du cube dans le classeur :
- Choisissez l'option Rapport de tableau croisé dynamique (PivotTable Report).
- Indiquez l'emplacement de destination (par défaut
=Feuil1!$A$1). - Cliquez sur OK.
La grille de calcul s'actualise : la zone vierge du Tableau Croisé Dynamique apparaît à gauche, et le volet latéral Champs de tableau croisé dynamique s'affiche à droite :
[!NOTE] Remarquez la structure du volet des champs : - Les Mesures (
Sales Amount,Total Product Cost,Order Quantity,Marge Brute) sont regroupées sous le symbole calculatrice \(\Sigma\). - Les Dimensions (Dim Date,Dim Product,Dim Customer,Dim Geography) sont présentées sous forme de dossiers hiérarchiques.
3. Explorer et analyser les données à la volée#
Pour construire votre premier rapport analytique interactif, faites glisser les champs dans les quatre quadrants en bas à droite : Lignes, Colonnes, Valeurs et Filtres.
Étape 1 : Choisir les axes d'analyse#
Dans l'onglet Insertion, vous pouvez à tout moment ajouter un nouveau TCD lié à la connexion existante :
- Développez la dimension
Dim Date\(\rightarrow\) glissez la hiérarchieCalendar Yeardans le quadrant Lignes :
- Développez le groupe de mesures et glissez
Sales Amount(Montant des ventes) dans la zone Valeurs :
Le chiffre d'affaires annuel apparaît immédiatement, calculé en quelques millisecondes par le serveur Analysis Services !
Étape 2 : Ajouter une dimension de détail (Drill-Down)#
- Développez la dimension
Dim Productet glissezEnglish Product Category Name(ou la hiérarchie des produits) sous l'année dans le quadrant Lignes :
- Utilisez les petits boutons d'expansion
+et de réduction-à côté de chaque année pour déplier le détail des ventes par catégorie :
- Appliquez un format Monétaire aux cellules pour obtenir un tableau de bord exécutif prêt pour la présentation :
4. Fonctionnalités avancées d'Excel connecté à SSAS#
En connectant Excel à un modèle Analysis Services, vous débloquez des fonctionnalités professionnelles impossibles sur de simples fichiers plats :
1. Extraction du détail (Drill-Through)#
Faites un double-clic sur n'importe quel chiffre d'affaires agrégé dans le TCD : Excel interroge automatiquement le serveur SSAS pour extraire les premières lignes sous-jacentes de la table de faits ayant composé ce montant, et les place dans une nouvelle feuille de calcul temporaire.
2. Segments (Slicers) et Chronologies (Timelines)#
Sélectionnez le TCD, allez dans l'onglet du ruban Analyse du tableau croisé dynamique :
- Cliquez sur Insérer un segment \(\rightarrow\) choisissez English Country Region Name.
- Cliquez sur Insérer une chronologie \(\rightarrow\) choisissez FullDateAlternateKey.
Vous obtenez des filtres visuels interactifs et tactiles pour piloter votre reporting en un clic.
3. Les formules de cube Excel#
Pour concevoir des tableaux de bord financiers sur mesure sans la rigidité structurelle d'un TCD standard, vous pouvez convertir votre TCD en formules de cube natives (Outils de tableau croisé dynamique \(\rightarrow\) Calculs \(\rightarrow\) Outils OLAP \(\rightarrow\) Convertir en formules) :
=VALEURS.CUBE("AdventureWorks_SSAS_Cube"; "[Measures].[Sales Amount]"; "[Dim Date].[Calendar Year].[2024]"; "[Dim Geography].[Country].[France]")
=MEMBRE.CUBE("AdventureWorks_SSAS_Cube"; "[Dim Product].[Category].[Bikes]")
Cette technique permet de placer des métriques décisionnelles à n'importe quel endroit précis d'un classeur officiel sans subir les contraintes de format des tableaux croisés.
Vous maîtrisez désormais l'intégralité du cycle de vie d'un projet décisionnel avec SQL Server Analysis Services, depuis la préparation des données jusqu'à leur valorisation dans Excel. Rendez-vous dans la leçon finale pour récapituler les bonnes pratiques et valider vos acquis par un quiz complet.
Commentaires