Connecting Power BI to ClickHouse

How Power BI connects to a self-hosted ClickHouse cluster

A BI team that needs dashboards over event or telemetry data it hosts itself has to decide how Power BI reaches the cluster. Power BI connects to ClickHouse through the ClickHouse ODBC driver and the built-in ClickHouse connector, and when the cluster sits on premises, a data gateway is placed between the Power BI service and the cluster. The ClickHouse docs state that the setup installs the ODBC driver on the machine running Power BI Desktop, so that driver is the first component to confirm on every workstation and gateway host. The connector is available by default from Power BI version 2.137.751.0, so teams should check their Power BI Desktop build against that version before planning around the built-in connector.

Both ClickHouse Cloud and self-managed deployments are supported sources, so the connection steps are largely the same whichever you run. What changes for a self-hosted cluster is the network path: the workstation, the gateway and the ClickHouse HTTP interface must be able to reach each other, and the account Power BI uses must be a dedicated, read-only database user rather than the default user. Confirm with your network team which hosts can reach the ClickHouse HTTP port, whether TLS is required between the gateway and the cluster, and where the gateway service account will run. These answers decide whether the gateway can sit inside the same network segment as the cluster or needs a documented firewall rule.

Before building reports, run a short proof of connection from Power BI Desktop against a pre-production cluster with representative tables, then repeat it from the gateway host using the same credentials. Record the Power BI Desktop build, the ODBC driver version and the gateway version, because a failure after an upgrade is much easier to trace when those versions are known. Ask the vendor or your partner to confirm the current connector and driver versions for your environment, since the supplied facts only establish the 2.137.751.0 default availability threshold. The ClickHouse Power BI guide walks through the installation steps and is the next read for the driver setup.

What you need before the first connection

Before Power BI can read from a self-hosted ClickHouse cluster, four things have to line up: the Power BI build, the driver on every machine that opens the report, a read-only database account, and a network path that the client or gateway can use over TLS. Each card below is a check to complete before the first connection test, not after the first failed refresh.

Power BI Desktop version

The ClickHouse connector is available by default from Power BI version 2.137.751.0. Check the build number under Help, then About in Power BI Desktop, because older installs will not show the built-in connector. If you cannot upgrade, the ODBC route still works, so confirm which path your report author will use. Also confirm that the version your gateway runs supports the same connector before you publish.

ClickHouse ODBC driver on each machine

The setup installs the ClickHouse ODBC driver, and that driver must be present on every workstation that opens the report and on the gateway host. Match the driver bitness to the Power BI Desktop build (most current installs are 64-bit), and confirm with the driver vendor which bitness and version pair they support. Record the driver version in your deployment notes so a later upgrade does not break existing reports.

Read-only ClickHouse user

Create a dedicated user for Power BI with only SELECT rights on the databases and tables the report needs. ClickHouse access control uses users, roles and row policies, and the documentation recommends restricting the default user, which starts with all permissions. Do not reuse an administrator account in the connection string, because the credentials are stored in the report and on the gateway. Ask who will rotate that password and how.

Network path to the cluster

Decide where the connection originates. Power BI Desktop connects from the analyst workstation, while the Power BI Service reaches an on-premises cluster only through an on-premises data gateway installed inside your network. Confirm that the chosen host can reach the ClickHouse HTTP or HTTPS port through firewalls and any load balancer in front of the cluster. Ask your network team to document the route, not just the port.

TLS certificate trust

If the cluster serves HTTPS, every client and gateway host must trust the certificate chain, including any internal certificate authority. Check that the certificate name matches the host name in the connection settings, and that the chain is installed in the trust store used by the driver on that machine. Certificate expiry is a common cause of refresh failures, so record the expiry date and the owner who renews it.

Gateway and driver compatibility

Gateway versions and ODBC driver versions do not always move together, and vendor support statements change between releases. Before you deploy, confirm with Microsoft and the ClickHouse driver maintainers which gateway release and driver build you should pair, and whether the built-in connector behaves the same on the gateway as in Desktop. Test one refresh end to end on the exact gateway build you plan to run in production.

