Use metric views with external BI tools

You can query Unity Catalog metric views from external BI tools through any SQL-based connection. The available patterns trade off setup effort against capability, so the sections below help you select the right approach for each tool and link to the configuration steps and limitations.

Choose an approach

The following table describes the patterns for working with metric views when your existing workloads are in partner BI tools.

Pattern When to use
Import data model with Genie Code Use the /importBI skill to import your data model to Azure Databricks.
Custom SQL with MEASURE() The tool passes SQL queries to Azure Databricks (for example, using a built-in 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 Azure Databricks and instead generates standard aggregations (SUM, COUNT) against measure columns. Azure 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.

Use Genie Code to import a Tableau or Power BI file

Add a Tableau or Power BI file to Genie Code and it replicates your existing data model as a metric view. You can upload a file (.twb, .twbx, .tds, .tdsx, or .pbit) directly into the Genie Code chat, or reference a file stored in a Unity Catalog volume. For help exporting files, see Tableau export documentation or Power BI report templates.

To upload a file in Genie Code chat:

  1. Click Catalog icon..
  2. Click the name of a schema where you want to store the metric view.
  3. Click Create > Metric view.
  4. Enter a name. Then, click Create.
  5. Click the Sparkle genie code icon. Genie Code icon in the top-right corner to open Genie Code.
  6. Click New chat.
  7. Choose Import from a BI tool or type /importBI command to begin importing.
  8. Attach your file from Tableau or Power BI to the chat.

These steps explain how to import your data model to Azure Databricks using a BI tool file. To migrate an entire Power BI or Tableau report into an AI/BI dashboard, see Import BI files using Genie Code instead.

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 metric views don't support SELECT *. The following query is the canonical pattern and works with any BI tool that supports SQL pass-through:

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.

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

Additional resources

  • Last updated on