Le monde de la BI n’a jamais été aussi compétitif qu’aujourd’hui. Parmi les plateformes des dernières années, Snowflake et Power BI apparaissent comme une combinaison populaire d’entreposage de données et de Data Visualisation.

Développé en 2012, Snowflake est désormais une plateforme de données incontournable. Cette plateforme, conçue nativement sur l’architecture cloud (Azure, GCP ou AWS), permet de centraliser, stocker, organiser, transformer, exécuter des requêtes et analyser tous les types de données (structurées ou semi-structurées). Il est possible d’utiliser différents langages pour réaliser cela, notamment du SQL ou du Python.

Lancé en 2013, Power BI est la solution de reporting et analytique majeure de Microsoft. En tant que leader du Gartner BI Magic Quadrant depuis plus d'une décennie, Power BI reste l'un des outils de business intelligence les plus utilisés par les clients Snowflake.

Alors, quelles sont les méthodes de connexion proposées par Snowflake et comment pouvons-nous assurer une connexion réussie et optimiser les performances entre ces deux plateformes ? 

Comment connecter Power BI et Snowflake ? 

Connexion à facteur unique : Utilisateur et mot de passe

La connexion avec un utilisateur et un mot de passe est une approche simple est courante pour connecter Power BI et Snowflake. Elle repose sur l'utilisation du connecteur ODBC ou du connecteur natif Snowflake, permettant à Power BI d'interroger directement les données stockées dans Snowflake.

Lors de la configuration de cette connexion, nous devons renseigner plusieurs informations comme le nom d'utilisateur, le mot de passe, l'URL du compte Snowflake, ainsi que le warehouse, la base de données et le schéma cible. Pour assurer la sécurité de cette connexion, Snowflake propose plusieurs paramètres dans la commande ALTER USER pour gérer les propriétés de l'utilisateur. Par exemple, si vous souhaitez forcer un utilisateur à changer son mot de passe à sa première connexion ou définir une expiration de mot de passe, vous pouvez utiliser cette commande SQL :

ALTER USER mon_utilisateur
SET MUST_CHANGE_PASSWORD = TRUE,
DAYS_TO_EXPIRY = 90;

Cependant, il est crucial d'ajouter un paramètre pour renforcer la sécurité de l'authentification, notamment avec l'option LEGACY_SERVICE.

ALTER USER mon_utilisateur SET TYPE = LEGACY_SERVICE;

Cette option permet aux utilisateurs ou applications de continuer à se connecter à Snowflake en utilisant un mot de passe traditionnel, même si Snowflake encourage progressivement la transition vers des méthodes d’authentification plus sécurisées. Toutefois, avec les nouvelles exigences de sécurité annoncées par Snowflake, l'authentification par mot de passe avec un seul facteur sera complètement désactivée à partir de novembre 2025 pour tous les utilisateurs. En conséquence, les utilisateurs ayant le type LEGACY_SERVICE seront automatiquement migrés vers le type SERVICE, ce qui implique un changement important dans la manière dont les connexions sont gérées.

Bien que l'authentification par utilisateur et mot de passe soit rapide à mettre en place, elle présente des risques de sécurité si elle n'est pas correctement protégée. Pour cette raison, il est fortement recommandé de migrer vers des méthodes d'authentification plus sécurisées, - comme l'authentification SSO (Single Sign-On) ou OAuth - qui offrent une gestion des identités plus robuste et qui répondent mieux aux exigences de sécurité des environnements de production.

Connexion SSO

La connexion via OAuth est une méthode d'authentification moderne et sécurisée, idéale pour les utilisateurs techniques qui doivent accéder à Snowflake sans passer par des mots de passe explicites. Cette méthode repose sur le service OAuth natif de Snowflake pour gérer le flux d'autorisation et fournir des tokens d'accès. Cela permet à Power BI d'établir une connexion sécurisée avec Snowflake en utilisant des identifiants centralisés.

