Joins between cubes

Joins create relationships between cubes in the data model.

They allow to build complex queries that involve members from multiple cubes. They also allow to reference members from other cubes in calculated members, views, and pre-aggregations.

When defining joins, it's important to understand join types and the direction of joins as well as how join paths and join hints are used to work with the joined cubes.

Join types

Cube supports three types of join relationships often found in SQL databases: one_to_one, one_to_many, and many_to_one.

For example, let's take two cubes, customers and orders:

cubes:
  - name: customers
    # ...
 
    dimensions:
      - name: id
        sql: id
        type: number
        primary_key: true
 
      - name: company
        sql: company
        type: string
 
  - name: orders
    # ...
 
    dimensions:
      - name: id
        sql: id
        type: number
        primary_key: true
 
      - name: customer_id
        sql: customer_id
        type: number

We could add a join to the customers cube:

cubes:
  - name: customers
    # ...
 
    joins:
      - name: orders
        relationship: one_to_many
        sql: "{CUBE}.id = {orders.customer_id}"

The join above means a customer has many orders. Let's send the following JSON query:

{
  "dimensions": ["orders.status", "customers.company"],
  "measures": ["orders.count"],
  "timeDimensions": [
    {
      "dimension": "orders.created_at"
    }
  ],
  "order": { "customers.company": "asc" }
}

The query above will generate the following SQL:

SELECT
  "orders".status "orders__status",
  "customers".company "customers__company",
  count("orders".id) "orders__count"
FROM
  public.customers AS "customers"
  LEFT JOIN public.orders AS "orders"
    ON "customers".id = "orders".customer_id
GROUP BY 1, 2
ORDER BY 2 ASC
LIMIT 10000

However, if we have guest checkouts, that would mean we would have orders with no matching customer. Looking back at the one_to_many relationship and its' resulting SQL, any guest checkouts would be excluded from the results. To remedy this, we'll remove the join from the customers cube and instead define a join with a many_to_one relationship on the orders cube:

cubes:
  - name: orders
    # ...
 
    joins:
      - name: customers
        relationship: many_to_one
        sql: "{CUBE}.customer_id = {customers.id}"

In the above data model, our orders cube defines the relationship between itself and the customer cube. The same JSON query now results in the following SQL query:

SELECT
  "orders".status "orders__status",
  "customers".company "customers__company",
  count("orders".id) "orders__count"
FROM
  public.orders AS "orders"
  LEFT JOIN public.customers AS "customers"
    ON "orders".customer_id = "customers".id
GROUP BY 1, 2
ORDER BY 2 ASC
LIMIT 10000

As we can see, the base table in the query is orders, and customers is in the LEFT JOIN clause; this means any orders without a customer will also be retrieved.

Many-to-many joins

A many-to-many relationship occurs when multiple records in a cube are associated with multiple records in another cube.

For example, let's say we have two cubes, topics and posts, pointing to the topics and posts tables in our database, respectively. A post can have more than one topic, and a topic may have more than one post.

In a database, you would most likely have an associative table (also known as a junction table or cross-reference table). In our example, this table name might be post_topics.

The diagram below shows the tables posts, topics, post_topics, and their relationships.

In the same way the post_topics table was specifically created to handle this association in the database, we need to create an associative cube post_topics, and declare the relationships from it to topics cube and from posts to post_topics.

cubes:
  - name: posts
    sql_table: posts
 
    joins:
      - name: post_topics
        relationship: one_to_many
        sql: "{CUBE}.id = {post_topics.post_id}"
 
  - name: topics
    sql_table: topics
 
    dimensions:
      - name: post_id
        sql: id
        type: string
        primary_key: true
 
  - name: post_topics
    sql_table: post_topics
 
    joins:
      - name: topic
        relationship: many_to_one
        sql: "{CUBE}.topic_id = {topics.id}"
 
    dimensions:
      - name: post_id
        sql: post_id
        type: string

In scenarios where a table doesn't define a primary key, one can be generated using SQL:

cubes:
  - name: post_topics
    # ...
 
    dimensions:
      - name: id
        sql: "CONCAT({CUBE}.post_id, {CUBE}.topic_id)"
        type: number
        primary_key: true

Virtual associative cubes. Sometimes there is no associative table in the database, when in reality, there is a many-to-many relationship. In this case, the solution is to extract some data from existing tables and create a virtual (not backed by a real table in the database) associative cube.

Let’s consider the following example. We have tables emails and transactions. The goal is to calculate the amount of transactions per campaign. Both emails and transactions have a campaign_id column. We don’t have a campaigns table, but data about campaigns is part of the emails table.

Let’s take a look at the emails cube first:

cubes:
  - name: emails
    sql_table: emails
 
    measures:
      - name: count
        type: count
 
    dimensions:
      - name: id
        sql: id
        type: number
        primary_key: true
 
      - name: campaign_name
        sql: campaign_name
        type: string
 
      - name: campaign_id
        sql: campaign_id
        type: number

We can extract campaigns data into a virtual campaigns cube:

cubes:
  - name: campaigns
    sql: >
      SELECT
        campaign_id,
        campaign_name,
        customer_name,
        MIN(created_at) AS started_at
      FROM emails GROUP BY 1, 2, 3
 
    measures:
      - name: count
        type: count
 
    dimensions:
      - name: id
        sql: campaign_id
        type: string
        primary_key: true
 
      - name: name
        sql: campaign_name
        type: string

The following diagram shows our data model with the Campaigns cube:

The last piece is to finally declare a many-to-many relationship. This should be done by declaring a one_to_many relationship on the associative cube, campaigns in our case.

cubes:
  - name: emails
    sql_table: emails
 
    joins:
      - name: campaigns
        relationship: many_to_one
        sql: >
          {CUBE}.campaign_id = {campaigns.id} AND {CUBE}.customer_name =
          {campaigns.customer_name}
 
    measures:
      - name: count
        type: count
 
    dimensions:
      - name: id
        sql: id
        type: number
        primary_key: true
 
      - name: campaign_name
        sql: campaign_name
        type: string
 
      - name: campaign_id
        sql: campaign_id
        type: number
 
  - name: campaigns
 
    joins:
      - name: transactions
        relationship: one_to_many
        sql: >
          {CUBE}.customer_name = {transactions.customer_name} AND
          {CUBE}.campaign_id = {transactions.campaign_id}
 
    dimensions:
      - name: id
        sql: id
        type: number
        primary_key: true
 
      - name: customer_name
        sql: customer_name
        type: string

Join tree

When Cube analyzes a query, it builds a join tree that connects all cubes involved in the query in compliance with the direction of joins. If the join tree cannot be built, the query will fail to execute. The presence of bidirectional joins or diamond subgraphs can complicate the join tree structure.

Direction of joins

All joins in Cube's data model are directed. They flow from the source cube (the one where the join is defined) to the target cube (the one referenced in the join).

Cube will respect the join graph when generating SQL queries. It means that source cubes will be on the left side of JOIN clauses and target cubes will be on the right side of JOIN clauses. Consider the following data model, consisting of cubes left and right:

cubes:
  - name: left
    sql: >
      SELECT 1 AS id, 11 AS value UNION ALL
      SELECT 2 AS id, 12 AS value UNION ALL
      SELECT 3 AS id, 13 AS value
 
    dimensions:
      - name: id
        sql: id
        type: number
        primary_key: true
 
      - name: value
        sql: value
        type: number
 
    joins:
      - name: right
        sql: "{left.id} = {right.id}"
        relationship: one_to_one
 
  - name: right
    sql: >
      SELECT 1 AS id, 101 AS value UNION ALL
      SELECT 2 AS id, 102 AS value UNION ALL
      SELECT 3 AS id, 103 AS value
 
    dimensions:
      - name: id
        sql: id
        type: number
        primary_key: true
 
      - name: value
        sql: value
        type: number
 
    # joins:
    #   - name: left
    #     sql: "{left.id} = {right.id}"
    #     relationship: one_to_one

