Introduction to Metric Views¶

Denodo Metric Views extend the Denodo semantic model, enabling the centralization of complex business metrics (KPIs) and dimensions within the virtual layer. As a specialized form of derived view, Metric Views are built upon other derived views. They offer a flexible management experience, allowing users to define and govern them through intuitive graphical wizards or directly via VQL. Regarding consumption, Metric Views offer the same integrated experience as any other views. They can be viewed and are accessible through various application interfaces including the Denodo Data Marketplace, Denodo Assistant, standard BI tools (via JDBC, ODBC, or Arrow Flight SQL), the Model Context Protocol (MCP), and REST APIs.

Introduction to Metric Views¶

Why Use Metric Views?¶

Denodo Metric Views establish a governance layer for business metrics and dimensions within the virtual data layer. As a central repository for key performance indicators (KPIs), Metric Views provide significant benefits for data consumers and modelers:

• Simplified Semantic Layer: Business logic, relationships, and aggregations are defined once and abstracted into a reusable definition. This simplifies data modeling, reduces duplication, and allows users to query complex KPIs without needing to write intricate SQL.

• Centralized Governance and Consistency: Metric Views serve as a single source of truth managed within the Denodo Catalog. This ensures that identical metric definitions are consistently reused across all dashboards, notebooks, and AI agents, eliminating discrepancies and confusion.

• Enhanced Consumption and Integration: Metric Views expose standardized metrics and dimensions through a consistent SQL interface and are fully accessible via Data Marketplace, and standard BI tools.

• Boosted Accuracy for AI and Agentic Systems: Metric Views act as a trusted source of truth that significantly improves the accuracy and reliability of automated insights and natural language queries driven by AI agents.

• Performance Optimization: Metric Views leverage the aggregate aware summaries smart caching in Denodo, which plays a critical role in ensuring optimal query performance by routing requests to pre-calculated aggregate data.

Key Terminology¶

• Measure: Represents raw numerical data or quantitative values, such as order_amount or sale_price, that serve as the foundation for mathematical calculations.

• Metric: An aggregation formula that provides business context to measures (e.g., Total Revenue = SUM(order_amount)). Metrics can also be derived by referencing other metrics to ensure consistency and maintainability (e.g., Average Order Value = Total Revenue / Order Count). From a business and consumption perspective, a metric represents a KPI(Key performance Indicator)

• Dimension: Categorical structures used to provide context for metrics, typically organized into logical groups like Time, Customer, or Product.

• Attribute: Specific columns within a dimension table used for filtering, grouping, or slicing metrics in a query.

• Source views: The views used inside the model of a metric view. From that views, all the dimensions and metrics fields are gathered and used to make the internal joins.

• Fact View: The primary source view in a Metric View that contains the measures (quantitative data). It is typically the “central” table in a star or snowflake schema.

• Dimension View: Source views that contain descriptive attributes (categorical data) used to filter or group the metrics. Dimension views are related to the Fact View (or other Dimension Views) via associations.

Definition in Design Studio¶

After opening the wizard, several tabs are available to configure the view:

• Model (metric): Defines the source views and the associations between them.

• Relationships: Allows overriding the join types for the different associations.

• Filter condition: An optional section to apply search conditions before calculating the metrics.

• Output: Specifies the dimension attributes and metrics that will be visible.

• Metadata: Standard metadata configuration for the view.

The Model (metric) tab is used to specify the source views for the metric. To add sources, drag the desired views from the Server Explorer into the workspace.

Using and Defining Associations¶

Once the source views are added, you must define how they are related. The wizard allows you to create associations by dragging a relationship from the icon available on each source view.

Each time you link two views, an internal association is created. Selecting a relationship link opens a configuration section at the bottom of the workspace to provide more details about the association.

This section is divided into four subtabs:

• Mappings: Used to specify the join fields between the source views.

• Properties: Used to define association properties and cardinalities. This configuration follows the same principles as standard associations in Denodo (see section Creating an Association Manually).

• Relationships: Allows you to override the join type. By default, it inherits the type based on the defined cardinalities, but it can be manually changed.

The following tables show how the join type is automatically determined based on the inherited association’s cardinality.

Case 1: Association with referential constraint

The table below shows the join type assigned to an association with referential constraint (default behavior), based on its cardinality:

Cardinality (Left endpoint)	Cardinality (Right endpoint)	Join Type
1	1	Inner
0..1	0..1	Left Outer
0..1	1	Right Outer
1	0..1	Left Outer
1..* (+)	1	Inner
1..* (+)	0..1	Left Outer
0..* (*)	1	Right Outer
0..* (*)	0..1	Left Outer
1	1..* (+)	Inner
0..1	1..* (+)	Right Outer
1	0..* (*)	Left Outer
0..1	0..* (*)	Right Outer

Case 2: Association without referential constraint

The table below shows the join type assigned to an association without referential constraint, based on its cardinality:

Cardinality (Left endpoint)	Cardinality (Right endpoint)	Join Type
1	1	Inner
0..1	0..1	Inner
0..1	1	Right Outer
1	0..1	Left Outer
1..* (+)	1	Inner
1..* (+)	0..1	Left Outer
0..* (*)	1	Right Outer
0..* (*)	0..1	Left Outer
1	1..* (+)	Inner
0..1	1..* (+)	Right Outer
1	0..* (*)	Left Outer
0..1	0..* (*)	Right Outer

• Metadata: Standard metadata for the association.

This process must be completed for all required associations. Keep the following points in mind:

• Full Connectivity: Every view in the model must be related to at least one other view.

• Single Connected Graph: All views must be reachable through associations, forming a single connected graph. This ensures that metrics can be correctly aggregated and filtered by any defined dimension attribute.

After establishing all associations, the final model should look similar to this:

Automatic Inclusion of Existing Associations¶

If an association already exists between any of the views included in the wizard, it will be automatically added to the workspace.

For example, if there are associations defined for some of the source views:

When these views are dragged into the Metric View wizard, the relationship is automatically established:

Defining Filters¶

The Filter condition tab allows you to define an optional search condition that will be applied to the source data before the metrics are calculated. If specified, this condition is mandatory and will always be included in the execution of the Metric View. This is useful for:

• Restricting the Data Set: Limit the analysis to a specific time period, region, or any other criteria relevant to the business metrics.

• Improving Performance: By filtering data at the source level, Denodo can optimize the query execution and reduce the volume of data processed during aggregation.

You can specify the condition by writing it directly in the text area.

Filter condition tab in Metric View wizard¶

Defining Output Attributes¶

The Output tab is where you define the structure of the Metric View by specifying its dimensions and metrics. This section determines how the data will be presented and how users can query the metrics.

Output tab in Metric View wizard¶

In this window, you can configure:

• Dimensions: These represent the logical entities used to categorize data. Within a dimension, you can define Dimension Attributes, which are the specific categorical elements that will be available for selection and grouping in queries.

• Generic Attributes: Custom attributes created and derived from existing dimension attributes sourced from the underlying source views. These are categorized as “Generic”.

• Metrics: These are the quantitative values based on the measures from the fact view.

By default, all fields from the source views are included as dimension attributes, except those with “Decimal” type, those are excluded for the default values (however they can be included manually).

Summary metric view¶

When you finish modeling the metric view and save the result, it will show a summary of the metric view, indicating the source views, the field dimensions and the field metrics.

VQL Definition and Advanced Syntax¶

Metric Views can also be created and managed using VQL. This provides more control for advanced modeling scenarios and allows for the automation of view creation.

CREATE [ OR REPLACE ] METRIC VIEW <name:identifier>
    [ FOLDER = <literal> ]
    [ DESCRIPTION = <literal> ]
    [ <tag list> ]
    [ ( <field properties> [, <field properties> ]* ) ]
    SOURCES (
        <view_alias:identifier> AS [ <database_name:identifier> . ] <view_name:identifier>
        [, <view_alias:identifier> AS [ <database_name:identifier> . ] <view_name:identifier> ]*
    )
    [ ASSOCIATIONS (
        <view_alias_1:identifier> <view_alias_2:identifier> [ <database_name:identifier> . ] <association_name:identifier> [ <join_type> ]
        [, <view_alias_1:identifier> <view_alias_2:identifier> [ <database_name:identifier> . ] <association_name:identifier> [ <join_type> ] ]*
       ) ]
    DIMENSIONS (
        <dimension_group_name:identifier> (
            <attribute_name:identifier> AS <expression>
            [, <attribute_name:identifier> AS <expression> ]*
        )
        [,
            <dimension_group_name:identifier> (
                <attribute_name:identifier> AS <expression>
                [, <attribute_name:identifier> AS <expression> ]*
            )
        ]*
        , <attribute_name:identifier> AS <expression>
        [, <attribute_name:identifier> AS <expression> ]*
    )
    METRICS (
        <metric_name:identifier> AS <aggregation_expression>
        [, <metric_name:identifier> AS <aggregation_expression> ]*
    )
    [ CHECK_INDIRECT_ACCESS { ON | OFF} ]
    [ CONTEXT ( <context_parameter> = <value> [, <context_parameter> = <value> ]* ) ]