Configuring the connection in Power BI Desktop

This sequence takes you from a clean Power BI Desktop installation to a working connection to a self-hosted ClickHouse cluster, with a sample table loaded and a published report. Each step ends with a check so that a failure is caught at the step where it occurs.

Confirm the Power BI Desktop version
Start by checking the version of Power BI Desktop on the analyst workstation. The ClickHouse connector is available by default from Power BI version 2.137.751.0, according to the ClickHouse Power BI guide, so older builds need to be updated before the built-in connector can be used. Open Help, then About, and record the version number. You are done when the version is equal to or later than 2.137.751.0, or when you have decided to use the ODBC route instead and recorded that choice for the team.
Install the ClickHouse ODBC driver
Install the ClickHouse ODBC driver on the same machine as Power BI Desktop, using the installer and version that match your operating system and your ClickHouse server release. The ClickHouse Power BI guide describes the driver as part of the setup. Confirm the driver name in the ODBC Data Source Administrator, under the Drivers tab, before you continue. You are done when the driver appears in the list with the expected name and version.
Create a DSN or choose the built-in connector
Decide between a system DSN, which stores the connection settings for ODBC-based access, and the built-in ClickHouse connector in Power BI Desktop. In the ODBC Data Source Administrator, add a System DSN that points to the ClickHouse driver, or in Power BI select Get data and search for ClickHouse. Name the DSN consistently so that other analysts can recognize it. You are done when the DSN or connector entry exists and is visible in the list of data sources.
Enter host, port, database and credentials
Enter the hostname of the ClickHouse server or load balancer, the HTTP or native port your cluster exposes, the target database, and the credentials for a dedicated read-only user. Confirm the port and whether TLS is required with your cluster administrator, since these depend on how your deployment is configured. Avoid using the default user, which starts with all permissions. You are done when the form accepts the values and the credentials are stored only in the connection dialog, not in a shared file.
Test the connection and preview a sample table
Run Test connection in the dialog, then navigate to a small, known table in the database. Preview the table in the Navigator and compare the row count against a query your cluster administrator runs directly on the server, such as a count on the same table. A matching count confirms that the connection, the database selection and the user privileges are correct. You are done when the test succeeds and the preview shows the expected row count and column types.
Choose import or DirectQuery mode
In the Load step, choose Import to copy the selected data into the Power BI model, or DirectQuery to send queries to ClickHouse as the report is used. Import suits smaller, stable datasets and report designs that need the full Power BI feature set, while DirectQuery keeps the data in the cluster and sends each interaction to it, which places continuous load on the server. Confirm the choice with the data owner and the cluster team before you load large tables. You are done when the model loads without errors and the chosen mode is recorded in the report notes.
Publish the report and verify the service connection
Publish the report from Power BI Desktop to the Power BI Service workspace. For an on-premises ClickHouse cluster, the Service must reach the cluster through an on-premises data gateway, so confirm gateway placement with your network team before you publish. Configure the data source credentials in the gateway settings, then refresh or open the report in the Service. You are done when the dataset refresh or DirectQuery visual completes successfully and returns the same row count you saw in Desktop.

Import or DirectQuery for ClickHouse datasets

In Import mode, Power BI runs a query against ClickHouse during a scheduled refresh and stores the result in its in-memory model. Report visuals then run against that copy, so interaction stays fast and does not touch the cluster. The trade-off is that the data is only as current as the last refresh, and each refresh adds query load on the cluster at a time you choose. This mode suits smaller curated extracts, such as daily aggregates or a summarized slice of an event table, where the model fits comfortably in memory.

Refresh window: Schedule refreshes outside peak ingestion and query hours, and confirm how long a full refresh takes against your data volume.
Model size: Import stores every loaded row in the model, so large raw event tables can exhaust capacity limits quickly.
Aggregation first: Pre-aggregate in SQL, or in a ClickHouse materialized view, so the refresh reads fewer rows.
Incremental refresh: Confirm whether your Power BI license and tenant settings support incremental refresh on this source before designing around it.
Failure handling: A failed refresh leaves yesterday's data in place, so decide who is alerted and how stale reports are labeled.

