Power BI

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/data-visualization/powerbi-and-clickhouse.md)

ClickHouse Supported

Microsoft Power BI can query or load into memory data from [ClickHouse Cloud](https://clickhouse.com/cloud) or a self-managed deployment.

There are several flavours of Power BI that you can use to visualise your data:

- Power BI Desktop: A Windows desktop application for creating Dashboards and Visualisations
- Power BI Service: Available within Azure as a SaaS to host the Dashboards created on Power BI Desktop

Power BI requires you to create your dashboards within the Desktop version and publish them to Power BI Service.

This tutorial will guide you through the process of:

- [Installing the ClickHouse ODBC Driver](#install-the-odbc-driver)
- [Installing the ClickHouse Power BI Connector into Power BI Desktop](#power-bi-installation)
- [Querying data from ClickHouse for visualization in Power BI Desktop](#query-and-visualise-data)
- [Setting up an on-premise data gateway for Power BI Service](#power-bi-service)

## Prerequisites[​](#prerequisites "Direct link to Prerequisites")

### Power BI Installation[​](#power-bi-installation "Direct link to Power BI Installation")

This tutorial assumes you have Microsoft Power BI Desktop installed on your Windows machine. You can download and install Power BI Desktop [here](https://www.microsoft.com/en-us/download/details.aspx?id=58494)

We recommend updating to the latest version of Power BI. The ClickHouse Connector is available by default from version `2.137.751.0`.

### Gather your ClickHouse connection details[​](#gather-your-clickhouse-connection-details "Direct link to Gather your ClickHouse connection details")

You'll need the following details for connecting to your ClickHouse instance:

- Hostname - ClickHouse
- Username - User credentials
- Password - Password of the user
- Database - Name of the database on the instance you want to connect to

## Power BI desktop[​](#power-bi-desktop "Direct link to Power BI desktop")

To get started with querying data in Power BI Desktop, you'll need to complete the following steps:

1. Install the ClickHouse ODBC Driver
2. Find the ClickHouse Connector
3. Connect to ClickHouse
4. Query and Visualize you data

### Install the ODBC Driver[​](#install-the-odbc-driver "Direct link to Install the ODBC Driver")

Download the most recent [ClickHouse ODBC release](https://github.com/ClickHouse/clickhouse-odbc/releases).

Execute the supplied `.msi` installer and follow the wizard.

  

Note

`Debug symbols` are optional and not required

#### Verify ODBC driver[​](#verify-odbc-driver "Direct link to Verify ODBC driver")

When the driver installation is completed, you can verify the installation was successful by:

Searching for ODBC in the Start menu and select "ODBC Data Sources **(64-bit)**".

  

Verify the ClickHouse Driver is listed.

  

### Find the ClickHouse Connector[​](#find-the-clickhouse-connector "Direct link to Find the ClickHouse Connector")

Note

Available in version `2.137.751.0` of Power BI Desktop

On the Power BI Desktop start screen, click "Get Data".

  

Search for "ClickHouse"

  

### Connect to ClickHouse[​](#connect-to-clickhouse "Direct link to Connect to ClickHouse")

Select the connector, and enter in the ClickHouse instance credentials:

- Host (required) - Your instance domain/address. Make sure to add it with no prefixes/suffixes.
- Port (required) - Your instance port.
- Database - Your database name.
- Options - Any ODBC option as listed
  in [ClickHouse ODBC GitHub Page](https://github.com/ClickHouse/clickhouse-odbc#configuration)
- Data Connectivity mode - DirectQuery

  

Note

We advise selecting DirectQuery for querying ClickHouse directly.

If you have a use case that has a small amount of data, you can choose import mode, and the entire data will be loaded to Power BI.

- Specify username and password

  

### Query and Visualise Data[​](#query-and-visualise-data "Direct link to Query and Visualise Data")

Finally, you should see the databases and tables in the Navigator view. Select the desired table and click "Load" to
import the data from ClickHouse.

  

Once the import is complete, your ClickHouse Data should be accessible in Power BI as usual.

  

## Power BI service[​](#power-bi-service "Direct link to Power BI service")

In order to use Microsoft Power BI Service, you need to create an [on-premise data gateway](https://learn.microsoft.com/en-us/power-bi/connect-data/service-gateway-onprem).

For more details on how to setup custom connectors, please refer to Microsoft's documentation on how to [use custom data connectors with an on-premises data gateway](https://learn.microsoft.com/en-us/power-bi/connect-data/service-gateway-custom-connectors).

## ODBC driver (import only)[​](#odbc-driver-import-only "Direct link to ODBC driver (import only)")

We recommend using the ClickHouse Connector that uses DirectQuery.

Install the [ODBC Driver](#install-the-odbc-driver) onto the on-premise data gateway instance and [verify](#verify-odbc-driver) as outlined above.

### Create a new User DSN[​](#create-a-new-user-dsn "Direct link to Create a new User DSN")

When the driver installation is complete, an ODBC data source can be created. Search for ODBC in the Start menu and select "ODBC Data Sources (64-bit)".

  

We need to add a new User DSN here. Click "Add" button on the left.

  

Choose the Unicode version of the ODBC driver.

  

Fill in the connection details.

  

Note

If you're using a deployment that has SSL enabled (e.g. ClickHouse Cloud or a self-managed instance), in the `SSLMode` field you should supply `require`.

- `Host` should always have the protocol (i.e. `http://` or `https://`) omitted.
- `Timeout` is an integer representing seconds. Default value: `30 seconds`.

### Get data into Power BI[​](#get-data-into-power-bi "Direct link to Get data into Power BI")

In case you don't have Power BI installed
yet, [download and install Power BI Desktop](https://www.microsoft.com/en-us/download/details.aspx?id=58494).

On the Power BI Desktop start screen, click "Get Data".

  

Select "Other" -> "ODBC".

  

Select your previously created data source from the list.

  

Note

If you didn't specify credentials during the data source creation, you will be prompted to specify username and password.

  

Finally, you should see the databases and tables in the Navigator view. Select the desired table and click "Load" to import the data from ClickHouse.

  

Once the import is complete, your ClickHouse Data should be accessible in Power BI as usual.

## Optimizing work with large datasets[​](#optimizing-work-with-large-datasets "Direct link to Optimizing work with large datasets")

PowerBI is designed for traditional row-based databases with moderate data volumes. When working with ClickHouse at scale (billions of rows), specific architectural patterns are required for optimal performance.

PowerBI automatically generates SQL queries with nested subqueries, complex joins, and on-the-fly transformations. These patterns work well with traditional SQL databases but can be inefficient when querying large-scale columnar databases like ClickHouse at scale.

**Recommended approach for large datasets:** Instead of querying raw tables directly, create dedicated `materialized views` in ClickHouse for each dashboard visualization. This gives you:

- Consistent, fast performance regardless of data volume
- Lower load on your ClickHouse cluster
- More predictable costs

Note

If your dashboards are slow, check ClickHouse's [`query_log`](/docs/operations/system-tables/query_log) to see what SQL queries Power BI is actually running. Common problems include nested subqueries, scanning entire tables, or inefficient joins. Once you identify the issue, create [materialized views](/docs/materialized-views) that solve those specific problems.

### Implementation best practices[​](#implementation-best-practices "Direct link to Implementation best practices")

#### Pre-aggregation strategy[​](#pre-aggregation-strategy "Direct link to Pre-aggregation strategy")