Microsoft Business Intelligence

23/10/2019 [Link]@[Link] 1
Objectifs
 Comprendre les notions de la BI
 Comprendre les outils de Microsoft BI
 La mise en place de techniques et méthodes de L'ETL
 Modélisation et alimentation d’un Datawarehouse
 Déployer des projets d’intégration sur des environnements
 Création de cube multidimensionnel d'analyse de données
 Concevoir des rapports et des tableaux de bord
 Comprendre les concepts de PowerPivot et Power View
 Comprendre les languages MDX, DAX et XMLA
 La mise en place d’un projet décisionnel

23/10/2019 [Link]@[Link] 2
Introduction à la formation
• La Business Intelligence : pourquoi?
• La Business Intelligence : qu’est-ce que c’est?
• Quels sont les outils de Microsoft BI?
• SQL Server Integration Services
• SQL Server Analysis Services
• SQL Server Reporting Services
• L’architecture de MSBI
• Etude de cas

23/10/2019 [Link]@[Link] 3
La Business Intelligence : pourquoi?

23/10/2019 [Link]@[Link] 4
La Business Intelligence : qu’est-ce que c’est?

Savoir
Structuration,
présentation
et analyse)

Information
Collecte et Consolidation

Données
ERP, CRM, Base de données, CSV, Excel …

Processus Business
23/10/2019 [Link]@[Link] 5
La Business Intelligence : qu’est-ce que c’est?
• Collecte de données
• Comprendre l’environnement
• Analyse de l’activité, des réussites et des échecs
• Outils de pilotage de performance
• Transformer les données en décisions
• Anticiper les évolutions du marché
 Les managers
Pour qui ?  La direction générale
 Tout le monde

23/10/2019 [Link]@[Link] 6
La Business Intelligence : qu’est-ce que c’est?

Les tendances du marché

BI Temps réel (RTBI)

BI Mobile
Self-Service BI
Big Data

23/10/2019 [Link]@[Link] 7
La Business Intelligence : qu’est-ce que c’est?

Microsoft Microsoft Microsoft

SQL Server SQL Server SQL Server
Integration Services Analysis Services Reporting Services

Integration Analyse Reporting

23/10/2019 [Link]@[Link] 8
 SQL Server Integration Services

ETL
l’ETL sert à :

 Chargement de données depuis les différentes sources

(Excel, CSV, BDD etc…)
 Nettoyer les données
 Transformer les données
(Création de colonne, changement type, Jointure de tables
 Transférer les données transformées Extraire Transformer Charger
(Chargement dans l’entrepôt de données ou des fichiers)
 Ordonnancer des taches

23/10/2019 [Link]@[Link] 9
SQL Server Analysis Services
• SQL Server Analysis Services permet de concevoir, de créer et de gérer des structures multidimensionnelles
qui contiennent des données agrégées provenant d'autres sources de données

 SSAS génère des cubes OLAP

 SSAS permet l’exploration de données
 SSAS permet également d'implémenter des algorithmes de Data Mining

OLAP

23/10/2019 [Link]@[Link] 10
SQL Server Reporting Services
• SQL Server Reporting Services est une plateforme de Reporting qui fournit des fonctionnalités de création
de rapports qui permettent d’extraire et de mettre en forme des données.
 SSRS est un outil de restitution des données
 SSRS permet de concevoir, créer, déployer et gérer des rapports

23/10/2019 [Link]@[Link] 11
L’architecture de MSBI

23/10/2019 [Link]@[Link] 12
Etude de cas : MANGO

23/10/2019 [Link]@[Link] 13
Etude de cas : SSIS (Integration Services)

ETL
OLTP

23/10/2019 [Link]@[Link] 14
Etude de cas : SSAS (Analysis Services)

Géographie
OLAP

23/10/2019 [Link]@[Link] 15
Géographie
Etude de cas : SSRS (Reporting Services)

Décision

23/10/2019 [Link]@[Link] 16
 SQL Server Management Studio
SQL Server Management Studio est un environnement intégré qui permet d'avoir accès, de configurer,
de gérer, d'administrer et de développer tous les composants de SQL Server.

SQL Server Management Studio associe un groupe d'outils graphiques à des éditeurs de script T-SQL,
MDX, …

23/10/2019 [Link]@[Link] 17
 L’espace de travail MSBI
Avant 2010, la création d’un projet SSIS se faisait auparavant sur un outil utilisant l’environnement basé sur
Microsoft Visual Studio et appelé « Business Intelligence Development Studio », aussi surnommé « BIDS ».
Dans les nouvelles versions de Microsoft SQL Server, l’outil a été renommé « SQL Server Data Tools for Business
Intelligence solutions».

SQL Server Data Tools est utilisé pour des services d’analyses, de Reporting et d’integration et présente un
environnement similaire à Visual Studio (Explorateur de solutions, sources de données …)
23/10/2019 [Link]@[Link] 18
 Environnement SSIS
Une fois le projet d’intégration créé, il sera représenté par un « Package » avec un format « .dtsx » qui contiendra
toutes les tâches de gestion de données.
Ce package, une fois ouvert présentera l’environnement de travail SSIS avec une boîte à outils cf. l’image
« Environnement SSIS »

23/10/2019 [Link]@[Link] 19
Environnement SSIS

L’espace de travail est constitué de plusieurs onglets :

• Flux de contrôle : permet la gestion des tâches que le package va exécuter.

• Flux de données : la sélection des données à traiter, les transformations et leurs destinations sont
effectuées à ce niveau pour chacune des tâches qui sont définis dans l’onglet « flux de contrôle ».
• Gestionnaire d’évènement : gestions des erreurs (ex : Erreurs affichées, Envoi de mail suite à une erreur...)
• Explorateur de package : contient les différents paramètres du package (variables, contraintes
d’exécution…)
• Résultat d’exécution : affiche une fois l'exécution lancée, chacune des étapes d’exécution du package (la
durée, les erreurs, l’avancement, le % …)

