Comment utiliser la fonctionnalité de requête directe Power BI – techsyncer

Lorsque vous créez des rapports Power BI, les spectateurs s’attendent à ce que le rapport soit actualisé périodiquement et que les données soient constamment mises à jour. Comment ? Mettre à jour manuellement les rapports fonctionne, mais Power Bi Direct Query offre une fonctionnalité bien meilleure, le rafraîchissement planifié.

Dans ce tutoriel, vous apprendrez comment tirer parti de la fonctionnalité Power Bi Direct Query en connectant un jeu de données stocké dans un serveur et en exécutant des requêtes sur vos données.

Restez à l’écoute et augmentez votre productivité tout en gagnant du temps dans le processus !

Prérequis

Ce tutoriel sera une démonstration pratique. Pour suivre, assurez-vous d’avoir ce qui suit :

Power Bi Desktop – Ce tutoriel utilise la version 2.109.1021.0 de Power Bi Desktop.

Un serveur SQL.

A code editor – This tutorial uses VS code version 1.71.

Passerelle de données Microsoft sur site.

SQL Server Management Studio (SSMS) – Ce tutoriel utilise SQL Server Management Studio 18.12.1.

Créer une base de données SQL à gérer avec Power BI Direct Query

Power Bi Direct Query vous permet de vous connecter directement à un ensemble de données et offre à votre projet la possibilité de charger des données en direct dans votre projet. Mais avant de créer une base de données SQL et une connexion, vous devez connaître le nom du serveur et le nom de la source de données pour obtenir les détails corrects.

Pour créer une base de données SQL, suivez ces étapes :

1. Recherchez ODBC dans votre barre de recherche, recherchez et cliquez sur Administrateur de source de données ODBC (64 bits) pour l’ouvrir.

2. Ensuite, accédez à l’onglet DSN système dans la fenêtre Administrateur de source de données ODBC, et cliquez sur Ajouter pour commencer à ajouter une nouvelle source de données.

3. Sélectionnez le pilote SQL Server dans la liste ci-dessous, et cliquez sur Terminer pour créer une source de données SQL.

DirectQuery ne prend pas en charge toutes les sources de données. MySQL n’est pas pris en charge, tandis que SQL l’est.

4. Maintenant, nommez votre source de données (MssqlDataSource), sélectionnez une instance SQL (POWERSERVER\POWERSERVER) installée sur votre machine, et cliquez sur Terminer.

Prenez note du serveur SQL et du nom de la source de données, car vous en aurez besoin pour votre chaîne de connexion Python.

5. Examinez les détails de votre source de données et cliquez sur Tester la source de données pour vérifier votre source de données.

Si la connexion est bonne, vous obtiendrez un message TEST COMPLÉTÉ AVEC SUCCÈS, comme indiqué ci-dessous.

Verifying testing the data source completed

Connexion au serveur SQL

Maintenant que vous avez créé une source de données pour votre serveur SQL, vous utiliserez cette source de données pour créer une chaîne de connexion pour votre code Python.

