Join path in preaggregations

[thePermission]

Is your feature request related to a problem? Please describe.
I have a datamodel where i can have a cube A, cube B and cube C. Cube A can join to cube B, cube B can join to cube C and cube A can also join to cube C. There is currently no way to define a preaggregation that joins cube A over cube B to cube C instead of directly joining cube A to cube C, except of extending cube A, cube B and cube C and only define the wanted join path. That leads to many exentions of cubes.

Describe the solution you'd like
I want to be able to define a join path in a preaggregation like the following example. (A pre_aggregation doesnt even need to be part of a cube, does it?)

cubes:
  - name: cubeA
    sql_table: cubeA
  
    joins:
      - name: cubeB
        relationship: many_to_one
        sql: "{CUBE.cubeB_id} = {cubeB.id}"
      - name: cubeC
        relationship: many_to_one
        sql: "{CUBE.cubeC_id} = {cubeC.id}"

  - name: cubeB
    sql_table: cubeB
  
    joins:
      - name: cubeC
        relationship: many_to_one
        sql: "{CUBE.cubeC_id} = {cubeC.id}"

pre_aggregations:
  - name: cubeAandC
      cubes:
      - join_path: cubeA
        dimensions:
          - dimension1
        measures:
          - measure1
      - join_path: cubeA.cubeB.cubeC
        dimensions:
          - dimension2
        measures:
          - measure2

Or i want to use a view and configure it to be preaggregated. Thats beeing said, its probably not possible or pretty hard to reuse this pre_aggregation for multiple views, but it would probably still find its usecases and makes it easy to figure out if a pre_aggregation can be used to a query for this view. Example:

views:
  - name: CubeA_and_CubeC_View
 
    cubes:
      - join_path: cubeA
        includes:
        - dimension1
        - measure1
      - join_path: cubeA.cubeB.cubeC
        includes:
          - dimension2
          - measure2

    preAggregation:
      - name: cubeA_and_cubeC_preagg
        includes: "*"
        time_dimension: cubeA.dimension1
        granularity: day

Describe alternatives you've considered
Extending cube A and only define a join to cube C directly

[igorlukanin]

I think this is a great proposal and I feel like the first solution (with join_paths that are similar to the ones in views) is the way forward.

A pre_aggregation doesnt even need to be part of a cube, does it?

It has to be currently, but I definitely see them being moved somewhere else, i.e., separate files.

[pnedelko]

In my case join path is also critical, therefore I use views. And I'm actually interested in pre-aggregations for views only.
My first thought was: "Can I define pre-aggregations on views?".

So it seems like without join_path it will create wrong pre-aggregations?

[igorlukanin]

Het @thePermission @pnedelko 👋

We've been polishing join path support in pre-aggregations recently. Take a look at the following example.

Data model:

cubes:
  - name: cubeA
    sql: >
      SELECT 1 AS id, 1 AS cubeB_id, 1 AS cubeC_id UNION ALL
      SELECT 2 AS id, 2 AS cubeB_id, 2 AS cubeC_id

    dimensions:
      - name: id
        sql: id
        type: number
        primary_key: true

      - name: cubeB_id
        sql: "{CUBE}.cubeB_id"
        type: number

      - name: cubeC_id
        sql: "{CUBE}.cubeC_id"
        type: number

      - name: dimension1
        sql: "CONCAT({CUBE}.cubeB_id, {CUBE}.cubeC_id)"
        type: string

    measures:
      - name: measure1
        type: count

    joins:
      - name: cubeB
        relationship: many_to_one
        sql: "{CUBE.cubeB_id} = {cubeB.id}"
      - name: cubeC
        relationship: many_to_one
        sql: "{CUBE.cubeC_id} = {cubeC.id}"

    pre_aggregations:
    - name: cubeAandC
      dimensions:
        - cubeA.dimension1
        - cubeA.cubeB.cubeC.dimension2
      measures:
        - cubeA.measure1
        - cubeA.cubeB.cubeC.measure2

  - name: cubeB
    sql: >
      SELECT 1 AS id, 1 AS cubeC_id UNION ALL
      SELECT 2 AS id, 2 AS cubeC_id

    dimensions:
      - name: id
        sql: id
        type: number
        primary_key: true

      - name: cubeC_id
        sql: "{CUBE}.cubeC_id"
        type: number

    joins:
      - name: cubeC
        relationship: many_to_one
        sql: "{CUBE.cubeC_id} = {cubeC.id}"

  - name: cubeC
    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

