Measure verification

Verification generates an SQL query from a measure's formula and lets you run it against the connected database, so you can confirm that the formula is translatable and returns the expected data. It is started with the Verify action in the Measures tab of a fact table and in the Measures catalog of the Data model page.

When the Verify action is available

The action appears only for measures that have a formula
The action is disabled when the connection used as the source of the measure is inactive
The action is blocked when the measure, or any element its formula references, has a critical error. The tooltip in that case reads: "Invalid measures/dimensions are used to calculate this measure"
A circular reference in the formula is not blocked in advance — it is reported as an error when the query is generated

Verification settings

Verify opens the Verify measure dialog with two settings:

Setting	Description
Filter for verification	One of the saved verification filters, used to restrict the data set. The default is No filter. Filters with a configuration error cannot be selected: they are shown greyed out with a warning icon and a tooltip ("Filter configuration error", or "The dimension has been deleted" when the filter references a deleted element)
Result limit	Maximum number of rows in the result: No limit, 10 rows, 50 rows, 200 rows or 500 rows. The default is 10 rows. The setting is hidden for aggregated measures, because an aggregate always returns a single row

For a measure assigned to a fact table, the list offers the verification filters of that fact table. For a measure that is not assigned to any fact table, the list offers the global filters of the project version. Filters are described on the Verification filters page.

Confirm with Verify. The system generates the SQL query and opens the result panel; the query is not executed at this point.

How the SQL query is built

The generated query is assembled from the measure's definition in the RMD and from the physical mapping configured on the Connections layer of the fact table.

Formula expansion. All references to other measures, facts and dimensions are resolved recursively. Derived elements are replaced with their own formulas, down to base elements — primary measures and primary facts that are mapped directly to a database column
Source mapping. Every base element is resolved to its Connected source — the connection, schema, table and column it points at. All elements involved must belong to the same connection; queries that would span two databases are rejected
Table analysis. When every element resolves to one table, the query selects from that table. When several tables are involved, the query joins them using the key links of the dimension groups: the foreign key on the fact table side and the primary key on the dimension group side. Joins are generated as LEFT JOIN
Base table. The table placed in the FROM clause is the one containing the measures of the fact; if there is none, a table referenced by a foreign key is used
Filters. The conditions of the selected verification filter are merged into the WHERE clause together with any conditions contained in the formula itself. Identical conditions are not repeated. When a filter uses a dimension that belongs to a dimension group, the dimension group's key link is used to reach the table that holds the column, so the required join is added to the query
Dialect. Table names, column names, aliases, quoting, the default schema and the join syntax are formatted for the DBMS of the connection: PostgreSQL, MySQL, MS SQL Server or ClickHouse

Function translation. Formula functions are replaced with the equivalent construct of the target DBMS. For example, a year extraction is generated as:

DBMS	Result
PostgreSQL	`EXTRACT(YEAR FROM column)`
ClickHouse	`toYear(column)`
MS SQL Server	`YEAR(column)`
MySQL	`YEAR(column)`

Aggregates with conditions. When a condition applies to an aggregate function (SUM, AVG, COUNT, MIN, MAX, COUNTDISTINCT), it is generated as a CASE WHEN expression inside the aggregate. The value used when the condition does not match is 0 for SUM and NULL for the other aggregates. COUNT results are cast to a numeric type.

Row limiting. For non-aggregate queries the row limit is applied with the syntax of the target DBMS — LIMIT … OFFSET … for PostgreSQL, MySQL and ClickHouse, and OFFSET … ROWS FETCH NEXT … ROWS ONLY for MS SQL Server, which additionally requires an ORDER BY clause and receives a generated one when the query has none.

Reading the generated SQL

The result panel opens at the bottom of the screen and shows the generated query in a read-only code block, formatted with indentation, line breaks and line numbers. Two actions are available above it:

Copy — copies the formatted query to the clipboard
Export code to text file — downloads the query as a .sql file

Executing the query and reading the result

Execute SQL script runs the generated query against the connected database.

The button is available to the Super administrator, Administrator, Developer and Analyst roles, and requires a valid license. A Viewer can read the generated SQL but cannot run it
The result is shown under the heading Script result as a table. An aggregated measure produces a single row with a single value; a non-aggregate measure produces one column with the calculation result plus the columns of the fields used in the query
Save the current page to a file exports the rows currently loaded in the table to a CSV file
If the query returns nothing, the panel shows: "Script execution results not found"

Paging behaviour depends on the row limit chosen before verification: when a limit of 10, 50, 200 or 500 rows was selected, the whole capped set is returned at once and there are no page controls. When No limit was selected (or the measure is aggregated), the result is paged and each page is fetched from the database.

Errors

Errors are reported as notifications. The most common ones and their meaning:

Message	Meaning
"Calculated measure {name} creates a loop in the formula" / "Circular dependency detected"	The formula references itself directly or through another measure. Fix the references before verifying
"Source not found for measure: {name}" / "Source not found for fact: {name}"	A base element has no Connected source configured. Map it on the Connections layer
"Failed to parse source for measure: {name}"	The stored connected source of the element is not in a usable format
"Multiple RDBs are not supported"	The formula mixes elements from different connections
"Measures reference multiple tables" / "Dimensions reference multiple fact tables"	The elements involved cannot be resolved into one consistent query
"No foreign keys found for dimensions" / "Cannot build a join" / "Invalid dimension keys"	A join is required but the key link is missing or incomplete. Check the foreign key on the fact table and the primary key on the dimension group
"Unable to determine base table"	The query has no table that can be placed in the `FROM` clause
"Aggregate functions are not supported in filters"	A filter condition contains an aggregate function
"Filter not found" / "Not all filters found"	The selected verification filter no longer exists
"Connection is inactive" / "SQL request cannot be executed because connection to the database is unavailable or was deleted"	The connection cannot be used. Check it in the Connections section
"An error occurred while executing the database query"	The database rejected the query. The message of the DBMS is shown with it
"The connection attempt to the database timed out. Please try again"	The database did not answer in time

Notes:

Verification does not change the measure, the fact table or any data in the connected database — it only reads
The query is generated for the DBMS of the connection that the measure's source points at, so the same formula can produce different SQL in different projects
Verification of a measure that is not assigned to a fact table is also available from the Measures catalog of the Data model page