Blog ENI : Toute la veille numérique !
Accès illimité 24h/24 à tous nos livres & vidéos ! 
Découvrez la Bibliothèque Numérique ENI. Cliquez ici
💥 Du 22 au 24 novembre : Accès 100% GRATUIT
à la Bibliothèque Numérique ENI. Je m'inscris !
  1. Livres et vidéos
  2. SQL Server Analysis Services (SSAS)
  3. Azure Analysis Services
Extrait - SQL Server Analysis Services (SSAS) Calculs analytiques et restitution de données dans Power BI et Azure
Extraits du livre
SQL Server Analysis Services (SSAS) Calculs analytiques et restitution de données dans Power BI et Azure Revenir à la page d'achat du livre

Azure Analysis Services

Création des mesures

Une mesure, comme un membre calculé de SSAS, correspond à un calcul personnalisé défini à l’aide d’une combinaison de données du jeu de données, d’opérateurs arithmétiques, de nombres et de fonctions.

Les résultats sont calculés lors de l’actualisation des visuels.

Une ou plusieurs mesures peuvent être utilisées dans une autre mesure.

Le langage utilisé pour créer des mesures est DAX (cf. chapitre Power BI Desktop, section Langage DAX).

Les mesures sont gérées à partir de la grille de mesures.

images/07ETI01.png

 Si la grille de mesures n’est pas présente en dessous de la table comme sur la capture précédente, cliquez sur l’icône Afficher la grille de mesures dans la barre d’outils Analysis Services.

images/07ETI02.png

 Si la barre d’outils Analysis Services n’est pas affichée, cliquez sur le menu Affichage - Barres d’outils - Analysis Services.

images/07ETI03.png

La direction souhaite connaître la marge brute, le taux de marge, le taux de marque, le coefficient de marge et le taux de la marge nette. Elle souhaite aussi comparer le chiffre d’affaires mensuel avec l’année précédente et le chiffre d’affaires cumulé de l’année en cours par rapport aux années précédentes....

Indicateurs de performance clés (KPI)

Un indicateur de performance clé est un calcul dont la valeur est présentée sous forme d’icône. En mode tabulaire, cet indicateur est créé à partir d’une mesure.

Pour mesurer l’indicateur de performance clé sur les objectifs du taux de marge nette et la tendance par rapport à l’année précédente, la création d’une mesure KPI Profit Objectif qui évalue l’objectif peut être une première étape. L’objectif du taux de marge nette est de 0.30, sauf pour le Canada, l’Allemagne et la France, qui ont respectivement un objectif de 0.34, de 0.36 et de 0.32.

La formule est la suivante :

KPI Profit Objectif:= SWITCH(SELECTEDVALUE('Régions'[Régions]), 
"Canada",0.34, "Germany", 0.36, "France", 0.32, 0.30)

Une seconde mesure nommée KPI Profit, qui correspond au taux de marge nette divisé par l’objectif, est nécessaire. Le résultat doit se situer au-dessus de 1.5 et ne doit pas être en dessous de 1.3.

La formule est la suivante :

KPI Profit:= [Taux marge nette]/[KPI Profit Objectif]

 Pour ajouter un indicateur de performance clé, effectuez un clic droit sur la mesure et choisissez Créer un indicateur de performance clé… dans le menu contextuel....

Dossier d’affichage

Lorsqu’une table comprend beaucoup de colonnes, il peut être souhaitable d’en regrouper certaines dans un dossier pour gagner en visibilité et les retrouver facilement.

Par exemple, tous les champs de la table de fait Ventes liés à la marge peuvent être disposés dans un dossier Marge, en affectant le libellé du dossier à la propriété Dossier d’affichage.

 Sélectionnez les calculs suivants en maintenant la touche [Ctrl] appuyée :

  • Marge Brute

  • Taux de marge

  • Taux de marque

  • Coeff marge

  • KPI Profit Objectif

  • Taux de marge nette

 Puis saisissez Marge dans la zone de saisie de la propriété Dossier d’affichage dans la fenêtre Propriétés.