Before you commit, confirm the refresh duration and peak memory on a representative extract, and check the connector behavior with your ClickHouse version.",

Where the on-premises gateway belongs

A Power BI Service refresh or DirectQuery connection to a ClickHouse cluster inside your network needs an on-premises data gateway, and that gateway belongs on a host that can reach the ClickHouse HTTP or native port and trust the certificates the cluster presents. Power BI Desktop talks to the cluster directly from an analyst's workstation, but the Service cannot reach private addresses on its own. Place the gateway in the same network segment as the cluster or in a segment with a firewall rule that allows only the required ports from the gateway to the ClickHouse nodes. Confirm which port your ODBC connection uses, whether TLS is enforced on it, and that the certificate chain is installed in the trust store of the gateway service account, not only in your own user profile.

Size the gateway host for the query work it will carry, not for the number of reports. Each refresh or DirectQuery request consumes gateway memory and CPU while data moves through it, and large import refreshes hold data in transit longer than small visual queries do. Keep the gateway on dedicated hardware or a dedicated virtual machine, away from the ClickHouse servers, so that a heavy refresh does not compete with merges and replication on the cluster. For availability, ask whether a gateway cluster with more than one member is needed for your refresh schedule. Confirm current sizing guidance with Microsoft's gateway documentation and your own tests, since this page does not supply specific hardware figures.

Before go-live, confirm two versions on the gateway host: the on-premises data gateway release and the ClickHouse ODBC driver installed there. The connector is available by default from Power BI version 2.137.751.0, but the gateway and driver must be checked separately because the gateway runs the connection in its own service context. Record both versions in your change log, and repeat the check after every gateway or driver update. Test a scheduled refresh end to end from the Service, not only a manual refresh in Desktop, because the two paths use different credentials and network routes.

Mapping ClickHouse types and identities to Power BI

This table compares how ClickHouse column types, database users and row policies translate into what Power BI shows its report authors and viewers. Read each row as a checkpoint to confirm in a test model before you build reports. The main takeaway is that ClickHouse decides which rows leave the server, while Power BI row-level security only decides what a viewer sees inside the model.

Item	ClickHouse side	Power BI side	What to confirm
DateTime and DateTime64	Stored with second or sub-second precision and an optional time zone	Mapped to a date/time type, and precision beyond what the driver returns may be lost	Compare sample values after import and check the time zone the ODBC driver applies
Decimal	Fixed precision and scale chosen when the column is created	Usually mapped to a decimal or fixed number type, which can change rounding in measures	Check totals in a test visual against a direct ClickHouse query
Nullable columns	Null is an explicit state tracked per column	Null values arrive as blanks, which can change averages and counts in DAX	Decide whether blanks should become zero or be excluded in the model, and document the choice
LowCardinality and Enum	Compression and dictionary types that store repeated text efficiently	Usually returned as plain text or integers, so the encoding is invisible in the model	Confirm the driver returns the labels you expect, not raw codes, for filter columns
Database users	A ClickHouse user with grants on specific databases and tables	The credential stored in the data source or gateway, shared by every report using it	Use a read-only user per report environment and confirm which tables it can see
Roles	Named sets of grants assigned to users, managed through SQL	No direct equivalent in Power BI, so the role must be mapped to a data source credential or a workspace	List which roles map to which report audience before building the data source
Row policies	Filters applied on the server before rows are returned to the client	Rows that the policy removes never reach the Power BI model	Test each user with a query in ClickHouse and in Power BI to confirm the same row counts
Power BI row-level security	Not part of ClickHouse; it filters rows inside the imported or queried model	Defined with roles and DAX filters in the semantic model and assigned to users in the Power BI service	Design it to match the ClickHouse row policies, since the two are maintained separately

