Can metric views be used to achieve sql cube functionality

[balajij8]

Metric Views is not a direct cube engine. It's a semantic abstraction layer. It allows to define dimensions, fact, measures with aggregations and then query it flexibly across tools.

You get multiple choices

• Star Schema Approach

  Adopt a star schema (Fact & Dimension along with joins in metric views) design as it simplifies metric view creation with easier governance and extensibility

• Modular Metric Views- Create separate modular metric views based on purpose (Sales by State - Metric View with a join between sales and state dimension, Sales by Maker - Metric view with a join between sales and product dimension). It aligns well with domain driven design and easy to manage

More details here

[Ashwin_DSA]

Hi @IM_01,

Yes. Metric views are explicitly designed to give you SQL cube-like behaviour.

A metric view lets you define measures once, independent of dimensions, then aggregate those measures over any combination of dimensions at query time, which is the core behaviour you get from cubes.

When querying a metric view, you can use GROUP BY GROUPING SETS (and thus CUBE/ROLLUP patterns) on its dimensions, so you can generate detail rows, subtotals, and grand totals in a single query, just like with GROUP BY CUBE.

The main difference vs. a classic OLAP cube is that metric views are SQL/UC-native and compute aggregations on demand (optionally with materialization for performance), rather than maintaining a separate multidimensional cube engine.

If this answer resolves your question, could you mark it as “Accept as Solution”? That helps other users quickly find the correct fix.

[IM_01]

@Ashwin_DSA  could you please provide an example .

[Ashwin_DSA]

Hi @IM_01,

Here is a simple example..

%sql
CREATE SCHEMA IF NOT EXISTS uc_customer_demo_catalog.metric_demo;

CREATE OR REPLACE TABLE uc_customer_demo_catalog.metric_demo.orders (
  order_id   INT,
  order_date DATE,
  country    STRING,
  product    STRING,
  revenue    DOUBLE
);

INSERT INTO uc_customer_demo_catalog.metric_demo.orders VALUES
  (1, '2025-01-01', 'US', 'Laptop', 1200.0),
  (2, '2025-01-02', 'US', 'Laptop',  800.0),
  (3, '2025-01-03', 'US', 'Phone',   500.0),
  (4, '2025-01-01', 'UK', 'Laptop', 1000.0),
  (5, '2025-01-02', 'UK', 'Phone',   400.0),
  (6, '2025-01-03', 'UK', 'Phone',   600.0),
  (7, '2025-01-04', 'DE', 'Laptop',  700.0),
  (8, '2025-01-04', 'DE', 'Phone',   300.0);

Created a metric view as below.

 And here is the query I used.. 

SELECT
  country,
  product,
  MEASURE(total_revenue) AS total_revenue
FROM uc_customer_demo_catalog.metric_demo.orders_metric_view
GROUP BY GROUPING SETS (
  (country, product),
  (country),
  (product),
  ()
)
ORDER BY country, product;

Does this give you an idea?

If this answer resolves your question, could you mark it as “Accept as Solution”? That helps other users quickly find the correct fix.

[IM_01]

Hi @Ashwin_DSA 

Thanks for the example . So it abstracts the logic of metrics can we make the group by cols dynamic actually the scenario is dynamically based on filters like - all or specific cols selection calculate metrics. So is it good to go with metric views or should I calculate & persist the metrics as table columns could you please suggest

[Ashwin_DSA]

Hi @IM_01,

You define the metric logic once (measures) and a set of dimensions. At query time, you can group by any subset of those dimensions (or none), so your "All" case (no group by), "group by 1 column", "group by 3 columns", etc., are all just different SQL queries over the same metric view.

Example patterns over the same metric view:

-- Grand total (like "All")
SELECT MEASURE(total_revenue) FROM uc_customer_demo_catalog.metric_demo.orders_metric_view;

-- Group by whatever dimensions the user selected
SELECT
  country,
  MEASURE(total_revenue)
FROM uc_customer_demo_catalog.metric_demo.orders_metric_view
GROUP BY country;

SELECT
  country,
  product,
  MEASURE(total_revenue)
FROM uc_customer_demo_catalog.metric_demo.orders_metric_view
GROUP BY country, product;

So for a UI where users can change filters and choose any combination of dimensions, metric views are the recommended approach. They keep your metric definitions centralized and let the UI/SQL decide the group-by dynamically.

You only need to pre-calculate and persist aggregates in tables when you have a small, fixed set of aggregates and you need very tight latency and are happy to manage extra tables.

Even then, Databricks also offers metric view materialization (pre-aggregated views behind the scenes) so you can keep the semantic layer clean and still get performance.

If this answer resolves your question, could you mark it as “Accept as Solution”? That helps other users quickly find the correct fix.