Modes de connexion

Quatre modes de connexion sont possibles avec Power BI :

Importation
DirectQuery
Live Connect
Modèle composite

Et une version récente et modifiée de DirectQuery)

DirectQuery pour les jeux de données et Analysis Services

Importation

Importe l'intégralité du jeu de données en mémoire
Mémoire de la machine qui héberge le jeu de données Power BI, soit Power BI Desktop, soit Power BI (cloud).
Il est nécessaire d’actualiser les données importées. Il faudra ainsi planifier des mises à jour de données si vous utilisez cette méthode. Sinon, les données deviendront obsolètes.
Tous les sources sont au moins en mode Importation.
Certains sources sont uniquement en mode Importation : Excel. D’autres proposent le choix entre Importation et DirectQuery : SQL Server.
Le moteur d’importation est xVelocity, qui réorganisent et compressent les données :
Ce mode impose le démarrage de SQL Server Analysis Services (installé par défaut).Dans le service Power BI : Paramètres d’espace de travail > onglet Stockage système :
Limite de taille : 1 Go x 10 dans les comptes Free et Pro, 100 Go avec un compte Premium par utilisateur et 400 Go en capacité Premium.
Scénario d’utilisation :
- Chaque table du modèle provient d’un type de source différent, par exemple : Excel + CSV + SQL Server.
- Tout le langage DAX peut être utilisé.
- Installer une passerelle de données pour permettre la mise à jour des données locales.
- Programmer une actualisation planifiée dans Power BI Service.

Avantages de l’importation

Données en mémoires (donc rapide)
Tout Power Query et tout le DAX

Inconvénients de l’importation

Nécessite une actualisation manuelle ou planifiée
Limite de la taille du modèle selon la licence

DirectQuery

DirectQuery ne charge pas de données dans le modèle Power BI.
Ne consomme pas de mémoire car DirectQuery est directement connecté à la source de données.
Concerne un nombre limité de source :

Liste des sources DirectQuery

Nom

Amazon Redshift

Azure HDInsight Spark

Azure SQL Database

Azure SQL Data Warehouse

Google BigQuery

IBM Netezza

Impala

Oracle Database

SAP Business Warehouse

SAP HANA

Snowflake

Spark

SQL Server

Teradata Database

Vertica

Les visualisations dans un rapport sont alimentées directement par une requête envoyée à la source de données.
Dans Power BI Destop, il n’y a pas d’onglet Affichage Table :
Dans la Barre d’état, un message indique des précisions sur la connexion :
Avec SQL Profiler, regarder l’exécution des requêtes SQL sous-jacentes. Une requête par visualisation, même si identique.
DirectQuery s'exécute beaucoup plus lentement que l'option Importer des données, qui charge les données en mémoire.L'utilisation de DirectQuery sans réglage des performances de la base de données source est une grave erreur.Par exemple avec une source SQL Server, on peut définir un index cluster sur une colonne, ce qui améliore considérablement la rapidité de réponse.

Performances

Réduction de requêtesCette option dans la section Filtres envoi moins de requêtes au serveur - il est conseillé de la cocher - et suivre le conseil de la section Segments :
Nombre maximal de connexions par source de données(DirectQuery dans Power BI - Power BI)L’augmentation de la valeur permet l’envoi d’un nombre plus élevé de requêtes (jusqu’au nombre maximal spécifié) à la source de données sous-jacente. Utile quand de nombreux visuels figurent sur une seule page ou quand de nombreux utilisateurs accèdent à un rapport en même temps.Une fois le nombre maximal de connexions atteint, les requêtes sont mises en file d’attente jusqu’à ce qu’une connexion soit disponible. L’augmentation de cette limite entraîne celle de la charge sur la source sous-jacente, si bien que le paramètre ne garantit pas une amélioration des performances globales.

Mode composite

Des sources Importation ET des sources DirectQuery dans un même rapport.

Idéalement, les dimensions sont importées (moindre volume et moins de mises à jour) et les faits (volume importants, données actualisées souvent).

Il existe un mode Double, qui revient à laisser Power BI choisir le meilleur mode (la source est toujours considérée comme DirectQuery, avec les limitations inhérentes)

Cf. Modèle composite plus bas dans cette page.

Limites

Certaines manipulations Power Query ne sont pas possibles, indiquées par un bandeau jaune. Les manipulations concernées dépendent de la source (SQL Server, Oracle...).
DAX et la modélisation sont également limités en mode DirectQuery.La création de tables calculées est autorisée en mode composite et la hiérarchie de dates par défaut n'est pas disponible. Certaines fonctions DAX, telles que les fonctions parent-enfant, ne sont pas disponibles.Certaines des mesures DAX complexes peuvent entraîner des problèmes de performances en mode DirectQuery. Il est préférable de commencer par des mesures simples telles que des agrégations simples, de tester les performances, puis d'ajouter progressivement des scénarios plus complexes.

