#label

Level of Detail calculations are now available in #Snowflake Semantic Views:

CREATE OR REPLACE SEMANTIC VIEW sales_sv
FACTS (
  -- Counts orders at customer grain,
  -- regardless of the query grain.
  orders_per_customer
    AS COUNT(orders.order_id)
      FIXED DIMENSIONS (customers.customer_id),
  -- Sums revenue at customer grain,
  -- regardless of the query grain.
  revenue_per_customer AS
    SUM(orders.order_amount)
      FIXED DIMENSIONS (customers.customer_id),
)

METRICS (
  avg_orders_per_customer AS AVG(orders_per_customer),
  avg_revenue_per_customer AS AVG(revenue_per_customer),
  avg_basket_size AS
    AVG(SUM(order_items. line_total)
      INCLUDE DIMENSIONS (orders.order id))

);

https://docs.snowflake.com/...c/sql#label-semantic-views-lod

Level of Detail (LOD) metrics for #Snowflake Semantic Views are now in public preview. If you’ve built enterprise BI models, you know the challenge: a metric’s correct grain often isn’t the grain of the chart. The logic gets duplicated in workbook calculations or awkward #SQL, and the same business question can end up with different answers across reports.

LOD brings that grain control into the semantic model. The new syntax supports FIXED, INCLUDE, and EXCLUDE:

FIXED DIMENSIONS lets you define calculations at a specific grain. For example, define orders per customer and revenue per customer, then roll each metric up by region.>
INCLUDE DIMENSIONS adds a finer grain before rolling results back up. Think average basket size by store and week, calculated at the order level.
EXCLUDE DIMENSIONS removes a dimension from the query grain, which is useful for share-of-parent metrics, like product or channel mix within a region.

This capability allows Semantic View builders to bring advanced BI modeling patterns into governed, reusable metric definitions, so that consuming apps and dashboards can all benefit from and re-use the same logic for multi-level metric calculations.

https://docs.snowflake.com/...c/sql#label-semantic-views-lod