Atelier : N°3

SQL Server BI Tools

Pr. MOUNTASSER IMADEDDINE
Objectifs..................................................................................................................................................... 2
I. Analysis Services dans SQL Server 2022 ........................................................................................... 3
I.1. Installation du moteur BD SQL Server 2022 ............................................................................. 3
I.2. SQL Server Management Studio (SSMS) & SQL Server Data Tools (SSDT) .............................. 5
I.3. Installation et configuration de Visual Studio avec prise en charge d'Analysis Services ....... 5
II. Création et Déploiement d'un Projet d’un Modèle Multidimensionnel ......................................... 6
II.1. Préparation de Données pour les Modèles Multidimensionnels ............................................ 7
II.2. Création du Modèle Multidimensionnel .................................................................................. 8
II.2.1. Vues de Source de Données comme Couche d'Interface................................................. 9
II.2.2. Création des Sources de Données pour le Modèle Multidimensionnel ........................ 10
II.2.3. Création des Vues de Sources de Données pour le Modèle Multidimensionnel .......... 11
II.2.4. Ajout de Dimensions, d'Attributs et de Hiérarchies ...................................................... 13
II.2.5. Ajout de Cubes et de Groupes de Mesures .................................................................... 16
II.2.6. Déploiement du Modèle Multidimensionnel ................................................................. 18
II.2.7. Parcourir le Cube ............................................................................................................. 20
II.2.8. Ajout de Mesures et de Calculs avec MDX ..................................................................... 21
II.2.9. Ajout d'indicateurs de performance clés à notre cube .................................................. 24
SQL Server BI Tools

Objectifs

Il existe aujourd’hui une offre très large d’outils BI. Les principaux fournisseurs de bases de
données, tels que Microsoft, Oracle, IBM et Teradata, disposent de leur propre suite d’outils de
ce type. Parmi les autres outils populaires, citons SAP, MicroStrategy, Qlik et Tableau. En plus
des outils commerciaux mentionnés ci-dessus, il existe également des outils open source, dont
Pentaho est le plus populaire. Dans ce qui suit, nous avons choisi une suite d’outils
représentative (Outils SQL Server de Microsoft). Microsoft SQL Server fournit une plate-forme
intégrée pour la création d’applications analytiques. Elle est composée de trois composants
principaux, décrits ci-dessous :
► Analysis Services permet de définir, d'interroger, de mettre à jour et de gérer des DW
(BD analytiques). Il existe deux modes : multidimensionnel et tabulaire. La différence
entre eux provient de leur paradigme sous-jacent (multidimensionnel ou relationnel).
Chaque mode est associé à un langage de requête, MDX et DAX, respectivement. Dans
cet atelier, nous abordons le mode multidimensionnel et son langage associé MDX lors
des scénarios de définition et d’interrogation du DW (BD analytique) pour l'étude de cas
Northwind.
► Integration Services prend en charge les processus ETL. Il est utilisé pour extraire des
données à partir de diverses sources de données, les combiner, nettoyer et résumer et,
enfin, remplir un entrepôt de données avec les données résultantes.
► Reporting Services permet de définir, générer, stocker et gérer des rapports. Les
rapports peuvent être créés à partir de divers types de sources de données, notamment
des entrepôts de données (DW) et des cubes OLAP, et peuvent être personnalisés et
livrés dans divers formats. Les utilisateurs peuvent afficher les rapports avec divers
clients, tels que des navigateurs Web ou des applications mobiles. Les clients accèdent
aux rapports via le composant serveur de Reporting Services.
Plusieurs outils permettent de développer et de gérer ces composants. Visual Studio est une
plateforme de développement qui prend en charge les projets Analysis Services, Reporting
Services et Integration Services. SQL Server Management Studio (SSMS) permet une gestion
intégrée de tous les composants SQL Server. De plus, Power BI est un outil de business
intelligence qui vise à permettre aux utilisateurs finaux d'une entreprise d'analyser des données
et de créer leurs propres visualisations de données sous forme de rapports et de tableaux de
bord sans faire appel à des spécialistes informatiques, ce que l'on appelle le BI en libre-service.
Tout d'abord, vous découvrirez un aperçu rapide de SQL Server 2022, apprendrez à choisir le
bon modèle analytique à utiliser et comprendrez leurs principales différences. Vous découvrirez
ensuite comment créer un modèle multidimensionnel avec SSAS et développer ce modèle avec
MDX. À la fin de cet atelier, vous serez en mesure de créer et de déployer le modèle
multidimensionnel pour prendre en charge les besoins analytiques reliés à une entreprise.

Pr. I.MOUNTASSER 2
 SQL Server BI Tools

I. Analysis Services dans SQL Server 2022

Lorsque vous vous préparez à créer vos modèles d'analyse dans SQL Server, vous devez comprendre les
bases de SQL Server Analysis Services (SSAS), notamment l'objectif de la plateforme globale, avec une
compréhension de base du produit. SSAS est distribué dans le cadre de la pile d'outils SQL Server. Cette
pile inclut une variété d'outils, tels que SQL Server Management Studio, services d'intégration SQL
Server, services de création de rapports SQL Server, etc.

Analysis Services est considéré comme une solution de stockage de données distincte optimisée pour
les charges de travail d'analyse et de création de rapports. Traditionnellement, Analysis Services était la
meilleure option pour organiser les données afin de les analyser facilement et de manière performante
à grande échelle. Analysis Services est indépendant de la source (source agnostic). Si vous pouvez vous
connecter à la source, vous disposez d'un cas d'utilisation pour Analysis Services si vous souhaitez des
analyses et des rapports plus efficaces. SQL Server Analysis Services (SSAS) continue d'être un ensemble
d'outils de premier plan à l'échelle de l'entreprise, permettant aux clients de fournir des données et des
analyses sur de grands ensembles de données avec d'excellentes performances. Analysis Services est
disponible en deux modes, le mode multidimensionnel et le mode tabulaire. Le choix entre ces deux
modes dépend de nombreux facteurs. En ce qui concerne le modèle de données, le modèle
multidimensionnel dispose de puissantes fonctionnalités pour créer des applications BI avancées. En
revanche, le modèle tabulaire est plus simple à comprendre et plus rapide à créer que le modèle
multidimensionnel. Cependant, les volumes de données pris en charge par le modèle tabulaire sont plus
petits que ceux du modèle multidimensionnel. En ce qui concerne le langage de requête, chacun de ces
modèles possède son propre langage de requête associé, MDX et DAX, respectivement. Les autres
facteurs importants incluent les performances, la prise en charge des outils clients et la plate-forme de
déploiement.

Étant donné que SSAS fait partie de la pile SQL Server, de nombreux outils peuvent être utilisés pour
prendre en charge les deux modes. Ainsi, nous disposons de deux outils clés utilisés pour créer et
interagir avec les modèles : SQL Server Management Studio (SSMS) et Visual Studio.

I.1. Installation du moteur BD SQL Server 2022

Pour lancer l'installation, vous devrez sélectionner le type d'installation adéquat (ex. Developer Edition).
Ensuite, vous devrez sélectionner le type d'installation Personnalisé. L'installation de base n'inclut pas
l'option d'installation d'Analysis Services. Accédez à Installation, puis choisissez d'installer une nouvelle
installation autonome. Cette page inclut également des liens de téléchargement pour SQL Server Data
Tools (SSDT) et SQL Server Management Studio (SSMS). Dans la première page de la configuration, vous
devez choisir votre licence. Comme nous utilisons l'édition développeur, nous pouvons la laisser par
défaut. Une nouvelle fonctionnalité intéressante est la facturation à l'utilisation via Azure. Étant donné
que l’installation de SQL Server est personnelle, désactivez la fonctionnalité « Extension Azure pour SQL
Server ».

Maintenant, nous pouvons enfin sélectionner les fonctionnalités que nous souhaitons installer.
Personnellement, j'installe le moteur de base de données, ainsi que SSIS et SSAS. SSRS a été retiré de la
configuration depuis sa « joint-venture » avec Power BI. Vous pouvez ensuite configurer le compte de
service. En général, laissez tout par défaut.

Pour la configuration de la base de données, vous pouvez opter pour le mode windows ou mixte pour
l'authentification. On ne sait jamais quand on a besoin d'une authentification SQL Server. N'oubliez pas
de vous ajouter en tant qu'administrateur et laissez le reste par défaut.

Pr. I.MOUNTASSER 3
SQL Server BI Tools

Dans l'écran de configuration de SSAS, vous pouvez choisir le format Tabular (par défaut)/
Multidimensionnel (notre choix) et vous pouvez vous ajouter en tant qu'administrateur. Nous avons
presque terminé. Nous obtenons un aperçu final, puis nous pouvons cliquer sur le bouton Installer.