images/07ETI15.png

 Répétez l’opération pour les indicateurs KPI Profit et KPI Profit Tendance. 

Le dossier sera visible à partir de l’outil de restitution.

Perspectives

Les perspectives représentent des sous-modèles. Par exemple, on souhaite créer un modèle pour le service logistique avec les mesures nécessaires à ce service. Cela permet d’éliminer les calculs qui n’ont pas de sens pour ce sous-ensemble. Elles sont décorrélées de la gestion des droits. Les KPI n’apparaissent pas dans la liste des colonnes pour gérer les perspectives.

 Pour créer une nouvelle perspective, exécutez la commande Créer et gérer :

  • Soit en cliquant sur le menu Extensions - Modèle - Perspectives - Créer et gérer.

images/07ETI16.png

  • Soit en exécutant un clic droit sur le dossier Perspectives de l’Explorateur de modèles tabulaires et en choisissant Créer et gérer dans le menu contextuel.

images/07ETI17.png

 Cliquez sur le bouton Nouvelle perspective et saisissez le libellé Logistique, par exemple.

Vous devez cocher les tables complètes ou les champs qui seront affichés dans les outils de restitution.

 Cochez toutes les tables, puis décochez les champs suivants de la table Produits :

  • Products

  • Produits

images/07ETI18.png

 Décochez la table Ventes, puis cochez les colonnes suivantes :

  • Coûts produits

  • Transports

  • N° ligne cde

  • Qté commandée

  • KPI Profit Objectif

  • Nb ligne cde

images/07ETI19.png

 Pour vérifier la perspective, sélectionnez la perspective...

Partitions

À chaque modèle correspond une partition physique qui contient les données. À chaque fois que les données d’un modèle sont mises à jour, la partition est traitée. Lorsque cette partition devient trop volumineuse et supérieure à la mémoire qui l’accueille, il est possible de la diviser.

Par exemple, pour les données de vente, il est logique de diviser la partition par année. L’intérêt est aussi de pouvoir paramétrer différemment la fréquence de mise à jour de chaque partition. Quatre partitions vont être créées pour les années 2018 à 2021.

 Pour créer une partition, activez le menu Extensions - Table ou effectuez un clic droit sur la table de l’onglet Tables de l’Explorateur de modèles tabulaires et choisissez Partitions… dans le menu contextuel.

images/07ETI22.png

Le gestionnaire de partitions s’affiche avec la définition de la partition par défaut. 

images/07ETI23.png

 Cliquez sur le bouton Copier pour créer une nouvelle partition. Saisissez Ventes2018 comme Nom de la partition.

images/07ETI24.png

 Cliquez sur le bouton Conception… afin de pouvoir modifier la requête à l’aide de l’éditeur Power Query.

Pour traiter les données de l’année 2018, un filtre doit être ajouté sur la colonne OrderDateKey...

Gestion des accès

Une fois votre modèle prêt à être utilisé, il est nécessaire de définir les droits d’accès. Au départ, seuls les administrateurs de l’instance SSAS peuvent s’y connecter. Il est possible de restreindre l’accès à la ligne. Par exemple, le service de contrôle de gestion basé en Europe doit avoir les accès aux données européennes uniquement. Le traitement du cube est réservé au service informatique.

Les accès sont gérés dans le dossier Rôles du projet. Un rôle regroupe les entités de sécurité (utilisateurs ou groupes d’utilisateurs), les "sécurisables" (tables et colonnes) et les autorisations (lecture, traitement…).

 

Table de fait Ventes

Tables de dimension

Direction commerciale

Lecture

Lecture

Contrôle de gestion zone Europe

Lecture des données de la France, de l’Allemagne et de la Grande-Bretagne uniquement, de toutes les mesures, sauf le coût standard du produit

Lecture des données de la France, de l’Allemagne et de la Grande-Bretagne uniquement

Service logistique

Lecture des quantités commandées, du nombre de ventes, des coûts standard d’un produit et des coûts de transport uniquement

