metrics

Metric views: KPIs defined once, sliced by any dimension

A metric view (new in 9.5) declares a model instead of a query: one fact view that holds the measures, the dimension views around it, the associations that join them, the dimensions a consumer may group and filter by, and the metrics — aggregations over the fact view. A query names the dimensions it wants, and Denodo builds the joins and the aggregation for exactly those, so "revenue" is the same number in every tool at every grain. BI tools, the Data Marketplace, Assisted Query and the MCP Server read it like any view, telling dimensions from metrics.

The model does not know this object, and most of what goes wrong with it returns a number, not an error. Associations and the join rules for any two views are /denodo:views; the descriptions consumers read are /denodo:semantics. Applying files is /denodo:execute; the working loop, the naming defaults and the safety rule are /denodo:vql. A mart — one aggregate of fixed grain for one consumer — is still a CREATE VIEW … GROUP BY in /denodo:views; a metric view is for figures many consumers slice their own way.

The rule: a metric view stands alone in its FROM

The only thing built directly on a metric view is a selection view — the metric view alone in its FROM, dimensions in SELECT and GROUP BY, each metric as evaluate_metric(<metric>), a filter on dimensions in WHERE. Everything else — another dimension view, another fact, a second metric view, arithmetic over metrics, a total — is done over selection views. Measured on 9.5.1, none of these says what is wrong: a join in the same FROM runs until the query timeout; arithmetic or ROUND around evaluate_metric, or SUM/AVG around a metric (AVG(revenue) returns the sum), is ignored, in a query and inside a CREATE VIEW alike; a ratio of two evaluate_metric returns no rows; evaluate_metric over another view is NULL (Silent failures, 1-3 and 10; Common mistakes, row 1).

Over a selection view — a view or a subquery — joins, ROUND, ratios and CASE work as in any view.

1. The model, before the statement

Part	What it must be	Check
Fact view	the one view every metric reads — metrics over two views are refused	its grain: SELECT COUNT(*), COUNT(DISTINCT <key>) FROM <fact>
Dimension views	one row per key, and that key declared as the view's PRIMARY KEY — a duplicated key multiplies every metric under it; an undeclared one can empty every HAVING grouped by it over a file source (Silent failures, 12)	SELECT COUNT(*), COUNT(DISTINCT <key>) FROM <dimension> — equal; column_is_primary_key in GET_VIEW_COLUMNS()
Associations	one per dimension, from the fact (or from the dimension a snowflake hangs on), a tree with no second path	SELECT association_name, mappings, valid FROM GET_ASSOCIATIONS() WHERE input_database_name = '<db>' AND input_type = 'views' AND input_name = '<fact>'
Fact rows without a dimension row	decide: kept in a NULL group, or dropped	SELECT COUNT(*), COUNT(<fk>) FROM <fact>, and orphans: … LEFT OUTER JOIN <dimension> … WHERE <fk> IS NOT NULL AND <dimension key> IS NULL

The checks in the table — verified: 9.5.1 (live, 2026-10-05).

Which fact rows survive is decided by the association, not by the query — verified: 9.5.1 (live, 2026-10-01):

In ASSOCIATIONS	Rows of the fact without a match	Dimension members without facts
RIGHT, dimension endpoint (0,1) or PRINCIPAL (0,1)	kept, in a NULL group of that dimension	absent
LEFT	dropped	present, metrics NULL (COUNT 0)
INNER, or RIGHT over a (1) endpoint	dropped	absent
nothing	from the association: dimension endpoint (1) → as INNER; PRINCIPAL (0,1) → as LEFT

• The association template in /denodo:views declares (1), under which RIGHT runs as INNER: right when the check above finds no NULL key and no orphan, wrong otherwise — then the association says (0,1): ENDPOINT <role> <fact view> (0,*) and ENDPOINT <role> <dimension view> PRINCIPAL (0,1), the rest of the template unchanged.
• The order of the two aliases does not matter; the type is read against the fact.
• A query without any dimension of a group applies no join for it: the grand total always counts every fact row, which is why a model that drops rows still shows the right total.
• Another association between the fact and the view under a dimension view (the base view a dimension iv_ selects from) turned RIGHT into INNER; dropping it restores RIGHT. Before you add an association near a metric view, run Verify.
• An association between views you did not create is a proposal to their owner (/denodo:semantics). Over someone else's base views, build your own iv_ views for the fact and each dimension (/denodo:views) — with the key declared, text trimmed — and put the associations between those: everything the model needs is then yours, and no association lands next to theirs. The metric view can also live in your own database and name views of another (<db>.<view> in SOURCES).