Securing the ClickHouse user behind the report

A Power BI connection is only as narrow as the database account behind it. The cards below cover the decisions that shape that account, from replacing the default user to limiting which networks can reach the cluster. Work through them together before the gateway goes live, because each one changes what the report can reach if credentials leak.

Replace the default user

The default user starts with all permissions, so a Power BI connection that uses it inherits far more access than a dashboard needs. Create a dedicated user for the report and restrict or disable the default account. Confirm in your environment that no other service, script or analyst is still signing in as default before you change it. Keep the report user separate from administrative accounts so that rotating one does not affect the other.

Scope roles to report tables

Grant the Power BI user read access only to the databases and tables that the semantic model actually queries. Build a role for that set, assign it to the user, and avoid granting wildcard privileges on the whole cluster. When a new report needs another table, add a grant deliberately rather than widening the role. Review the grants whenever the model changes, because Power BI will query whatever the account is allowed to see.

Filter rows with row policies

Where different business units or regions should see different rows through the same report, use row policies on the underlying tables instead of building separate reports for each audience. A row policy applies to the user or role it is attached to, so the filter holds even when a dashboard author removes a slicer. Test each policy with the report user itself, and confirm that the aggregated visuals match the expected row counts for each group.

Encrypt the connection with TLS

Credentials and result sets travel between the gateway or Power BI Desktop and the cluster, so the connection should use TLS. Configure the server with a valid certificate, enable the secure HTTP port, and make the ODBC data source require encrypted transport. Ask your network team to confirm which certificate authority the client machines and gateway hosts trust, since a self-signed certificate that is not installed on the gateway will fail at refresh time.

Store credentials in the gateway

For scheduled refresh through an on-premises data gateway, the report user's password is stored by the gateway service, not in the published dataset. Limit who can administer the gateway and who can edit its data source credentials. Use a password that is rotated on a schedule your security team sets, and plan the update in the gateway admin page so refreshes do not fail silently after rotation. Never embed the password in a shared .pbix file.

Limit runaway queries with quotas

A single interactive visual with a wide date range can scan far more data than expected, and a scheduled refresh can compete with it. Settings profiles and quotas let you cap resource use and query volume for the report user. Agree the limits with the people who own the dashboards, test them against the heaviest report page, and adjust them before users report timeouts. Quota values are environment specific, so confirm them with your own load testing rather than copying figures from another cluster.

Choose the authentication method

Decide whether the report user is a local ClickHouse account or is verified by an external authenticator such as LDAP, Kerberos, SSL X.509 certificates, an HTTP authenticator or JWT. External options centralize password policy and offboarding, but they add dependencies that the gateway must reach at refresh time. Ask your identity team which option already governs service accounts, and confirm with the ClickHouse documentation that the Power BI driver path supports it before committing.

Restrict network reachability

Only the Power BI gateway hosts, the Power BI Desktop machines that build reports, and the monitoring tools should reach the ClickHouse HTTP and native ports. Enforce this with firewall rules, security groups or a network policy, and keep the cluster off public addresses. Review the allowed source list whenever gateway hosts change, and make sure the cluster's listen settings match the network design so that an accidental bind to all interfaces is caught in testing.

Common Power BI to ClickHouse connection failures

This table lists the failures teams most often hit when Power BI reaches a self-hosted ClickHouse cluster, with the likely cause and the first fix to try for each. Work down the rows in order, since driver and network problems block every later step.