Lecture

 À partir de l’Explorateur de modèles...

Déploiement et traitement

1. Définition

Traiter le modèle permet d’effectuer la mise à jour des données et les calculs. Déployer le modèle envoie les métadonnées et le résultat du traitement dans une instance SSAS.

Un même modèle peut être déployé sur plusieurs instances. Les instances peuvent être internes, dans un service Azure ou dans un espace Power BI Premium. À un déploiement correspond une instance. Ces instances peuvent être gérées dans le gestionnaire de configurations.

Dans le cadre de l’étude de cas, le modèle va être déployé dans deux instances, la première en interne et la seconde dans un service Azure.

2. Déploiement interne

Avant de déployer un modèle, il est nécessaire de déterminer, dans les propriétés du projet, l’instance où le modèle va être envoyé.

 Cliquez sur le menu Projet et choisissez Propriétés de MonProjetTabulaire (MonProjetTabulaire correspond au libellé du projet).

 Indiquez le nom de l’instance dans la zone de saisie Serveur (MONSERVEUR\SSASTAB, par exemple) et le nom de la base de données dans la zone de saisie Base de données (il s’agit du libellé de la base de données OLAP déployée dans l’instance SSAS, ici MonProjetTabulaire.

images/07ETI48.png

 Puis cliquez sur le bouton OK pour valider la configuration.

Il est possible de générer le projet avant de déployer le modèle, pour anticiper les erreurs avant le déploiement.

 Cliquez sur le menu Générer, puis choisissez Générer la solution (raccourci : [Ctrl][Shift] B) ou Générer MonProjetTabulaire (raccourci : [Ctrl] B). Dans le cadre de l’étude de cas, les deux possibilités sont identiques puisqu’il n’y a qu’un seul projet dans la solution.

images/07ETI49.png

Voici le message qui atteste que la solution est correctement générée :

images/07ETI50.png

 Cliquez sur le menu Générer - Déployer la solution. La fenêtre de suivi du déploiement s’affiche jusqu’à la fin de l’opération.

images/07ETI51.png

 Cliquez sur le bouton Fermer pour revenir au projet....

Restitution dans Power BI

1. Restitution à partir du modèle tabulaire interne

Une fois le modèle réalisé, traité et déployé, il peut être présenté via Excel ou d’autres applications, comme l’outil de visualisation de données Power BI.

L’objectif de ce chapitre n’est pas de proposer un cours sur Power BI, mais de montrer comment utiliser les données d’un modèle tabulaire via cet outil. Il est possible de choisir d’autres visuels pour raconter une histoire avec les données, mais cet ouvrage s’attache à comparer les différentes manières d’effectuer des analyses multidimensionnelles et à inspecter les calculs et leurs résultats.

Une fois le rapport connecté au modèle tabulaire, cinq pages vont être créées pour présenter l’ensemble des données du projet. Cela va permettre de vérifier certains comportements du modèle qui ne sont pas pris en compte dans la grille du projet SSAS de Visual Studio, comme l’interaction avec les dimensions.

Les cinq pages sont les suivantes :

  • Calendrier, pour vérifier les analyses chronologiques.

  • SansCalculs, pour examiner les formats des nombres.

  • AvecCalculs, pour inspecter les calculs.

  • KPI, pour présenter l’indicateur de performance clé.

  • YTD, pour observer certaines mesures.

Si les résultats ne correspondent pas à ce qui devrait être, il est nécessaire d’effectuer les corrections dans le projet SSAS, de déployer le modèle, puis de revenir sur le rapport et d’actualiser les données en cliquant sur le bouton Actualiser dans la barre d’outils de l’onglet Accueil, dans la section Requêtes. 

images/07ETI84.png

Voici la première page du rapport qui va être créée, avec trois visuels utilisant les hiérarchies chronologiques.

images/07ETI85.png

 Ouvrez l’application Power BI Desktop et cliquez sur Obtenir les données. Sélectionnez Base de données SQL Server Analysis Services et cliquez sur Se connecter.

 Renseignez la zone Serveur en indiquant le nom de l’instance Analysis Services (ici, MONSERVEUR\SSASTAB), et renseignez la zone Base de données (ici, MonProjetTabulaire - même nom que le projet)....

Exercices

Les exercices suivants sont à réaliser à partir du projet SSAS nommé SSASTABDistrisys créé au chapitre Mode tabulaire (cf. section Corrigés).

Créer des mesures

Créez les mesures calculées suivantes avec les règles de calcul correspondantes et le format d’affichage approprié :

  • % marge = Marge / CA, en pourcentage

  • % remise = Remise / Prix catalogue, en pourcentage

  • Cout total = Cout Direct Matière + Cout Direct Main Oeuvre + Cout Indirect, monétaire

  • Prix de vente moyen = CA / Quantité, monétaire

  • Nb lignes facture, entier avec séparateur de milliers

Cette dernière mesure compte le nombre de lignes de facturation.

Créer un indicateur de performance clé

Créez un indicateur de performance clé nommé KPI Marge. L’objectif du pourcentage de marge est de 0.3. Une marge inférieure à 0.27 est insatisfaisante. Mesurez la tendance par rapport au mois précédent.

Créer un dossier

Créez un dossier Coûts qui regroupe les colonnes CoutDirectMainOeuvre, CoutDirectMatiere, CoutIndirect et Cout total.

Créer une perspective

Créez une perspective nommée Suivi ventes par site, avec les dimensions site et temps, la hiérarchie Produits et la table facture sans les coûts.

Créer des partitions

Créez une partition...

Corrigés

Créer des mesures

% marge = Marge / CA, en pourcentage

 Positionnez-vous sur la table Facture et affichez la grille de mesures en cliquant sur l’icône Afficher la grille de mesures dans la barre d’outils.

 Cliquez sur une des cases de la grille et saisissez la formule suivante :

% marge:=SUM(Facture[Marge])/SUM(Facture[CA])

 Choisissez le format Pourcentage à partir de la fenêtre Propriétés de la mesure, en bas à droite de l’écran.

images/07ETI109.png

% remise = Remise / Prix catalogue, en pourcentage

 Cliquez sur une autre case et saisissez la formule suivante :

% Remise:=SUM(Facture[Remise])/SUM(Facture[PrixCatalogue])

 Puis choisissez le format Pourcentage, comme pour la mesure précédente.

images/07ETI110.png

Cout total = Cout Direct Matière + Cout Direct Main Oeuvre + Cout Indirect, monétaire

 Cliquez sur la case suivante et saisissez la formule ci-après :

Cout total:=SUM(Facture[CoutDirectMatiere])+
SUM(Facture[CoutDirectMainOeuvre])+SUM(Facture[CoutIndirect])

 Sélectionnez la valeur Devise pour la propriété Format de la mesure.

Prix de vente moyen = CA / Quantité, monétaire

 Saisissez le code suivant dans une autre cellule de la grille :

Prix de vente moyen:= SUM(Facture[CA])/SUM(Facture[Quantite])

 Sélectionnez la valeur Devise pour la propriété Format de la mesure.

 Cliquez sur la case suivante et saisissez la formule ci-après :

Nb lignes facture:=COUNTA(Facture[NumFacture])

 Sélectionnez la valeur Nombre entier pour la propriété Format de la mesure, puis la valeur True pour la propriété Afficher le séparateur des milliers.

Créer un indicateur de performance clé

 Effectuez un clic droit sur la mesure % marge et choisissez Créer un indicateur de performance clé… dans le menu contextuel.

 Réglez l’option Valeur absolue à 1 et saisissez la valeur 0,27 dans le premier taquet et la valeur 0,3 dans le second taquet de la glissière de paramétrage.

images/07ETI111.png

Créer un dossier

 Sélectionnez les trois colonnes CoutDirectMainOeuvre, CoutDirectMatiere, CoutIndirect dans la table Facture, puis saisissez Coûts dans la zone de saisie de la propriété Dossier...