DirectQuery with ClickHouse in Power BI Report Server?

[Dimitri_MM]

Hi everyone,

I’m trying to use DirectQuery with a ClickHouse data source in Power BI Report Server.

• In Power BI Desktop the ClickHouse connector lets me work in DirectQuery mode without any issues.
  
• However, the same connector is not available in Power BI Report Server.
• The ClickHouse documentation (see https://clickhouse.com/docs/integrations/powerbi) suggests using an ODBC connection for the server version, but the ODBC driver only supports Import mode.
  

Because I’m dealing with several billions of rows, Import mode is not feasible for me.

Can anyone confirm whether DirectQuery with ClickHouse is possible in Power BI Report Server?
If it is supported, could you point me to the required steps or a suitable driver?
Otherwise, are there any work‑arounds to achieve near‑real‑time querying on such large datasets?

Thank you for your help!

[V-yubandi-msft]

Hi @Dimitri_MM ,

Have you had a chance to review the response @tayloramy  shared earlier. I hope it helped clarify your question. If you haven’t looked at it yet, please take a moment to check and let me know if you need any additional details.

Thank you.

[Dimitri_MM]

Hello @V-yubandi-msft 
Right now, trying to Use a Paginated Report (RDL) on PBIRS via ODBC.
I will let the community know if it had been successful.

[tayloramy]

Hi @Dimitri_MM, 

Let us know if it works well. If it does, please mark the answer here as the solution so that future community members can easily find it. 

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, please mark this as the solution. 

[tayloramy · Accepted solution]

Hi @Dimitri_MM,

DirectQuery to ClickHouse is not supported in Power BI Report Server (PBIRS) today.

PBIRS only supports DirectQuery for a specific set of built-in data sources, and ODBC in PBIRS is refresh/import only (no Live/DirectQuery). ClickHouse is not on the supported DirectQuery list, and custom connectors are not supported on PBIRS. See Microsoft’s PBIRS data source matrix for details (note ODBC shows no Live/DirectQuery, and ClickHouse is not listed) (Power BI report data sources in Power BI Report Server). The ClickHouse team also notes their ODBC path is import-only and recommends the native connector for DirectQuery, which targets Desktop + the cloud service via gateway, not PBIRS (ClickHouse Power BI integration). For history on custom connectors and PBIRS, see this Microsoft forum confirmation (Custom connectors not supported in PBIRS).

What you can do instead

1. Use a Paginated Report (RDL) on PBIRS via ODBC.
   Paginated reports execute queries at view time (effectively “live”). You can point Report Builder at the ClickHouse ODBC driver and get near-real-time results without importing billions of rows (ODBC support for Paginated Reports). This is often the best PBIRS option for very large datasets.
2. If the cloud is an option, use Power BI Service with the ClickHouse connector in DirectQuery.
   Build the report in Desktop using the official ClickHouse connector (DirectQuery) and publish to the Service with an on-premises data gateway (ClickHouse Power BI integration).
3. Stage to a PBIRS-supported DirectQuery source.
   Replicate only the required aggregates or filtered slices from ClickHouse into a source that PBIRS supports for DirectQuery (for example SQL Server, Oracle, Teradata, or PostgreSQL as listed here: PBIRS data source matrix). This keeps the PBIRS report interactive while ClickHouse remains your system of record.

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, please mark this as the solution.