Skip to main content
Last updated on

Use metric views with external BI tools

You can query Unity Catalog metric views from external BI tools through any SQL-based connection. This page describes the connection patterns and the trade-offs between them, so you can pick the right approach for each tool. After you choose an approach, follow the linked pages for the configuration steps and limitations.

Choose an approach​

The following table describes the connection patterns for querying a metric view from a BI tool.

Pattern

When to use

Custom SQL with MEASURE()

The tool passes SQL queries to Databricks (for example, using a native query or custom SQL option). Most BI tools support this approach.

Wrapper view

You want to avoid writing SQL in the BI tool. You define a standard view that wraps the metric view with the MEASURE() logic, then point the tool at that view like any other table.

BI compatibility mode

The tool can't pass custom SQL queries to Databricks and instead generates standard aggregations (SUM, COUNT) against measure columns. Databricks rewrites the generated queries to apply the correct measure logic. See Query metric views with BI compatibility mode for supported scenarios, best practices, and limitations.

Built-in partner integration

The partner has added built-in support for metric views, so measures and dimensions appear directly in the tool without custom SQL.

Query a metric view using custom SQL​

All measure evaluations in a metric view query must use the MEASURE aggregate function (or its AGG alias in Databricks Runtime 18.1 and above). You must list each column explicitly because SELECT * is not supported. The following query is the canonical pattern and works with any BI tool that supports SQL pass-through:

SQL
SELECT
  `Order Month`,
  MEASURE(`Total Revenue`),
  MEASURE(`Order Count`)
FROM main.sales.orders_metric_view
GROUP BY ALL;

For additional query patterns, including filtering and joining metric view results with other tables, see Query metric views.

Query a metric view through a wrapper view​

If you don't want to write SQL in the BI tool, define a standard view in Unity Catalog that wraps the metric view with the MEASURE() pattern. The view embeds the measure logic, so the BI tool can query it like any other table without custom SQL. This works with any tool that can connect to a Unity Catalog table, including Power BI.

SQL
CREATE VIEW main.sales.orders_report AS
SELECT
  `Order Month`,
  MEASURE(`Total Revenue`) AS `Total Revenue`,
  MEASURE(`Order Count`) AS `Order Count`
FROM main.sales.orders_metric_view
GROUP BY ALL;

Point the BI tool at the wrapper view (for example, main.sales.orders_report) and query it directly. Because a standard view defines a fixed set of dimensions, create a wrapper view for each combination of dimensions your reports need.

Tool-specific guidance​

The following table summarizes the recommended approach for common BI tools.

Tool

Recommended approach

Power BI

Use the Databricks Power BI connector's Native query option with the MEASURE() pattern in DirectQuery mode. See Native SQL queries. BI compatibility mode is no longer available in the Power BI connector. For details and a recommended alternative, see Requirements.

Tableau

Use Tableau's Custom SQL connection option with the MEASURE() pattern, or enable BI compatibility mode from the Initial SQL field in the connection dialog.

Sigma

Use a Sigma dataset backed by custom SQL with the MEASURE() pattern.

Other JDBC or ODBC tools

Use the MEASURE() pattern through the tool's pass-through SQL option. See Work with metric view metadata using the Databricks JDBC Driver for how metric view metadata is exposed to JDBC and ODBC clients.

Built-in partner integrations​

Some partners are adding built-in support for Unity Catalog metric views so that measures and dimensions appear directly in the tool without writing custom SQL. As partners release these integrations, they are documented on the partner pages under Connect to BI partners using Partner Connect.

Next steps​

On this page