Power BI

Microsoft Power BI peut interroger les données de ClickHouse Cloud ou d’un déploiement autogéré, ou les charger en mémoire.

Il existe plusieurs versions de Power BI que vous pouvez utiliser pour visualiser vos données :

• Power BI Desktop : une application de bureau Windows pour créer des tableaux de bord et des visualisations
• Power BI Service : disponible dans Azure sous forme de SaaS pour héberger les tableaux de bord créés avec Power BI Desktop

Power BI nécessite que vous créiez vos tableaux de bord dans la version Desktop, puis que vous les publiiez dans Power BI Service.

Ce tutoriel vous guidera à travers les étapes suivantes :

• Installation du pilote ODBC ClickHouse
• Installation du connecteur ClickHouse Power BI dans Power BI Desktop
• Interrogation des données de ClickHouse pour les visualiser dans Power BI Desktop
• Configuration d’une passerelle de données locale pour Power BI Service

Ce tutoriel suppose que Microsoft Power BI Desktop est installé sur votre ordinateur Windows. Vous pouvez télécharger et installer Power BI Desktop ici

Nous vous recommandons de mettre Power BI à jour vers la dernière version. Le ClickHouse Connector est disponible par défaut à partir de la version 2.137.751.0.

Vous aurez besoin des informations suivantes pour vous connecter à votre instance ClickHouse :

• Nom d’hôte - ClickHouse
• Nom d’utilisateur - Identifiants de l’utilisateur
• Mot de passe - Mot de passe de l’utilisateur
• Base de données - Nom de la base de données de l’instance à laquelle vous souhaitez vous connecter

Pour commencer à interroger les données dans Power BI Desktop, vous devez suivre les étapes suivantes :

1. Installez le pilote ODBC ClickHouse
2. Recherchez le ClickHouse Connector
3. Connectez-vous à ClickHouse
4. Interrogez et visualisez vos données

Téléchargez la dernière version du pilote ODBC ClickHouse.

Exécutez le programme d’installation .msi fourni et suivez l’assistant.

Une fois l’installation du pilote terminée, vous pouvez vérifier qu’elle s’est bien déroulée comme suit :

recherchez ODBC dans le menu Démarrer, puis sélectionnez « ODBC Data Sources (64-bit) ».

Vérifiez que le pilote ClickHouse figure bien dans la liste.

Dans l’écran de démarrage de Power BI Desktop, cliquez sur « Get Data ».

Recherchez « ClickHouse »

Sélectionnez le connecteur, puis saisissez les identifiants de l’instance ClickHouse :

• Host (obligatoire) - Le domaine/l’adresse de votre instance. Veillez à l’ajouter sans préfixe ni suffixe.
• Port (obligatoire) - Le port de votre instance.
• Database - Le nom de votre base de données.
• Options - Toute option ODBC, comme indiqué sur la page GitHub ODBC de ClickHouse
• Mode de connectivité des données - DirectQuery

• Indiquez le nom d’utilisateur et le mot de passe

Enfin, vous devriez voir les bases de données et les tables dans la vue Navigator. Sélectionnez la table souhaitée et cliquez sur "Load" pour importer les données depuis ClickHouse.

Une fois l’importation terminée, vos données ClickHouse devraient être accessibles dans Power BI comme à l’accoutumée.

Pour utiliser Microsoft Power BI Service, vous devez créer une passerelle de données locale.

Pour en savoir plus sur la configuration des connecteurs personnalisés, consultez la documentation de Microsoft sur la façon d’utiliser des connecteurs de données personnalisés avec une passerelle de données locale.

Nous vous recommandons d’utiliser le ClickHouse Connector avec DirectQuery.

Installez le Pilote ODBC sur l’instance de la passerelle de données locale et vérifiez comme décrit ci-dessus.

Une fois l’installation du pilote terminée, vous pouvez créer une source de données ODBC. Recherchez ODBC dans le menu Démarrer et sélectionnez « ODBC Data Sources (64-bit) ».

Ajoutez ici un nouveau DSN utilisateur. Cliquez sur le bouton « Add » à gauche.

Choisissez la version Unicode du pilote ODBC.

Renseignez les informations de connexion.

Si vous n'avez pas encore installé Power BI, téléchargez et installez Power BI Desktop.

Sur l'écran de démarrage de Power BI Desktop, cliquez sur "Get Data".

Sélectionnez "Other" -> "ODBC".

Sélectionnez la source de données que vous avez créée précédemment dans la liste.

Enfin, vous devriez voir les bases de données et les tables dans la vue Navigator. Sélectionnez la table souhaitée, puis cliquez sur "Load" pour importer les données depuis ClickHouse.

Une fois l'importation terminée, les données ClickHouse devraient être accessibles dans Power BI comme d'habitude.

PowerBI est conçu pour les bases de données relationnelles traditionnelles avec des volumes de données modérés. Lorsque vous utilisez ClickHouse à grande échelle (des milliards de lignes), des schémas d'architecture spécifiques sont nécessaires pour obtenir des performances optimales.

PowerBI génère automatiquement des requêtes SQL avec des sous-requêtes imbriquées, des jointures complexes et des transformations à la volée. Ces schémas fonctionnent bien avec les bases de données SQL traditionnelles, mais peuvent s'avérer inefficaces lors de l'interrogation de bases de données columnaires à grande échelle comme ClickHouse.

Approche recommandée pour les grands jeux de données : au lieu d'interroger directement les tables brutes, créez des vues matérialisées dédiées dans ClickHouse pour chaque visualisation de tableau de bord. Cela vous permet de bénéficier de :

• Performances rapides et constantes, quel que soit le volume de données
• Charge réduite sur votre cluster ClickHouse
• Coûts plus prévisibles

Créez des vues matérialisées à plusieurs niveaux d’agrégation :

• Des agrégations horaires pour des tableaux de bord récents et détaillés
• Des agrégations quotidiennes pour les tendances historiques
• Des rollups mensuels pour les rapports à long terme
• Conservez les données brutes avec un TTL approprié pour des analyses ad hoc

• Définissez des clés ORDER BY adaptées à vos schémas de requêtes
• Utilisez le partitionnement pour les données de séries temporelles
• Convertissez les petites tables de dimensions en dictionnaires pour des recherches efficaces
• Tirez parti des projections pour optimiser davantage les requêtes

Les types d'entiers non signés tels que UInt64 ou supérieurs ne sont pas chargés automatiquement dans le jeu de données, car Int64 est le plus grand type de nombre entier pris en charge par Power BI.

Dans cet exemple, la table pageviews comporte une colonne UInt64, reconnue par défaut comme "Binary". "Transform Data" ouvre le Power Query Editor, où il est possible de réattribuer le type de la colonne, en la définissant par exemple comme Text.

Une fois l'opération terminée, cliquez sur "Close & Apply" dans le coin supérieur gauche, puis poursuivez le chargement des données.