Support formation Architecture Power BI

Architecture de stockage & connexion

Modes de connexion

Gonzague Ducos

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.

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

Liste des sources DirectQuery

⁠

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 identiques.

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êtes Cette 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.

Live connection (connexion dynamique)

Live Connection est utilisée avec 4 types de sources de données.

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.

Les types de sources de données pris en charge par ce type de connexion sont les suivants :

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

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

DirectQuery est une connexion principalement à des bases de données ou à des moteurs analytiques non Microsoft, ou à des bases de données relationnelles (telles que SQL Server, Teradata, Oracle, SAP Business Warehouse, etc.).

Live Connection est une connexion à quatre sources : SSAS Tabulaire, SSAS MultiDimensionnel, Azure Analysis Services et jeu de données Power BI.

DirectQuery dispose encore de fonctionnalités Power Query limitées avec certaines sources de données (telles que les bases de données SQL Server).

Live Connection ne contient aucune fonctionnalité Power Query.

Vous pouvez créer des colonnes calculées simples dans Direct Query ; ceux-ci seront convertis en scripts T-SQL en arrière-plan.

Vous ne pouvez pas créer de colonnes calculées dans Live Connection.

Vous pouvez utiliser des mesures au niveau du rapport et exploiter toutes les fonctions DAX dans une instance Live Connection.

En mode DirectQuery, vous pouvez avoir des capacités de mesure limitées. Pour les mesures plus complexes, vous devez vérifier les performances (et certaines fonctions telles que les fonctions parent-enfant ne sont pas disponibles). Certaines mesures complexes peuvent ralentir considérablement les performances.

Le mode DirectQuery est généralement plus lent que Live Connection.

Une instance Live Connection est généralement moins flexible que le mode 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 :

une table en DirectQuery (FactInternetSales d’une base SQL Server),

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⁠

une table en Importer (DimCustomer d’un fichier Excel).

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 :

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 :

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

Avantages & inconvénients des types de connexion

Type de connexion

Avantages

Inconvénients

Importer des données ou actualiser planifié

Connexion la plus rapide possible

Power BI est entièrement fonctionnel

Combine des données provenant de différentes sources

Expressions DAX complètes

Transformations Full Power Query

Connexion la plus rapide possible

Power BI est entièrement fonctionnel

Combine des données provenant de différentes sources

Expressions DAX complètes

Transformations Full Power Query

DirectQuery

Les sources de données à grande échelle sont prises en charge. Aucune limitation de taille

Les modèles prédéfinis dans certaines sources de données peuvent être utilisés instantanément

Fonctionnalités Power Query très limitées

Type 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 charge

De nombreuses organisations disposent déjà de modèles SSAS, elles peuvent donc les utiliser en tant que Live

Connexion sans réplication dans Power BI

Mesures au niveau du rapport

Les 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 Query

Impossible de combiner des données provenant de plusieurs sources

Type de connexion plus lent : le réglage des performances dans la source de données est nécessaire

There are no rows in this table

⁠

Want to print your doc?
This is not the way.

Try clicking the ⋯ next to your doc name or using a keyboard shortcut (

CtrlP

) instead.