It defines a join that is directed from left to right. If you query for left.value and right.value, Cube will generate the following SQL query. As you can see, left is on the left side of the JOIN clause, and right is on the right side:

SELECT
  "left".value "left__value",
  "right".value "right__value"
FROM (
  SELECT 1 AS id, 11 AS value UNION ALL
  SELECT 2 AS id, 12 AS value UNION ALL
  SELECT 3 AS id, 13 AS value
) AS "left"
LEFT JOIN (
  SELECT 1 AS id, 101 AS value UNION ALL
  SELECT 2 AS id, 102 AS value UNION ALL
  SELECT 3 AS id, 103 AS value
) AS "right" ON "left".id = "right".id
GROUP BY 1, 2

If you comment out the join definition in the left cube and uncomment the join definition in the right cube, Cube will generate the following SQL for the same query:

SELECT
  "left".value "left__value",
  "right".value "right__value"
FROM (
  SELECT 1 AS id, 101 AS value UNION ALL
  SELECT 2 AS id, 102 AS value UNION ALL
  SELECT 3 AS id, 103 AS value
) AS "right"
LEFT JOIN (
  SELECT 1 AS id, 11 AS value UNION ALL
  SELECT 2 AS id, 12 AS value UNION ALL
  SELECT 3 AS id, 13 AS value
) AS "left" ON "left".id = "right".id
GROUP BY 1, 2

As you can see, the direction of joins greatly influences the generated SQL and, conseqently, the final result set.

Bidirectional joins

As a rule of thumb, it's not recommended to define bidirectional joins in the data model (i.e., having both cubes define a join to each other) by default. However, it can still be useful for some valid analytical use cases.

Consider the following data model with orders and customers for an e-commerce that has both registered and guest customers (they have NULL as customer_id):

cubes:
  - name: orders
    sql: >
      SELECT 1 AS id, 1001 AS customer_id, 123 AS revenue UNION ALL
      SELECT 2 AS id, 1001 AS customer_id, 234 AS revenue UNION ALL
      SELECT 3 AS id, 1002 AS customer_id, 345 AS revenue UNION ALL
      SELECT 4 AS id, NULL AS customer_id, 456 AS revenue
 
    dimensions:
      - name: id
        sql: id
        type: number
        primary_key: true
 
      - name: customer_id
        sql: customer_id
        type: number
 
    measures:
      - name: order_count
        type: count
 
      - name: total_revenue
        sql: revenue
        type: sum
 
    joins:
      - name: customers
        sql: "{orders.customer_id} = {customers.id}"
        relationship: many_to_one
 
  - name: customers
    sql: >
      SELECT 1001 AS id, 'Alice' AS name UNION ALL
      SELECT 1002 AS id, 'Bob' AS name UNION ALL
      SELECT 1003 AS id, 'Eve' AS name
 
    dimensions:
      - name: id
        sql: id
        type: number
        primary_key: true
 
      - name: name
        sql: name
        type: string
 
    measures:
      - name: customer_count
        type: count
 
    # joins:
    #   - name: orders
    #     sql: "{orders.customer_id} = {customers.id}"
    #     relationship: one_to_many

Querying customers.name and orders.order_count will produce the following result:

As you can see, the result set only includes registered customers; we get no data for guest checkouts. Check the generated SQL query:

SELECT
  "customers".name "customers__name",
  count("orders".id) "orders__order_count"
FROM (
  SELECT 1001 AS id, 'Alice' AS name UNION ALL
  SELECT 1002 AS id, 'Bob' AS name UNION ALL
  SELECT 1003 AS id, 'Eve' AS name
) AS "customers"
LEFT JOIN (
  SELECT 1 AS id, 1001 AS customer_id, 123 AS revenue UNION ALL
  SELECT 2 AS id, 1001 AS customer_id, 234 AS revenue UNION ALL
  SELECT 3 AS id, 1002 AS customer_id, 345 AS revenue UNION ALL
  SELECT 4 AS id, NULL AS customer_id, 456 AS revenue
) AS "orders" ON "orders".customer_id = "customers".id
GROUP BY 1