Bonnes pratiques Snowflake et Power BI pour optimiser la connexion et les performances

La connexion via SSO (Single Sign-On) entre Power BI et Snowflake permet aux utilisateurs d'accéder à Snowflake via Power BI en utilisant un seul jeu d'identifiants. Ce mécanisme repose sur OAuth v2, ce qui lui confère de nombreux avantages. L'objectif est d'intégrer l'authentification via un fournisseur d'identité, tel que Microsoft Entra ID, afin de garantir une gestion centralisée des utilisateurs et une sécurité renforcée. Cela simplifie l'accès aux données stockées dans Snowflake via Power BI tout en assurant une meilleure gestion des identités et une sécurité accrue.

1/ Création de l'intégration de sécurité OAuth dans Snowflake

Tout d’abord, on doit créer une intégration de sécurité OAuth (nécessaire d’utiliser le rôle ACCOUNTADMIN) qui permet à Snowflake de jouer le rôle de serveur d'autorisation. Cela permet à Snowflake de reconnaître Power BI comme une application autorisée à utiliser OAuth pour l'accès aux données. Pour ce faire, il faut spécifier plusieurs paramètres dans la commande CREATE SECURITY INTEGRATION. Cette configuration comprend des paramètres comme l'URL du fournisseur OAuth, les informations d'application et les rôles associés :

Bonnes pratiques Snowflake et Power BI pour optimiser la connexion et les performances

  • External_oauth_type = azure : Le type d'authentification OAuth externe est défini ici Azure AD (Microsoft Entra ID).
  • External_oauth_issuer = 'https://sts.windows.net/<tenant_id>/' : L'URL de l'autorité de sécurité qui émet les jetons d'authentification OAuth. Dans ce cas, elle pointe vers le Issuer d'Azure AD. Pour retrouver l’URL de l’autorité, nous pouvons aller sur Azure ID → Overview → Tenant ID.
    Bonnes pratiques Snowflake et Power BI pour optimiser la connexion et les performances
  • External_oauth_jws_keys_url : Ceci est l'URL qui permet de récupérer les clés JSON Web Signature (JWS) utilisées pour vérifier la signature des jetons OAuth. Cette URL est spécifique à Azure AD et permet à Snowflake de s'assurer que les jetons OAuth sont valides et qu'ils proviennent bien de l'émetteur légitime.
  • External_oauth_audience_list : Ceci définit la liste des audiences OAuth. Elle précise les applications autorisées à accéder aux ressources via OAuth.
  • External_oauth_token_user_mapping_claim : Le claim UPN (User Principal Name) est utilisé pour mapper les jetons OAuth aux utilisateurs Snowflake.
  • External_oauth_snowflake_user_mapping_attribute : Cette ligne définit l'attribut de l'utilisateur Snowflake à utiliser pour faire le mappage des utilisateurs. Nous pouvons utiliser aussi l’attribut ‘login_name’ ou ‘email_address

2/ Configuration SSO dans Power BI

Dans Power BI, pour établir une connexion entre Power BI et Snowflake via OAuth, il est nécessaire de créer une nouvelle connexion en suivant les étapes ci-dessous :

  • Dans les “Paramètres” → “Gérer des connexions et des passerelles”
  • Ensuite “+ Nouveau” pour créer une nouvelle connexion
  • Lors de la configuration de la connexion, choisissez la méthode d'authentification OAuth2.

Bonnes pratiques Snowflake et Power BI pour optimiser la connexion et les performances

Bonnes pratiques Snowflake et Power BI pour optimiser la connexion et les performances

Attention : ce qui s’appelle “Entrepôt” dans l’écran de connexion de Fabric / Power BI correspond au warehouse Snowflake.

Il vous faudra ensuite vous connecter via votre identifiant Microsoft.

