Query Databricks metric views in Sigma (Beta)

Databricks Unity Catalog metric views let you define governed, reusable business logic in Databricks. A metric view allows you to model metrics, dimensions, and joins in a declarative format that BI tools and AI agents can query with standard SQL.

When you connect a Databricks workspace to Sigma, metric views in that workspace become available in Sigma. You can use the metrics, dimensions, and related columns defined in a metric view to do the following in Sigma:

• Build a table in a data model using a metric view as a source.
• Add tables, pivot tables, and charts to a workbook that use a metric view as a source.
• Join, union, or transpose metric view elements with other elements.
• Browse metric views in the data catalog.
• Query a metric view with custom SQL.

For more details about Databricks metric views, see Unity Catalog metric views in the Databricks documentation.

Limitations

The following is not yet supported:

• Metric views that contain window measures, including range and semi-additive aggregations.

When working with metric views in Sigma, the following limitations apply:

• Custom SQL queries cannot reference a Sigma element sourced from a metric view using the sigma_element() function. Query the metric view directly instead.

Requirements

The following privileges must be granted in Databricks to each user querying metric views in Sigma (if the Databricks connection uses OAuth) or to the user or service principal associated with the connection (if the connection uses a personal access token):

• SELECT on each metric view you want to work with in Sigma.
• SELECT on the underlying tables referenced by the metric views you want to work with in Sigma.

For more details on granting privileges, see Set permissions in the Databricks documentation.

Browse a Databricks metric view in the data catalog

If a metric view is defined in the Databricks workspace connected to Sigma, you can use the data catalog to browse the view as a table, as well as the dimensions, metrics, and joins it defines:

1. From Sigma Home, select Connections to open the list of connected data sources.

2. Select the data connection you want to browse.

3. In the left navigation panel, search or browse to locate the metric view.

4. Select the metric view to view more details:

   • On the Overview tab, review the metric view’s columns and data as a table.
   • On the Columns tab, review column names, data types, formats, and descriptions.
   • On the Metrics tab, review the names, formulas, formats, and descriptions of any metrics defined on the view.
   • On the Joins tab, review the lineage of tables joined to the base table in the metric view’s definition.
   • On the Access tab, review the users and teams with access to the metric view as a data source.

5. Click Explore to open the metric view in an exploration.

Use a Databricks metric view as a data source

To use a metric view as a source for a data element:

1. Open a data model or workbook for editing.

2. Add a data element to your document.

3. Browse to and select the metric view in your Databricks connection to use as the element source.

   When you select a metric view, all dimensions from the view are added to the element as source columns, and metrics from the view become accessible in the element’s metrics panel.

After you add a metric view table as a source, you can:

• Add columns to the element from related tables.
• Use metrics defined on the metric view in calculations and charts.
• Join, union, or transpose the element with other elements.
• Use the element as a source for child elements.

Query a metric view with custom SQL

To query a metric view with custom SQL, list the dimensions you want in the output in the SELECT statement and wrap your desired measures with the MEASURE() aggregate function.

For example, given a Databricks metric view with a definition that matches the example in the Databricks documentation, Metric views: TPCH example, with the following characteristics:

• A metric view called tpch_sales_analytics.
• A source table of samples.tpch.orders with joins to customer and nation tables.
• Dimensions, for example, order_date, customer_nation, and market_segment.
• Measures, for example, order_count and open_order_revenue.

The following query returns the order count and open order revenue for each order_date:

1
SELECT
2
  order_date,
3
  MEASURE(order_count),
4
  MEASURE(open_order_revenue)
5
FROM catalog.schema.tpch_sales_analytics
6
GROUP BY order_date

For more details, see Query metric views in the Databricks documentation.

For more information about using custom SQL in Sigma, see Write custom SQL.