The customers cube is on the left side of the JOIN clause, since the direction of joins is from customers to orders. This means that the query will only return registered customers and all orders by guest customers will be excluded.

Now, if you uncomment the join definition in the orders cube and comment out the join definition in the customers cube, running the same query will produce the following result:

As you can see, now the result set includes guest checkouts, but we have no data for registered customers who have not placed any orders (namely, Eve). Check the generated SQL query, which reveals why:

SELECT
  "customers".name "customers__name",
  count("orders".id) "orders__order_count"
FROM (
  SELECT 1 AS id, 1001 AS customer_id, 123 AS revenue UNION ALL
  SELECT 2 AS id, 1001 AS customer_id, 234 AS revenue UNION ALL
  SELECT 3 AS id, 1002 AS customer_id, 345 AS revenue UNION ALL
  SELECT 4 AS id, NULL AS customer_id, 456 AS revenue
) AS "orders"
LEFT JOIN (
  SELECT 1001 AS id, 'Alice' AS name UNION ALL
  SELECT 1002 AS id, 'Bob' AS name UNION ALL
  SELECT 1003 AS id, 'Eve' AS name
) AS "customers" ON "orders".customer_id = "customers".id
GROUP BY 1

Bidirectional joins often lead to the ambiguity in the data model and can produce ambiguous results, as Cube may not know which direction to follow when generating SQL queries. You can remove the ambiguity by using join paths and join hints.

Diamond subgraphs

A diamond subgraph is a specific type of join structure where there's more than one join path between two cubes, e.g., users.schools.countries and users.employers.countries. Join structures like a.b.c + a.c or a.b.c.d + a.b.d are also be considered diamond subgraphs for the purpose of this section.

In the following example, four cubes are joined together as a diamond: a joins to b and c, and both b and c join to d:

cubes:
  - name: a
    sql: >
      SELECT 1 AS id UNION ALL
      SELECT 2 AS id UNION ALL
      SELECT 3 AS id
 
    dimensions:
      - name: id
        sql: id
        type: number
        primary_key: true
 
      - name: d_via_b
        sql: "{b.d.id}"
        type: number
 
      - name: d_via_c
        sql: "{c.d.id}"
        type: number
 
    joins:
      - name: b
        sql: "{a.id} = {b.id}"
        relationship: one_to_one
 
      - name: c
        sql: "{a.id} = {c.id}"
        relationship: one_to_one
 
  - name: b
    sql: >
      SELECT 1 AS id UNION ALL
      SELECT 2 AS id UNION ALL
      SELECT 3 AS id
 
    dimensions:
      - name: id
        sql: id
        type: number
        primary_key: true
 
    joins:
      - name: d
        sql: "{b.id} = {d.id}"
        relationship: one_to_one
 
  - name: c
    sql: >
      SELECT 1 AS id UNION ALL
      SELECT 2 AS id UNION ALL
      SELECT 3 AS id
 
    dimensions:
      - name: id
        sql: id
        type: number
        primary_key: true
 
    joins:
      - name: d
        sql: "{c.id} = {d.id}"
        relationship: one_to_one
 
  - name: d
    sql: >
      SELECT 1 AS id UNION ALL
      SELECT 2 AS id UNION ALL
      SELECT 3 AS id
 
    dimensions:
      - name: id
        sql: id
        type: number
        primary_key: true

When querying a.d_via_b, Cube will generate the following SQL query, joining through b:

SELECT
  "d".id "a__d_via_b"