Une fois votre identification validée, si l’utilisateur Microsoft est reconnu par le “Security integration” de Snowflake préalablement configuré, alors la connexion est établie. Elle pourra être utilisée pour rafraîchir un modèle sémantique ou pour copier des données dans une activité de copie d’un pipeline sans nouvelle demande d’authentification de type MFA.

On rappelle que la correspondance se fait soit entre le nom de l’utilisateur Microsoft et soit le “login_name” Snowflake, soit “l’adresse mail” renseignée pour l’utilisateur.

Une fois l'utilisateur authentifié, un token OAuth est généré et utilisé par Power BI pour se connecter à Snowflake, sans nécessité de re-demander l'authentification pendant une période déterminée.

Dans Power BI, vous pouvez donc modifier les paramètres de configuration de la connexion en activant l’option SSO

Bonnes pratiques Snowflake et Power BI pour optimiser la connexion et les performances

Limites d’utilisations :

Malheureusement pour le moment, les SPN (service principal names) ne peuvent pas être utilisés dans cet écran de connexion.

Nous espérons qu’à terme, les services principaux Microsoft puissent être utilisés nativement dans les écrans de connexions Power BI et être rapprochés d’un utilisateur Snowflake de type SERVICE. En effet, l’utilisation d’un utilisateur de type UPN (User Principal Name) pour les tâches planifiées n’est pas une bonne pratique mais semble être à ce jour la seule possibilité de s’affranchir d’un mot de passe “Snowflake”.

3/ Configuration de l'authentification multifacteur (MFA)

En premier temps, il faut toujours configurer une intégration de sécurité Power BI dans Snowflake. Cela se fait à l’aide de la commande SQL CREATE SECURITY INTEGRATION comme vu au-dessus, avec une connexion OAuth standard. Le flux de travail est similaire à l’OAuth standard, mais il peut inclure des configurations supplémentaires qu’on doit ajouter :

  • EXTERNAL_OAUTH_USER_MFA = TRUE : Ce paramètre supplémentaire permet d'activer la Multi-Factor Authentication (MFA) pour les utilisateurs lors de l'authentification via SSO.

L'activation de la MFA ajoute un niveau de sécurité supplémentaire. Lorsqu'un utilisateur tente de se connecter à Power BI via SSO, après l'authentification primaire auprès de Microsoft Entra ID, il est invité à fournir un deuxième facteur de validation, comme un code envoyé via Duo Mobile. Cela renforce la sécurité en exigeant une preuve supplémentaire de l'identité de l'utilisateur.

Bon à savoir

Dans le cadre de l'authentification SSO, le paramètre TYPE = PERSON doit être utilisé pour indiquer que l'authentification est destinée à un utilisateur humain, au contraire d' une connexion automatisée ou de service (type = service). Cela est crucial dans le cadre de la MFA, car les facteurs d'authentification supplémentaires, comme la validation par une application mobile (Duo Mobile, OKta, etc), ne sont généralement pas compatibles avec des processus automatisés. En définissant l'intégration avec le type PERSON, Snowflake s'assure que la sécurité renforcée par la MFA sera appliquée à la session de l'utilisateur.

Pour compléter la configuration de l'authentification et garantir une connexion sécurisée entre Power BI et Snowflake, on doit bien comprendre les mécanismes d'importation des données. Deux méthodes courantes sont disponibles pour interagir avec les données de Snowflake depuis Power BI : Data Import et DirectQuery.

Data Import ou DirectQuery ?

Power BI propose deux modes principaux pour interagir avec les données de Snowflake : Import et DirectQuery. Chacun de ces modes présente des avantages et des limitations en fonction des besoins de performance, de flexibilité et de sécurité des utilisateurs.

Power BI propose un connecteur direct avec Snowflake dans Power BI Desktop, sur la barre de recherche où nous trouvons l’option « Obtenir des données » avec tous les connecteurs :

Bonnes pratiques Snowflake et Power BI pour optimiser la connexion et les performances