Avantages

Ensemble de données à grande échelle
Limitation de taille uniquement pour la source de données
Pas besoin d'actualiser les données

Inconvénients

Les transformations Power Query sont limitées
La modélisation est limitée
DAX est limité
Vitesse plus lente du rapport

Live connection (connexion dynamique)

Live Connection est utilisée avec 4 types de sources de données.
- Azure Analysis Services
- Modèle tabulaire SQL Server Analysis Services
- Modèle multidimensionnel SQL Server Analysis Services
- Jeu de données de service Power BI
Ne stocke pas une deuxième copie des données en mémoire.
Les visualisations interrogent la source de données à partir de Power BI.
Ces quatre types sont la technologie SQL Server Analysis Services (SSAS). Vous ne pouvez pas utiliser Live Connexion avec le moteur de base de données SQL Server. Mais la technologie SSAS peut être basée sur le cloud (Azure Analysis Services) ou locale (SSAS localement).
Power Query n’est pas accessible.
En général plus rapide que DirectQuery car le modèle SSAS est chargé en mémoire du serveur, l’importation restant le plus rapide.
Aucune autre source ne peut être ajoutée au rapport.
On peut ajouter des mesures DAX, uniquement dans le rapport (”report-level measure”). Utiliser ces mesures pour créer des mises en forme conditionnelles plutôt que des mesures de calcul, qui devraient plutôt se trouver dans le modèle SSAS.

Se connecter

Obtenir les données > Analysis Services :
Naviguer jusqu’au modèle souhaité (noter que l’on sélectionne le modèle, pas des tables) :

Après connexion, la Barre d’état affiche :

Suivre avec SQL Server Profiler

Cf. Optimisations

Passerelle [WiP]

Une passerelle est nécessaire pour se connecter au modèle SSAS. Après publication, on obtient ce message :

Différences avec DirectQuery

Modèle composite

Une partie de votre modèle peut être une connexion DirectQuery à une source de données (par exemple, une base de données SQL Server) et qu'une autre partie peut utiliser Importer des données (par exemple, un fichier Excel) :
Mise en œuvre : L'importation de données est idéale pour l'analyse ultra-rapide des données et est flexible, tandis que DirectQuery est bon pour les tables Big Data et l'actualisation des données.Le modèle composite combine les avantages d'Importer et de DirectQuery en un seul modèle.À l'aide du modèle composite, vous pouvez utiliser des tables volumineuses dans DirectQuery tout en important des tables plus petites à l'aide de l'option Importer des données :
Si le type de source le permet, le choix de connexion Importer et DirectQuery sont proposés, comme ici avec le type SQL Server :Ce qui affiche dans la barre d’état :Ajoutons une source Importer, comme un fichier Excel. Un message de sécurité s’affiche :Le mode devient Mixte (donc Modèle composite) :
Modes de stockage : Import, DirectQuery ou Double (Dual).
- Ce mode peut être modifié sur les tables DirectQuery (une table en Importer ne peut pas passer en mode DirectQuery ou Double) :
- Soit on bascule en mode Importer : opération définitive.
- Soit on bascule en mode Double. Une version “Importer” des données est stockée dans le modèle. Power BI choisira le meilleur mode en fonction de la visualisation :
  - Si des colonnes d’une table Importer et d’une table Double sont dans une même vignette, c’est la version “Importer” de la table Double qui sera choisi.
  - Si des colonnes d’une table DirectQuery et d’une table Double sont dans une même vignette, c’est la version DirectQuery qui sera choisi.
Impact sur la modélisation
- Relations : toutes les relations sont possibles
- Tables calculées (en DAX) : sur toute table du modèle, seront en mode Importée (donc actualisation nécessaire)
- Colonne calculée (en DAX) : dans une table DirectQuery, la formule ne doit utiliser que des colonnes de la table elle-même (la fonction LOOKUPVALUE n’est pas disponible).
Exemple avec 3 sources :Dans cet exemple, on a :
1. une table en DirectQuery (FactInternetSales d’une base SQL Server),
2. une table en DirectQuery d’un modèle sémantique (table Product d’un jeux de données, sous forme d’une connexion à une base de données SQL Server Analysis Services). Cf la section suivante.
3. une table en Importer (DimCustomer d’un fichier Excel).

Télécharger l’exemple

