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