Views
The best practice is to use views to specify explicit join paths for SQL API queries. While BI tools would see a view as a table, in fact, no materialization is performed until Cube is queried through the SQL API. At the query time, Cube will try to maximize member pushdown, so only required parts of the view are materialized at query time. Cube also solves fan and chasm traps based on the dimensions selected in the query, so if measure aggregation types are properly set up, you will see correct results in BI tools even though cubes and views are seen just as tables. Consider the following data model:CROSS JOIN and __cubeJoinField
The SQL API also supports joins through the __cubeJoinField virtual column, which
allows to control how specific cubes are joined. This is considered an advanced
functionality, and views should be used where possible. Join can also be done through
CROSS JOIN.
Usage of CROSS JOIN or __cubeJoinField instructs Cube to perform join as it’s defined
in a data model while using provided cubes as join hints.
For example, the following query joins the orders and products tables under
the hood on orders.product_id = products.id, exactly the same way as the
REST (JSON) API query does:
CROSS JOIN:
__cubeJoinField to define joins between cube
tables. In tools that allow defining custom SQL datasets, you can use joined
tables as a dataset SQL. For example:
EXPLAIN plan for this query:
product_description is in the inner selection, it isn’t
evaluated in the final query as it isn’t used in any way.
Joining views on a shared dimension
When you join two views on a dimension that resolves to the same underlying cube member and group by that dimension, Cube doesn’t perform a row-level join. Instead it merges them into a single multi-fact query: each view becomes its own aggregating subquery and the results are stitched together on the shared key, so measures from both views are combined without fan-out.JOIN type (INNER, LEFT, RIGHT, FULL) controls which keys are
kept. This only applies to grouped queries whose GROUP BY is the join key. See
multi-fact views for the full explanation.
Multi-fact queries across views
Joins between views in the SQL API are for multi-fact queries: measures from different views, each aggregated on a dimension the views share, combined into one result. They are not a way to navigate the data model. A dimension you want next to a measure belongs in the view through its join path, not in a join between views. Write a multi-fact query as one common table expression (CTE) per view, aggregated by the shared dimension, and join the CTEs on it:SELECT.
- Put filters inside the CTE they apply to, as
WHERE status = 'completed'above. There they are part of that view’s query. AWHEREon the outer query is applied after the join. - The
JOINtype works as in SQL:FULL JOINkeeps every city from both views,LEFT JOINkeeps the cities of the left view,INNER JOINkeeps the cities present in both. - Aggregating the joined result again, for example
SUM(total_amount)by a coarser dimension, is only correct for additive measures. For non-additive measures, group each CTE at the grain you need.
CUBESQL_SQL_PUSH_DOWN, Cube runs each CTE as its own
query and joins the results as post-processing.