CREATE OR REPLACE METRIC VIEW sales_view
    FOLDER = '/3. views'
    DESCRIPTION = 'Example of metric view'
    TAGS (sales_data, sensitive)
    (
        customer_address_full (sourcetypesize = '255', description = 'Full customer address')
    )
    SOURCES (
        nation AS vdb_tpch.psql_tpch_nation,
        orders AS vdb_tpch.psql_tpch_orders,
        region AS vdb_tpch.psql_tpch_region,
        customer AS vdb_tpch.psql_tpch_customer
    )
    ASSOCIATIONS (
        nation region vdb_tpch.psql_tpch_region_psql_tpch_nation LEFT,
        customer nation vdb_tpch.psql_tpch_nation_psql_tpch_customer,
        orders customer vdb_tpch.psql_tpch_customer_psql_tpch_orders INNER
    )
    DIMENSIONS (
        orders_dim(
            order_date AS orders.o_orderdate
        ),
        customer_dim(
            customer_address_full AS concat(customer.c_address, '-', nation.n_name),
            customer_name AS customer.c_name,
            customer_address AS customer.c_address
        ),
        nation_dim(
            nation_name AS nation.n_name
        ),
        customer_comment AS customer.c_comment
    )
    METRICS (
        count_order_keys AS count(orders.o_orderkey),
        order_average_value AS avg(orders.o_totalprice)
    )
 CONTEXT ('I18N' = 'us_pst');

Validating a Metric View¶

When creating or modifying a Metric View, the system performs several validation checks to ensure the data model is sound and the metrics can be calculated correctly. To successfully define a Metric View, the following requirements must be met:

• Consistent Data Model Hierarchy: Ensure that the underlying views follow a clear Star schema or Snowflake schema pattern. This structure guarantees that relationships between dimensions and facts are well-defined.

• Fact-Based Metric Definitions: All metrics (KPIs) must involve one fact view, which is the single view containing all the metric definitions. The system validates that all metrics belong to the same fact view and does not allow metrics to be defined across different tables.

• Clear Join Paths: The associations between views must form a tree-like structure. It is important to avoid diamond paths (multiple join paths between two views), as they can lead to ambiguity in the query generation and potential data duplication.

• Valid Cardinality: Defining correct cardinality is essential. The model should reflect the real-world relationship between entities (e.g., Many-to-One from fact to dimensions) to prevent common modeling traps like chasm or fan-out traps.

• Functional Separation: Ensure that aggregation functions are only used within metric definitions, while derived functions and conditional logic are used appropriately in both dimension and metric attributes.

Querying via VQL¶

Querying a Metric View follows a specific pattern in VQL. Queries must always include at least one dimension attribute and use the evaluate_metric() function for metric fields.

The basic structure of a query against a Metric View is as follows:

SELECT <dimension_fields>, evaluate_metric(<metric_field>)
FROM metric_view
GROUP BY <dimension_fields>;

The evaluate_metric() function is a specialized aggregation function that instructs the engine to consume the specified metric as defined within the Metric View. This function can only be used with metric fields; it is not permitted for use with dimension attributes.

SELECT nation_name, customer_name, SUM(count_order_keys)
FROM sales_view
GROUP BY nation_name, customer_name;

Example Query

SELECT nation_name, customer_name, evaluate_metric(count_order_keys)
FROM sales_view
GROUP BY nation_name, customer_name;

Example Query with only the same metric field

SELECT evaluate_metric(count_order_keys)
FROM sales_view

Using WHERE and HAVING Clauses

Metric View queries support both WHERE and HAVING clauses for filtering data.

• Example with WHERE: The WHERE clause filters the source data before grouping.

SELECT nation_name, customer_name, evaluate_metric(count_order_keys)
FROM sales_view
WHERE nation_name LIKE 'E%'
GROUP BY nation_name, customer_name;

• Example with HAVING: The HAVING clause filters the results after the metrics have been evaluated and grouped.

SELECT nation_name, customer_name, evaluate_metric(count_order_keys)
FROM sales_view
GROUP BY nation_name, customer_name
HAVING nation_name LIKE 'E%' AND evaluate_metric(count_order_keys) >= 2;

Advanced Grouping with Derived Functions

You can apply derived functions or conditional logic to dimension attributes within the SELECT and GROUP BY clauses.

• Using a derived function:

SELECT concat(region_name, '_', nation_name), evaluate_metric(count_order_keys)
FROM sales_view
GROUP BY nation_name, region_name;

• Using Conditional Logic (CASE):

SELECT (CASE WHEN (trim(region_name) = 'AFRICA' AND trim(nation_name) = 'ETHIOPIA') THEN 1 ELSE 0 END) AS "condField",
       evaluate_metric(count_order_keys)
FROM sales_view
GROUP BY nation_name, region_name;

Consumption via BI Tools¶

Metric Views are designed to be consumed by standard Business Intelligence (BI) tools. This section describes how to configure and use Power BI to access and visualize metrics from Denodo.

Connecting from Power BI

To access Denodo Metric Views, you should use the Denodo connector integrated into Power BI.

Denodo connector in Power BI¶

The connection is established via ODBC. It is essential to have the Denodo ODBC driver previously configured on your system. For detailed instructions on setting up the ODBC access, refer to the section Access Through ODBC.

When configuring the connection in Power BI, ensure the settings match your DSN configuration.

ODBC configuration for Denodo in Power BI¶

Selecting and Loading Metric Views

Once the connection is established, the Navigator will display the available views. Select the Metric View you wish to consume.

Selecting a Metric View in Power BI Navigator¶

After loading the view, the available fields will appear in the Data pane on the right. Power BI distinguishes between dimensions and metrics based on their metadata:

• Dimension Attributes: Fields without a summation symbol (∑) are categorical fields used for grouping and filtering.

• Metrics: Fields marked with the summation symbol (∑) represent the pre-defined metrics within the Metric View.

Dimension attributes and metrics in Power BI Data pane¶

Visualizing Data

By selecting the desired dimensions and metrics, Power BI automatically generates and executes a VQL query in the background. Denodo then aggregates the metrics according to the selected dimensions.

Metric View visualization in Power BI¶

You can also apply Power BI filters to further refine the results. These filters are also delegated to Denodo to ensure optimal performance.

Applying filters in Power BI¶

Creating Derived Views Over Metric Views¶

You can create derived views (such as selection or projection views) based on a Metric View. The only requirement is that the query defining the derived view must follow the same structure as standard VQL queries against Metric Views (see section Querying via VQL).

Specifically, the query must include at least one dimension attribute, use the evaluate_metric() function for the metrics, and include the corresponding GROUP BY clause.

For example, you can create a projection view over a Metric View as follows:

CREATE OR REPLACE VIEW dv_sales_view AS
SELECT nation_name, customer_name, evaluate_metric(count_order_keys)
FROM sales_view
GROUP BY nation_name, customer_name;

The following image shows the creation and execution of the dv_sales_view:

Creating and executing a derived view over a Metric View¶

Following this same logic, a Metric View can be used as part of a join, a union, or any type of subquery, as long as the subquery accessing the Metric View adheres to the required query structure.

Wizard to create a Derived Views Over Metric Views¶

To simplify the creation of derived views over Metric Views, a specialized wizard is available. This wizard guides you through the process of selecting dimensions, metrics, and applying conditions, ensuring the resulting query follows the required structure (use of evaluate_metric() and GROUP BY).

The following steps describe how to use this wizard:

1. Open the wizard: From the Server Explorer, right-click on the Metric View and select New > Selection.

   

2. Wizard initialization: The wizard opens showing the Metric View as the source.

   

3. Where conditions (optional): You can specify filter conditions in the Where Conditions tab.

   

4. Group by: Use the drag-and-drop system to specify the dimension attributes by which the metrics will be grouped.

   

5. Output tab: In this tab, you select the fields to be projected. Similar to a standard derived view, you can include any desired fields.

   

6. Include aggregate fields: If you want to include an aggregate field (a metric), you must do it from this section.

   

7. Expression definition: A window will appear to define the expression. Note that you must use the evaluate_metric() function for metric fields of the Metric View.

   

8. Save and Summary: Finally, after including all fields, clicking Save will redirect you to the summary of the created derived view.

   

Standard Privileges and Permissions¶

Access to Metric Views is managed through the standard Virtual DataPort authorization system. You can grant EXECUTE and METADATA privileges to users and roles at the metric view level.

For more information on how to configure these permissions, please refer to the section Administration of Databases, Users, Roles and Their Access Rights.

Security Policies and Fine-Grained Restrictions¶

Currently, Metric Views have the following characteristics regarding data access restrictions:

• Row restrictions, masking and custom policies: They cannot be applied directly on Metric views due to its special characteristics. The way to apply them is to configure them over the used views inside the metric model, in that way they would be fully respected and enforced during the execution of a Metric View.

  • Global Security Policies: Global Security Policies will not be applied directly over Metric Views. Policies will apply to the underlying views.

    

    Creating a Global Security Policies shows a warning related to metrics¶

    

    Warning inside summary metric when it has assigned a tag¶

• Column Privileges: You can define column-level privileges on Metric Views to restrict access to specific dimension attributes or metrics. See the section Column Privileges for details.

• Indirect Access: Metric Views support the Indirect Access privilege, allowing you to control how data is accessed through derived elements. For more information, see the section Limit Indirect Access to Views Shared with Other Development Teams.

Materialization and Smart Query Acceleration using Summaries¶

Metric Views cannot be directly cached or materialized. The correct method to achieve this is either by materializing the underlying views, building the metrics over a single view, or leveraging Summaries.

When it comes to Summaries, Metric Views are designed to operate seamlessly alongside Smart Query Acceleration Using Summaries. Summaries are built to accelerate ad-hoc reporting and analytics scenarios by pre-calculating and storing the aggregation results of frequently queried metrics. Metric Views serve as a perfect partner here because their metrics and aggregations are defined in advance, maximizing summary utilization. Blending Metric Views with Summaries stands as a performance optimization best practice, allowing you to pre-compute and store frequently requested metrics grouped by specific dimensions. When paired with the Smart Query Acceleration Using Summaries and the Automatic Summary Recommendations capabilities, query response times for metric views can be drastically reduced and optimized.

For example, consider a model with the following relationship: region - nation - customer - orders, and having a VQL metric like this:

CREATE OR REPLACE METRIC VIEW sales_view
    SOURCES (
        nation AS psql_tpch_nation,
        orders AS psql_tpch_orders,
        region AS psql_tpch_region,
        customer AS psql_tpch_customer
    )
    ASSOCIATIONS (
        nation region psql_tpch_region_psql_tpch_nation LEFT,
        customer nation psql_tpch_nation_psql_tpch_customer,
        orders customer psql_tpch_customer_psql_tpch_orders INNER
    )
    DIMENSIONS (
        orders_dim(
            order_date AS orders.o_orderdate
        ),
        customer_dim(
            customer_address_full AS concat(customer.c_address, '-', nation.n_name),
            customer_name AS customer.c_name,
            customer_address AS customer.c_address
        ),
        nation_dim(
            nation_name AS nation.n_name
        ),
        customer_comment AS customer.c_comment
    )
    METRICS (
        count_order_keys AS count(orders.o_orderkey),
        order_average_value AS avg(orders.o_totalprice)
    );

If your users frequently calculate the number of orders per customer and nationality and also the average price order by the same parameters, you can create a Summary that pre-calculates these values using the following query:

SELECT nation_name, customer_name, evaluate_metric(count_order_keys) as count_orders ,evaluate_metric(order_average_value) as order_average
FROM sales_view
GROUP BY nation_name, customer_name;

When creating the Summary, you would configure it as shown in the following image and using the previous vql:

Creating a Summary for a Metric View¶

So if we execute the metric view with the exact same query, we will see that the summary will be executed instead:

Executing the metric view using the same query from summary¶

Now, if we select a subset from the original query, something like this:

SELECT nation_name, evaluate_metric(count_order_keys)
FROM sales_view
GROUP BY nation_name;

And we execute it, the summary will be executed again instead of the metric, and it would be adapted to the selected subset:

Executing the metric view using a subset from the original query¶

This is a good practice and it is recommended to apply this practice to every KPI used within your business model to ensure optimal performance across the board.

Performance Comparison

The following example demonstrates the performance benefits of using Metric Views in combination with Summaries compared to executing a standard query that performs manual joins and aggregations.

Using the previous query to create the summary:

SELECT nation_name, customer_name, evaluate_metric(count_order_keys),evaluate_metric(order_average_value)
FROM sales_view
GROUP BY nation_name, customer_name;

In the first case, we execute a query against the Metric View that is optimized by a Summary. The execution time is significantly low because the metric values are pre-calculated and they are taking into account the two dimensions of the query.

Execution time using Metric View and Summary (31ms)¶

In contrast, if we execute this query with the summary disabled:

Execution time using standard query without Metric View and Summary (182ms)¶

We can see that the execution time increases and with the summary enable we got a 83% of execution enhancement approximately.

While the difference in this specific example may seem small, it is important to note that the performance gain becomes much more significant as the complexity of the formulas, the number of joins, and the volume of data increase.