23/10/2019 [Link]@[Link] 20
Environnement SSIS

La boîte à outils contient différentes catégories d’outils en fonction de l’onglet choisi :

• Source de flux de données : choix de sources de données (Bases de données, Fichiers...)

• Transformation du flux de données : les changements et fonctions pouvant être appliqués aux données
importées.
• Destination du flux de données : (Bases de données, Fichiers...)
• Il contient également des outils effectuant des tâches prédéfinis :
• Des Conteneurs : Utilisé pour contenir ou boucler sur un certain nombre d’évènements
• Autres Outils : tels que l’exécution de tâches (SQL / XML / FTP / Mail / Insertion …)
• Parmi les tâches, on retrouve également des fonctions pratiques similaire aux fonctions SQL pour
permettre un traitement plus facile des données : Sort, Union, Merge, Agrégation …
• Chacun de ces blocs doivent être glissés dans l’onglet « Flux de données » et ensuite être liés à une source
pour être configurés et utilisés.

23/10/2019 [Link]@[Link] 21
 Environnement SSIS
Le flux de données est souvent constitué d’une succession de plusieurs tâches qui peuvent être structurés et
organisés selon l’ordre d’exécution voulu avec la possibilité de gérer la réussite ou l’échec de la tâche.

Exemple de package : Extraction des données d’un fichier plat

Chaque bloc étant lié par une flèche, il représente le chemin entrepris
par le processus en fonction du résultat de la tâche donné, la flèche
verte est suivie lors d’une réussite et la flèche rouge dans le cas d’un
échec ou erreur.
Une fois ce bloc exécuté, il y’a deux possibilités, si la conversion réussit,
les données spécifiées dans le script de conversion sont insérées dans
la base de données.
Dans le cas contraire, on insère les erreurs liées dans une table
spécifique de la base de données.

23/10/2019 [Link]@[Link] 22
SSIS: TP1 Extraction, conversion, fractionnement conditionnel, gestion des erreur et transfert

Montant

Conversion de
[Link] données [Link]

Fractionnement
conditionnel

MontantInf
[Link]
BDD

23/10/2019 [Link]@[Link] 23
SSIS: TP2 Jointure et tri de données

Médecin
[Link] .csv
Trier Manuellement

Trier

Jointure

BDD

23/10/2019 [Link]@[Link] 24
 SSIS: TP3 Unir tout, fusion et multidiffusion

[Link]
[Link] Unir Tout

Multidiffusion
NB: Pour fusionner les données avec
le composant Fusionner, les données
doivent être triées

[Link]
[Link]
BDD

23/10/2019 [Link]@[Link] 25
Entrepôt de données ( Data warehouse )

• Définition d’un entrepôt de données

