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:

• Installing the ClickHouse ODBC Driver
• Installing the ClickHouse Power BI Connector into Power BI Desktop
• Querying data from ClickHouse for visualistion in Power BI Desktop
• Setting up an on-premise data gateway for Power BI Service

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.

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

Verify the ClickHouse Driver is listed.

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

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

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

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

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

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

Select "Other" -> "ODBC".

Select your previously created data source from the list.

Note

If you did not 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.

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.

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