This is part 2 of 3 in a series where I take you through working with Metric Views.
- Part 1: Introduction to Metric Views
- Part 2: Metric Views in AI/BI Dashboards & Genie
- Part 3: Metric Views with Power BI and Tabular Editor
Introduction
In Part 1, we defined what metric views are, how you build and maintain them. Now we shift focus to how these views become consumable by business-users, via dashboards and conversational analytics. To do this, we will start by setting up a simple semantic model based on the TPCH sample data, and then get to work understanding how Metric Views work across the Databricks platform. Specifically we will cover:
- Defining a Semantic Model and Metric Views using the new yaml version 1.1, which includes metadata, formatting and lots of other neat new features
- How metric views can be used AI/BI dashboards
- How metric views feed into Genie for natural-language analytics
- Best practices, sharing, and governance aspects
Setup
To make things tangible, I have built a small Star Schema based model in Databricks based on the TPCH sample dataset. Apart from a bit of renaming, the main thing that has been set up, is definition of business keys, surrogate keys and primary/foreign key relations. The goal is to demonstrate how Metric Views work, and for that we need a model, but nothing too fancy. The model represents a simplified sales domain built around a single Fact table and its related Dimensions.
Fact table:
- fact_lineitem; transactional data including quantity, price, discount, and order references.
Dimension tables:
- dim_customer; customer attributes such as name, market segment, and nation.
- dim_orders; order-level attributes including order date, status, and total price.
- dim_part; product-level attributes (part key, name, type, and brand).
- dim_supplier; supplier-level attributes (supplier key, name, nation).
- dim_date; calendar table that is used as a role-playing dimension across 3 different dates on the fact.
Metric View
The metric view is designed using the new yaml version 1.1. This opens up a lot of new opportunities for metadata including comments, synonyms and data type configurations, which are useful not only for your Business Users, for context and documentation, but also for Genie which relies on metadata to better understand your logic. For more context around the semantic metadata the full documentation is available at Databricks
Source Code
The solution is created using a notebook, and a sql script, which allows you to set up the solution in your own workspace and follow along. The code is shared here on GitHub.
Consuming Metric Views
Both AI/BI Dashboards and Genie Spaces can be built directly on top of tables across your Unity Catalog. The advantage of Metric Views is, that it allows you to combine Facts and Dimensions, and define measures, relationships and metadata definitions that can be re-used across your entire domain.
AI/BI Dashboards
AI/BI Dashboards is Databricks' product which offers a native end-to-end BI experience. It is built on top of Unity Catalog, supports self-service creation of analytical datasets, visualizations, and integration with conversational analytics. When you have a Metric view defined, the dashboard layer can simply consume this, instead of consuming tables directly.
To create an AI/BI Dashboard on top of a Metric View:
- In Databricks SQL workspace, navigate to Dashboards.
- Create a new dashboard and go to the Data tab and select Add Data Source and select your Metric View.
- Now you are ready to start the visualization part of the work; Create graphs, filters, text boxes and enjoy the existing dimensions and measures, or create new dashboard specific measures based on existing logic.
Genie
Genie is the natural-language interface inside Databricks aimed at business users: you ask “What was our revenue last quarter in EMEA?”. Genie translates your SQL, runs it, returns tabular and/or visual output. Because metric views encapsulate business logic and semantics, Genie can leverage that metadata and mappings to deliver accurate, governed answers.
The initial setup of using a Metric View in Genie is very similar to what we just saw above for AI/BI Dashboards:
- In Databricks SQL workspace, navigate to Genie.
- Create a new Genie Space and select your Metric View as tour data source
- Now you are ready to start configuring the Genie. Provide sample questions and/or queries, teach it additional synonyms or instructions or attach additional data for cross-collaborations
SQL querying
I just want to include this here as an honorable mention. While business users may prefer interacting with Genie and Dashboards, business analysts might prefer being able to query data directly themselves, or you might want to use a Metric View to expose data to a subsequent solution outside
Metric Views are queryable directly, allowing re-use of existing Metric View definitions in subsequent data models or even building Metric Views on top of other Metric Views (... that one might get a little messy down the road). To query a metric view, the main distinction from regular SQL is the use of the MEASURE command as shown below:
SELECT `Customer Region`, `Ship Year`, MEASURE(`Total Net Amount`) AS revenue, MEASURE(`Total Orders`) AS orders, MEASURE(`Average Order Value`) AS aov FROM main.demo_tpch.order_metrics_mv WHERE `Ship Year` = 1997 GROUP BY `Customer Region`, `Ship Year` ORDER BY revenue DESC;
So for all that work, what do we actually get?
Building a semantic layer, is all about enabling your business users to get closer to that self-service analytics dream. By keeping the entire solution inside of Databricks, we enable the re-use of all the key governance, lineage and security features that already exist across the Data Platform. Being able to define relations, computing measures and adding additional semantics are options that we do not normally see as part of a Lakehouse. This options extends the reach of the Lakehouse platform all the way to the end-users.
Genie and Dashboards working side-by-side and in unison. Sorry for the lack of attention to the dashboard. They can actually get really pretty now with themes, colors and text, if you know what you are doing.
The key benefits of using Metric Views as the source for your solution, can be summarized to;
- Consistency: all visuals and conversational queries rely on the same metric definitions.
- Governance: because the metric view is under Unity Catalog governance, access controls and lineage apply.
- Scalability: business users get self-service via dashboards, Genie and also SQL analytics directly on the Metric view.
- Extendibility: when a new measure or dimension is added in the metric view, dashboards and Genie automatically pick it up (once surfaced in UI) without redefining logic everywhere.
The ability for end-users to query, talk to and analyze data using consistent definitions and extend these are where we get to the actual value creation of the Data Platform.
Summary and next steps
In this article we moved from definition to consumption, using the built-in Databricks tools.
In the next and final part of the series we’ll explore Part 3: Metric Views and Power BI + Tabular Editor, demonstrating how those same semantic definitions can be surfaced in external BI tools.