2. Templates

Each template is one file, applied whole. CREATE OR REPLACE METRIC VIEW re-applies safely; views built on it stay valid while the metrics and dimensions they name stay.

The metric view

-- verified: 9.5.1 (live, 2026-10-07)
CONNECT DATABASE sales_analytics;

CREATE OR REPLACE METRIC VIEW household_metrics
    FOLDER = '/03 - business entities'
    DESCRIPTION = 'Household KPIs by income band and buying potential. Query: SELECT <dimensions>, evaluate_metric(<metric>) FROM household_metrics GROUP BY <dimensions>.'
    (
        buy_potential   ( description = 'Buying potential band of the household, as in the source (for example ''0-500'').' ),
        household_count ( description = 'Number of households.' ),
        avg_dependents  ( description = 'Average number of dependents per household.' )
    )
    SOURCES (
        households AS bv_household_demographics,
        bands      AS bv_income_band
    )
    ASSOCIATIONS (
        households bands a_income_band_household RIGHT
    )
    DIMENSIONS (
        income_band (
            income_band_sk     AS bands.ib_income_band_sk,
            income_lower_bound AS bands.ib_lower_bound,
            income_upper_bound AS bands.ib_upper_bound
        ),
        household (
            buy_potential AS TRIM(households.hd_buy_potential)
        )
    )
    METRICS (
        household_count AS COUNT(households.hd_demo_sk),
        dependents      AS SUM(CAST('long', households.hd_dep_count)),
        avg_dependents  AS AVG(households.hd_dep_count)
    );

Clause order: FOLDER → DESCRIPTION → TAGS → ( field properties ) → SOURCES → ASSOCIATIONS → DIMENSIONS → METRICS. Each alias in SOURCES is how dimensions and metrics name that view. a_income_band_household is the association template of /denodo:views; its (1) endpoint makes this RIGHT run as INNER, which is right here because every household has its band. bv_income_band declares its PRIMARY KEY in its own file (Silent failures, 12).

• A dimension is any expression over the sources without an aggregate: TRIM, CASE, COALESCE(<dim>.<label>, '(not specified)') to name the NULL group, GETYEAR(<date>), a CONCAT over two views. Labels and buckets belong here, in the definition (Silent failures, 13). Groups (income_band ( … )) are how consumers see them; an attribute after the last group, , <name> AS <expr>, is a generic one.
• A metric is an aggregate over fact columns: SUM, COUNT(<column>), COUNT(DISTINCT …), AVG, MIN, MAX, with CASE inside (SUM(CASE WHEN … THEN amount ELSE 0 END)) and arithmetic over aggregates (SUM(fee) / NULLIF(SUM(amount), 0)). It cannot name another metric or use COUNT(*) — both are invalid field: missing source schema for field: <metric>; repeat the aggregates instead.
• The dialect rules hold inside a metric (/denodo:vql, Expressions: VQL is not PostgreSQL): SUM over an int column stays int, hence CAST('long', …) above — never over a decimal, which it truncates; an int divided by an int is an integer division (Silent failures, 11). ROUND goes inside the metric (ROUND(AVG(x), 2)) or over a selection view's column.
• AVG(x) and COUNT(x) skip NULLs: say in the metric's description whether the KPI is AVG(x) or SUM(x) / COUNT(<key>), a missing value counted as zero.
• A filter that every metric obeys goes into the fact view — an iv_ derived view with the WHERE, named in SOURCES. The FILTER ( … ) clause the Design Studio wizard writes after SOURCES fails on some queries with Error applying metric transformation. — details in references/metric-views.md.
• Field descriptions go in the field properties above or by ALTER VIEW <metric view> ( ALTER COLUMN … ADD ( DESCRIPTION = '…' ) ). A selection view inherits the description of a dimension it passes through, never that of a metric: describe evaluate_metric columns in the selection view (/denodo:semantics).
• DESC VQL returns CREATE METRIC VIEW without OR REPLACE and without CONTEXT: a reference, not your file. A metric view takes no cache (Metric views do not support cache mode): cache its sources (/denodo:cache, Templates).