Figure 1. Etapes d'Installation et Configuration des Composants SQL Server

Remarque :
Si c'est la première fois que vous installez SQL Server, il est important de noter que SQL Server Reporting
Services (SSRS) et SQL Server Management Studio (SSMS) ne sont pas inclus. Ces deux produits doivent
être téléchargés séparément.

Pr. I.MOUNTASSER 4
SQL Server BI Tools

Egalement, vous constaterez que SQL Server a tendance à être très gourmand en ressources. Je vous
recommande de désactiver les services lorsque vous ne les utilisez pas. Vous pouvez les activer et de les
désactiver selon vos besoins.

I.2. SQL Server Management Studio (SSMS) & SQL Server Data Tools (SSDT)
Le lien d'installation de SSMS/SSDT se trouve sur la même page que l'édition SQL Server 2022 Developer.
Il se trouve généralement en bas de la page avec tous les outils de support. Lorsque vous sélectionnez
le lien SSMS/SSDT, vous serez redirigé vers la page Microsoft contenant des instructions et des détails
sur la version actuelle de Management Studio (opter pour la version Community). Téléchargez et
installez SSMS/SSDT. Il s'agit d'une installation simple sans options qui ont un impact sur le travail que
nous allons effectuer dans l’atelier.

Figure 2. Installation de SSMS et SSDT

I.3. Installation et configuration de Visual Studio avec prise en charge d'Analysis Services
Visual Studio et SQL Server ont connu de nombreuses combinaisons de travail différentes au fil des
années. En revanche, Visual Studio est configuré différemment. Pour vous débuter votre expérience
d'apprentissage sans acheter une licence pour Visual Studio, la meilleure option est probablement
l'édition Community. Dans le cadre de l'installation, vous devrez sélectionner une ou plusieurs charges
de travail. Bien que vous puissiez choisir d'autres charges de travail pour l'installation, pour nos besoins,
vous devez sélectionner « stockage et traitement des données dans la boîte de dialogue d'installation
de Visual Studio ». Une fois l'installation terminée, lancez Visual Studio. Sous les options de démarrage,
choisissez « Continuer sans code ». L'étape suivante consiste à installer les extensions pour créer des
projets Analysis Services.

Ouvrez d'abord la boîte de dialogue « Gérer les extensions ». Vous pouvez l'ouvrir en sélectionnant
l'option Extensions dans Visual Studio et en sélectionnant Gérer les extensions. Vous devrez filtrer la
liste des options pour les extensions spécifiques aux données. Choisissez Projets Microsoft Analysis
Services, puis cliquez sur Télécharger. Cela lancera le processus de téléchargement et d'installation de
l'extension.

Une fois l'installation terminée (il se peut que vous deviez fermer Visual Studio), vous devriez pouvoir
créer un nouveau projet et sélectionner les types de projets Analysis Services dans les options.

Pr. I.MOUNTASSER 5
 SQL Server BI Tools

II. Création et Déploiement d'un Projet d’un Modèle Multidimensionnel

Cette section couvrira la création d'un modèle multidimensionnel avec SQL Server Analysis Services 2022
et développera ce modèle avec des mesures d'expressions multidimensionnelles. Une fois terminé, le
modèle sera déployé sur un serveur de développement.

Les modèles multidimensionnels sont les structures OLAP d'origine prises en charge dans SQL Server
Analysis Services (SSAS). Ayant débuté en tant que services, ces outils sont désormais considérés comme
matures par Microsoft et aucune mise à jour majeure n'est prévue dans un avenir proche. La version
actuelle d'Analysis Services continue de s'appuyer fortement sur ce modèle de modélisation des
données.

Il faut noter que lors de la planification des dimensions et faits, les principes suivants doivent être gardés
à l'esprit :

• Sachez quel problème vous essayez de résoudre. Vous devez savoir ce que l'entreprise essaie
de comprendre. Votre conception doit comprendre les différents domaines d'activité qui
doivent être analysés.

• Comprendre le grain (i.e. niveau du détail). Il est important de savoir quel sera le grain de
chacune des tables de faits. Devez-vous prendre en charge plusieurs grains ?

• Développez votre matrice de bus. Vous devez comprendre vos faits et vos dimensions. Cela
changera continuellement tout au long de votre implémentation, mais comprendre votre
premier fait et sa dimensionnalité vous aidera à ajouter des faits et à savoir quand vous devez
ajouter des dimensions. Planifiez vos dimensions conformes de base afin de pouvoir démarrer
la construction rapidement et étendre la capacité de votre solution en fonction des besoins de
votre entreprise.

Avertissement :
À ce stade, Il faut noter que passer trop de temps dans le processus de conception sera contre-productif.
Les entreprises sont généralement très impatientes. Si vous essayez de tout résoudre dès le début, vous
ne construirez jamais rien. Essayez toujours d'identifier comment vous pouvez obtenir les gains dont

Pr. I.MOUNTASSER 6
SQL Server BI Tools

vous avez besoin de manière itérative. Vous devez toujours fournir une solution à l'entreprise afin qu'elle
puisse voir vos progrès et soutenir vos efforts à mesure que vous élaborez la solution.

II.1. Préparation de Données pour les Modèles Multidimensionnels

Cette section se concentre sur la préparation de données. Pour créer correctement un modèle
multidimensionnel, vos données doivent être mises en forme à l'aide de techniques de modélisation
dimensionnelle. Vous découvrirez la théorie de la modélisation dimensionnelle, les pratiques de
conception pour prendre en charge ces modèles et d'autres techniques pour prendre en charge
l'utilisation de cubes avec SQL Server. Sans cette préparation des données, vous aurez du mal à
construire des cubes efficaces et performants.

L'architecture fondamentale pour créer avec succès des modèles multidimensionnels dans SQL Server
est le modèle schéma en étoile. Alors que Microsoft continuait d'améliorer Analysis Services, l'un des
éléments clés était d'adopter la conception de modèles dimensionnels comme élément clé de la création
de cubes. Les tables de dimensions doivent être planifiées pour prendre en charge la conception logique
de dimension conforme dont vous disposez. Les tables de faits ont généralement des métriques et des
clés de dimension. Sachez qu'il s'agit d'une conception assez simple. Dans de nombreux cas, les tables
de faits auront des champs supplémentaires non mesurables.

Après avoir conçu votre schéma en étoile, quelle est la prochaine étape ? Vous devez élaborer un plan
pour pouvoir charger les données à partir des systèmes sources et les placer correctement dans votre
schéma en étoile (i.e. DW). Je vous recommande de prévoir de mettre en scène (data staging) vos
données dans une BD avant de les charger dans votre schéma en étoile. Le processus de recherche et
de normalisation peut ajouter une charge importante sur le système à partir duquel vous effectuez le
chargement. En disposant d'une BD de mise en scène (staging DB) ou d'un schéma de mise en scène,
vous pouvez charger des données à partir de divers systèmes sources avec une transformation minimale.
Ensuite, en utilisant les ressources dédiées au processus, les données peuvent être chargées de la mise
en scène vers le schéma en étoile sans qu'il y ait d'impact négatif sur la source. La fréquence à laquelle
vous mettez en scène les données peut être gérée en fonction des besoins du système source.

Lorsque vous travaillez avec n'importe quel type de solution d'entrepôt de données, ETL et ELT sont les
modèles les plus couramment référencés. L'ordre des lettres – ETL et ELT – décrit quand et où les
données seront transformées ou mises en forme. ETL est la solution la plus traditionnelle. Cela implique
généralement un outil spécialisé tel que SSIS à utiliser entre les deux ensembles de données. Dans une
solution ETL, pour charger le schéma en étoile à partir de la mise en scène, SSIS se connecte à l'ensemble
de données de mise en scène, introduit les données dans l'outil, utilise les étapes de l'outil pour
manipuler les données, puis charge les données manipulées dans le schéma en étoile. L'essentiel ici est
que le travail de transformation le plus important est effectué pendant que les données sont en cours
de chargement dans le schéma en étoile. SSIS et les outils similaires font circuler les données et utilisent
la mémoire et le stockage temporaire pour apporter les modifications requises aux données en cours de
route.

ELT déplace la charge de travail de transformation vers la destination. Cette technique est devenue à la
mode plus récemment, car les développeurs cherchent à utiliser la puissance de traitement et les
capacités du système de destination pour transformer les données (surtout si on utilise une
infrastructure cloud). Dans ce modèle, les données sont d'abord déplacées de la source vers la
destination à l'aide de l'outil de déplacement de données de votre choix (ex. SSIS). Une fois qu'elles ont
été déplacées, des tâches sont lancées qui utilisent généralement SQL et des procédures stockées pour
transformer les données dans le schéma en étoile. Il s'agit d'un choix populaire, si votre BD de
préparation se trouve sur la même instance ou si vous utilisez un schéma pour la préparation. Cela

