Skip to content

SQL query generation

Every data mart is a declarative description of a dataset: the query itself is generated by the system from the composition, the aggregation levels, the filters and the settings of the data mart. The same generated query is used for verification and for the object created in the database, so understanding how it is assembled explains what the result will look like.

What the query is built from

Part of the data mart Effect on the query
Facts and primary dimensions Read directly from their connected source, parsed as connection, table and column
Calculated elements — calculated measures, derived facts and derived dimensions Their formulas are translated into SQL expressions according to the rules of the formula language
Aggregation levels Determine the fields the measure is grouped by
Filters Become conditions that restrict the returned records
Hidden elements Take part in filtering and calculations but are left out of the returned columns
Dimension groups Joined to the fact table through the primary and foreign keys configured in the relationship between them

How the query is assembled

  • The set of tables involved is determined from the connected sources of all elements, and a base table is chosen for the FROM clause. Measure tables have priority; when there are none, the foreign key tables of the dimension groups are used
  • Dimension groups are attached with LEFT JOIN on the primary and foreign key pairs defined by their relationships to the fact tables
  • A measure whose formula has no aggregate function is embedded as an ordinary column of the query. When such a formula carries its own condition, the column is wrapped so that the value is returned only for the rows that satisfy the condition, and NULL otherwise
  • A measure with an aggregation level is calculated in a separate subquery that groups the data by the fields of that level. The subquery is then joined back to the main query on the aggregation keys
  • A filter whose condition references a calculated element is expanded first: the reference is replaced with the SQL expression of that element's formula
  • The query is formatted for the dialect of the target database. If the target database cannot be determined, the query is generated for PostgreSQL

Query shape by data mart configuration

Configuration Query shape
One fact table, all columns from one table A direct SELECT ... FROM ... query
One fact table, columns from several tables A query with LEFT JOIN to the dimension groups and subqueries for aggregated measures
One fact table, grouping with dimension groups and no explicit aggregation levels A two-level query: the inner part aggregates the data by foreign keys, the outer part joins the dimension groups to resolve the dimension values. This keeps the number of joins performed during aggregation low
Several fact tables, UNION One query per fact table, in which columns the fact table does not have are replaced with NULL and a service column fact with the fact table name is added. The queries are combined with UNION ALL
Several fact tables, JOIN One subquery per fact table with its dimensions and measures, plus a subquery holding the unique combinations of dimension values. The result joins each fact table subquery to that set of combinations, and common measures are calculated on top of them

Results and pagination

  • When no row limit is set, the total number of records is determined first, and the query is then extended with the paging instructions of the target dialect so that the result can be browsed page by page
  • When a row limit is selected, the query returns at most that many rows
  • Database errors that occur while the query runs are translated into readable messages

Limitations

  • All elements of a data mart must resolve to a single database connection. Elements from several connections cause an error when the query is generated
  • A filter cannot use an element whose formula contains an aggregate function
  • A data mart with critical errors or an invalid composition is not generated at all: verification and view creation stay unavailable until the composition is corrected