Skip to main content

Create a metric view

Preview

This feature is in Public Preview.

Learn how to create a metric view to centralize business logic and consistently define key performance indicators across reporting surfaces. See Unity Catalog metric views. This tutorial demonstrates how to create a metric view using the Catalog Explorer UI. To define metric views using SQL, see 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 16.4 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 Data icon. 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 0.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. Replace the contents of the editor with the following definition:

    YAML
    version: 0.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')

  2. (Optional) Rename your metric view. A name for your metric view is automatically populated in the text field above the definition. You can keep or edit the default name. Metric view names can include alpha-numeric values and underscores.

  3. (Optional) Use the schema drop-down menu to choose the schema where you want to save your metric view. By default, the selected schema is the same as where the table resides.

  4. Click Create.

Your metric view is ready for consumption by any user with at least SELECT privileges on the view. See Unity Catalog privileges and securable objects to learn more about managing permissions. To learn more about how users can query this 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 that opens.

Step 3: Add a description, comments, and tags​

From the overview tab:

  • Click Add description to add a description to the 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 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 objects in a Unity Catalog metastore.

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 16.4 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​

Last updated on