FROM (
  SELECT 1 AS id UNION ALL
  SELECT 2 AS id UNION ALL
  SELECT 3 AS id
) AS "a"
LEFT JOIN (
  SELECT 1 AS id UNION ALL
  SELECT 2 AS id UNION ALL
  SELECT 3 AS id
) AS "b" ON "a".id = "b".id
LEFT JOIN (
  SELECT 1 AS id UNION ALL
  SELECT 2 AS id UNION ALL
  SELECT 3 AS id
) AS "d" ON "b".id = "d".id
GROUP BY 1

However, when querying a.d_via_c, Cube will generate the following SQL query, joining through c:

SELECT
  "d".id "a__d_via_c"
FROM (
  SELECT 1 AS id UNION ALL
  SELECT 2 AS id UNION ALL
  SELECT 3 AS id
) AS "a"
LEFT JOIN (
  SELECT 1 AS id UNION ALL
  SELECT 2 AS id UNION ALL
  SELECT 3 AS id
) AS "c" ON "a".id = "c".id
LEFT JOIN (
  SELECT 1 AS id UNION ALL
  SELECT 2 AS id UNION ALL
  SELECT 3 AS id
) AS "d" ON "c".id = "d".id
GROUP BY 1

SELECT
  "d".id "a__d_via_b",
  "d".id "a__d_via_c"
FROM (
  SELECT 1 AS id UNION ALL
  SELECT 2 AS id UNION ALL
  SELECT 3 AS id
) AS "a"
LEFT JOIN (
  SELECT 1 AS id UNION ALL
  SELECT 2 AS id UNION ALL
  SELECT 3 AS id
) AS "b" ON "a".id = "b".id
LEFT JOIN (
  SELECT 1 AS id UNION ALL
  SELECT 2 AS id UNION ALL
  SELECT 3 AS id
) AS "d" ON "b".id = "d".id
LEFT JOIN (
  SELECT 1 AS id UNION ALL
  SELECT 2 AS id UNION ALL
  SELECT 3 AS id
) AS "c" ON "a".id = "c".id
GROUP BY 1, 2

Similar to bidirectional joins, diamond subgraphs often lead to the ambiguity in the data model and can produce ambiguous results, as Cube may not know which direction to follow when generating SQL queries. You can remove the ambiguity by using join paths and join hints.

Join paths

Join paths serve as the recommended mechanism to remove the ambiguity of joins in the data model, including cases of bidirectional joins and diamond subgraphs.

A join path is defined as a sequence of cube names, separated by dots, that will be followed in that specific order when resolving a cube or its member, e.g., users.locations.countries.flag_aspect_ratio.

Calculated members

When referencing a member of another cube in a calculated member, you can use a join path to specify the path to follow, as demonstrated in the diamond subgraphs example.

Views

When referencing cubes in a view, you literally provide join paths via the join_path parameter. The bidirectional joins example can be disambiguated with the following views:

views:
  - name: total_revenue_per_customer
    cubes:
      - join_path: orders
        includes:
          - total_revenue
 
      - join_path: orders.customers
        includes:
          - name
 
  - name: customers_without_orders
    cubes:
      - join_path: customers
        includes:
          - name
 
      - join_path: customers.orders
        includes:
          - order_count

Pre-aggregations

When referencing members of another cubes in a pre-aggregation, you can also use join paths, as shown in the following example:

cubes:
  - name: a
    sql: >
      SELECT 1 AS id, 1 AS b_id, 1 AS c_id UNION ALL
      SELECT 2 AS id, 2 AS b_id, 2 AS c_id
 
    dimensions:
      - name: id
        sql: id
        type: number
        primary_key: true
 
      - name: dimension1
        sql: "CONCAT({CUBE}.b_id, {CUBE}.b_id)"
        type: string
 
    measures:
      - name: measure1
        type: count
 
    joins:
      - name: b
        relationship: one_to_one
        sql: "{CUBE}.b_id = {b.id}"
 
      - name: c
        relationship: one_to_one
        sql: "{CUBE}.c_id = {c.id}"
 
    pre_aggregations:
      - name: a_and_c
        dimensions:
          - a.dimension1
          - a.b.c.dimension2
        measures:
          - a.measure1
          - a.b.c.measure2
 
  - name: b
    sql: >
      SELECT 1 AS id, 1 AS c_id UNION ALL
      SELECT 2 AS id, 2 AS c_id
 
    dimensions:
      - name: id
        sql: id
        type: number
        primary_key: true
 
    joins:
      - name: c
        relationship: one_to_one
        sql: "{CUBE}.c_id = {c.id}"
 
  - name: c
    sql: >
      SELECT 1 AS id UNION ALL
      SELECT 2 AS id
 
    dimensions:
      - name: id
        sql: id
        type: number
        primary_key: true
 
      - name: dimension2
        sql: "{CUBE}.id * 3"
        type: string
 
    measures:
      - name: measure2
        sql: "{CUBE.dimension2}"
        type: sum

When this pre-aggregation is built, Cube will generate and execute the following SQL query:

CREATE TABLE prod_pre_aggregations.a_a_and_c AS
SELECT
  "c".id * 3 "c__dimension2",
  CONCAT("a".b_id, "a".b_id) "a__dimension1",
  sum("c".id * 3) "c__measure2",
  count("a".id) "a__measure1"
FROM (
  SELECT 1 AS id, 1 AS b_id, 1 AS c_id UNION ALL
  SELECT 2 AS id, 2 AS b_id, 2 AS c_id
) AS "a"
LEFT JOIN (
  SELECT 1 AS id, 1 AS c_id UNION ALL
  SELECT 2 AS id, 2 AS c_id
) AS "b" ON "a".b_id = "b".id
LEFT JOIN (
  SELECT 1 AS id UNION ALL
  SELECT 2 AS id
) AS "c" ON "b".c_id = "c".id
GROUP BY 1, 2

As you can see, in this query, a is joined to c though b here, even though a direct join from a to c is also defined in the data model. However, Cube respects join paths from the pre-aggregation definition and uses them to generate the SQL query.

Join hints

Join hints serve as an auxiliary mechanism to remove the ambiguity of joins at the query time. When possible, use join paths in the data model instead.

SQL API

In queries to the SQL API, cubes can be joined via the CROSS JOIN clause or via __cubeJoinField. In any case, Cube will analyze the query and follow provided join hints.

Let's run the following query with the data model from the diamond subgraphs example:

SELECT
  a.id AS id,
  d.id AS d_via_b
FROM a
CROSS JOIN b
CROSS JOIN d
GROUP BY 1, 2

The following SQL query will be generated:

SELECT
  "a".id "a__id",
  "d".id "d__id"
FROM (
  SELECT 1 AS id UNION ALL
  SELECT 2 AS id UNION ALL
  SELECT 3 AS id
) AS "a"
LEFT JOIN (
  SELECT 1 AS id UNION ALL
  SELECT 2 AS id UNION ALL
  SELECT 3 AS id
) AS "b" ON "a".id = "b".id
LEFT JOIN (
  SELECT 1 AS id UNION ALL
  SELECT 2 AS id UNION ALL
  SELECT 3 AS id
) AS "d" ON "b".id = "d".id
GROUP BY 1, 2

If the SQL API query contained CROSS JOIN c instead of CROSS JOIN b, then the generated SQL query would contain a join through c instead of b.

REST API

In queries to the REST API, join hints can be provided via the joinHints parameter.

The SQL API query from the previous section can be rewritten as a REST API query with join hints as follows:

{
  "dimensions": [
    "a.id",
    "d.id"
  ],
  "joinHints": [
    ["a", "b"],
    ["b", "d"]
  ]
}

The same SQL query as in the previous section will be generated.

Similarly, if the joinHints parameter contained [["a", "c"], ["c", "d"]] instead of [["a", "b"], ["b", "d"]], then the generated SQL query would contain a join through c instead of b.

Troubleshooting

Can't find join path

Sometimes, you might come across the following error message: Can't find join path to join 'cube_a', 'cube_b'.

