Présentation de Power BI

Power BI est un outil de visualisation de données, c’est-à-dire qu’il permet de raconter une histoire avec les données. Cet outil d’aide à la décision puissant permet d’effectuer des mesures et de créer des indicateurs clés.

Ce chapitre n’a pas vocation à traiter de Power BI dans son ensemble. Il existe des ouvrages dédiés à ce logiciel, dont ceux d’André Meyer-Roussilhon. Il s’agit ici de démontrer que les résultats des calculs effectués sont identiques aux résultats obtenus avec un cube SSAS.

La version Desktop de Power BI est utilisée. Elle n’est pas collaborative.

Dans le chapitre précédent, Power BI a été utilisé pour créer des visuels à partir de la source Base de données SQL Server Analysis Services.

Dans ce chapitre, la source de données est la base AdventureWorks. Il s’agit de la base utilisée pour créer le projet SSAS dans Visual Studio.

Les mesures vont être créées directement dans Power BI, sans passer par un projet SSAS.

L’étude de cas décrite dans le chapitre Projet décisionnel, à la section Études de cas, est reprise et traitée dans Power BI Desktop.

La première étape consiste à se connecter...

Création d’un jeu de données dans Power BI

1. Connexion à la source de données

Pour débuter, il est nécessaire de se connecter aux données afin de créer un rapport Power BI. Les données, dans notre exemple, vont être rapatriées dans le rapport en local. L’accès à la source se fait en lecture. Power BI ne modifie pas la source de données et n’utilise pas les ressources nécessaires à cette source de données.

Les données de l’étude de cas sont collectées dans la base de données SQL Server AdventureWorks. Elles vont être récupérées dans Power BI Desktop.

L’installation de Power BI Desktop a été traitée dans le chapitre SSAS Installation, à la section Installation de Power BI Desktop.

Ouvrez Power BI et cliquez sur Obtenir les données.

Sélectionnez Base de données SQL Server, puis cliquez sur le bouton Se connecter.

Saisissez le nom de l’instance SQL Server dans la zone de saisie Serveur (ici MONSERVEUR\MONINSTANCE) et le nom de la base de données dans la zone de saisie Base de données (ici, AdventureWorksDW2019).

Laissez activé le bouton d’option Importer sous Mode de connectivité des données, puis cliquez sur OK.

Le mode DirectQuery est utilisé pour des quantités massives de données (pétaoctets).

L’écran suivant permet de s’identifier.

Dans le cas présent, les informations d’identification courantes sont utilisées, donc l’option présentée par défaut reste inchangée.

Cliquez sur Se connecter, puis sur OK, pour utiliser les données non chiffrées.

Cochez les tables de dimension et de fait suivantes, nécessaires à l’étude de cas, pour les sélectionner :

DimDate
DimProductSubCategory
DimReseller
DimSalesTerritory
FactResellerSales

Puis cliquez sur le bouton Charger.

La propriété Inclure des colonnes de relation étant cochée par défaut au moment de la saisie des informations de connexion, il n’est pas utile d’ajouter les tables DimProduct et DimProductCategory. Les colonnes de ces tables seront intégrées...

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. section Langage DAX).

L’étude de cas prévoit les mesures suivantes :

Marge brute (montant hors taxes - coût de production).
Taux de marge ((marge brute / coût de production) * 100).
Taux de marque ((marge brute / montant hors taxes) * 100).
Coefficient de marge (montant hors taxes / coût de production).
Marge nette (marge brute - coût de transport et taxe).
Nombre de commandes.

Créez la première mesure qui calcule la marge brute :

À partir de la vue Rapport, effectuez un clic droit sur la table Ventes et choisissez Nouvelle mesure rapide dans le menu.

Sélectionnez le calcul Soustraction de la catégorie Opérations mathématiques.

Puis à partir de la liste des champs, faites glisser la colonne Montant ventes HT dans la zone Valeur de base et le champ Coûts produit dans la zone Valeur à...

Langage DAX

1. Présentation du langage DAX

Le langage DAX (Data Analysis Expressions) est un ensemble d’opérateurs, de constantes et de fonctions qui permet d’effectuer des calculs via une formule ou une expression. Ce langage puissant d’analyse des données est utilisé par Excel via Power Pivot, par Power BI, SSAS mode tabulaire, SQL Server Manager Studio et Azure Analysis Services. Ce langage, comme Power BI, évolue régulièrement.

Il est possible de créer des paramètres dans les formules DAX. Ces paramètres reçoivent des valeurs uniques, des noms de table ou de colonne.

Les variables sont utilisées dans les formules DAX. Le mot-clé VAR permet de déclarer une variable. RETURN exécute le calcul des variables et stocke les résultats en mémoire durant le déclenchement de la formule.

Exemple de déclaration de variable

VAR mavariable1 = SUM('Ventes'[Montant ventes HT]) 
VAR mavariable2 = COUNTROWS('Ventes') 
RETURN

Le langage DAX accepte les commentaires. Trois syntaxes sont possibles :

--ma ligne en commentaire 
//ma ligne en commentaire 
/* mon paragraphe en commentaire */

En DAX, un calcul s’effectue danse deux types de contexte : le contexte de ligne ou le contexte de filtre. Dans le contexte de ligne, le calcul s’effectue pour chaque ligne. Le contexte de filtre s’applique implicitement selon la construction de la page ou du rapport, en tenant compte des segments (visuels qui permettent de filtrer un ou plusieurs objets), d’autres visuels, de filtres, etc.