Pr. I.MOUNTASSER 7
SQL Server BI Tools

permet aux équipes de développement de se concentrer sur moins de langages de codage, ce qui facilite
la maintenance et la dotation du personnel en continu.

Il n'existe pas de choix de conception parfait ou correct. Les solutions plus matures combinent ces
techniques, dans le but de suivre la meilleure approche pour le travail à accomplir. Quel que soit l’option
choisie, cette section devrait vous donner une compréhension de base des principales options que vous
pouvez utiliser pour déplacer et façonner vos données dans le schéma en étoile, qui est utilisé pour
prendre en charge les modèles multidimensionnels.

II.2. Création du Modèle Multidimensionnel

Jusqu'à présent, l'accent a été mis sur la préparation du schéma en étoile dans la base de données
relationnelle afin que vous puissiez créer le modèle multidimensionnel. Cette section se concentrera sur
une décision de conception qui a un impact sur la conception de la BD, mais également sur la façon dont
vous travaillez avec Analysis Services lui-même. Dans le cadre de la création d'un modèle
multidimensionnel, des vues de source de données sont créées dans Analysis Services pour servir de
mappage entre les modèles relationnel et multidimensionnel.

Figure 3. Structure d'un Projet SSAS

Les vues de source de données (DSV) font partie intégrante d'un projet de modèle multidimensionnel.
C'est là que les données sources sont modélisées pour le modèle multidimensionnel. Les DSV prennent
en charge la création de tables, de champs et de relations personnalisés. L'objectif final est d'obtenir
des données sous forme de schéma en étoile qui peuvent être facilement utilisées par le modèle
multidimensionnel. Ainsi, une DSV définit le schéma utilisé pour remplir une BD Analysis Services. Ce
schéma est dérivé des schémas des différentes sources de données. En effet, certaines transformations
sont souvent nécessaires pour charger des données à partir de sources dans l'entrepôt. Par exemple, les
exigences courantes consistent à sélectionner certaines colonnes d'une table, à ajouter une nouvelle
colonne dérivée à une table, à restreindre les lignes de table sur la base de certains critères spécifiques
et à fusionner plusieurs colonnes en une seule. Ces opérations peuvent être effectuées dans la DSV en
remplaçant une table source par une requête nommée écrite en SQL ou en définissant un calcul nommé,
qui ajoute une colonne dérivée définie par une expression SQL. De plus, si les systèmes sources ne
spécifient pas les clés primaires et les relations entre les tables à l'aide de clés étrangères, celles-ci
peuvent être définies dans la DSV.

Analysis Services permet à l'utilisateur de spécifier des noms conviviaux pour les tables et les colonnes.
Afin de faciliter la visibilité et la navigation pour les grands entrepôts de données, il offre également la
possibilité de définir des vues personnalisables au sein d'un DSV, appelées diagrammes, qui affichent
uniquement certaines tables. En se basant sur l'entrepôt de données Northwind, nous pouvons voir la
table de faits Sales et les tables de dimensions associées. Nous pouvons aussi ajouter des calculs

Pr. I.MOUNTASSER 8
SQL Server BI Tools