1. Lancez votre visual studio et appuyez sur CTRL+SHFT+` pour ouvrir un nouveau terminal.

2. Ensuite, exécutez la commande sqlcmd ci-dessous pour vous connecter à votre instance de serveur SQL.

sqlcmd -S SQL_SERVER -E

3. Une fois connecté, exécutez les requêtes suivantes pour créer une nouvelle base de données (CREATE DATABASE) appelée MSSQLDB.

CREATE DATABASE MSSQLDB;
GO

4. Créez un fichier Python DBconnect.py et ajoutez le code ci-dessous, qui vous permet de vous connecter à votre base de données SQL en utilisant SQLAlchemy ORM pour Python.

L’engine marque le point de départ de votre application SQLAlchemy. L’engine décrit le pool de connexions et le dialecte pour le Python Database API Specification (DBAPI). Python DBAPI est une spécification dans Python pour définir des modèles d’utilisation communs pour tous les packages de connexion de base de données. Cette spécification communique avec la base de données spécifiée.

//DBconnect.py
import sqlalchemy as sa
from sqlalchemy import create_engine
import urllib
import pyodbc
# Création d'une chaîne de connexion, en utilisant urllib pour formater le texte.
conn = urllib.parse.quote_plus(
# Les valeurs de la chaîne de connexion sont prises à partir de la source de données
'Data Source Name=MssqlDataSource;'

'Driver={SQL Server};'

'Server=POWERSERVER\POWERSERVER;'

'Database=MSSQLDB;'

'Trusted_connection=yes;'

)

try:
# Création d'une connexion avec le moteur sqlalchemy 
	coxn = create_engine('mssql+pyodbc:///?odbc_connect={}'.format(conn))
	print("Passed")
except:
	print("failed!")

5. Créez un fichier Python appelé CreateTable.py dans votre dossier principal et ajoutez le code ci-dessous, puis exécutez-le. Le code ci-dessous crée une table appelée « students » dans votre base de données SQL.

//CreateTable.py
from DBconnect import coxn
from sqlalchemy import Table, Column, Integer, String, MetaData

# Crée une structure de métadonnées qui contient les définitions des tables 
  # et des objets associés tels que l'index, la vue, les déclencheurs, etc.
meta = MetaData()

# Représente la commande CREATE TABLE en syntaxe SQL standard pour créer une table.
students = Table(
'students', meta,
Column('id', Integer, primary_key = True),
Column('name', String),
Column('lastname', String),
)

# Utilise l'objet moteur pour créer tous les objets de table définis 
  # et stocke les informations dans les métadonnées.
meta.create_all(coxn)

6. Ensuite, ouvrez votre SSMS et vérifiez la base de données, la table et les colonnes créées.

7. Créez un fichier Python appelé WriteToTable.py, ajoutez le code ci-dessous, puis exécutez-le.

Ce code contient la logique pour écrire des valeurs dans la table de la base de données.

//WriteToTable.py
# Importation de la table "students" depuis le fichier CreateTable.py.
from CreateTable import students
# Importation de la chaîne de connexion depuis le fichier DBconnect.py.
from DBconnect import coxn
# Insertion de plusieurs valeurs dans la table "students"
ins = students.insert().values([
   {'name':'Bob','lastname':'Marley'},
   {'name':'Bob','lastname':'Dylan'}
])
conn = coxn.connect()
conn.execute(ins)

Préparation de la passerelle de données sur site

Maintenant que vous avez créé une chaîne de connexion et réussi à vous connecter à la base de données, vous devrez créer une passerelle de données. Cette passerelle de données sera responsable de connecter votre base de données à Power BI.

1. Ouvrez la passerelle de données sur site et connectez-vous.

2. Vérifiez l’état de votre passerelle de données sur site et confirmez qu’elle est en ligne, comme indiqué ci-dessous.

Checking if the On-premises data gateway is online

3. Ensuite, ouvrez Power BI, cliquez sur Obtenir des données dans l’onglet Accueil du ruban et choisissez SQL Server pour initier la connexion de votre source de données à Power BI.

4. Maintenant, connectez-vous à DirectQuery avec ce qui suit :

Insérez votre nom de serveur et le nom de base de données dans les champs correspondantsAssurez-vous que l’option DirectQuery est sélectionnée.Cliquez sur OK pour vous connecter à DirectQuery.

5. Sélectionnez une table (étudiants) de la base de données et cliquez sur Charger pour charger les données.

6. Ensuite, cliquez sur l’icône de table pour utiliser une visualisation de table pour afficher les données.

Le tableau ci-dessous montre les données que vous avez chargées à partir de la table de base de données

7. Cliquez sur le menu Fichier pour accéder aux actions à effectuer sur Power BI.

8. Maintenant, cliquez sur Publier → Publier sur Power BI pour publier votre rapport de données.

9. Cliquez sur Ouvrir votre rapport une fois que votre publication est réussie, redirigeant votre navigateur vers la liste des activités récentes que vous avez effectuées dans Power BI (étape 10).

10. Cliquez sur le rapport pour l’ouvrir, comme indiqué ci-dessous.

Lors de la première configuration, vous pourriez rencontrer un problème de passerelle comme ci-dessous, car vous n’avez pas encore connecté la source de données du projet à la passerelle de données de Power BI.

11. Cliquez sur l’icône des paramètres sous la colonne Actions pour initier l’ajout d’une nouvelle connexion de passerelle. Ce faisant, vous pouvez corriger l’erreur de configuration de la passerelle.

Initiating adding a new gateway connection

12. Configurez la nouvelle connexion de passerelle, et cliquez sur Créer pour créer une nouvelle connexion entre votre source de données et la passerelle.

13. Enfin, une fois que vous voyez que la passerelle fonctionne, sélectionnez le nom de la source de données (MssqlDataSource) dans le champ de liste déroulante Correspond à, et cliquez sur Appliquer.

Visualisation et Gestion des Rapports de Données

Après avoir établi une connexion entre la passerelle et Power BI, vous pouvez visualiser votre rapport et configurer une planification de rafraîchissement.

1. Accédez à la page d’accueil de vos rapports Power BI.

La performance de Power Bi Direct Query dépend de la source de données sous-jacente. Le temps nécessaire à la source de données sous-jacente pour répondre aux demandes déterminera à quelle vitesse l’utilisateur pourra voir le rapport. Ou s’ils verront les rapports avec succès en premier lieu.

2. Ensuite, cliquez sur le rapport, comme indiqué ci-dessous, pour l’ouvrir.

Après avoir ouvert le rapport, vous devriez voir les données, comme indiqué ci-dessous.

3. Retournez à la page d’accueil du rapport, et cliquez sur Actualiser → Planifier un rafraîchissement pour définir un rafraîchissement planifié du rapport.

Outre la création de rapports en direct, Power Bi Direct Query vous permet de définir un rafraîchissement planifié, mettant automatiquement à jour vos rapports.

4. Maintenant, sélectionnez l’intervalle de rafraîchissement de votre choix dans le champ déroulant, et cliquez sur Appliquer pour appliquer les modifications.

5. Ouvrez votre script Python WriteToTable.py et ajoutez le code ci-dessous pour insérer plus d’entrées dans votre base de données avec les éléments suivants, puis exécutez le script.

Exécuter ce code ne fournit pas de sortie, mais vous vérifierez les valeurs insérées dans la table plus tard dans les étapes suivantes.

Il n’y a pas d’éditeur de requête Power Query pour les sources multidimensionnelles comme SAP Business Warehouse, ce qui limite ce que vous pouvez réaliser avec vos données.

//WriteToTable.py
# Importation de la table des étudiants à partir du fichier CreateTable.py.
from CreateTable import students
# Importation de la chaîne de connexion coxn à partir du fichier DBconnect.py.
from DBconnect import coxn

# Insertion de plusieurs valeurs dans la table des étudiants
ins = students.insert().values([
{'name':'Damian','lastname':'Marley'},
{'name':'Rita','lastname':'Marley'},
{'name':'Ziggy','lastname':'Marley'},
{'name':'Sam','lastname':'Dylan'},
{'name':'Jakob','lastname':'Dylan'},
{'name':'Maria','lastname':'Dylan'}
])
# Création du curseur de connexion.
conn = coxn.connect()
# Exécution de la commande insert()
conn.execute(ins)

6. Ensuite, passez à votre navigateur Power BI et cliquez sur l’onglet Jeux de données + flux de données.

Si tout se passe bien, vous verrez l’heure de la dernière actualisation et la prochaine actualisation de l’ensemble de données comme sur la capture d’écran ci-dessous.

Notez que Power Bi Direct Query est sensible aux changements et à la mise en forme. Vous pouvez rencontrer une erreur si votre éditeur de requête Power contient des étapes complexes.

Verifying the last refreshed time and next refresh of the dataset

Comme prévu, le rechargement se produit automatiquement après 15 minutes, et la prochaine actualisation est programmée pour se produire dans 15 minutes supplémentaires.

Conclusion

Tout au long de ce tutoriel, vous avez appris comment utiliser la fonctionnalité de requête directe de Power Bi pour connecter votre ensemble de données stocké sur votre serveur. Vous avez également abordé l’exécution de requêtes directement sur vos données.

La création de rapports est un processus long de collecte, de nettoyage, de téléchargement vers une source de données, de chargement des données dans Power BI et de travail sur les visuels Power BI. Ce processus mentionné est donné. Mais ensuite, vous pouvez soit rafraîchir le jeu de données manuellement, soit actualiser les données automatiquement selon le calendrier défini.

Power BI Direct Query a ses inconvénients lors de la manipulation des données de votre ensemble de données. Mais le fait que vous puissiez créer un rapport une fois et ne plus jamais travailler sur l’arrière-plan est une incitation massive lors de l’utilisation de cette fonctionnalité.