Generated SQL query for the pre-aggregation:

CREATE TABLE prod_pre_aggregations.cube_a_cube_aand_c AS
SELECT
  q_0."cube_c__dimension2",
  q_0."cube_a__dimension1",
  "cube_c__measure2" "cube_c__measure2",
  "cube_a__measure1" "cube_a__measure1"
FROM
  (
    SELECT
      "main__cube_c".id * 3 "cube_c__dimension2",
      CONCAT("main__cube_a".cubeB_id, "main__cube_a".cubeC_id) "cube_a__dimension1",
      count("main__cube_a".id) "cube_a__measure1"
    FROM
      (
        SELECT
          1 AS id,
          1 AS cubeB_id,
          1 AS cubeC_id
        UNION ALL
        SELECT
          2 AS id,
          2 AS cubeB_id,
          2 AS cubeC_id
      ) AS "main__cube_a"
      LEFT JOIN (
        SELECT
          1 AS id,
          1 AS cubeC_id
        UNION ALL
        SELECT
          2 AS id,
          2 AS cubeC_id
      ) AS "main__cube_b" ON "main__cube_a".cubeB_id = "main__cube_b".id
      LEFT JOIN (
        SELECT
          1 AS id
        UNION ALL
        SELECT
          2 AS id
      ) AS "main__cube_c" ON "main__cube_b".cubeC_id = "main__cube_c".id
    GROUP BY
      1,
      2
  ) as q_0
  INNER JOIN (
    SELECT
      "keys"."cube_c__dimension2",
      "keys"."cube_a__dimension1",
      sum("cube_c_key__cube_c".id * 3) "cube_c__measure2"
    FROM
      (
        SELECT
          DISTINCT "cube_c_key__cube_c".id * 3 "cube_c__dimension2",
          CONCAT(
            "cube_c_key__cube_a".cubeB_id,
            "cube_c_key__cube_a".cubeC_id
          ) "cube_a__dimension1",
          "cube_c_key__cube_c".id "cube_c__id"
        FROM
          (
            SELECT
              1 AS id,
              1 AS cubeB_id,
              1 AS cubeC_id
            UNION ALL
            SELECT
              2 AS id,
              2 AS cubeB_id,
              2 AS cubeC_id
          ) AS "cube_c_key__cube_a"
          LEFT JOIN (
            SELECT
              1 AS id,
              1 AS cubeC_id
            UNION ALL
            SELECT
              2 AS id,
              2 AS cubeC_id
          ) AS "cube_c_key__cube_b" ON "cube_c_key__cube_a".cubeB_id = "cube_c_key__cube_b".id
          LEFT JOIN (
            SELECT
              1 AS id
            UNION ALL
            SELECT
              2 AS id
          ) AS "cube_c_key__cube_c" ON "cube_c_key__cube_b".cubeC_id = "cube_c_key__cube_c".id
      ) AS "keys"
      LEFT JOIN (
        SELECT
          1 AS id
        UNION ALL
        SELECT
          2 AS id
      ) AS "cube_c_key__cube_c" ON "keys"."cube_c__id" = "cube_c_key__cube_c".id
    GROUP BY
      1,
      2
  ) as q_1 ON (
    q_0."cube_c__dimension2" = q_1."cube_c__dimension2"
    OR (
      q_0."cube_c__dimension2" IS NULL
      AND q_1."cube_c__dimension2" IS NULL
    )
  )
  AND (
    q_0."cube_a__dimension1" = q_1."cube_a__dimension1"
    OR (
      q_0."cube_a__dimension1" IS NULL
      AND q_1."cube_a__dimension1" IS NULL
    )
  )

As you can see, Cube follows join paths and does cubeA LEFT JOIN cubeB LEFT JOIN cubeC here.

I guess this is exactly what you wanted, right? 😄

[thePermission]

Yea that should work for it. But still i dont feel like pre aggs should be inside a cube to be honest. But still thats a huge step forward!!!! Thx

[igorlukanin]

But still i dont feel like pre aggs should be inside a cube to be honest

I share that sentiment to some extent 😄

However, we can definitely approach this as a separate feature request/enhancement.

[igorlukanin]

Some docs if anyone needs them: https://cube.dev/docs/product/data-modeling/concepts/working-with-joins#join-paths