• Définition
• Caractéristiques
• Datamart
• Modélisation d’un entrepôt de données
• Les approches de modélisations
• La modélisation, qu’est-ce que c’est?
• Table de fait
• Table de dimension
• Modèle en étoile
• Modèle en flocon
• Modèle en constellation

23/10/2019 [Link]@[Link] 26
Entrepôt de données : Définition

• Ralph Kimball

« Un entrepôt de données est constitué peu à peu par les datamarts de l'entreprise
regroupant le niveau d'agrégation et d'historisation au sein d'un même base ».

• Bill Inmon

« Un entrepôt de données est une collection de données orientées sujets, intégrées,

non volatiles, historiées, organisées pour le support d'un processus d'aide à la
décision »

23/10/2019 [Link]@[Link] 27
Entrepôt de données : Définition
• Un Data Warehouse, ou entrepôt de données, est une base de données dédiée au stockage de
l'ensemble des données utilisées dans le cadre de la prise de décision et de l'analyse décisionnelle.

• Le Data Warehouse est exclusivement réservé à cet usage. Il est alimenté en données depuis les bases
de production grâce notamment aux outils d'ETL Extract Transform Load.

• Un data Warehouse est une représentation centralisée de toutes les données de l’entreprise

Caractéristiques:
• Orienté sujet
• Intégré
• Non Volatile
• Historié

23/10/2019 [Link]@[Link] 28
Entrepôt de données : Caractéristiques
Orienté sujet
Les données sont organisées par thème. Les ventes par exemple, seront rapatriées des
différentes bases OLTP de production et regroupées.

Production Produit

Employés Client

Facturation Vendeur

Données BD Data Warehouse

23/10/2019 [Link]@[Link] 29
Entrepôt de données : Caractéristiques
 Intégré
Les données proviennent de sources hétérogènes utilisant chacune un type de format. Elles sont
intégrées avant d'être proposées à utilisation.

23/10/2019 [Link]@[Link] 30
Entrepôt de données : Caractéristiques
 Non volatile
Les données ne disparaissent pas et ne changent pas au fil des traitements, au fil du temps
(en lecture seule).

Modification
Ajout Accès
Chargement Data Warehouse
Production

Supression

 Historisé
Les données non volatiles sont aussi horodatées. On peut ainsi visualiser l'évolution dans le temps
d'une valeur donnée.

23/10/2019 [Link]@[Link] 31
Entrepôt de données : Datamart
Un magasin de données ( ou Datamart en anglais) est un sous-ensemble d'un entrepôt de données. Il
contient des données ciblées et regroupées pour répondre à des besoins d'un secteur particulier de
l'entreprise.
Il est plus facile à comprendre, à manipuler ainsi le temps de réponse est plus réduit par rapport à un
entrepôt de données.

Vente

Finance
Data Warehouse

RH

23/10/2019 [Link]@[Link] 32
Entrepôt de données : Modélisation

• Les approches de modélisations

• La modélisation, qu’est-ce que c’est?
• Table de fait
• Table de dimension
• Modèle en étoile
• Modèle en flocon
• Modèle en constellation

23/10/2019 [Link]@[Link] 33
Entrepôt de données : Modélisation
• Les approches de modélisations

VS

Ralph Kimball Bill Inmon

Processus Bottom-Up Top-Down
Organisation Datamarts Datawarehouse
Shématisation Etoile Flocon

23/10/2019 [Link]@[Link] 34
Entrepôt de données : Modélisation
• Les approches de modélisations
Que chacun construise ce qu’il veut,
on intégrera ce qu’il faudra quand il On fait rien tant que tout n’est pas
faudra! désigné

Ralph Kimball Bill Inmon

23/10/2019 [Link]@[Link] 35
Entrepôt de données : L’approche de Ralph Kimball
Bon Ali, tu fais Et toi Karim, tu
quoi? Les ventes fais quoi? Les achats

Datamart
Achats
Ventes Ventes

23/10/2019 [Link]@[Link] 36
Entrepôt de données : L’approche de Bill Inmon
Allez les gars, on met achats
quoi dans ce machin?

ventes

stock
Datawarehouse

RH

23/10/2019 [Link]@[Link] 37
Entrepôt de données : Table de faits

• Un fait, c’est une ligne dans une table de faits

Table de faits
Il s’est passé quelque chose
Il s’est passé autre chose
Il s’est passé quelque chose d’autre

