Skip to main content

Power BI

Microsoft Power BI can query or load into memory data from ClickHouse 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:

Prerequisites​

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

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​

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​

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​

Download the most recent ClickHouse ODBC release.

Execute the supplied .msi installer and follow the wizard.

Installing the ODBC driver
Note

Debug symbols are optional and not required

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)".

Creating a new ODBC Data Source

Verify the ClickHouse Driver is listed.

Verify ODBC existence

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

Getting started with Power BI Desktop

Search for "ClickHouse"

Choosing the data source

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
  • Data Connectivity mode - DirectQuery
Filling ClickHouse instance information
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
Username and password prompt

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.

Navigator view

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


Power BI Service​

In order to use Microsoft Power BI Service, you need to create an on-premise data gateway.

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.

ODBC Driver (Import Only)​

We recommend using the ClickHouse Connector that uses DirectQuery.

Install the ODBC Driver onto the on-premise data gateway instance and verify as outlined above.

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)".

Creating a new ODBC Data Source

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

Adding a new User DSN

Choose the Unicode version of the ODBC driver.

Choosing Unicode Version

Fill in the connection details.

Connection Details
Note

If you are 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​

In case you don't have Power BI installed yet, download and install Power BI Desktop.

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

Getting started with Power BI Desktop

Select "Other" -> "ODBC".

Data Sources menu

Select your previously created data source from the list.

Select ODBC Data Source
Note

If you did not specify credentials during the data source creation, you will be prompted to specify username and password.

Navigator view

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.

Navigator view

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

Known Limitations​

UInt64​

Unsigned integer types such as UInt64 or bigger won't be loaded into the dataset automatically, as Int64 is the maximum whole number type support by Power BI.

Note

To import the data properly, before hitting the "Load" button in the Navigator, click "Transform Data" first.

In this example, pageviews table has a UInt64 column, which is recognized as "Binary" by default. "Transform Data" opens Power Query Editor, where we can reassign the type of the column, setting it as, for example, Text.

Navigator view

Once finished, click "Close & Apply" in the top left corner, and proceed with loading the data.