Ensuite, nous nous connectons avec le serveur et la base de données dans Snowflake en remplissant les identifiants de Snowflake :

Bonnes pratiques Snowflake et Power BI pour optimiser la connexion et les performances

Pour retrouver les informations du serveur, nous pouvons aller sur notre compte Snowflake et utiliser l’option « Copy account URL » comme présenté ci-dessous :

Bonnes pratiques Snowflake et Power BI pour optimiser la connexion et les performances

« Options avancées » nous permet de rajouter les spécifications plus détaillées sur la connexion. Avec l’option « Instruction SQL », nous pouvons par exemple indiquer les colonnes précises que nous voulons inclure :

Bonnes pratiques Snowflake et Power BI pour optimiser la connexion et les performances

Ensuite une fenêtre de connexion de Snowflake va s’ouvrir et nous demander les identifiants de notre compte Snowflake :

Bonnes pratiques Snowflake et Power BI pour optimiser la connexion et les performances

Lorsque nous nous connectons à Snowflake dans Power BI, nous avons 2 possibilités d’importation des données :

  • Import
  • Direct Query

Bonnes pratiques Snowflake et Power BI pour optimiser la connexion et les performances

1/ Import

C’est le mode de connexion par défaut. Il est basé sur «VertiPaq Storage Engine», un moteur de stockage qui crée la connexion entre la source de données et Power BI. L’idée derrière ce mode est de copier les données se trouvant dans Snowflake dans un modèle de données porté par Power BI. À chaque actualisation ou rafraîchissement des données, ce modèle est mis à jour et écrase les anciennes données par les nouvelles.

AvantagesLimitations
Performance accrue :
En stockant les données, le temps de traitement est réduit, ce qui diminue également le temps des calculs et d’affichage des visuels.
Limitations de volume :
Le fait de stocker toutes les données n’est pas la meilleure solution avec des bases de données volumineuses car cela peut prolonger le temps de rafraîchissement des rapports BI. Ce retard peut, dans certains cas, conduire à des erreurs de timeout.
De plus, la taille des datasets peut être limitée aussi par les types de licences de Power BI.
Power Query :
Accès à Power Query pour nettoyer et transformer les données.
Capacité de stockage :
Il faut tenir compte des limitations liées à la taille des modèles ou à la capacité de stockage maximale selon les options de licence.

2/ Direct Query

C’est le deuxième mode de connexion qui suit quant à lui une logique à l’inverse de Import. Ici, chaque fois qu’on veut afficher les données, une requête va partir vers Snowflake et retourner les résultats dans Power BI.

AvantagesLimitations
Temps réel :
Le plus grand avantage de cette méthode est la capacité de refléter les données et de tirer les analyses en temps « presque » réel.
La compatibilité :
Ce mode de connexion est compatible avec Snowflake mais pas forcément avec toutes les sources de données.
Sécurisation :
Il permet l'application des règles de sécurité au niveau de la source de données. Les polices de sécurité niveau lignes et colonnes peuvent donc être portées directement par Snowflake. La connexion Snowflake peut se faire avec l’utilisateur visualisant les données, plutôt que via un utilisateur technique qui porte la connexion.
Dépendance :
La performance des rapports est dépendante de la base de données.
Aussi, les données n’étant pas stockées dans Power BI, et donc les requêtes étant jouées directement sur Snowflake, les coûts d’exécution de Snowflake sont à prendre en compte.
Capacité de stockage :
Il n’y a aucune limite pour la taille de dataset.
Limites fonctionnelles :
Limitations sur l'utilisation de DAX / Power Query.

Optimisation de la modélisation du rapport

Il est important de noter que la manière de modéliser influence la performance de Power BI. À l'instar de nombreux autres outils BI, Power BI est optimisé pour fonctionner avec des modèles dimensionnels et des schémas en étoile (Star Schema) plutôt que d'utiliser des tables trop dénormalisées ou trop volumineuses.  
 

