Create a metric view using the Catalog Explorer UI

Learn how to create a metric view to centralize business logic and consistently define key performance indicators across reporting surfaces. This tutorial demonstrates how to create a metric view using the Catalog Explorer UI. To define metric views using SQL, see Use SQL to create and manage metric views and CREATE VIEW.

Prerequisites

• You must have SELECT privileges on the source data objects.
• You must have the CREATE TABLE privilege and the USE SCHEMA privilege in the schema where you want to create the metric view.
• You must also have the USE CATALOG privilege on the schema's parent catalog.
• CAN USE permissions on a SQL warehouse or other compute resource running Databricks Runtime 17.2 or above.

A metastore admin or the catalog owner can grant you all of these privileges. A schema owner or user with the MANAGE privilege can grant you USE SCHEMA and CREATE TABLE privileges on the schema.

Step 1: Choose a data source

Your metric view can be based on a table, view, or SQL query. This tutorial uses the orders table in the samples catalog’s tpch schema.

1. Click Catalog in the workspace sidebar.
2. Use the search bar in the schema browser to find the samples.tpch.orders table. Click orders to show table details.
3. Click Create > Metric view. The YAML editor opens.

Step 2: Define metrics

The YAML definition for this metric view includes the following top-level fields:

• version: Defaults to 1.1. This is the version of the metric view specification.
• source: samples.tpch.orders is a fully-qualified table name. All dimensions and measures on this metric view use the specified table as the data source.
• filter: All queries on this view return records with an o_orderdate after January 1, 1990.
• dimensions: Order Month, Order Status, and Order Priority are defined as dimensions.
• measures: Four measures (Order Count, Total Revenue, Total Revenue per Customer, and Total Revenue for Open Orders) are defined, each with an aggregation expression specified.

1. Use the catalog and schema drop-down menu to choose where you want to save your metric view. Querying users must have at least USE CATALOG and USE SCHEMA privileges to access the metric view.

2. Enter a name for your metric view. Metric view names can include alpha-numeric values and underscores.

3. Replace the contents of the editor with the following definition:

   YAML

   version: 1.1
   
   source: samples.tpch.orders
   filter: o_orderdate > '1990-01-01'
   
   dimensions:
     - name: Order Month
       expr: DATE_TRUNC('MONTH', o_orderdate)
   
     - name: Order Status
       expr: CASE
         WHEN o_orderstatus = 'O' then 'Open'
         WHEN o_orderstatus = 'P' then 'Processing'
         WHEN o_orderstatus = 'F' then 'Fulfilled'
         END
   
     - name: Order Priority
       expr: SPLIT(o_orderpriority, '-')[1]
   
   measures:
     - name: Order Count
       expr: COUNT(1)
   
     - name: Total Revenue
       expr: SUM(o_totalprice)
   
     - name: Total Revenue per Customer
       expr: SUM(o_totalprice) / COUNT(DISTINCT o_custkey)
   
     - name: Total Revenue for Open Orders
       expr: SUM(o_totalprice) FILTER (WHERE o_orderstatus='O')

4. Click Create.

Your metric view is ready for consumption by any user with at least SELECT privileges on the metric view. See Unity Catalog privileges and securable objects to learn more about managing permissions. To learn more about how users can query this metric view, see Query a metric view.

After creating a metric view, you can review the source, filter, and specified measures and dimensions in the Overview tab that opens.

Step 3: Add a description, comments, and tags

You can add descriptions, comments, and tags either in the YAML definition or through the Overview tab.

To add using YAML (recommended for version control):

Add comment fields directly in your YAML definition. See Version specification changelog for syntax details.

YAML

version: 1.1
comment: 'Orders metrics for revenue analysis'

dimensions:
  - name: Order Month
    expr: DATE_TRUNC('MONTH', o_orderdate)
    comment: 'Month when the order was placed'

measures:
  - name: Total Revenue
    expr: SUM(o_totalprice)
    comment: 'Sum of all order prices'

To add using the Overview tab:

• Click Add description to add a description to the metric view's metadata.
• To add comments, hover over the Comment column for the row where you want to add a comment.
• To add tags to a measure or dimension, hover over the Tags column where you want to add a tag.
• To add a tag to the metric view, click Add tags on the right side of the page.

Step 4: (Optional) Edit the YAML definition

You can view the YAML definition and open the YAML editor from the Details tab.

1. Click Details.
2. Click ...more lines to view the full definition.
3. Click Edit to open the YAML editor.
4. Add or delete dimensions or measures. Click Save.

Step 5: (Optional) Set permissions

Metric views respect the same hierarchical permissions model as other Unity Catalog securable objects. No new permissions are necessary if your metric view is stored in a schema that the intended consumers have read access to. You can explicitly assign permissions on the metric view from the Permissions tab.

1. Click Permissions.
2. Use the UI to grant and revoke permissions for other Databricks users.

For more details, see Grant permissions on an object.

Query a metric view

You can query a metric view from the workspace's built-in SQL editor or any SQL editor that can access your data on Databricks. To query a metric view, you must be attached to a SQL warehouse or other compute resource running Databricks Runtime 17.2 or above.

The following sample query evaluates the three listed measures and aggregates over Order Month and Order Status. It returns results sorted by Order Month.

All measures must be wrapped in the MEASURE function. See measure aggregate function.

SQL

SELECT
 `Order Month`,
 `Order Status`,
 MEASURE(`Order Count`),
 MEASURE(`Total Revenue`),
 MEASURE(`Total Revenue per Customer`)
FROM
 orders_metric_view
GROUP BY ALL
ORDER BY 1 ASC

Next steps

Now that you've created a metric view using SQL, explore these related topics:

Query and consume metric views

• Query a metric view.
• Use metric views with AI/BI dashboards.
• Use metric views with AI/BI Genie.
• Set alerts on metric views.

Advanced metric view features

• Use semantic metadata in metric views.
• Use joins in metric views.
• Use window measures in metric views.
• YAML syntax reference.

Alternative creation methods

• Use SQL to create and manage metric views.
• CREATE VIEW.
• ALTER VIEW.
• DESCRIBE TABLE.

Governance and security

• Manage privileges in Unity Catalog.
• Groups.
• Unity Catalog privileges and securable objects.