nommés (identifiés par une icône spéciale à gauche du nom de l'attribut) et des relations ou remplacer
une table par une requête nommée. Ces calculs nommés sont utilisés pour définir et parcourir les
dimensions. Un exemple de calculs est donné ci-après :

• Dans la table de dimension Employee, le calcul nommé FullName combine le prénom et le nom
avec l'expression : FirstName + ' ' + LastName

• Dans la table de faits Sales, le calcul nommé OrderLineDesc combine le numéro de commande et
la ligne de commande à l'aide de l'expression CONVERT(CHAR(5),OrderNo) + ' - ' +
CONVERT(CHAR(1),OrderLineNo)

Avertissement :
Pourquoi modifier la forme des données ici ? Certains concepteurs apportent des modifications ici parce
que c'est pratique. Les modifications sont apportées ici dans le but de concevoir un cube et ne
nécessitent aucune autre intervention. En revanche, cette commodité n'est pas une bonne raison pour
apporter des modifications ici. Souvent, ces modifications ne sont pas bien documentées ou faciles à
découvrir. De plus, lorsque la source change, il faut veiller à ce que le DSV gère également ces
modifications. Sinon, les cubes ne seront pas traités, ce qui crée de la frustration chez les utilisateurs
finaux ou les consommateurs du cube lorsque ce dernier sera indisponible.

Ainsi, la meilleure raison de mettre en forme les données dans la DSV est lorsque les développeurs ne
peuvent pas facilement modifier les vues de base de données, car cela permet de prendre en charge le
modèle de schéma en étoile dans la BD sous-jacente. Si c'est la raison, je vous recommande d'en faire
le modèle de travail en cours. Vous ne pourrez pas profiter de meilleures techniques de conception de
vue à partir de la BD, mais cela vous permettra d'effectuer les modifications nécessaires pour prendre
en charge la conception de modèles multidimensionnels. Sachez que les DSV complexes peuvent avoir
un impact significatif sur le traitement du modèle (chargement des données). Je recommande d'utiliser
uniquement les vues de source de données lorsque c'est le cas. L'option préférée consiste à transférer
la conception de la vue vers la base de données relationnelle, où la gestion et les performances sont
meilleures.

II.2.1. Vues de Source de Données comme Couche d'Interface

La pratique préférée consiste à utiliser les vues de base de données comme couche d'interface. Pour
cela, il existe deux principes à prendre en compte : la flexibilité et la protection. Les vues de base de
données vous permettent d'être flexible dans votre conception. Les vues peuvent être utilisées pour
prendre en charge les calculs, les agrégations et la conception de colonnes. Dans une vue, un calcul peut
être ajouté pour réduire la charge de travail de calcul dans le cube. Par exemple, les mesures suivantes
font partie du fait de vente : Vente totale, Vente totale avec taxes et Vente totale avec frais d'expédition
et taxes. Elles utilisent Vente totale, Taxe totale et Frais d'expédition totaux comme mesures de base.
Celles-ci peuvent certainement être calculées dans le cube à l'aide d'expressions multidimensionnelles
(MDX).

Cependant, ces mesures ne seront pas pré-agrégées et dégraderont les performances. S'elles sont pré-
calculées dans la vue, elles n'auront pas besoin de calculs à créer dans le cube. Cet exemple fonctionne
déjà pour prendre en charge l'optimisation de l'agrégation dans le cube. Un autre exemple est que SUM
est généralement plus performante que COUNT dans un cube. Si chaque ligne compte pour 1, l'ajout
d'une colonne avec la valeur 1 à la vue permet au cube d'optimiser l'agrégation à l'aide d'une somme
plutôt que de compter les lignes.

Enfin, en utilisant une vue pour couvrir toutes les tables utilisées dans le DSV, des modifications peuvent
être apportées pour optimiser les tables sous-jacentes tout en garantissant qu'il existe une interface

Pr. I.MOUNTASSER 9
SQL Server BI Tools

pour le modèle relationnel qui ne change pas. Cela protège le cube contre toute rupture involontaire
lorsque vous modifiez le schéma sous-jacent pour optimiser la BD ou pour modifier les fonctionnalités.
Par exemple, si un système source modifie la façon dont une date est formatée, la vue peut modifier le
format afin qu'il corresponde à ce qui est attendu dans le cube, évitant ainsi les problèmes de
traitement. Cette méthode évite également les problèmes causés par des modifications de base dans la
base de données sous-jacente, telles que l'ajout ou la suppression de champs et les modifications de
nom d'objet (tables ou colonnes).

Je recommande d'utiliser des vues comme interface ou couche d'abstraction, car cela protégera le cube
des perturbations et conduira à une meilleure satisfaction de l'utilisateur. L'optimisation et la prise en
charge de l'agrégation sont également utiles, mais la protection est plus importante.

II.2.2. Création des Sources de Données pour le Modèle Multidimensionnel

Un entrepôt de données récupère ses données à partir d'un ou de plusieurs magasins de données. Une
source de données contient des informations de connexion à un magasin de données, qui incluent
l'emplacement du serveur, un identifiant et un mot de passe, une méthode de récupération des données
et des autorisations de sécurité. Analysis Services prend en charge plusieurs types de sources de
données. Si la source est une BDR, SQL est utilisé par défaut pour interroger la base de données. Dans
notre exemple, il existe une seule source de données qui se connecte à l'entrepôt de données
Northwind. Les données ont déjà été organisées à l'aide de techniques de modélisation dimensionnelle.

Figure 4. Configuration d'une Source de Données

Les bases de données Analysis Services sont créées dans Visual Studio. Dans cette section, nous allons
créer le projet et nous connecter aux sources de données que nous avons créées précédemment. Cela
créera un modèle de données dans Analysis Services qui servira de base pour le reste du travail. SSAS

Pr. I.MOUNTASSER 10
SQL Server BI Tools

prend en charge la connexion à de nombreux systèmes de données différents. Pour notre exemple
pratique, nous nous connecterons à SQL Server 2022 et à la base de données Northwind.

Les connexions à la base de données sont créées dans la section Sources de données de l'Explorateur de
solutions (Figure 4). Commençons par :

1. Cliquez sur Nouvelles sources de données… pour ouvrir l'écran Assistant de source de données.
2. Sur l'écran suivant, sélectionnez Comment définir la connexion et modifiez la sélection pour
Créer une source de données basée sur une connexion existante ou nouvelle. Cliquez ensuite
sur le bouton Nouveau nouvellement affiché. Cela ouvre l'écran Gestionnaire de connexions.
3. Définissez les propriétés de connexion suivantes :
a. Fournisseur : choisissez Native OLEDB\SQL Server Native Client 11.0.
b. Nom du serveur : saisissez le nom de votre serveur ici.
c. Authentification : Utilisez l'authentification Windows. Cependant, vous devrez peut-
être utiliser l'authentification SQL ainsi que le nom d'utilisateur et le mot de passe si
vous rencontrez des problèmes avec l'authentification Windows. Vous pouvez utiliser le
nom d'utilisateur et le mot de passe que vous avez créés lors de l'installation de SQL
Server 2022.
d. Se connecter à une BD. Vous pouvez soit sélectionner la BD dans la liste, soit la saisir.
Les deux options fonctionnent.
4. Cliquez sur le bouton « Tester la connexion » pour vérifier que l'authentification fonctionne et
que la connexion est bonne. Cliquez sur OK après avoir testé la connexion.
5. Revenir à l'assistant de connexion avec votre connexion nouvellement créée dans la liste
Connexions de données déjà sélectionnée. Si elle n'est pas sélectionnée, sélectionnez-la
maintenant.
6. Sélectionner la boîte de dialogue Informations d'emprunt d'identité « Impersonation
Information » de l'écran Assistant de source de données. Cela vous permet de sélectionner les
options d'authentification que vous utiliserez lors du chargement des données dans la BD SSAS
une fois celle-ci créée. Pour nos besoins, sélectionnez Utiliser les informations d'identification
de l'utilisateur actuel. Cela est utile pour le développement et le déploiement locaux que nous
utiliserons. Cependant, d'autres options constituent de meilleurs choix pour les déploiements
de production. L'option que vous choisissez pour un déploiement de production dépendra des
méthodes de sécurité et d'authentification qui ont été mises en œuvre au sein de votre
organisation.
7. Donnez un nom à la source de données (la valeur par défaut convient) et cliquez sur Terminer.

II.2.3. Création des Vues de Sources de Données pour le Modèle Multidimensionnel

Les vues de source de données (DSV) servent de couche de traduction entre la source de vos données
et le modèle SSAS. Les DSV dans SSAS servent de couche d'abstraction à partir de la source de données
sous-jacente. Cela signifie que les données qui ne figurent pas dans un DSV ne sont pas accessibles au
modèle multidimensionnel. Si vous souhaitez utiliser les données pour la conception, elles doivent
exister dans le DSV. Dans les situations où l'équipe de l'entrepôt de données et l'équipe de veille
stratégique (BI) ne sont pas les mêmes, le développement de modèles multidimensionnels peut être
considérablement entravé si les vues de base de données ne peuvent pas être créées facilement. Dans
un cas comme celui-ci, un DSV peut faciliter le processus.

Les étapes suivantes (Figure 5) vous aideront à créer un DSV à partir de notre entrepôt de données :

1. Cliquez le dossier Vues de source de données dans l’Explorateur de solutions et sélectionnez

Nouvelle vue de source de données pour ouvrir l’assistant correspondant.

Pr. I.MOUNTASSER 11
SQL Server BI Tools

2. Sélectionner une source de données existante ou en créer une nouvelle. Nous avons créé la
source de données dans la section précédente, elle doit donc figurer dans la liste des sources de
données relationnelles.
3. Dans la liste Objets disponibles, vous pouvez voir toutes les tables et vues auxquelles vous avez
accès à partir de la source de données. Si vous avez mis en place une sécurité pour vos
développeurs, qui limite les schémas ou les objets qu'ils peuvent voir, ces derniers seront filtrés
de la même manière. Le nom du schéma est entre parenthèses après le nom de la table ou de
la vue. Déplacer les objets de la liste des objets restants vers la liste des objets inclus. Vous
pouvez aussi sélectionner toutes les tables associées à la table ajoutée via l'option Ajouter des
tables associées. Par exemple, si nous sélectionnions la table de Faits puis cliquions sur ce
bouton, toutes les tables en relation seront ajoutées à la liste Objets inclus.
4. Donnez un nom à votre DSV. Le nom par défaut utilise le même nom que la source de données.
Cela convient à notre utilisation, mais n'hésitez pas à le modifier pour quelque chose qui vous
semble plus significatif. Une fois que vous avez fait cela, cliquez sur Terminer pour fermer
l'assistant.

Figure 5. Configuration d'une Vue de Source de Données

Double-cliquez sur la DSV que vous venez de créer pour ouvrir la conception de vue.

Figure 6. DSV de notre DW Northwind

Pr. I.MOUNTASSER 12
SQL Server BI Tools

Notez aussi la possibilité d’ajouter des diagrammes pour chaque cas d’études (i.e. table de faits incluse).
Les diagrammes nous aident à organiser les vues autour de thèmes. On peut également mettre à jour
une table existante et y ajouter des champs supplémentaires, en remplaçant, par exemple, la table par
une nouvelle requête nommée.

II.2.4. Ajout de Dimensions, d'Attributs et de Hiérarchies

Dans SSAS, les dimensions sont composées d'un groupe d'attributs et des hiérarchies qui définissent la
dimension. Les dimensions sont les segments que nous utilisons dans nos cubes pour filtrer et segmenter
nos données à des fins d'analyse. Les attributs sont les différents éléments liés dans notre dimension.
Par exemple, si nous utilisons la dimension Ville, les attributs incluent le nom de la ville, la clé de la ville,
le pays, etc. Les hiérarchies nous aident à organiser nos attributs. Une hiérarchie nous donne un chemin
d'accès clair à nos données. Dans notre dimension Ville, nous allons créer une hiérarchie Géographie
composée du Pays, de l'État ou de la Province et de la Ville dans cet ordre. L'objectif des hiérarchies est
de pouvoir parcourir nos données de manière claire.

Dans les sections suivantes, nous allons créer nos dimensions à l'aide de la fenêtre Assistant de
dimension (Figure 7). Il s'agit de la méthode la plus efficace pour ce faire. Une fois les dimensions créées,
nous allons créer les hiérarchies et les relations d'attributs. Il s'agit de la tâche de développement la plus
élémentaire requise pour créer des dimensions. Les sections restantes présenteront des techniques
spécifiques plus avancées qui peuvent être utilisées pour améliorer la convivialité et les performances
des dimensions.

Figure 7. Ajouter une Dimension

1. Cliquez avec le bouton droit sur le dossier Dimensions de votre projet Analysis Services. L'option
Nouvelle dimension s'affiche. Cliquez dessus pour lancer l'assistant.
2. Vous verrez une liste d'options pour créer des dimensions. Étant donné que nous utilisons un
entrepôt de données complet et que nous avons créé notre DSV, nous utiliserons l'option par
défaut, c'est-à-dire Utiliser une table existante. Une fois cette option sélectionnée, cliquez sur
Suivant.

Pr. I.MOUNTASSER 13
SQL Server BI Tools

Avertissement :
Deux des options sont conçues pour les situations où vous avez besoin d'une dimension Date en
générant une table dans le DSV ou la source de données sous-jacente. Bien que pratique, nous
déconseillons généralement ce processus. La raison en est que la plupart du temps, l'assistant ne génère
pas les attributs dont vous avez besoin dans votre modèle. La plupart des DW, comme le nôtre,
disposent d'une dimension Date créée pour prendre en charge cette fonctionnalité. La troisième option
utilise un modèle générique pour créer des tables de dimension. Par exemple, vous pouvez créer une
dimension client à l'aide de l'option Modèle client. Cependant, vous aurez toujours besoin de données
pour prendre en charge cette fonctionnalité. Si vous recherchez des exemples de conception pour divers
besoins commerciaux, cela peut être une bonne façon de faire des expériences.

3. Sélectionnez Vue de la source de données, Table principale et Colonnes clés.

4. Sélectionnez les attributs que nous souhaitons inclure dans notre dimension. Vous avez trois
options pour chaque attribut. En sélectionnant Nom de l'attribut, vous choisissez de l'inclure
dans la dimension. L'option suivante est Activer la navigation. Cette option définira la propriété
qui rend l'attribut disponible dans les outils client. Enfin, vous pouvez choisir un Type d'attribut
(Nous n'avons pas besoin de changer notre type d'attribut dans note cas).

Vous devriez maintenant voir l'onglet Conception de votre dimension ouvert dans Visual Studio. Nous
devons maintenant ajouter nos dimensions restantes. Pour chacune des dimensions, vous suivrez des
étapes similaires à celles que nous avons suivies ici. De plus, dans la section Type d'attribut, vous verrez
qu'il existe une liste de types Géographie. Vous pouvez, par exemple, le définir comme type d'attribut
pour la ville, l'État, la province, la région, le pays et le continent. Vous devez également définir la
dimension comme type Géographie. Cela augmente la prise en charge dans les outils de visualisation.
Egalement, la dimension Date peut avoir ses types d'attributs définis. Lorsque vous cliquez sur la liste
déroulante, vous trouverez Date, qui se développe en catégories de dates et en types spécifiques pour
ces catégories.

Figure 8. Onglet Conception de Dimension

Finalement, vous devriez voir toutes vos dimensions dans le dossier Dimensions de l'Explorateur de
solutions. Tous les onglets de conception des dimensions devraient également être ouverts dans Visual
Studio.

Les hiérarchies sont essentielles à une bonne conception dans tous les modèles de BI. Cependant, dans
les modèles multidimensionnels dans SSAS, elles sont encore plus importantes. Elles améliorent
l'expérience utilisateur, améliorent les performances, définissent les niveaux d'agrégation et affinent les
modèles de stockage des données.

Pr. I.MOUNTASSER 14
SQL Server BI Tools

Tout d'abord, nous devons faire la différence entre les hiérarchies naturelles et artificielles. Une
hiérarchie naturelle est un ensemble d'attributs dans une dimension qui sont liés selon un modèle allant
du plus petit au plus grand groupe. Par exemple, une hiérarchie naturelle courante est la date. Une
hiérarchie artificielle ou non naturelle se produit lorsque les attributs ne sont pas liés selon un modèle
naturel et peuvent avoir des tailles de groupe incohérentes. Ces hiérarchies sont généralement utiles à
l'entreprise pour l'analyse, mais ne sont pas bien organisées dans les données. Cela n'est pas idéal car
ce type ne peut pas être optimisé pour SSAS. Bien qu'il soit possible de les créer dans SSAS, cela n'est
généralement pas recommandé.

Une fois qu'une dimension a été créée, chaque attribut est créé sous forme de hiérarchie à deux niveaux.
Les niveaux sont Tous (ALL) et l'attribut lui-même. Comme nous l'avons vu précédemment, cela permet
de prendre en charge les agrégations et le stockage. Lorsque des mesures sont ajoutées ultérieurement,
chaque attribut sera agrégé au niveau Tous et au niveau de l'attribut individuel. Dans certains cas, c'est
ce que nous souhaitons. Cependant, nous ne traitons généralement pas la plupart des attributs de
manière isolée, c'est pourquoi nous créons des hiérarchies.

Nous allons commencer par la dimension Customer (Figure 9). Accédez à l'onglet Conception pour la
dimension Customer. Nous allons travailler avec les volets Attributs et Hiérarchies pour créer nos
hiérarchies. En règle générale, nous devrions connaître les options de hiérarchie grâce à des données ou
à une analyse commerciale. Dans ce cas, nous allons créer la hiérarchie « Géographie », qui prendra en
charge les continents, pays, régions et villes.

1. Faites glisser l'attribut Country sur le volet Hiérarchies. Cela créera une nouvelle hiérarchie avec
Country comme premier niveau.
2. Renommez la hiérarchie Géographie. Vous pouvez le faire en cliquant sur Hiérarchie dans la
nouvelle table de hiérarchie ou en modifiant le nom dans la fenêtre Propriétés, qui se trouve
généralement sur le côté droit de l'écran, sous Explorateur de solutions.
3. Ensuite, faites glisser les attributs État, Région, etc. sur la hiérarchie que vous venez de créer
dans cet ordre. Vous devez cibler la ligne <nouveau niveau> dans la hiérarchie Géographie. Ne
vous inquiétez pas si vous la déposez au mauvais endroit ; vous pouvez déplacer les niveaux en
les faisant glisser vers le haut ou vers le bas selon vos besoins.
4. Enfin, ajoutez l'attribut Ville comme niveau le plus bas. En tant que niveau clé ou feuille, c'est là
que la relation sera établie avec la table de faits, telle que définie dans le DSV.

Figure 9. Création de la Hiérarchie

En restant dans la dimension Customer, vous avez peut-être vu les avertissements dans les hiérarchies
vous informant que les relations d'attributs ne sont pas en place. C'est la prochaine étape sur laquelle
nous allons nous concentrer pour le développement de notre dimension.

Les relations d'attribut dans SSAS prennent en charge des optimisations supplémentaires de requête et
de stockage. SSAS utilise les relations définies pour consolider les opérations de traitement, ce qui rend
le chargement des données dans un modèle multidimensionnel (traitement) et l'interrogation des

Pr. I.MOUNTASSER 15
SQL Server BI Tools

données dans le modèle plus efficaces. SSAS optimise le stockage en utilisant davantage de compression
lorsque les relations d'attribut sont définies. Lorsque nous avons créé les hiérarchies, SSAS a supposé
que les relations pouvaient être optimisées en fonction des relations dans les hiérarchies. Ajoutons les
relations qui prennent en charge les hiérarchies que nous avons créées.

1. Lorsque vous ouvrez l'onglet Relations d'attribut dans la fenêtre Conception de la dimension
Customer, vous verrez que tous les attributs qui ont été ajoutés à nos hiérarchies ont été
mappés à Customer_ID. Les attributs restants sont considérés comme des attributs directs qui
ont une relation 1:1 avec Customer_ID.
2. Vous pouvez modifier le mappage de plusieurs manières. L'option la plus simple et parfois la
plus frustrante est de faire un glisser-déposer. Vous pouvez également ajuster ou créer la
relation en cliquant avec le bouton droit sur la relation d'attribut dans le volet de droite, sous la
fenêtre de mappage. Cela ouvrira une boîte de dialogue, dans laquelle vous pourrez ajouter ou
créer la relation dont vous avez besoin. Quel que soit le modèle que vous choisissez, vous devriez
obtenir un ensemble de relations correspondant aux hiérarchies que nous avons créées.

Figure 10. Ajout de Relations d'Attributs

Vous devez suivre les étapes précédentes pour chacune des dimensions que nous avons créées.
N'oubliez pas de créer la hiérarchie, puis de mettre à jour les relations d'attribut pour chacune de ces
dimensions.

II.2.5. Ajout de Cubes et de Groupes de Mesures

Dans les modèles multidimensionnels, les cubes sont généralement mappés aux tables de faits de notre
source de données. Ils ont des relations avec toutes les dimensions pertinentes et contiennent des
mesures qui peuvent généralement être agrégées.

Pr. I.MOUNTASSER 16
SQL Server BI Tools

Dans les modèles multidimensionnels dans SSAS, une BD fait généralement référence à la structure
globale du modèle SSAS. Le modèle est composé de dimensions, de cubes (qui contiennent plusieurs
groupes de mesures), des DSV et des sources de données. Dans SSMS, vous verrez une structure similaire
à celle de Visual Studio. Le projet dans Visual Studio est l'équivalent de la BD dans SSMS. Cela étant dit,
le cube peut également faire référence à l'ensemble de la base de données, même si les cubes sont des
structures spécifiques dans le modèle et utilisées par les utilisateurs et les développeurs. Les groupes de
mesures dans SSAS sont généralement organisés autour de tables de faits et partagent un ensemble
commun de dimensions. Ce modèle suit le paradigme du modèle dimensionnel et c'est pourquoi il est
important d'avoir des schémas en étoile pour prendre en charge les modèles multidimensionnels.

Figure 11. Ajout d'une Cube et de Groupes de Mesures

Afin de créer un Cube (Figure 11), suivez ces étapes :

1. Dans l'Explorateur de solutions, cliquez avec le bouton droit sur le dossier Cubes et sélectionnez
Nouveau cube, pour ouvrir la fenêtre de l'Assistant Cube. Cliquez sur Suivant dans l'écran
d'ouverture.
2. Sélectionnez « Utiliser les tables existantes » dans l'écran Sélectionner la méthode de création,
puis cliquez sur Suivant.
3. Dans l'écran Sélectionner les tables du groupe de mesures, sélectionnez les tables Customer,
Product, etc. puis cliquez sur Suivant.
4. Dans l'écran Sélectionner les mesures, cochez les cases des mesures souhaitées.
5. Dans l'écran Sélectionner les dimensions existantes, sélectionnez toutes les dimensions si elles
n'ont pas déjà été sélectionnées. Cliquez sur Suivant.
6. Changez le nom du cube en Northwind_Cube et cliquez sur Terminer.

Vous devriez maintenant voir la fenêtre de conception du cube Northwind_Cube (Figure 12).

Pr. I.MOUNTASSER 17
SQL Server BI Tools

Figure 12. Cube Nothwind

Les volets spécifiques de l'onglet Structure du cube sont Mesures, Dimensions et Vue de la source de
données. Vous pouvez ajouter ou modifier des mesures dans le volet Mesures. Si vous cliquez sur une
mesure, vous pouvez consulter les propriétés de cette mesure, y compris son agrégation, son format et
sa source. Dans le volet Dimensions, vous pouvez ajouter une dimension ou accéder à la fenêtre de
conception d'une dimension à partir de ce volet. Si vous avez besoin d'une nouvelle dimension ou si
vous devez supprimer ou ajouter une dimension à la structure du cube, vous pouvez le faire ici. L'autre
volet du cube est la vue de la source de données. Il s'agit du diagramme visuel de la structure sous-
jacente qui prend en charge le cube et ses groupes de mesures.

II.2.6. Déploiement du Modèle Multidimensionnel

Les étapes suivantes ne doivent être effectuées que si vous n'avez pas encore configuré les propriétés
de déploiement de votre projet. Pour que ces étapes fonctionnent, SSAS en mode multidimensionnel
doit être en cours d'exécution (tout en créant une BD dont le nom correspond au nom du projet
analytique). Cela nous permet de préparer le projet pour le traitement (Figure 13).

