CUBE
MICROSOFT
SQL SERVER
A N A LY S I S
SERVICES
A U T E U R : J O N AT H A N F O N TA N E L
D AT E : 0 3 / 0 1 / 2 0 2 3
TP ISIMA ZZ3 F3 BUSINESS INTELLIGENCE 1
PLAN DU TP
2
• Présentation de la suite Microsoft BI
• Ateliers sur SSAS
PRÉSENTATION DE MICROSOFT
BI
• La Suite Microsoft BI : Outil tout-en-un pour gérer un projet de BI : SQL
Server Data Tools (SSDT)
SSIS SSAS RS
• Intégration de • Réalisation de • Création de
données cubes rapports
(ETL)
3
SSAS : SQL SERVER ANALYSIS SERVICES
• SSAS : création de cube
– Création de cube
– Exploration de données
– Interface avec Excel ou RS
4
ATELIER SUR SSAS
5
• Restauration d’un entrepôt de données
• Création d’un projet
• Création d’une source de données
• Création d’une vue de source de données
• Changement des libellés des tables
• Création d’une dimension
• Création d’un cube
• Modification de deux dimensions
• Vérification des propriétés du cube
• Déploiement du cube
ATELIER SUR SSAS
6
• Exploration du cube
• Ajouter des mesures dans le cube et les paramétrer en fonction du type
• Re-Déployer et Naviguer dans le cube modifié
• Ajouter une hiérarchie au niveau d’une dimension
• Re-Déployer et Naviguer dans le cube modifié
ATELIER SUR SSAS
7
• Restauration d’un entrepôt de données
à partir de l’exemple Microsoft :
Adventure Works DW
– Télécharger le fichier .mdf sur
https://perso.isima.fr/~jofontan/BI/TP2/
Atelier%20SSAS/
– Puis copier le dans le dossier
C:\Program Files\Microsoft SQL
Server
2019\MSSQL15.MSSQLSERVER\MSSQ
L\DATA
– Ouvrir SS Management Studio
– Puis faire un clic droit sur
« Databases »
– Cliquer sur « Attach »
ATELIER SUR SSAS
8
• Restauration d’un entrepôt
de données à partir de
l’exemple Microsoft :
Adventure Works DW
– Supprimer le fichier de log
dans « Détails de la base de
données »
– Puis cliquer sur ok
ATELIER SUR SSAS
9
• Créer un user « userDWH » avec pour mdp « isima »
• Mettre AdventureWorksDW2008R2 comme Database par défault
• Puis dans User Mapping, cocher la base AdventureWorksDW2008R2
• Et cliquer pour cette base sur db_datareader
• Cliquer sur ok
ATELIER SUR SSAS
10
• Visualisation des différentes tables de l’entrepôt : Adventure Works
DW
• Regarder le contenu des tables
• Pour avoir une vue des métadonnées
Se rendre sur le slide suivant avec
le schéma du datawarehouse
ATELIER SUR SSAS
• Identifier les dimensions et les tables de fait
ATELIERS SUR SSAS
• Création d’un projet dans Visual Studio 2019 en tapant analysis dans la
barre de recherche :
12
ATELIERS SUR SSAS
• Renseigner un nom de projet
13
ATELIER SUR SSAS
14
• Création d’une source de données :
– Cliquer sur Nouvelle source de données
– Puis sur Nouveau
ATELIER SUR SSAS
15
• Création d’une source de
données :
– Nom du serveur : localhost
– Authentification : avec
l’identifiant créé dans les slides
précédents à userDWH
– Puis renseigner l’entrepôt de
données que l’on vient de
restaurer
ATELIER SUR SSAS
16
• Création d’une source de données :
– Cliquer sur Suivant
ATELIER SUR SSAS
17
• Création d’une source de données :
– Utiliser le compte SQL Server Cube créé
ATELIER SUR SSAS
18
• Vue des sources de données :
– Faire un clic droit sur Vue de source de données
– Puis cliquer sur suivant
ATELIER SUR SSAS
19
• Vue des sources de données :
– Déplacer les tables suivantes de gauche vers la droite : DimCustomer,
DimDate, DimGeography, DimProduct et FactInternetSales
ATELIER SUR SSAS
20
• Vue des sources de données :
– On peut consulter la vue créée avec les contraintes entre les tables qui
sont reprises
ATELIER SUR SSAS
21
• Création d’une dimension :
ATELIER SUR SSAS
22
• Création d’une dimension :
– Cliquer sur Suivant avec la première option « Utiliser une table existante »
ATELIER SUR SSAS
23
• Création d’une dimension :
– Sélectionner la dimension DimDate
ATELIER SUR SSAS
24
• Création d’une dimension :
– Sélectionner les attributs
suivants
– Puis modifier le type d’attribut
pour avoir les mêmes attributs
que la capture
ATELIER SUR SSAS
25
• Création d’une dimension :
ATELIER SUR SSAS
26
• Création d’un cube :
ATELIER SUR SSAS
27
• Création d’un cube :
ATELIER SUR SSAS
28
• Création d’un cube :
– Cliquer sur Suggérer pour que Visual Studio détermine vous aide à
trouver les mesures
ATELIER SUR SSAS
29
• Création d’un cube :
ATELIER SUR SSAS
30
• Création d’un cube :
ATELIER SUR SSAS
31
• Création d’un cube :
ATELIER SUR SSAS
32
• Création d’un cube :
ATELIER SUR SSAS
33
• Modification de la dimension Customer :
ATELIER SUR SSAS
34
• Modification de la dimension Customer :
– Faire glisser les attributs de la source de données vers la gauche pour
enrichir les attributs de la dimension Customer du cube
ATELIER SUR SSAS
35
• Modification de la Dimension Product :
ATELIER SUR SSAS
36
• Modification de la Dimension Product :
– Faire glisser les attributs de la source de données vers la gauche pour
enrichir les attributs de la dimension Product du cube
ATELIER SUR SSAS
37
• Vérification des propriétés du cube :
– Expliquer pourquoi les dimensions du cube (en bas à gauche) ne sont pas
identiques aux dimensions déclarées ?
ATELIER SUR SSAS
38
• Vérification des propriétés du cube :
– A quoi sert cet onglet ?
ATELIER SUR SSAS
39
• Vérification des propriétés du cube :
– A quoi sert cet onglet ?
ATELIER SUR SSAS
40
• Vérification des propriétés du cube :
– Cliquer dans paramètre de stockage
– Donner la différence entre MOLAP, HOLAP et ROLAP sans regarder la
diapositive suivante ?
ATELIER SUR SSAS
41
• Différence entre MOLAP ROLAP et HOLAP :
• Source : http://www710.univ-lyon1.fr/~elghazel/
• Pour aller plus loin : https://docs.microsoft.com/fr-fr/analysis-
services/multidimensional-models-olap-logical-cube-objects/partitions-
partition-storage-modes-and-processing?view=asallproducts-allversions
ATELIER SUR SSAS
42
• Déploiement du 1er cube :
– Pour déployer votre cube, il est nécessaire de déclarer le serveur Analysis
Services qui va recevoir votre cube
– Pour cela, faire un clic droit sur votre projet
– Puis sur cliquer sur propriété
ATELIER SUR SSAS
43
• Déploiement du 1er cube :
– Vérifier que les paramètres sont les mêmes que ci-dessous
ATELIER SUR SSAS
44
• Déploiement du 1er cube :
– Clic droit sur votre projet
– Puis cliquer sur Déployer
ATELIER SUR SSAS
45
• Déploiement du 1er cube :
– Ouvrir SS Management Studio
– Puis se connecter de la manière suivante en remplaçant A210-PC-PROF par le
nom de votre machine ou localhost
ATELIER SUR SSAS
46
• Déploiement du 1er cube :
– Ouvrir votre projet dans Database pour trouver votre cube déployé
– Puis faire un clic droit
– Cliquer sur Browse
ATELIER SUR SSAS
• Via le navigateur, naviguer dans votre cube pour obtenir cette
visualisation des KPIs « Sales Amount » et « Fact Internet Sales
Nombre » par « Product Line » et par « City » avec un filtre sur le
pays (FR)
47
ATELIER SUR SSAS
48
• Modifier le cube pour rajouter un certain nombre de mesures (indicateurs)
: TaxAmt
ATELIER SUR SSAS
49
• Changer le FormatString des mesures qui sont des montants pour mettre
Currency :
ATELIER SUR SSAS
50
• Générer et déployer à nouveau votre cube
ATELIER SUR SSAS
51
• Naviguer dans le cube pour obtenir la visualisation suivante
• Expliquer à quoi elle pourrait servir ?
ATELIER SUR SSAS
52
• Dans la dimension Customer, créer une hiérarchie du pays à la ville en
faisant des glisser et déposer. Il est nécessaire de rajouter l’attribut State
Province Name dans la dimension du cube au préalable
ATELIER SUR SSAS
53
• Générer et déployer à nouveau votre cube
ATELIER SUR SSAS
54
• Naviguer dans le cube pour vous servir de la hiérarchie :
ATELIER SUR SSAS
• Gestion des droits classiques
autour des cubes :
https://docs.microsoft.com/fr-
fr/sql/analysis-
services/multidimensional-
models/grant-database-permissions-
analysis-services?view=sql-server-
2017
• Pour utiliser le cube depuis Excel, il
est possible d’attribuer des droits
de lecture à un utilisateur Windows.
Pour cela, il suffit de suivre la
procédure suivante :
– Se connecter en tant
qu’administrateur sur SQL Server
Management Studio
– Ouvrir l’arborescence jusqu’à rôle
– Puis cliquer sur Nouveau rôle 55
ATELIER SUR SSAS
• Nommer le rôle Accès
• Puis se rendre dans Appartenance et ajouter le compte de votre binôme
56
ATELIER SUR SSAS
• Puis se rendre dans Cubes et donner les droits d’accès en lecture
• Valider puis se connecter avec la session de votre binôme
57
ATELIER SUR SSAS
58
• Ouvrir Excel puis créer une connexion vers votre cube déployé
– Se connecter à votre serveur (nom de votre PC dans ce cas précis)
– Utiliser l’identifiant de la session Windows
ATELIER SUR SSAS
59
• Ouvrir Excel puis créer une connexion vers votre cube déployé
– Se connecter à votre serveur (nom de votre PC dans ce cas précis)
– Utiliser l’identifiant de la session Windows
ATELIER SUR SSAS
60
• Sélectionner votre cube, et donner un nom à la connexion
• Importer les données en tant que
Tableau croisé dynamique
ATELIER SUR SSAS
61
• Avec votre tableau croisé dynamique, créer les rapports suivants :
– Hiérarchie de pays, régions, villes en ligne
– Années en colonne
– Indicateurs : Sales Amount, Fact Internet Sales, Total Product Cost
– Comment analysez vous les résultats ?
– Quel pays à la meilleure performance ?
– Comment évolue la tendance ?
– Quelles sont les villes qui créent le plus de CA ?
ATELIER SUR SSAS
62
• Avec votre tableau croisé dynamique, créer les rapports suivants :
– Hiérarchie de pays, régions, villes en ligne
– Puis Nom des modèles en ligne
– Années en colonne
– Indicateurs : Total Product Cost et Order Quantity
– Questions :
• Quels sont les produits les plus vendus ?
• Est ce que ce sont toujours les mêmes produits qui sont dans le top des ventes en
fonction des pays et des années ? Justifier ?
• Quel est votre analyse sur ces chiffres ?
• Quelles sont vos recommandations ?