L’éditeur de formule DAX est doté de l’IntelliSense, qui suggère les références, les fonctions, les formules en fonction de ce que le système autorise à saisir.

Il est possible d’utiliser le site https://www.daxformatter.com/ pour mettre en forme le code afin de le rendre plus lisible.

2. Mesures...

Restitution dans Power BI

Pour présenter et vérifier les données et les calculs, cinq pages vont être créées :

Calendrier, pour vérifier les analyses chronologiques.
SansCalculs, pour examiner les formats des nombres.
AvecCalculs, pour inspecter les calculs.
KPI, pour présenter les indicateurs de performance clés.
YTD, pour observer certaines mesures.

Elles sont semblables aux pages créées au chapitre SSAS projet complément, à la section Restitution dans Power BI. Cela permet de démontrer que les résultats sont quasi identiques, quelle que soit la méthode choisie.

La table Indicateurs comporte plusieurs mesures. Avant de créer des visuels et d’améliorer la visibilité des mesures, un dossier peut être créé, regroupant tous les calculs liés à la marge par exemple.

Positionnez-vous sur la vue Modèle et déployez la table Indicateurs dans le volet Champs. Sélectionnez la mesure Coeff marge et saisissez Marge dans la zone de saisie Afficher le dossier du volet Propriétés. Validez en appuyant sur la touche [Entrée]. Le dossier apparaît dans la table Indicateurs, avec le calcul Coeff marge en dessous.

Sélectionnez les indicateurs suivants :

KPI Profit Etat
KPI Profit Objectif
KPI Profit Tendance
Marge brute
Taux de marge
Taux de marque
Taux marge nette

Puis renouvelez l’opération précédente pour qu’ils soient déplacés dans le dossier Marge.

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

Pour créer le premier visuel, qui va être un simple tableau utilisant la hiérarchie Calendrier de la date de commande, à partir de la vue Rapport, cliquez dans l’ordre sur les attributs Année et Semestre de la hiérarchie Calendrier de la table Temps, sur la mesure Qté commandée de la table Ventes, sur la mesure Nb commandes...

Exercices

Se connecter à la base

Connectez-vous à la base DistrisysDW et sélectionnez les tables suivantes.

DimClient
DimGeographie
DimProduit
DimSite
DimTemps
FactFacture

Nettoyer les données

Nettoyez les données, comme suit :

DimClient : supprimez les tables liées FactBilanCommandeClient, FactFacture et FactFactureEntete, puis déployez la table DimGeographie pour afficher les colonnes Pays, Departement, CodePostal et Ville.
DimProduit : gardez les colonnes Produit_PK, ProduitCode, Produit, SousFamille et Famille.
DimSite : gardez les colonnes Site_PK, Site et DimGeographie, puis déployez DimGeographie pour cocher Departement, Pays, et Ville.
DimTemps : gardez les colonnes Temps_PK, Date, AnneeNom, SemestreNom, TrimestreNom et MoisNom.
FactFacture : supprimez les tables associées et la colonne AuditFluxAjout_FK.
Supprimez la table DimGeographie.
La première facture date du 1^er janvier 2021. Supprimez les lignes de la requête DimTemps avant cette date.

Spécifier la table de dates

Spécifiez la table de dates.

Modifier une agrégation

Modifiez l’agrégation du champ NumFacture pour calculer le nombre de lignes de facture.

Ajouter une colonne calculée

Ajoutez la colonne Année qui correspond à l’année sur 4 digits.

Renommer des tables et des colonnes

Renommez la table DimClient...

Corrigés

Se connecter à la base

Ouvrez Power BI Desktop, puis cliquez sur Obtenir les données à gauche de l’écran d’accueil. Sélectionnez Base de données SQL Server, puis cliquez sur le bouton jaune Se connecter en bas de la fenêtre Obtenir les données.

Saisissez le nom de l’instance SQL Server et celui de la base de données (ici, DistrisysDW). Laissez activé le bouton radio Importer sous Mode de connectivité des données, puis cliquez sur OK.

Cochez les tables suivantes :

DimClient
DimGeographie
DimProduit
DimSite
DimTemps
FactFacture

Puis cliquez sur le bouton Charger.

Enregistrez le rapport et nommez-le ch6-VentesFacturées par exemple.

Nettoyer les données

Cliquez sur l’icône Transformer les données de la barre d’outils.

Pour DimClient :

Sélectionnez les colonnes FactBilanCommandeClient, FactFacture et FactFactureEntete en maintenant enfoncée la touche [Ctrl], puis exécutez un clic droit et choisissez Supprimer les colonnes dans le menu contextuel.

Cliquez sur l’icône qui représente une double flèche à gauche du libellé DimGeographie, puis cochez les colonnes Pays, Departement, CodePostal et Ville. Décochez la case Utiliser le nom de la colonne d’origine comme préfixe.

Pour DimProduit :

Naviguez vers la table DimProduit et sélectionnez les colonnes Produit_PK, ProduitCode, Produit, SousFamille et Famille en maintenant enfoncée la touche [Ctrl], puis effectuez un clic droit et choisissez Supprimer les autres colonnes dans le menu contextuel.