Utiliser ou consommer le datamart
Vue d’ensemble
Il existe une dizaine d’outils permettant de consommer la donnée des datamarts :
-
les requêtes Power Query Online à l’intérieur des datamarts,
-
les requêtes SQL à l’intérieur des datamarts,
-
les insights (ou synthèse d’intelligence artificielle),
-
les explorations de données Power BI (en ligne),
-
les rapports Power BI (en ligne),
-
les applications Power BI (en ligne),
-
les datamarts Power BI,
-
les rapports paginés (en ligne),
-
les rapports Power BI Desktop,
-
les tableaux croisés Excel,
-
les présentations PowerPoint,
-
SSMS,
-
Azure Data Studio,
-
SQLBI Dax Studio.
Nous allons passer en revue ces différents médias dans ce chapitre sous l’angle du consommateur final, mais aussi et surtout de l’ingénieur Data qui devra les produire et nous allons aussi expliquer les modalités pour les rendre disponibles.
Utiliser les requêtes visuelles
1. Mode d’emploi des requêtes visuelles
Les requêtes visuelles sont un moyen rapide de mettre à disposition des concepteurs de datamart une visualisation des données et un export vers Excel des données brutes.
On peut également supprimer des colonnes ou des lignes dans la requête visuelle et faire des jointures ou fusions, sans toutefois pouvoir faire de transformations structurantes (comme des typages de colonnes par exemple).
Les requêtes visuelles ne sont pas visibles des concepteurs de rapports métier.
Le prérequis pour que cela fonctionne est de se loguer au nom d’une personne qui a au moins le droit d’écrire sur le datamart.
a. Récupérer la source de données exemple au préalable
Au chapitre Power Query Online à la section Importer une source JSON, nous avons créé une table EvolutionPrixGaz.
Pour créer des requêtes visuelles Power Query ou en voir le résultat dans Power BI Service, il faut modifier le datamart.
Le mode opératoire de création d’une requête visuelle est :
Allez sur la page du datamart. Pour cela :
Allez sur le portail Power BI service. Dans le volet rapide de gauche, cliquez sur Parcourir. Filtrez par le type Datamart. Cliquez sur le nom du datamart 6.Finance.
Si le datamart n’est pas présent dans Parcourir, cela provient du fait qu’il n’est ni récent, ni favori, ni partagé, c’est donc un datamart que vous n’avez pas ouvert depuis longtemps. Il faut alors aller sur le hub de donnée et en profiter pour le placer en favori. Ceci est expliqué dans la section Requêter en Power Query Excel de ce chapitre.
Cliquez sur le bouton de vue rapide Requête dans la zone basse. Ensuite, allez en haut sur le bouton Nouvelle requête visuelle, comme indiqué dans l’image qui suit.
b. Aperçu de l’interface des requêtes visuelles
Créez une requête visuelle : glissez-déplacez la table EvolutionPrixGaz dans la grande zone...
Créer une requête SQL dans le datamart
1. Mode opératoire
Si vous étiez déjà sur une requête visuelle, voici les instructions pour ajouter votre propre requête SQL au datamart :
Ruban Accueil - Nouvelle requête SQL ou cliquez sur la vue requête et par le ruban Accueil - Nouvelle requête SQL.
Un bouton Run apparaît, avec une zone de code numérotée.
2. Partager une requête
Lorsque vous créez des requêtes, qu’elles soient SQL ou visuelles, elles vous sont propres. Pour partager une requête avec les autres utilisateurs, faites un glisser-déposer de la requête vers la zone Requêtes Partagées.
3. Liste des raccourcis de l’éditeur SQL
Voici la liste des raccourcis-clavier réellement opérationnels et leur signification dans l’ordre alphabétique de l’éditeur de requête SQL du datamart :
Raccourci |
rôle du raccourci |
[Ctrl][Flèche à droite] |
Aller un mot à droite |
[Ctrl][Flèche à gauche] |
Aller un mot à gauche |
[Ctrl] Z |
Annuler |
[Flèche en bas] |
Déplacer le curseur vers le bas |
[Flèche en haut] |
Déplacer le curseur vers le haut |
[Shift][Tab] |
Diminuer l’indentation |
[Tab] |
Indenter |
[Ctrl][Entrée], [Shift][Entrée] |
Lancer le script SQL |
[Ctrl] Q |
Nouvelle requête SQL |
[Ctrl] F... |
Exemples de requêtes SQL utiles
1. Mesurer la taille de la base du datamart
On sait que la limite de taille est de 100 Go. Voici une requête très utile pour mesurer la taille d’un datamart (réf. SQL 5.1) :
Sp_spaceused
Voici le résultat qui donne la taille de base et l’espace temporaire non alloué :
2. Lister les tables et leurs dates
Voici un petit script (réf. SQL 1.2) qui récupère le nom de la base du datamart et la liste des tables (ces tables existent nativement et sont stockées dans Azure SQL sous la notion de vue) :
-- Obtenir le nom de la base du datamart et la liste
de ses tables et vues
SELECT table_catalog [database], table_schema [schema],
table_name [name], table_type [type]
FROM INFORMATION_SCHEMA.TABLES
order by [schema]
Cliquez sur Run.
Voici le résultat qui apparaît en dessous. Le schéma model est celui qui contient les tables de datamart. La table database_firewall_rules est une table système interne.
Il est également possible d’aller dans Azure Data Studio et de se brancher sur le datamart pour voir le même résultat.
Voici un second script qui permet de tracer les dates d’évolution des tables du datamart (code réf. SQL 5.5).
-- Production d'un tableau des dates des tables et vues du datamart
SELECT name as nom,object_id as id, type_desc as type,
create_date as Date_de_creation,modify_date as date_de_maj
FROM sys.views
Voici le résultat dans le datamart, un clic droit permet de copier l’aperçu dans le presse-papiers de Windows :
Le bouton Télécharger le fichier Excel situé ci-dessous dans le volet d’aperçu des données permet de créer un fichier Excel dynamique, en français, avec une requête Power Query directement pontée sur le serveur Azure SQL du datamart, sous réserve que le port ne soit pas fermé.
Le bouton Explorer ces données est une nouveauté 2024. Il permet de générer une analyse de type matrice (tableau croisé) ou un visuel simple (histogramme, courbe, etc.) et de l’enregistrer sans devoir passer par Power BI pour élaborer un rapport.
L’exploration des données est destinée...
Modalité d’authentification
Le point de terminaison du datamart est accessible avec Microsoft Entra ID avec support MFA, il n’est pas utilisable en mode SQL, ni mixte. Microsoft Entra ID est le nouveau nom d’Azure Active Directory.
Ce mode sécurisé utilise un dispositif multifacteur d’authentification (ou MFA) d’Azure. Il s’appuie sur plusieurs moyens pour vérifier vos droits d’accès : votre compte professionnel, l’application Authenticator de votre smartphone et enfin votre navigateur, soit trois moyens de vous authentifier. À défaut, vous pouvez utiliser votre courriel. Microsoft Entra ID est le nouveau nom d’Azure Active Directory. Il se positionne comme ouvert à tout appareil, pas seulement un PC sous Windows.
Pour s’y connecter, il faut donc entrer son compte professionnel, c’est-à-dire son e-mail professionnel, son mot de passe et vérifier avec son application d’authentification (Authenticator sur Iphone ou Android) en saisissant un code dans son smartphone.
Consommer depuis Excel
1. Requêter en Power Query Excel
Nous avons vu dans la section Exemples de requêtes SQL utiles - Lister les tables et leurs dates de ce chapitre qu’on peut exporter un fichier Excel dynamique. Nous pouvons également travailler depuis Excel 365 client lourd dit « client de bureau ».
Pour consommer le datamart en tant que serveur SQL pour des tables à plat, lancez Excel.
Cliquez sur le ruban Données, puis Obtenir des données.
Cliquez sur À partir d’Azure et sur À partir d’une base de données Azure SQL.
Une fenêtre apparaît :
Serveur : indiquez ici la chaîne de connexion au datamart, qui ressemble à chainedecaractèresalphadecimaux.datamart.pbidedicated.windows.net et que l’on récupère depuis le Datamart.
Pour cela, allez sur le portail Power BI puis sur le volet rapide de gauche Hub de données. Choisissez le domaine pour gagner du temps : Finances ici.
Filtrez au besoin sur le type Datamart.
Cliquez sur le nom du datamart à consommer. La page d’accueil du datamart apparaît : ici 6.Finance.
La chaîne de connexion est visible dans Plus d’options - Copier la chaîne de connexion. C’est ce qu’il vous faut en guise de nom de serveur.
-
Base de données : pour remplir ce champ, récupérez...
Consommer en ligne dans Power BI Service
Il existe six supports pour consommer la donnée des datamarts :
-
les applications,
-
les insights (ou synthèse d’intelligence artificielle),
-
les rapports Power BI en ligne,
-
les rapports Power BI Desktop,
-
les rapports paginés (en ligne),
-
et plus récemment, les explorations de données en ligne.
Au préalable, il est conseillé de promouvoir le jeu de données (modèle sémantique) dans ses paramètres pour le faire connaître comme fiable, et Activer la découverte, pour faciliter la prise de connaissance des utilisateurs.
Pour accéder à cet écran, il faut retourner dans le Hub de données et cliquer sur le bouton Filtrer pour afficher cette fois le type Modèle sémantique uniquement. Ensuite, il faut afficher le bouton Plus d’options et choisir Paramètres dans la liste déroulante.
Pour l’instant, le propriétaire utilisait et consommait le datamart. Mais pour information, la consommation de la chaîne de connexion du modèle sémantique et la consommation du datamart sont ouvertes aussi à ceux qui ont obtenu les droits de lecture, et le droit de générer des rapports sur le modèle sémantique par défaut.
Vous verrez les modalités pratiques de l’attribution des droits d’accès dans le chapitre Partager les datamarts, section Détail des droits par niveau.
Ici Adèle a obtenu les droits de collaborateur sur l’espace de travail Editions et de repartage pour partager ses travaux. Elle va pouvoir consommer :
-
dans un insight : appelé ici état automatique,
-
dans un rapport en ligne de votre cru,
-
dans un rapport paginé à créer pour afficher et exporter les rapports volumineux,
-
dans Excel.
1. Consommer les rapports en ligne
Le simple consommateur comme Jean ci-dessous, qui dispose de droit d’accès de type visionneuse de l’espace de travail et n’est pas administrateur du datamart mais simplement lecteur du datamart, voit les rapports existants rattachés au datamart dans la barre rapide Parcourir (à gauche) - Partagé avec moi.
Le simple consommateur en visionneuse voit le jeu de données (ou « modèle sémantique » du...
Consommer depuis PowerPoint
Pour consommer un rapport Power BI Service issu du datamart depuis PowerPoint 365, cliquez sur le bouton Power BI visible depuis le ruban Insertion.
Ensuite, un assistant apparaît, et un lien vous renvoie vers la page internet de la page d’accueil de votre Power BI Service. Une authentification intervient pour vérifier vos droits d’accès. Copiez-collez l’URL du rapport qui vous intéresse dans la zone Coller une URL à partir de Power BI ici de l’assistant.
Cliquez ensuite sur Insérer à droite de l’URL.
Le rapport apparaît. Il est possible de le filtrer si un filtre a été prévu par le concepteur.
Le bouton 1 Option des données permet de filtrer ou de cacher le filtre, de mettre à jour le rapport, ou d’obtenir un résumé visuel des données (insight).
Le bouton 2 Option de style propose Plan - Outline pour ajouter une bordure autour du visuel. Décoché, le bord est invisible.
Le bouton 3 Options de complément permet d’afficher de l’aide sur l’assistant, ou d’effacer le contenu pour revenir à la saisie de l’URL du rapport précédente.
La flèche basse 4 Réduire la barre d’outil permet de développer ou cacher la barre d’outils affichant les boutons précédents....
Consommer depuis Power BI Desktop
Pour consommer le datamart depuis Power BI Desktop, depuis le ruban Accueil, utilisez la liste déroulante Hub de données OneLake et cliquez sur Datamarts.
L’écran qui apparaît à cette occasion figure dans la section qui suit.
1. Se connecter en Live Connect
Le bouton Datamart du groupe Hub de données OneLake ci-dessus ouvre le centre des données du portail Power BI. Choisissez le datamart cible et ouvrez-le en connexion directe avec le menu (1) Se connecter en bas de la fenêtre.
Se connecter (1) est l’option par défaut. Elle permet de se connecter en direct au datamart sans faire d’import en local sur votre PC. L’option (2) propose de se connecter au point de terminaison SQL du datamart, comme s’il s’agissait d’un serveur Azure SQL DB.
Le datamart 3d.Peche utilise une table ControleDesPeches (issue de la base SQL AlexisDb), la table Calendrier (issue également de la base AlexisDb), la table des départements (depuis le fichier departements.csv dans SharePoint) et la table de suivi des actions.
En bas de l’application Power BI Desktop, sous Page 1, vous pouvez lire en petit un texte Connecté dynamiquement au jeu de données Power BI : nom du datamart.
L’accès du Desktop à un datamart se fait ici à l’ouverture via une connexion directe dite Live...
Consommer depuis SSMS
SQL Server Management Studio (SSMS) est l’outil couteau suisse de toute base SQL Server ou Azure SQL.
Le prérequis est de copier la chaîne de connexion SQL du datamart.
Pour cela, allez sur l’espace de travail du datamart, cliquez sur Plus d’options et sur Copier la chaîne de connexion SQL au niveau du datamart.
Dès le départ il faut se connecter au moteur de base de données du serveur et, pour cela, nous allons utiliser la chaîne de terminaison SQL Server. Seule l’authentification Microsoft Entra MFA (anciennement Azure Active Directory - AAD) est autorisée.
On se connecte ensuite via une fenêtre d’identification, puis on confirme avec l’application Authenticator.
Une fenêtre du navigateur confirme l’authentification avec le message suivant : Authentication complete.
Le principal intérêt ici de SSMS est de produire des requêtes SELECT sur les données, d’élaborer comme ci-dessous des vues et de générer automatiquement des requêtes SQL avec l’outil de création de vue multitable.
Le mode opératoire pour créer une requête sur les données du datamart est :
Placez-vous sur la base du datamart 6.Finance db_powerbiprodfra et cliquez sur le bouton + à gauche des bases de données pour étendre la vue....
Consommer depuis Azure Data Studio
Il est possible d’aller directement à Azure Data Studio (ADS) depuis le datamart, ce qui évite de recopier le point de terminaison SQL. La manipulation est la suivante :
Cliquez sur le portail Power BI (app.powerbi.com). Allez sur votre Espace de travail et cliquez sur Plus d’options au niveau de la ligne du datamart. Cliquez ensuite sur Paramètres. Étendez Paramètres du serveur et cliquez sur Edit in Azure Data Studio.
L’écran suivant apparaît actuellement à chaque fois :
Si votre ADS est ouvert, le plus souvent cet écran apparaît en trop, et la bonne nouvelle est qu’ADS se charge quand même avec le bon point de terminaison déjà chargé :
Cliquez sur Open.
Cliquez sur Database : choisissez la base commençant par db_.
Indiquez un nom pour retrouver votre connexion plus tard et un groupe de serveurs que vous allez créer : DATAMARTS ici, par exemple.
Cliquez sur Connect.
Une fenêtre s’ouvre : attention les boutons Backup et Restore ne sont pas supportés pour une base datamart. Le bouton Refresh actualise la base.
Cliquez droit sur le datamart puis sur Manage.
En cliquant sur New Notebook, vous générez une page blanche.
En cliquant sur + Cell puis sur Code cell, vous pouvez créer une section de texte et la déplacer au besoin.
Vous pouvez créer du code SQL (Python 3 ou Powershell). Le résultat est exportable sous...
Consommer depuis les outils de BI externes (DAX Studio, Tabular Editor…)
1. DAX STUDIO
Assez déroutant au début, Dax Studio sert à évaluer des mesures et des champs dans leur contexte sans pour autant faire d’aller et retour entre le datamart et un rapport de test sur le datamart.
Il exige une syntaxe qu’il faut connaître et son générateur de requête Query Builder est assez sommaire, mais suffisant pour tester les mesures créées, sans s’ennuyer à développer la partie supplémentaire propre au Studio et qui permet d’afficher de premiers résultats pour tester vos formules DAX.
Dans ses outils de mesures de performance, seul le compteur Client Ms fonctionne depuis la version 3.0.7 avec les datamarts.
Pour plus de détail pour vous connecter, reportez-vous au chapitre Modéliser les données et créer les mesures, section Ergonomie DAX Studio.
Les options avancées sont utiles lorsque vous rencontrez des conditions spécifiques.
Par exemple, si vous avez un grand nombre de datamarts dans votre espace de travail, ou si vous êtes pressé, vous pouvez pointer directement sur un datamart au lieu d’utiliser la liste déroulante des datamarts dans l’éditeur DAX, le choix proposé par défaut.
Pour cela vous pouvez entrer en dur le nom du datamart dans Initial Catalog (ou bien choisir dans le catalogue des datamarts).
Autre exemple d’usage, vous voulez tester un rôle RLS créé dans votre datamart.
Dans ce cas, entrez ce nom dans le champ Roles. Par exemple ici, aucun rôle n’a été choisi dans la ligne Roles des options avancées.
EVALUATE TOPN (500, ’GlossaireSPOTransposé’) donne le résultat suivant :
Ci-dessous, le "role" a été défini à Nederland (il n’y a pas de liste de choix ici).
Le résultat de la même requête DAX Studio n’est plus affiché qu’avec une seule ligne suite au filtrage et le nom du rôle est affiché. Ce qui montre au passage la capacité du RLS des datamarts pour filtrer les tables en fonction de votre langue.
Enfin, étant donné que vous pouvez tester les mesures DAX, il peut être intéressant de mesurer...
Les explorations de données en ligne
Une exploration est une vue simplifiée des données sous forme tabulaire ou visuelle sans passer par un rapport. C’est un tableau croisé sans code semblable à ce que vous pouvez obtenir par un export Excel, mais cette fois sans avoir besoin de passer par un fichier, car c’est un objet BI enregistré en ligne dans un espace de travail.
Voici le mode opératoire pour mettre en œuvre une exploration de données :
Trouvez un modèle sémantique ou un rapport que vous voulez explorer. L’espace de travail ou le hub de données peuvent vous les présenter. Allez sur Plus d’options et Explorer ces données.
Choisissez les données à explorer sur le volet de droite (champs en ligne, colonnes ou valeurs) du tableau croisé en matrice ou en visuel ou les deux. Le choix des données impacte automatiquement à la fois les tableaux croisés (ou « matrices ») et les visuels.
La section Réorganiser les données permet non seulement de mettre les champs en colonnes plutôt qu’en lignes ou en valeurs par simple déplacement, mais aussi de choisir l’agrégation (somme, moyenne, etc.).
Il vous suffit ensuite d’enregistrer l’exploration dans un espace de travail .
Le bouton Enregistrer, en haut à gauche...