1. Cliquez avec le bouton droit sur le nom du projet dans l'Explorateur de solutions et sélectionnez
Propriétés dans le menu.
2. Cela ouvrira les pages de propriétés du projet. Vous verrez la page Propriétés de configuration
et trois sections appelées Génération, Débogage et Déploiement.
3. Dans la section Génération, définissez Édition du serveur de déploiement sur Développeur.
4. Dans la section Déploiement, définissez Nom du serveur sur le serveur que vous exécutez en
mode multidimensionnel. Dans la plupart des cas, Localhost, l'option par défaut, ne sera pas
correcte si vous configurez votre environnement avec des instances nommées.
5. Cliquez sur OK pour appliquer ces modifications. Votre projet devrait maintenant être prêt à
être déployé sur Analysis Services.
6. Cliquez avec le bouton droit sur le cube et sélectionnez Traiter. Cette étape ne s'applique que si
vous avez apporté des modifications au projet depuis la dernière fois qu'il a été traité. Vous

Pr. I.MOUNTASSER 18
SQL Server BI Tools

verrez un message indiquant Le contenu du serveur semble obsolète. Souhaitez-vous d'abord

générer et déployer le projet ? Sélectionnez Oui pour continuer la création et le déploiement.
7. Une fois les modifications du projet créées et déployées, une boîte de dialogue de processus
s'affiche. Pour cette section, laissez les paramètres par défaut tels quels et cliquez sur Exécuter
pour continuer.
8. Une fois cette opération terminée, vous pouvez fermer toutes les fenêtres ouvertes.

