How to correctly join cubes from different data sources?

[anna-poz]

Problem

I have cube A that is based on a BigQuery database, and cube B that is based on a Postgres database.

Both of these cubes have an id column, and I would like to be able to join them, so I can get and additional column from Postgres that I don't have in BigQuery. In the simplified example below, I would like to end up with a join that has columns for A.id, A.name, B.status (where A.id == B.id).

Is this possible? If so, could you please provide more detail on how to set it up?

I have tried to follow the example in https://cube.dev/docs/reference/data-model/pre-aggregations#rollup_join, but it doesn't quite apply, since I don't have any measures on which I would like to rollup.

Thank you!

Related Cube.js schema

cube(`A`, {
  data_source: `bigquery`,
  ...
  dimensions: {
    id: {
        sql: `id`,
        type: `string`,
        primaryKey: true,
        public: true,
      },
   name: {
        sql: `name`,
        type: `string`,
      },
  }
})

cube(`B`, {
  data_source: `postgres`,
  ...
  dimensions: {
    id: {
        sql: `id`,
        type: `string`,
        primaryKey: true,
        public: true,
      },
   status: {
        sql: `status`,
        type: `string`,
      },
  }
})

[igorlukanin]

Hi @anna-poz 👋

I have tried to follow the example in https://cube.dev/docs/reference/data-model/pre-aggregations#rollup_join, but it doesn't quite apply, since I don't have any measures on which I would like to rollup.

Indeed, rollup join is the way to go here. You don't really need to have measures in those rollups. Could you please try and report if this worked for you?

[anna-poz]

Here is my updated cube definition:

cube(`A`, {
  data_source: `bigquery`,
  sql_table: `table_a`,

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

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

    bId: {
      sql: `b_id`,
      type: `string`,
    },
  },

  pre_aggregations: {
    a_rollup: {
      dimensions: [CUBE.id, CUBE.name, CUBE.bId],
    },

    a_b_rollup: {
      type: `rollup_join`,
      dimensions: [CUBE.id, CUBE.name, CUBE.bId, B.id, B.name],
      rollups: [CUBE.a_rollup, B.b_rollup],
    },
  },

  joins: {
    B: {
      relationship: `belongsTo`,
      sql: `${CUBE.bId} = ${B.id}`,
    },
  },
})

cube(`B`, {
  data_source: `postgres`,
  sql_table: `table_b`,

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

  pre_aggregations: {
    b_rollup: {
      dimensions: [CUBE.id, CUBE.name],
    },
  },
})

What I am trying to do in the cube playground is to create a query with 2 columns: A.name and B.name.

And the error is Internal: Error during planning: Can't find index to join table dev_pre_aggregations.a_a_rollup_ytzup3wj_xn44dln1_1jm0ump on a__b_id. Consider creating index: CREATE INDEX a_a_rollup_ytzup3wj_xn44dln1_1jm0ump_a__b_id ON dev_pre_aggregations.a_a_rollup_ytzup3wj_xn44dln1_1jm0ump (a__b_id)

[anna-poz]

@igorlukanin any thoughts?