Failure	Likely cause	First fix to try
Driver bitness mismatch	The ODBC driver is installed in one bitness (32 or 64 bit) but Power BI Desktop or the gateway runs the other, so the data source is not found	Install the ClickHouse ODBC driver matching the bitness of Power BI Desktop and the gateway service, then recreate the DSN in the matching ODBC Administrator
Certificate trust error	The cluster uses a TLS certificate issued by an internal CA that the Desktop or gateway host does not trust	Import the internal root and intermediate CA into the Windows trusted store on that host, then confirm the certificate name matches the host used in the connection
Gateway cannot reach the cluster	The on-premises gateway host lacks a route, firewall rule or DNS entry for the ClickHouse HTTP or native port	Test the port from the gateway host with a plain network check, then open the required port from the gateway subnet only
DirectQuery timeouts on large tables	Visuals send aggregations that scan large unsorted tables, or several visuals fire queries at once	Reduce visual count, add filters on the table's ordering key, and move heavy aggregations to a materialized view or rollup table
Unsupported data types	Some ClickHouse types such as nested, map or certain Nullable and enum forms do not map cleanly to Power BI column types	Cast problem columns to a basic type (string, integer, decimal, date) in a view and point the report at the view instead of the base table
Refresh failure after credential rotation	The gateway data source still holds the old database password or key, so scheduled refreshes fail while Desktop still works	Update the credentials in the gateway data source settings in Power BI Service, then run a manual refresh to confirm
Import model cannot be published	The Desktop file exceeds the size limits or uses an unsupported connection mode for the workspace	Confirm the workspace capacity and file size, then reduce imported columns or switch heavy tables to DirectQuery where the cluster can serve them

Questions teams ask before connecting Power BI to ClickHouse

How much effort and time does a Power BI to ClickHouse connection take?

The driver installation and first connection are usually the small part. Effort grows with the number of reports, the size of the tables they read, how many gateways and networks the traffic crosses, and how long your security review takes. Installing the ClickHouse ODBC driver on a workstation or gateway is a short task, while designing views, row-level access, refresh schedules and gateway placement for several published reports takes longer. Ask any partner for a quote built from your report count, largest table, network zones and approval steps, and ask which items are one-time setup and which recur, such as driver updates and gateway patching.

Can our own team connect Power BI to self-hosted ClickHouse without outside help?

Yes, a team with Windows administration, ODBC experience and some ClickHouse knowledge can usually make a first connection without a partner. The gaps tend to appear later: choosing import or DirectQuery for large tables, placing an on-premises data gateway between Power BI Service and a cluster that sits behind your firewall, creating read-only database users, and getting the security team to approve the traffic paths. An outside partner adds most value on those design decisions and on documenting the setup so it survives staff changes. Your team can also do the work with a partner reviewing the design.

What does the integration need from our environment?

Plan for four inputs before work starts: a network path from the machines that run Power BI Desktop and the gateway to the ClickHouse HTTP or HTTPS port, a read-only ClickHouse user with access only to the databases and tables reports need, accounts for the people who publish and refresh reports, and a test cluster with representative data. The test cluster matters because queries that feel fast on a small sample can time out or overload production once reports refresh on a schedule. Confirm the exact ports, TLS requirements and gateway version against the ClickHouse Power BI guide and your own environment before you commit to a design.

Should we use import or DirectQuery for ClickHouse data in Power BI?

Import is usually the better starting point for smaller, aggregated datasets that can refresh on a schedule, because Power BI then serves visuals from its own in-memory model. DirectQuery suits large tables where data must be current and where ClickHouse can answer the query quickly, but every visual sends a query to the cluster, so concurrency and query design matter. Many teams combine the two, importing summary tables and using DirectQuery only for detail views. Test both modes on your largest table and watch cluster load during refresh and peak viewing hours before choosing.

What drives the cost of connecting Power BI to ClickHouse?

The main cost drivers are the number of reports and datasets, table size and query complexity, how many people use the reports at once, where the on-premises gateway runs, and how much security review the connection needs. Costs split into one-time work such as design, configuration, testing and documentation, and recurring work such as driver and gateway updates, refresh monitoring and changes when schemas evolve. Licensing for Power BI itself is set by Microsoft and depends on your agreement, so confirm it separately. When requesting a quote, ask for the scope list, the assumptions about data volume and users, and the handover material.

Does connecting Power BI to ClickHouse Cloud differ from connecting to a self-hosted cluster?

