How to influence the join path in a query?

[bentesha]

Given I have three cubes A, B, C, and D, which are related as follows:

A -> B -> D
A -> C -> D

In the query below, how does CubeJs determine the join path to D from A? Is there a way to hint that it goes through B and not C? Is there a way to hint this in a query without having to hack schema files?

{
  measures: ['D.total'],
  dimensions: ['A.id', 'A.name']
}

It seems that it is up to CubeJs to determine how to join cubes which could impact results significantly. Anything I am missing in the docs?

[paveltiunov]

@bentesha Hey Benedict! It's a really great question! So most of the times for the case you described separate cube should be introduced because cube semantics inferred from the join. There's a really small percentage of cases where cube should be only one and join route should be selected. Currently there's no way to select such route. It's selected depending on order of measures and then dimensions and filters. We're thinking about providing such an API.

Could you please elaborate on your use case in detail? Domain description would help a lot.

[bentesha]

A quick solution is usually to introduce a cube that joins tables in the order that you prefer. But this approach is hacky and could be overkill if you are working with a lot of tables.

I think it is very important for the join path/direction to be predictable, and users should be able to dictate how this is done from the query aspect.

One way to do this is to introduce hints in queries as follows:

{
  dimensions: ['Customer.id', 'Customer.businessName'],
  measures: ['SalesOrder.total'],
  filters: [{
    dimension: 'Product.id',
    operator: 'equals',
    values: ['abcde']
  }],
  hints: {
    joinOrder: ['Customer', 'SalesOrder', 'SalesOrderItem', 'Product']
  }
}

CubeJS always uses LEFT JOIN to join tables. The hints option presents an opportunity for users to decide on not only the direction of join, but also the order and join type used.

For instance, to indicate that SalesOrder and SalesOrderItem should use INNER JOIN, above hint could be rewriten as follows:

joinOrder: ['Customer, ['SalesOrder', 'SalesOrderItem'], 'Product']

Alternatively, the array could be replaced with string. In which case [] would indicate LEFT and () would indicate INNER join

joinOrder: '[Customer, (SalesOrder, SalesOrderItem), Product]'

Since the hints property is optional, this should be backward compatible.

[paveltiunov]

@bentesha Could you please share what are possible joins for example you provided?

[bentesha]

The problem here is, I have created a frontend report generation tool that allows a user to pick the columns (dimensions) and measure fields they need. What I have noticed is that the order of the join would change depending (not sure on this) on the order in which the columns were selected, even though the order of the columns shouldn't actually have an impact on the way the tables are joined.

Now, there is no way to make sure the join order remains the same regardless of the order in which the columns are specified in the query.

I can write a piece of code to make sure columns are organized in a particular order in a query to get the desired effect, and then re-organizes back the columns in the order the user prefers just to make sure the join order remains the same.

But then, this doesn't seem to be necessary. There should be a way for users to influence the order in which cubes are joined, regardless of the complexity of cube relationships. Leaving everything to the engine to decide how cubes are joined might be counter-productive at times.

The solution of introducing a new cube only works when the query is known during development.
It doesn't work for dynamic queries.

[paveltiunov]

@bentesha Makes sense. And what type of UI you want to build in order to provide your users with ability to select join order?

[ifokeev]

@paveltiunov seems like 1 input and the radio button: input for the column name and the radio for INNER/LEFT.

This feature is legit, I think. Suppose, CubeJS needs to give users more custom settings.

[bentesha]

@bentesha Makes sense. And what type of UI you want to build in order to provide your users with ability to select join order?

@paveltiunov The app our company built for a client is relying on CubeJs for report generation. It has a built-in UI for report customization allowing users to choose columns (dimensions/measures) they want and specify filters to zoom on data.

However, the behavior of CubeJs is not predictable when it comes to joining cubes, as the join order depends on the order in which dimensions are specified. So report data will vary depending on the order in which columns are displayed.

To make things even more complicated, as the application grows, newly introduced cubes might break existing queries as new cubes might introduce alternative join paths to the engine and there is no way to tell which join path CubeJs will choose.

I think the lack of this kind of customization makes CubeJs a solution that is more appealing to small-sized projects with a handful of cubes and simple relationships.

[paveltiunov]

@bentesha Totally makes sense.

There're 2 approaches to solve this problem in general:

1. Don't allow cycles in joins. 95% of all analytics solutions go this way so the problem doesn't exist. As a workaround multiple cubes should be defined for polymorphic relation. Here's more on it: https://cube.dev/docs/polymorphic-cubes#top
2. Allow cycles and provide a way to define join for user.

We went with 2 option in mind so we're open to support this option in API. I'm just trying to figure out how do envision UI for a user so we can provide appropriate API for your use case? Are your users experienced enough to write cube joins in a text field or you want to provide them with all possible options so they can choose one?

[bentesha]

@paveltiunov

But the problem I stated above has nothing do to with cycles. It is just that, when CubeJs sees more than one possible way to join tables, there is no way to tell which one CubeJs should pick from the query point of view.

[paveltiunov]

@bentesha Yeah. Sorry. I should be more specific about terms. By cycles I mean undirected cycles. It's basically polytree VS directed graph.

So I totally got the problem you have. Just trying to figure what's the best solution here. And it's why I'm curious how experienced your users are and what UI here will be acceptable for them?

[bentesha]

@paveltiunov My users have very little technical knowledge. They don't even know what MySQL or Postgre is. They are, however, familiar with productivity tools like MS Excel. To them, a table is just rows and columns and nothing more.

We do have a UI for choosing the type of report a user wants. For instance, "Revenue report". Essentially, each report is a cube. Users can then decide how they would like to view 'revenue', either on a per-day basis, per sales agent, per customer, per region etc. This gives them the flexibility to decide how revenue data should be presented.

Each report has a set of possible 'views'. For example, a user could choose to view revenue by a sales agent, region, customer, as times series data etc. Each view has a set of possible columns a user can choose from.

What the UI tool basically does is guiding the user in choosing a report, configure columns and filters - and the reporting tool would use this information to construct a query and send it to CubeJs

Now, the biggest problem with CubeJs is its query syntax. It is very limited. It needs to be expanded to make it possible for users to specify additional information, like hinting on how cubes should be joined etc.

Another area I think the query syntax needs improvement is specifying how filters are applied - either before or after cubes are joined.

For instance, given the cubes below

cube('Customer', {
  sql: `select id, name from customers`,

  joins: {
    Order: {
      relationship: 'hasMany',
      sql: `${CUBE}.id = ${Order}.customerId`
    }
  },

  measures: {
    count: {
      sql: `id`,
      type: 'count'
    }
  },

  dimensions: {
    id: {
      sql: `id`,
      type: `string`,
      primaryKey: true
    },

    name: {
      sql: `name`,
      type: 'string'
    }
  }
})

cube(`Order`, {
  sql: `select customerId, date, total from orders`,

  measures: {
    count: {
      type: `count`
    },

    total: {
      sql: `total`,
      type: `sum`
    }
  },

  dimensions: {
    id: {
      sql: `id`,
      type: 'number',
      primaryKey: true
    },

    customerId: {
      sql: `customerId`,
      type: `string`
    },

    date: {
      sql: `date`,
      type: 'time'
    }
  }
});

How do we write a query to retrieve customers who ordered less than 200 last month?

The query below does not return customers with zero orders. This is because the date filter is applied after tables are joined and would eliminate all rows with a null date value (i.e. customer with no matching orders) even though such data should be included

{
  "dimensions": ["Customer.id", "Customer.name"],
  "measures": ["Order.total"],
  "timeDimensions": [{
    "dimension": "Order.date",
    "dateRange": "last month"
  }],
  "filters": [{
    "dimension": "Order.total",
    "operator": "lt",
    "values": ["200"]
  }]
}

[paveltiunov]

@bentesha Makes sense. I think we can stick with this format proposed by you for now:

{
  dimensions: ['Customer.id', 'Customer.businessName'],
  measures: ['SalesOrder.total'],
  filters: [{
    dimension: 'Product.id',
    operator: 'equals',
    values: ['abcde']
  }],
  hints: {
    joinOrder: ['Customer', 'SalesOrder', 'SalesOrderItem', 'Product']
  }
}

So the implementation can add cubes to join graph from first to last one. It means there's no requirement to have direct connection between two cubes, only that there's a join path between two.

Regarding LEFT vs INNER join I believe we should stick with this one: #494
For cases where do you need to save NULL rows please see: #482