Selection views, a total and a share

-- verified: 9.5.1 (live, 2026-10-07)
CONNECT DATABASE sales_analytics;

CREATE OR REPLACE VIEW households_by_band
    FOLDER = '/03 - business entities'
    DESCRIPTION = 'Households per income band, from the metrics of household_metrics. One row per income band.'
    AS SELECT income_band_sk,
              evaluate_metric(household_count) AS household_count,
              evaluate_metric(avg_dependents)  AS avg_dependents
       FROM household_metrics
       GROUP BY income_band_sk
    CONTEXT ('formatted' = 'yes');

CREATE OR REPLACE VIEW households_total
    FOLDER = '/03 - business entities'
    DESCRIPTION = 'All households, from the metrics of household_metrics. One row.'
    AS SELECT evaluate_metric(household_count) AS household_count,
              evaluate_metric(avg_dependents)  AS avg_dependents
       FROM household_metrics
    CONTEXT ('formatted' = 'yes');

CREATE OR REPLACE VIEW household_share_by_band
    FOLDER = '/03 - business entities'
    DESCRIPTION = 'Each income band''s share of all households, in percent. One row per income band.'
    AS SELECT b.income_band_sk,
              b.household_count,
              ROUND(100.0 * b.household_count / t.household_count, 2) AS household_share_pct,
              ROUND(b.avg_dependents, 2)                              AS avg_dependents
       FROM households_by_band b
            CROSS JOIN households_total t
    CONTEXT ('formatted' = 'yes');

• A total comes from the metric, never from adding up rows. households_total asks the metric view with no dimension at all — allowed when the SELECT holds metrics only; a literal next to them needs a GROUP BY (For this type of query, the group by fields are mandatory.). Summing the rows of households_by_band is right for a sum or a count and wrong for an average (an average of averages) and for a COUNT(DISTINCT …) (a customer in two bands counts twice); for a coarser grain ask the metric view at that grain.
• A view or fact from outside the model joins to a selection view grouped by the join key — GROUP BY <key> in the selection, then JOIN <other view> ON s.<key> = …. Two metric views side by side: one selection view each, at the same dimensions — aliased to one name when the two metric views call it differently — joined on them; a FULL OUTER JOIN keeps a member only one of them has, and the key column is then COALESCE(a.<key>, b.<key>).
• Arithmetic over metrics — a ratio, a percent, rounding — is a column of the view over the selections, as household_share_pct above.
• An average per member — per customer, per store — is a selection grouped by the member, averaged in the view above: SELECT type, AVG(m.total) FROM (SELECT type, customer_id, evaluate_metric(revenue) AS total … GROUP BY type, customer_id) m GROUP BY type — verified: 9.5.1 (live, 2026-10-05). Its denominator is the members that have facts; members without any are absent under RIGHT. When they must count as zero, take the members from a dimensions-only query (it lists every one) and LEFT OUTER JOIN the totals to it.
• When nothing may be created — a question to answer, not a view to build — the same views are subqueries: SELECT … FROM (<selection>) b CROSS JOIN (<total>) t.

Periods: year over year, to date, running totals

Read references/periods.md before any year-over-year, to-date or running-total figure: its templates, and what decides them — a partial first or last period, which every total check passes; a coarser period, which adds up only sums and counts; "today", the server's clock and not the data's; the year before, a join and not LAG.

Query it ad hoc

-- verified: 9.5.1 (live, 2026-10-07)
SELECT income_band_sk, buy_potential,
       evaluate_metric(household_count) AS household_count
  FROM household_metrics
 WHERE buy_potential = '>10000'
 GROUP BY income_band_sk, buy_potential