DirectQuery pour les jeux de données Power BI et Analysis Services

Contexte

La connexion classique avec un jeux de données est Live Connection, mais avec certaines limitations, comme l’impossibilité d’utiliser Power Query.Depuis Décembre 2020, on peut se connecter à un jeux de données en mode DirectQuery, et donc potentiellement ajouter d’autres sources de données, et donc utiliser Power Query.

Avec le mode Importation, chaque utilisateur en libre-service commencera à importer des données dans son propre modèle de données, et bientôt vous vous retrouverez avec de nombreux modèles de données, de nombreuses duplications et des silos de modèles Power BI partout :

De différentes sources, certaines sources sont les mêmes.
Des calculs répétés
Pas de cohérence
Beaucoup de redondance
Pas une source unique de vérité
Beaucoup de surcroit de temps, effort et de dépense budgétaire
Tous les résultats ne sont pas les mêmes
Perte de confiance dans les résultats de Power BI

Le mode DirectQuery pour les jeux de données Power BI et Analysis Services permet aux utilisateurs en libre-service de réutiliser le modèle existant et d'y ajouter d'autres options dans un nouveau modèle :

Tirer parti du modèle central
Apporter plus des jeux de données
Modèle libre-service et modèle d’entreprise combinés
Moins de redondance
Plus de cohérence
Réutiliser au lieu de refaire
Plus de confiance dans les résultats de Power BI

Cette fonctionnalité est plus qu'une fonctionnalité : c'est là que les deux mondes de la BI d'entreprise et de la BI en libre-service se rejoignent.

Autorisations

Autoriser les points de terminaison XMLA et l’analyse dans Excel avec les modèles sémantiques locaux.Si ce commutateur est désactivé, il est impossible d’établir une connexion DirectQuery à un modèle sémantique Power BI.
Les utilisateurs peuvent utiliser des modèles sémantiques Power BI dans Excel à l’aide d’une connexion active.Si ce commutateur est désactivé, les utilisateurs ne peuvent pas établir de connexions actives aux modèles sémantiques Power BI. Le bouton Apporter des modifications à ce modèle n’est donc pas accessible :
Autoriser la connexion DirectQuery aux modèles sémantiques Power BI (cf. Portail d’administration).

Limitations

Pas pour des jeux de données dans Mon espace de travail.
Le mode Live connection est remplacé le mode Direct Query

Mode opératoire

Dans un nouveau rapport, Obtenir les données > Jeux de données Power BI.
Sélectionner un jeux de données (modèle sémantique).La Barre des tâches affiche ce message :
La connexion est actuellement en Live Connection :
Si on tente d’ajouter une source (Obtenir les données) ou si on clic sur Apporter des modifications à ce modèle dans la Barre d’état, vous obtenez le message :
Confirmer en cliquant sur Ajouter un modèle local.
Valider les tables auxquelles se connecter et cliquer sur Envoyer :
La Barre d’état affiche désormais :
Le processus d’importation d’un la seconde source se poursuit. Vous devriez voir ce message :
Le mode de connexion aux tables du modèle initiale est désormais en DirectQuery :
Si des noms sont identiques entre les modèles, un message vous en informe :

Aucune requête pour le modèle de données d’origine n’est créée dans Power Query.

Avantages & inconvénients des types de connexion

Type de connexion	Avantages	Inconvénients
Importer des données ou actualiser planifié	Connexion la plus rapide possiblePower BI est entièrement fonctionnelCombine des données provenant de différentes sourcesExpressions DAX complètesTransformations Full Power Query	Connexion la plus rapide possiblePower BI est entièrement fonctionnelCombine des données provenant de différentes sourcesExpressions DAX complètesTransformations Full Power Query
DirectQuery	Les sources de données à grande échelle sont prises en charge. Aucune limitation de tailleLes modèles prédéfinis dans certaines sources de données peuvent être utilisés instantanément	Fonctionnalités Power Query très limitéesType de connexion plus lent : le réglage des performances dans la source de données est nécessaire
Live connexion	Sources de données à grande échelle prises en charge. Aucune limitation de taille pour autant que SSAS prenne en chargeDe nombreuses organisations disposent déjà de modèles SSAS, elles peuvent donc les utiliser en tant que LiveConnexion sans réplication dans Power BIMesures au niveau du rapportLes moteurs analytiques MDX ou DAX dans la source de données de SSAS peuvent être un atout majeur pour la modélisation par rapport à DirectQuery	Pas de Power QueryImpossible de combiner des données provenant de plusieurs sourcesType de connexion plus lent : le réglage des performances dans la source de données est nécessaire