23/10/2019 [Link]@[Link] 38
Entrepôt de données : Table de dimension

• Les dimensions donnent le contexte du fait

Quand? Où?

Table de faits Dimension Temps Dimension Lieu

Il s’est passé quelque chose Hier Ici
Il s’est passé autre chose Hier Là bas
Il s’est passé quelque chose d’autre Aujourd’hui Ici

23/10/2019 [Link]@[Link] 39
Entrepôt de données : Les mesures

• Les mesures donnent les valeurs numériques du fait

Table de faits
Article Magasin Date Quantité Prix
A 1 Hier 3 10
B 2 Hier 2 15
C 3 Aujourd’hui 4 20

Dimension Dimension Dimension Mesure Mesure

23/10/2019 [Link]@[Link] 40
Entrepôt de données : Table de faits (1)

• C'est la table centrale du modèle dimensionnel, elle contient les valeurs numériques de ce qu'on
désire mesurer.
• Les informations dans une tables de fait sont numériques afin d'appliquer des fonctions multi-lignes
(SUM, COUNT, AVG...)
• Elle doivent être additives, semi-additives ou non additives
Fait ventes
Clé Date
Clés étrangère des tables de Clé Client
dimension Clé Produit
Clé magasin
Prix
Mesures
Quantité

23/10/2019 [Link]@[Link] 41
Entrepôt de données : Table de faits (2)

Type de fait:

• Additif : Additionnable suivant toutes les dimensions

 Chiffre d'affaires, bénéficies etc...

• Semi-additif : Additionnable suivant certaines dimensions

 Solde d'un compte bancaire : Pas de sens d'addition suivant l'axe date.

• Non additif : Non additionnable quelque soit la dimension

 Prix unitaire : L'addition sur n'importe quelle dimension n'a pas de sens

23/10/2019 [Link]@[Link] 42
Entrepôt de données : Table de dimension (1)

• C'est la table qui représente les axes d'analyses, elle contient les détails sur les faits

• Dimension = axe d'analyse

Dim Produit
Clé de substitution Id Produit
Code produit
Libelle
Attributs de la dimension Type
Catégorie
Marque

23/10/2019 [Link]@[Link] 43
Entrepôt de données : Table de dimension (2)

Dimension Temps :

• Commune à toutes les tables de faits

• Très importante pour la traçabilité

Dim Temps
Clé de substitution Id Temps
Jour
Mois
Attributs de la dimension Années
Trimestre
Semestre
...
23/10/2019 [Link]@[Link] 44
Entrepôt de données : Modèle en étoile

Avantages :
• Simple à élaborer
• Facilité de navigation
• Performant
Inconvénients
• Redondance dans les dimensions
• Alimentation complexe

23/10/2019 [Link]@[Link] 45
Entrepôt de données : Modèle en flocon

Avantages :
• Maintenance des tables de
dimension simplifiée
• Réduction de la redondance
Inconvénients
NB: Modèle en flocon = Modèle en étoile + normalisation des dimension
• Navigation couteuse
• Nombreuses jointures
23/10/2019 [Link]@[Link] 46
Entrepôt de données : Modèle en constellation

Série des étoiles ou des flocons qui partagent une à plusieurs dimensions.

23/10/2019 [Link]@[Link] 47
OLAP: Analyse multidimensionnelle OLAP

 Un entrepôt de données contient des données nombreuse, homogènes, exploitables,

multidimensionnelles et consolidées.

 Analyser les données d'un entrepôt de données c'est :

• Résumer
• Observer
• Appliquer des formules statistiques
• Synthétiser des données selon plusieurs dimensions.

Comment exploiter ces données à des fin d'analyse ?

 traditionnellement avec des requêtes OLTP
 Avec des cubes OLAP

23/10/2019 [Link]@[Link] 48
OLAP: Analyse multidimensionnelle OLAP (1)

 Acronyme : On-line Analytical Processing

 Caractéristiques : Représente les données sous la forme d'un cube multidimensionnel (Hypercube) où
chaque côté est une dimension d'analyse et chaque case une métrique.

 Le cube OLAP présente les avantages suivants :

• Obtenir des informations déjà agrégés selon les besoins de l’utilisateur.

• Simplicité et rapidité d’accès.
• Capacité à manipuler les donnés agrégées selon différentes dimensions.
• Temps de réponse très court.
• Pas de jointure.