The Power BI side is largely the same, since the ClickHouse Power BI guide covers both ClickHouse Cloud and self-managed deployments and uses the ODBC driver in either case. The differences lie in network reach and responsibility. A managed service is reachable over the public internet, while a self-hosted cluster often sits in a private network that requires a gateway, firewall rules or VPN access. With self-hosting, your team also owns TLS certificates, user administration, upgrades and backups. Confirm which of these applies to your environment before estimating the work.

What security reviews should we expect before exposing ClickHouse data to Power BI?

Expect reviews of the network path between Power BI Service, the gateway and the cluster, the credentials Power BI stores, the users and roles that reports run under, and whether row-level restrictions match your access model. Security teams usually ask for encrypted connections, a dedicated read-only account instead of the default user, and clear logging of who queries what. ClickHouse access control supports users, roles, row policies and settings profiles, and its documentation recommends restricting the default user, which starts with all permissions. Ask your partner to map these controls to your policies before the review begins, and confirm the specifics against the ClickHouse documentation for your version.

Testing the report before users depend on it

Test the Power BI connection on a pre-production ClickHouse cluster loaded with representative data and the queries your reports will actually run, before any business user relies on the numbers. The first check is reconciliation. For each report visual, run the equivalent SQL directly against ClickHouse through a client you already trust, then compare row counts, sums, distinct counts and date-bucketed aggregates against what Power BI shows. Pay attention to type mappings, time zones on DateTime columns, and how Nullable values and empty strings are treated, because mismatches here usually come from the driver or the model rather than the data itself. Record the results so the same comparison can be rerun after every driver or cluster change.

Next, test refresh and DirectQuery under the concurrency you expect in production, not the single-user load of a developer's laptop. For import models, time a full refresh and an incremental refresh against the pre-production cluster, and confirm the refresh finishes inside the window your users need. For DirectQuery, open the report with several sessions at once, apply typical filters and watch the query log and cluster resource usage in ClickHouse. Confirm which Power BI version and ODBC driver version each test ran against, because the connector is built into Power BI from version 2.137.751.0 and the driver is installed separately. Ask your ClickHouse team to confirm which of the supported versions your cluster runs.

Then check permission boundaries with a non-admin ClickHouse user that has only the grants the report needs, ideally a read-only user with row policies applied to the tables. Verify that the report shows only the rows that user should see, that write statements fail, and that the connection cannot reach databases outside its scope. Finally, write the handover record: the server and port, the database and user names (never passwords), the driver and Power BI versions, the gateway machine and its service account, and the named gateway owner who will apply patches and respond to refresh failures. Keep the record with the report so the next owner can rebuild the connection without guessing.

How DeployOpen scopes and delivers a Power BI connection

DeployOpen plans and implements the Power BI connection to your self-hosted ClickHouse cluster inside your own infrastructure. Engagements are fixed-scope, so the connection path, gateway placement, query model and handover are agreed in writing before any build work starts.

How we scope

Written scope names your ClickHouse endpoints, Power BI Desktop and Service versions, and the ODBC driver version to install.
Scope records whether reports use import or DirectQuery, with a dataset list and refresh expectations for each.
Scope states whether an on-premises data gateway is required and which network segment it will run in.

How we build

Install and configure the ClickHouse ODBC driver on Power BI hosts, and confirm the connector behavior against your Power BI build.
Create read-only ClickHouse users and roles for report access, with row policies where data must be filtered by audience.
Configure the gateway, its service account and TLS settings, then test refresh and DirectQuery paths against a non-production cluster.

How we hand over

Deliver a connection runbook covering driver versions, gateway configuration, credential rotation and dataset ownership.
Document the report query patterns tested, with notes on which tables suit import and which need DirectQuery.
Walk your analysts and platform engineers through troubleshooting steps for refresh failures and slow visuals.

How we support

Ongoing support, upgrades and testing of the connection are available as a retainer after handover.
Retainer work can cover driver updates and Power BI release checks against your cluster version and gateway.
Ask for a quote based on the written scope, including who maintains the gateway and who handles access requests.