HAVING evaluate_metric(household_count) > 50
 ORDER BY household_count DESC;

Works	Does not
WHERE on dimensions	WHERE evaluate_metric(m) > … — Aggregate functions are not allowed here; WHERE <metric> > … — an Error executing query from the route
HAVING evaluate_metric(m) > …, HAVING <dimension> …	HAVING <alias> — Field not found; HAVING <metric> — is not a group by field
ORDER BY <alias> or ORDER BY evaluate_metric(m)	ORDER BY a field not in the SELECT — Field not found '<f>' in view with schema
an expression over a dimension in SELECT, grouped by the raw dimension — one row per raw value, every metric kept	the same expression grouped by it or by its alias (Silent failures, 13)
COUNT(*) — the fact rows of the group	GROUP BY <metric> — The group by fields can only be dimensions or literals
LIMIT	no GROUP BY with a dimension in SELECT, or SELECT * — 0 rows, no error

A query of dimensions only lists every member of the dimension view — every year of a calendar — not the members that have facts. Ask with a metric to see what has data.

3. Read what exists

-- verified: 9.5.1 (live, 2026-10-07)
SELECT column_name, column_organization, column_dimension, column_definition, column_vdp_type
  FROM GET_VIEW_COLUMNS()
 WHERE input_database_name = 'sales_analytics' AND input_view_name = 'household_metrics';

SELECT name, sources, associations, filter
  FROM GET_METRIC_VIEWS('sales_analytics', 'household_metrics');

The first lists every dimension and metric with its expression — what a query may name; add column_remarks for the descriptions. The second gives the sources and, per association, the join type: 0 INNER, 1 LEFT, 2 RIGHT, null none written. GET_METRIC_VIEWS takes a LIKE pattern only as a positional argument; WHERE input_name = '<pattern>' returns nothing. These two are the way to read a metric view; vql desc … --vql of it, or of a view built on it, brings back its data sources and their encrypted passwords (/denodo:execute, Commands).

Who applies what

A new metric view and the views over it, created in this session, are yours to apply and verify. Changing a metric view you did not create in this session, even to add one metric: show the file and get a yes (/denodo:vql). A missing dimension or metric in another team's metric view is a proposal to them; until they add it, your own metric view over the same sources answers the question without touching theirs.

Verify — the totals check

The one check that catches the silent failures of the model: the same figure three ways.

-- verified: 9.5.1 (live, 2026-10-07)
CONNECT DATABASE sales_analytics;

-- 1. The fact itself.
SELECT COUNT(hd_demo_sk) AS fact_total FROM bv_household_demographics;

-- 2. The metric with no dimension: no join is applied.
SELECT evaluate_metric(household_count) AS metric_total FROM household_metrics;

-- 3. Once per dimension group: its slices add up to the same total.
SELECT SUM(s.household_count) AS slices_total, COUNT(*) AS slices
  FROM (SELECT income_band_sk, evaluate_metric(household_count) AS household_count
          FROM household_metrics
         GROUP BY income_band_sk) s;

You see	It means
1 = 2 = 3	every fact row is in every slicing
3 lower than 2	fact rows dropped under that dimension: NULL or orphan keys with INNER, LEFT, no type, or a (1) endpoint (section 1)
3 higher than 2	a key duplicated in that dimension view: those facts are counted twice
2 differs from 1	the metric reads a different column or a FILTER; read its definition (section 3)

On a metric view you did not build the same three queries tell you what its associations do with the rows: you cannot read the join from its definition alone.

Run step 3 for one attribute of each dimension group — each association is applied only when a query names its dimensions. Use a sum or a count for it; then compare every other metric with plain SQL over the fact and the dimension — one FULL OUTER JOIN of the selection and the plain GROUP BY on the dimensions, keeping the rows where any figure differs, checks every slice at once. Compare averages with a tolerance: the metric and hand-written SQL may type them differently and differ in the last digit. Also: SELECT name, view_status FROM GET_VIEWS() WHERE input_database_name = '<db>' AND input_retrieve_invalid_views_only = true is empty, and every selection view returns rows — verified: 9.5.1 (live, 2026-10-05).