Dans les bonnes pratiques, la plupart du travail de modélisation de données et de calcul statique doit être effectué en amont dans Snowflake, plutôt que dans Power BI pour des raisons de performances et de réutilisation de la donnée.
Afin d’optimiser les requêtes il est impératif de suivre quelques règles que vous connaissez plus ou moins si vous travaillez déjà avec Power BI. Nous allons classer ces règles en deux catégories (générales et recommandées par Snowflake) :

Règles générales :

  • Optimiser les requêtes, comme par exemple éviter les SELECT *. Il est important d'inclure uniquement les tables, lignes et colonnes nécessaires pour répondre aux demandes, afin d'optimiser la mémoire et les performances.
  • Utiliser les fonctions SQL intégrées de Snowflake (par exemple, TRIM, SUBSTRING, REGEXP) pour la transformation des données au lieu de Power Query. Idéalement, toutes les transformations devront être réalisées dans Snowflake. Cela inclut la création de tables de faits et de tables de dimensions appropriées.

Règles recommandées par Snowflake :

  • Utiliser les fonctionnalités de profilage et d'historique des requêtes de Snowflake pour identifier et optimiser les requêtes
  • Exploiter la mise en cache des requêtes de Snowflake pour stocker et réutiliser les résultats des requêtes fréquemment exécutées
  • Utiliser des vues matérialisées qui nous aident à accélérer l'accès aux données fréquemment interrogées. Cela peut considérablement améliorer les performances de nos rapports Power BI. Par exemple, si nous avons une table de dimension de ventes avec des millions de lignes, nous pouvons créer une vue matérialisée qui calcule et stocke le total des ventes par mois. Alors, au lieu de recalculer ces totaux à chaque fois qu'un rapport Power BI est consulté, la vue matérialisée fournira rapidement ces données pré-calculées, améliorant ainsi le temps de réponse des requêtes et donc celui des rapports. Attention, une vue matérialisée comporte certaines limitations, et est entièrement managée en tâche de fond par Snowflake : les coûts supplémentaires sont importants à évaluer par rapport aux cas d’utilisation.
  • D’autres fonctions comme les Search Optimization Service ou la Query Acceleration Service peuvent aussi améliorer les performances. De la même façon que les vues matérialisées, des coûts supplémentaires sont à évaluer par rapport aux cas d’utilisation

Conclusion

Le choix du mode de connexion entre Power BI et Snowflake dépend largement des exigences de sécurité, de performance et de gestion des données. En résumé :

  • Connexion à Facteur Unique (Utilisateur et Mot de Passe) : Bien que simple et rapide à mettre en place, cette méthode est de plus en plus obsolète en raison des risques de sécurité.
  • Connexion OAuth : Ce mode est plus sécurisé car il permet l'authentification sans transmission de mots de passe via un jeton d'accès OAuth. Idéal pour les utilisateurs techniques ou les applications automatisées.
  • Connexion Single Sign-On (SSO) : C’est la méthode la plus sécurisée et la plus intégrée dans les environnements d’entreprise. Elle repose sur un fournisseur d’identité et peut être couplée avec la MFA pour une sécurité renforcée.
  • Quoi qu’il arrive, les connexions en “User / Password” uniquement sont à proscrire du fait d’une sécurité pas assez importante.

Il est aussi important de considérer la manière dont les données sont chargées dans Power BI, soit en DirectQuery ou en Import. Pour des rapports nécessitant des mises à jour fréquentes mais avec des volumes de données gérables, Import est souvent préféré. En revanche, pour des scénarios où la fraîcheur des données est plus importante, DirectQuery constitue la meilleure option, bien que son impact sur la performance et les coûts d'exécution doit être suivi attentivement.

Vous souhaitez bénéficier d'experts, de développeurs, ou d'une formation sur Snowflake ou Power BI ? Rendez-vous sur la page Contact.