Figure 13. Processus de Traitement d'un Cube

L'un des problèmes les plus courants et les plus ennuyeux avec SSAS dans un environnement de réseau
de développement non professionnel est dû à l'emprunt d'identité. Vous vous souvenez peut-être que
lorsque nous avons créé la source de données au début, nous avons utilisé Utiliser les informations
d'identification de l'utilisateur actuel pour l'emprunt d'identité.

Dans cette configuration, cela ne fonctionnait pas lors du traitement, car l'utilisateur actuel est le
compte de service SSAS NT, qui exécute le service. Il s'agit d'un service par défaut qui a été créé par
l'utilisateur lors de la configuration du compte. Pour contourner ce problème, j'ai ajouté ce compte
d'utilisateur à ma base de données Nortwind_DW dans le rôle db_datareader. Si vous rencontrez des
problèmes de traitement, vous pouvez utiliser cette option. Je ne le recommanderais pas pour une
utilisation en production. Un compte système avec les autorisations appropriées doit être utilisé pour
gérer ce scénario.

Pr. I.MOUNTASSER 19
SQL Server BI Tools

II.2.7. Parcourir le Cube

Une fois le Cube déployé, il faut se connecter via SQL Server Management Studio à notre BD SSAS. Nous
vous recommandons de traiter le cube une fois de plus pour vous assurer que toutes vos modifications
sont en place. Voici quelques éléments clés à prendre en compte dans le navigateur :

1. Il s'agit du bouton « Changer d'utilisateur ». Vous pouvez choisir l'expérience utilisateur que
vous souhaitez voir. Cela est lié à la sécurisation de vos données de cube et peut impliquer des
objets et des données filtrés.
2. Il s'agit du bouton « Reconnecter ». Utilisez-le après le traitement de votre modèle afin de
pouvoir voir les dernières modifications.
3. Cette liste déroulante vous permet de basculer entre MDX et DAX. Dans la plupart des cas, vous
utiliserez MDX lorsque vous travaillerez avec des modèles multidimensionnels.
4. Cette zone est la zone de filtre. Vous pouvez y faire glisser une dimension pour limiter les
résultats de la requête. Ajoutez un filtre de date ici. Faites glisser la dimension Date vers cette
zone. Laissez l'opérateur défini sur Égal. Dans Expression de filtre, sélectionnez une date. Cela
filtrera les résultats sur la date saisie.
5. Il s'agit de la zone de métadonnées. À partir de là, vous pouvez faire glisser des mesures, des
indicateurs de performance clés et divers composants de dimension, y compris la dimension
complète, les hiérarchies et les attributs, vers la zone de requête.
6. Il s'agit de la zone de requête. Vous déposez ici des éléments de la zone de métadonnées pour
créer la requête. Vous verrez les résultats au fur et à mesure de la création. Vous avez besoin
d'au moins une mesure et d'une dimension pour voir les données ici.

Figure 14. Parcourir le Cube

Ceci conclut la partie construction du modèle multidimensionnel. À ce stade, vous avez créé avec succès
un projet multidimensionnel Analysis Services. Vous avez ajouté des dimensions et des cubes au projet.
Vous avez également déployé votre projet en tant que BD Analysis Services et traité cette base de
données afin de pouvoir la charger avec des données. Vous disposez désormais d'un cube qui prend en
charge les analyses de base, ce qui signifie que vous pouvez parcourir le cube dès maintenant à l'aide
d'outils tels qu'Excel ou Power BI. Nous avons terminé notre création et notre déploiement en
parcourant les données que nous avons déployées sur le cube. Dans ce qui suit, nous continuerons à

Pr. I.MOUNTASSER 20
SQL Server BI Tools

développer le cube en y ajoutant des calculs et des indicateurs de performance clés. Nous explorerons
également nos données à l'aide de SSMS et de MDX.

II.2.8. Ajout de Mesures et de Calculs avec MDX

Maintenant que nous avons créé le cube, le moment est venu de l'améliorer avec MDX. MDX, ou
expressions multidimensionnelles, est le langage utilisé pour interroger un modèle multidimensionnel
et créer des calculs dans le cube. Dans cette section, nous allons présenter les concepts de base de MDX
pour la création de mesures, de calculs et de requêtes. Bien que MDX ait une syntaxe et une structure
similaires à SQL, l'implémentation et les résultats ne sont pas similaires. Nous utiliserons MDX pour
étendre les fonctionnalités du cube avec des mesures, des calculs et des KPI. Les mesures et les calculs
peuvent être utilisés pour normaliser les mesures commerciales pour tous les utilisateurs. Les KPI, ou
indicateurs de performance clés, sont utilisés pour visualiser les performances et les tendances par
rapport à des objectifs spécifiés. MDX remplit deux fonctions principales lorsque l'on travaille avec des
données multidimensionnelles : interroger les données et améliorer le cube.