Silent failures

Each runs without an error — verified: 9.5.1 (live, 2026-10-01).

You wrote	What you get	Instead
1. AVG(<sum metric>), SUM(<avg metric>), MAX(…)	the metric's own aggregation	evaluate_metric; another aggregation is another metric, or a view over a selection
2. evaluate_metric(a) * 100, ROUND(evaluate_metric(a), 2)	a, unchanged	the expression over the selection view's column
3. evaluate_metric(a) / evaluate_metric(b)	no rows	a ratio metric SUM(x) / NULLIF(SUM(y), 0), or the division over a selection
4. SELECT * FROM <metric view>, a dimension without GROUP BY	no rows	dimensions in GROUP BY, metrics in evaluate_metric
5. RIGHT over an association with a (1) dimension endpoint	facts without a match dropped from every grouped query; the grand total still right	(0,1) on the association; the totals check
6. LEFT, or no type over a PRINCIPAL (0,1) endpoint	every dimension member, with empty metrics, and the unmatched facts dropped	RIGHT
7. a dimension view with a duplicated key	every metric of that key multiplied	one row per key in the dimension view
8. a metric without an aggregate (n AS f.qty)	accepted; every query with it returns no rows	an aggregate
9. the sum of a selection's rows as a total	an average of averages; distinct counts counted twice	the metric with no dimension
10. evaluate_metric(x) over a selection view	NULL	x — evaluate_metric belongs to the metric view only
11. a share as SUM(CASE … 1 ELSE 0 END) / COUNT(x)	0 — integer division	1.0 * SUM(…) / COUNT(x)
12. HAVING evaluate_metric(m) > … grouped by the key of a dimension view that declares no PRIMARY KEY, over a file source	no rows: the plan is INCOMPATIBLE_QUERY_VIEW, VOID PLAN (the same query delegated to SQL Server answered)	declare the key in the dimension view's file; or filter the selection view's column in a view above
13. COALESCE(<dim>, '…') AS d, UPPER(<dim>) AS d, GETYEAR(<date>) or a CASE in a query, grouped by it or by its alias, over sources delegated to a database (measured on SQL Server)	every COUNT(DISTINCT …) metric missing from the result — the plan already projects without it; sums, counts and averages stay (over file sources it stayed) — the expression grouped as itself: verified: 9.5.1 (live, 2026-10-06)	the expression in the dimension's definition, or GROUP BY <dim> and the label in the view above
14. a query over a selection view that reads none of its metric columns — SELECT MIN(<date>), a COUNT(*)	every member of the dimension view, with facts or without: a calendar's every day — verified: 9.5.1 (live, 2026-10-06)	read the metric in the same query: WHERE <metric column> > 0

Common mistakes

You wrote	Server says	Fix
a metric view joined to a view in one FROM	nothing, then Error: Time out processing data	a selection view, then the join
per AS total / cnt — a metric over metrics; COUNT(*)	invalid field: missing source schema for field: per	repeat the aggregates; COUNT(<fact column>)
MAX(…) in a dimension	The following fields cannot be projected: max(…) AS <dim>	aggregates go in METRICS
metrics over two source views	There are metric fields referencing different views. Metrics must be built using fields from the same view.	one fact view; the other figure is a second metric view
two SOURCES, no ASSOCIATIONS	invalid associations: It can't be empty	name the association
evaluate_metric(<dimension>)	There are dimensions fields with function aggregation …	dimensions go in GROUP BY
a literal next to metrics, no GROUP BY	For this type of query, the group by fields are mandatory.	GROUP BY '<literal>', or the literal in the view above
a view AS SELECT * FROM <metric view>	created; its queries fail with Error applying metric transformation.	a selection view
a metric removed while a selection view names it	the selection view turns INVALID	put the metric back, or change the selection first (/denodo:views, "Before a column changes")

Reference

• references/metric-views.md — the full CREATE METRIC VIEW grammar, the join types measured case by case, the FILTER clause, what a metric view refuses (cache, row restrictions), how consumers read it, summaries, and the procedures that describe it.