23/10/2019 [Link]@[Link] 49
OLAP: Analyse multidimensionnelle OLAP (2)

23/10/2019 [Link]@[Link] 50
OLAP: OLAP vs OLTP
Caractéristiques OLTP OLAP
Orientation Transaction Analyse
Modélisation Modélisation Entité-Association Etoile/flocon

Granularité Détail Résumées-Agrégées

Données Actualisation Actualisées, mises à jour Historiées,

recalculées
Taille Faible Importante
Unité de travail Transaction Requête complexe

Traitement Accès Lecture / Ecriture Lecture

Nb tuples accédés Dizaine Millions

Utilisateur Agent opérationnel Analyste/Décideur

Utilisateurs
Nombre d’utilisateurs Milliers Dizaines

23/10/2019 [Link]@[Link] 51
OLAP: Opération OLAP
3 catégories d’opérations élémentaires :
 Restructuration : concerne la représentation, permet un changement de point de vue selon
différentes dimensions : opérations liées à la structure, manipulation et visualisation du cube :
• Rotate / Pivot
• Switch
 Granularité : concerne un changement de niveau de détail : opérations liées au niveau de
granularité des données :
• Roll-up
• Drill-Down
 Ensembliste : concerne l’extraction et l’OLTP classique:
• Slice, dice
• Selection
• Projection
• Jointure (Drill-cross)
23/10/2019 [Link]@[Link] 52
OLAP: Opération OLAP (1)
 Slice

23/10/2019 [Link]@[Link] 53
OLAP: Opération OLAP (2)
 Dice

23/10/2019 [Link]@[Link] 54
OLAP: Opération OLAP (3)
 Rotate

23/10/2019 [Link]@[Link] 55
OLAP: Opération OLAP (3)
 Drill down : C'est la possibilité de zoomer sur une dimension pour avoir une vision plus fine
 Exemple : zoomer sur une année pour voir les 4 trimestres ou de passer du pays aux différentes
régions

 Roll up : C'est l'opération inverse qui permet d'aggréger les composantes de l'un des axes
 Exemple : regrouper les mois en trimestre ou les régions en pays

23/10/2019 [Link]@[Link] 56
OLAP: Opération OLAP (3)
 Rotate

23/10/2019 [Link]@[Link] 57
OLAP: Opération OLAP (3)
Système ROLAP (Relational OLAP):
Base relationnelle, utilisant un SGBD relationnel classique et organisée pour réagir comme une base OLAP
Lente et peu performante

Système MOLAP (Multidimensionnal OLAP):

Utilisant un SGBD multidimensionnel (MOLAP), elle est l'application physique du concept OLAP
(réellement d'une structure multimensionnelle)
Très rapide et performante

Système HOLAP (Hybrid OLAP):

Utilisant un SGBD Relationnel avec des opérateurs et algorithmes adéquats (StarJoin, Index Bitmap,
GROUP BY CUBE,...)
C'est un compromis : Une base MOLAP pour les données souvent consultées (La minorité selon Pareto),
une base ROLAP pour les autres (La majorité)

23/10/2019 [Link]@[Link] 58
Le language MultiDimensional eXpression MDX

23/10/2019 [Link]@[Link] 59
MDX: Attributs dimensionnels

23/10/2019 [Link]@[Link] 60
MDX: Hiérarchies ( utilisateur )

23/10/2019 [Link]@[Link] 61
MDX: Mesures

23/10/2019 [Link]@[Link] 62
MDX: Membres

23/10/2019 [Link]@[Link] 63
MDX: Tuples (1)

23/10/2019 [Link]@[Link] 64
MDX: Tuples (2)

23/10/2019 [Link]@[Link] 65
MDX: Tuples (3)

23/10/2019 [Link]@[Link] 66
MDX: Requêtes OLAP (1)

23/10/2019 [Link]@[Link] 67
MDX: Requêtes OLAP (2)

23/10/2019 [Link]@[Link] 68
MDX: Requêtes OLAP (3)

23/10/2019 [Link]@[Link] 69
MDX: Requêtes OLAP (4)

23/10/2019 [Link]@[Link] 70
MDX: Requêtes OLAP (5)

23/10/2019 [Link]@[Link] 71
MDX: Requêtes OLAP (6)

23/10/2019 [Link]@[Link] 72
MDX: Requêtes OLAP (7)

23/10/2019 [Link]@[Link] 73