SQL est un langage de requête tabulaire. Il renvoie des données sous forme de colonnes et de lignes. Un
modèle multidimensionnel a plus de profondeur que des colonnes et des lignes. Cela signifie que vous
pouvez avoir des lignes, des colonnes et d'autres dimensions. Conceptuellement, chaque point de
données d'un groupe de mesures est intersecté par chaque niveau de chaque dimension du cube. Cela
vous permettra de parcourir le modèle de différentes manières. Bien que MDX ait la même construction
de langage de base que SQL, il ajoute des expressions qui prennent en charge la navigation
multidimensionnelle des données.

Dimensions : Nous avons créé des dimensions lors de la création du cube. Les dimensions sont les
éléments de base de MDX. Par exemple, si nous créons un calcul pour les ventes moyennes, une
dimension définit le contexte de la moyenne. Les dimensions découpent et filtrent les données pour
créer le contexte de la mesure et de l'agrégation.

Niveaux : Les hiérarchies définissent les niveaux d'une dimension. Pour cette raison, MDX prend en
charge les niveaux au sein des hiérarchies d'une dimension pour explorer les niveaux de la dimension
vers le haut et vers le bas. Par exemple, vous pouvez utiliser MDX pour déterminer les valeurs aux
niveaux Année, Mois et Jour dans la dimension Date. Il est important de comprendre que les données
dimensionnelles peuvent être représentées avec plusieurs hiérarchies et, en tant que telles, avoir
différentes façons d'afficher les données dans le code.

Figure 15. Hiérarchie Calendar

Membres : Les membres sont les valeurs ou les noms réels d'éléments spécifiques dans un niveau. Par
exemple, les membres représentant des éléments individuels dans chaque niveau seraient
Année = 2019, Mois = Mars et Jour = 15/03/2020.

Tuples et ensembles : Les tuples représentent l'intersection des membres. Par exemple, examinons les
dimensions : Date et Client et recherchions la moyenne par mois et par client. Pour être plus précis, nous

Pr. I.MOUNTASSER 21
SQL Server BI Tools

voulons voir cela pour juin 2019 pour Customer_1. Cela donnerait le tuple suivant : ([June 2019],
[Customer_1]). À cette intersection de dimensions, nous voulons voir les ventes moyennes. Les
ensembles sont un groupe de tuples qui peuvent être utilisés pour déterminer un ensemble de valeurs
combinées entre des tuples liés. Par exemple, vous pouvez utiliser un ensemble de tuples si vous
souhaitez obtenir les ventes moyennes pour juin et juillet dans notre exemple. Cet ensemble pourrait
être exprimé de la manière suivante : {([Customer_1], [June 2019]), ([Customer_1],[July 2019]) }. Cela
nous donnerait les ventes moyennes de Customer1 pour juin et juillet 2019. Lorsque vous travaillez avec
MDX, de nombreuses expressions renvoient un ensemble pour vous permettre d'effectuer un calcul.

Pour comprendre MDX, il faut comprendre appelons-la la ponctuation de MDX :

• Les crochets [ ] sont utilisés pour encapsuler les noms des éléments de conception et des
membres. Tout d'abord, lorsque vous travaillez avec des éléments de conception, nous devons
utiliser des crochets : [Année]. Ce niveau contient toutes les années en tant que membres.

• Les parenthèses () sont utilisées pour encapsuler les tuples, comme indiqué précédemment.
Cependant, les parenthèses sont également utilisées avec les fonctions et les mathématiques.

• Les accolades {} sont utilisées pour encapsuler des ensembles dans MDX.

• Les points sont utilisés pour créer des définitions pour les membres et pour créer des fonctions.
Par exemple, La définition d’un membre peut-être : Dimension.Hierarchy.Level.Member.

Comprendre la structure de la requête : Lorsque vous examinez une requête classique, qu'elle soit SQL
ou MDX, vous voyez la structure de base de SELECT, FROM, WHERE. Cependant, dans un cube, ces
éléments ne fonctionnent pas de la même manière :

• SELECT est utilisé pour définir le contenu que vous souhaitez afficher. Dans MDX, vous devez lui
indiquer où vous souhaitez que ces données aillent. S'agit-il de la colonne, de la ligne ou d'une
autre dimension ? C'est là que nous commençons à avoir du mal à comprendre MDX. Dans une
requête, vous spécifiez ON COLUMNS ou ON ROWS. Vous devez également savoir que ON 0 est
égal aux colonnes et ON 1 est égal aux lignes.
• FROM est utilisé pour définir le cube que vous interrogez.
• WHERE est utilisé pour découper ou filtrer les données sur une définition d'ensemble.
L'ensemble est utilisé pour définir le filtre de la requête, comme le mois pour lequel vous
souhaitez voir les données. Cela permet de créer efficacement un sous-cube ou une sous-section
du cube auquel appliquer le reste de la requête.

Maintenant, nous utiliserons SQL Server Management Studio (SSMS) pour créer une requête. Le
processus de création d'une requête est le suivant (Figure 16):

1. Connectez-vous à votre base de données SQL Server Analysis Services. Une fois connecté à
la BD, la structure et le contenu du cube s’affiche dans l'onglet Métadonnées.
2. Cliquer sur Nouvelle requête ou MDX pour créer une nouvelle fenêtre de requête MDX. Les
informations sur les métadonnées sont très utiles lors de la création de requêtes. Vous
pouvez glisser-déposer de la fenêtre de métadonnées vers le panneau de requête pour
effectuer des requêtes. Cela permet de comprendre comment façonner les différents
éléments de données pour la requête.
3. Créer une requête qui nous montrera le total des commandes pour chaque client.
Commencez par saisir SELECT dans la fenêtre de requête. Faites glisser les dimensions
nécessaires vers la fenêtre de requête. Ajoutez la fonction .members au nom du niveau. Cela
renverra l'ensemble des membres.

Pr. I.MOUNTASSER 22
SQL Server BI Tools

4. Spécifiez Sur les colonnes ou Sur 0 (ou columns) pour spécifier que vous souhaitez voir cela
dans les colonnes.
5. Ensuite, ajoutez la clause FROM et faites glisser ou saisissez le nom du cube dans la clause
FROM.
6. Exécutez la requête. Elle affichera la mesure par défaut, qui peut varier pour vous car nous
n'avons pas explicitement défini cette valeur lors de la création du cube.
7. Ajoutez une clause WHERE ou un segment pour une date spécifique (ex.2016). Faites glisser
l’année en question sur la fenêtre de requête après votre clause WHERE.

Figure 16. Requêtes MDX

Maintenant que vous avez créé avec succès votre première requête avec MDX, nous allons parcourir
certaines variantes qui vous seront utiles à mesure que nous continuons à développer le cube et à créer
des requêtes plus sophistiquées.

Ajout de mesures explicites à notre requête. Dans cet exemple, nous allons modifier la requête pour
spécifier les mesures que nous souhaitons afficher et déplacer nos clients vers les lignes.

Figure 17. Requêtes Sophistiquées MDX

Utilisation de NON EMPTY. Toutes les requêtes incluent l'intersection de toutes les combinaisons
possibles. Dans un cube, bon nombre de ces combinaisons peuvent aboutir à une valeur vide, ou null.
Ce phénomène est courant dans les cubes et peut entraîner des résultats de requête supérieurs à ceux
souhaités. Ainsi, nous allons introduire deux nouvelles fonctions : crossjoin et children. crossjoin crée
un ensemble cartésien qui inclut tous les tuples possibles entre [COMPANY NAME] et [Nbr Qt Sales].
Cela nous permet de spécifier la mesure que nous recherchons et de la segmenter par client. children
renvoie un ensemble de membres ou d'enfants de niveau supérieur dans la hiérarchie. Ce que vous

Pr. I.MOUNTASSER 23
SQL Server BI Tools

pouvez remarquer, c'est que dans de nombreux cas, il n'y a aucune valeur dans de nombreuses cellules.
Si nous voulons masquer ces cellules car ils ne nous aident pas. Nous pouvons le faire en ajoutant NON
EMPTY au début de notre ligne Client.

Ajout de membres calculés à notre requête. Nous avons créé le cube avec de nombreuses mesures
intégrées (ex. Nbr Sales Amount). Ces mesures ont été conçues pour être additionnées. Dans la requête
précédente, nous avons utilisé Nbr Orders pour déterminer le nombre de commandes pour la catégorie
fruit de mer selon le pays des clients. Utilisons ces informations pour créer deux mesures que nous
pouvons utiliser dans notre requête : [Nbr Seafood] et [% Seafood]. Lorsque vous ajoutez des membres
calculés à une requête, utilisez la clause WITH. Chaque nouveau membre possède une définition de
membre au format suivant : MEMBER name AS expression. L'ordre dans lequel vous créez les membres
est également important.