It indicates that a query failed because it includes members from cubes that can't be joined in order to generate a valid query to the upstream data source. Please check that you've defined necessary joins and that they have correct directions.

Also, please consider using views since they incapsulate join paths and completely remove the possibility of the error in question. You might also consider setting the public parameter to false on your cubes to hide them from end users.

If you’re building a custom data application, you might use the meta endpoint of the REST API. It groups cubes into connectedComponents to help select those ones that can be joined together.

Primary key is required when join is defined

Sometimes, you might come across the following error message: cube_a cube: primary key for 'cube_a' is required when join is defined in order to make aggregates work properly.

It indicates that you have a cube with joins and pre-aggregations. However, that cube doesn't have a primary key.

When generating SQL queries, Cube uses primary keys to avoid fanouts. A fanout happens when two tables are joined and a single value gets duplicated in the end result, meaning that some values can be double counted.

Please define a primary key dimension in this cube to make joins and pre-aggregations work correctly.

If your data doesn't have a natural primary key, e.g., id, you can define a composite primary key by concatenating most or all of the columns in the table. Example:

cubes:
  - name: cube_a
    # ...
 
    dimensions:
      - name: composite_key
        sql: CONCAT(column_a, '-', column_b, '-', column_c)
        type: string
        primary_key: true

Transitive join pitfalls

Let's consider an example where we have a many-to-many relationship between users and companies through the companies_to_users cube:

cubes:
  - name: users
    sql: >
      SELECT 1 AS id, 'Ali' AS name UNION ALL
      SELECT 2 AS id, 'Bob' AS name UNION ALL
      SELECT 3 AS id, 'Eve' AS name
 
    measures:
      - name: count
        type: count
 
    dimensions:
      - name: id
        sql: id
        type: string
        primary_key: true
 
  - name: companies
    sql: >
      SELECT 11 AS id, 'Acme Corporation' AS name UNION ALL
      SELECT 12 AS id, 'Stark Industries' AS name
 
    dimensions:
      - name: id
        sql: id
        type: string
        primary_key: true
 
      - name: name
        sql: name
        type: string
 
  - name: companies_to_users
    sql: >
      SELECT 11 AS company_id, 1 AS user_id UNION ALL
      SELECT 11 AS company_id, 2 AS user_id UNION ALL
      SELECT 12 AS company_id, 3 AS user_id
 
    joins:
      - name: users
        sql: "{CUBE}.user_id = {users.id}"
        relationship: one_to_many
 
      - name: companies
        sql: "{CUBE}.company_id = {companies.id}"
        relationship: one_to_many
 
    dimensions:
      - name: id
        # Joins require a primary key, so we'll create one on-the-fly
        sql: "CONCAT({CUBE}.user_id, ':', {CUBE}.company_id)"
        type: string
        primary_key: true

With this data model, querying users.count as a measure and companies.name as a dimension would yield the following error: Can't find join path to join 'users', 'companies'.

The root cause is that joins are directed and there's no join path that goes by join definitions in the data model from users to companies or in the opposite direction.

In Visual Model, you can see that both users and companies are to the right of companies_to_users, meaning that there's no way to go from users to companies moving left to right or right to left:

One possible solution is to move one of two joins from the companies_to_users cube to either users or companies cube. Please note that it would affect the query semantics and thus the final result:

cubes:
  - name: users
 
    joins:
      - name: companies_to_users
        sql: "{CUBE}.id = {companies_to_users}.user_id"
        relationship: one_to_many
 
    # ...
 
  - name: companies_to_users
 
    joins:
      # - name: users
      #   sql: "{CUBE}.user_id = {users.id}"
      #   relationship: one_to_many
 
      - name: companies
        sql: "{CUBE}.company_id = {companies.id}"
        relationship: one_to_many
 
    # ...

Now there's a join path that goes by join definitions in the data model from users to companies_to_users to companies. Visual Model also shows that you can reach companies from cubes by going left to right.