Ajout de calculs et de mesures à votre cube. Nous avons créé des calculs lors de l'interrogation du cube.
Maintenant, faisons-en une partie permanente du cube. Dans cette section, nous allons passer de SSMS
comme outil principal à Visual Studio et au projet SSAS. Nous continuerons à utiliser SSMS pour les tests
et l'expérimentation, vous devrez donc avoir les deux outils ouverts. Dans notre cas d'utilisation ici, nous
supposerons que nos utilisateurs professionnels essaient de déterminer quelles catégories ont le plus
d'impact sur leur activité. Pour cela, rouvrez votre projet Visual Studio, et ouvrez la fenêtre de
conception du cube. Dans la fenêtre de conception, sélectionnez l'onglet Calculs. Ce sera notre point de
départ pour ajouter des mesures calculées et des ensembles nommés créés avec MDX. Vous pourrez
créer des mesures et des ensembles nommés dans le volet de requête (avec commentaires pour que les
autres concepteurs comprennent ce que font les calculs). La fonction CALCULATE ne doit pas être
supprimée. Le volet Organisateur de scripts répertorie les éléments de calcul créés. Aussi, les outils de
calcul contiennent une grande partie du même contenu que celui avec lequel nous avons travaillé dans
SSMS lors de la création de nos requêtes. Le glisser-déposer est également pris en charge ici.

1. Cliquez avec le bouton droit dans le volet Organisateur de scripts ou en sélectionnez

Nouveau membre calculé dans la barre de menus. Ce qui ouvre un formulaire.
2. Parcourir chaque valeur pour créer notre membre calculé [Seafood Products]. Expression,
c'est ici que se déroule le calcul MDX. Notre calcul renvoie la quantité des produits Fruits de
mer.

Vous avez peut-être remarqué qu'il n'y avait pas de bouton d'enregistrement ou de fermeture sur ce
formulaire. Ce formulaire a écrit le calcul dans le script en arrière-plan. Pour voir le travail que vous avez
effectué, vous devez changer la vue en script.

Il est maintenant temps de voir notre travail en action (Figure 18). Vous pouvez traiter les modifications
en cliquant sur le bouton Traiter dans la barre d'outils. Acceptez les invites par défaut. Une fois le
traitement des groupes de mesures terminé, revenez à SSMS et actualisez votre volet Explorateur
d'objets. Créez une requête MDX simple en utilisant la nouvelle mesure. Comme vous pouvez le
constater, on n'a plus besoin de créer les mesures dans chaque requête ; elles font désormais partie de
la structure du cube lui-même.

II.2.9. Ajout d'indicateurs de performance clés à notre cube

Les KPI sont utilisés par les entreprises pour évaluer les performances au fil du temps. Les KPI dans les
modèles multidimensionnels dans SSAS sont basés sur un serveur et peuvent être utilisés par divers

Pr. I.MOUNTASSER 24
SQL Server BI Tools

outils d'utilisateur final tels qu'Excel. L'avantage ici est qu'un KPI d'entreprise peut être créé et partagé
facilement au sein d'une organisation. Un KPI typique est construit sur les composants suivants :

• Objectif ou cible
• Valeur ou valeur réelle
• Statut de la valeur par rapport à l'objectif
• Tendance de la valeur à atteindre l'objectif

Ces valeurs peuvent utiliser des indicateurs qui fonctionnent généralement à partir d'un système de
valeurs de -1 à 1. Ici, -1 est mesuré comme non-atteinte de l'objectif et 1 comme atteinte de l'objectif.
Toutes ces valeurs sont construites avec MDX, ce que nous allons approfondir maintenant.

Figure 18. Ajout et Utilisation de Calcul

Création de KPI. Tout d'abord, vous devez ouvrir l'onglet KPI dans la fenêtre Conception de cube de
votre projet Visual Studio. Créez un nouveau KPI en cliquant avec le bouton droit de la souris dans le
volet Organisateur de KPI ou en sélectionnant l'option Nouveau KPI dans la barre d'outils. Cela ouvrira
un formulaire similaire à celui que nous avons utilisé lors de la création des membres calculés. Un KPI
est généralement utilisé pour atteindre un objectif. Poursuivant notre thème d'analyse des catégories
de produits, notre entreprise souhaite voir la vente de fruits de mer augmenter de 10 % d'une année
sur l'autre (YOY). Notre KPI sera construit avec les attributs suivants :

• Objectif : Croissance de la quantité vendue de 10 % YOY.

• Mesure : seafood product.

Pr. I.MOUNTASSER 25
SQL Server BI Tools

Notre première étape consistera à ajouter d'autres membres calculés pour prendre en charge nos KPI.
Dans le cadre du processus de conception et de découverte, il est courant d'utiliser SSMS pour travailler
sur les membres dont vous avez besoin et avec lesquels vous souhaitez effectuer des tests. Une fois la
découverte terminée, les membres calculés MDX de prise en charge suivants doivent être ajoutés au
cube :

• Vous pouvez les ajouter à l'onglet Calculs de la fenêtre Conception de cube.

• Créer un dossier distinct appelé Support KPI pour nous aider à organiser ces membres calculés.
• Chaque mesure fonctionne avec [Mesures].[Quantité] et la hiérarchie Date, en particulier avec le
niveau [Year]. Nous allons combiner cette mesure avec nos différentes catégories d'articles pour
créer les KPI. Nous pouvons les utiliser pour créer des KPI similaires ultérieurement.

Figure 19. Calculs pour Créer KPIs

Voici les noms pour ces calculs, [Current FY Quantity], [Previous FY Quantity], [Previous FY Quantity] et
[Quantity Status]. Avant de passer à la création des KPI, il existe plusieurs nouvelles expressions à ajouter
à votre vocabulaire MDX. ANCESTOR est une fonction qui renvoie un membre qui se trouve plus haut
dans la hiérarchie. Vous pouvez spécifier le nombre de niveaux que vous souhaitez monter ou le niveau
spécifique auquel vous souhaitez accéder. Dans notre cas, nous voulons être au niveau [Year] quel que
soit notre point de départ. Deux fonctions membres sont également utilisées dans ces mesures.
currentmember fait référence au membre actuellement dans le contexte. Il sera utilisé comme point de
référence pour le calcul. previousmember est utilisé pour revenir en arrière. nextmember existe
également pour avancer. Dans notre cas, si currentmember est 2015, previousmember renverra 2014
et nextmember renverra 2016. Ces fonctions sont utilisées régulièrement pour naviguer entre les
membres liés. Le dernier élément de code à noter est l'instruction CASE. Il existe deux méthodes

Pr. I.MOUNTASSER 26
SQL Server BI Tools

courantes pour gérer ce type de calcul : CASE et IIF. CASE est généralement plus efficace en termes de
traitement, mais IIF peut fonctionner pour des expressions plus simples. Les deux options évaluent une
expression et renvoient un résultat pour un résultat vrai et un résultat faux. Découvrez les deux options
afin de pouvoir disposer d'options de conception lors de la création de calculs MDX.

À l'aide de notre formulaire KPI, voici le processus que nous allons suivre pour créer ceci :

→ Expression de valeur : dans la valeur, nous utiliserons [Current FY Quantity] avec [Dim
Product].[CATEGORY NAME].&[Seafood]. Cela nous donnera la quantité de l'année en cours, qui
est la valeur que nous utiliserons comme valeur réelle pour comparer avec l'objectif.
→ Expression d'objectif : L'expression d'objectif est la cible de 10 % que nous avons créée,
combinée au niveau [Dim Product].[CATEGORY NAME].&[Seafood]. Voici le code de l'objectif.
→ Expression d'état : L'expression d'état utilise la mesure [Quantity Status] avec la même
référence croisée [Dim Product].[CATEGORY NAME].&[Seafood]. Cela renverra -1, 0 ou 1 selon
les critères que nous avons définis.
→ Indicateur d'état : sélectionnez le type d'indicateur « Formes » pour notre exemple. Vous
devriez explorer l'option intégrée pour voir comment ils réagissent à votre KPI.

Assurez-vous de traiter le cube lorsque vous avez terminé le KPI. Si vous souhaitez en savoir plus sur
l'utilisation des KPI, vous pouvez approfondir vos connaissances en utilisant les tendances et d'autres
options plus avancées. Vous pouvez afficher les indicateurs KPI avec Excel. C'est vraiment l'un des
meilleurs moyens de confirmer que vous avez correctement configuré les indicateurs.

Figure 20. KPI Affiché avec Excel

Pr. I.MOUNTASSER 27