Pre-aggregations with association based access control

I have a use case where I need to authorise users based on an association table. Unfortunately, it seems like I need to build my pre-aggregations with each row including the user’s information, causing the pre-aggregation data to multiply by the number of users. I was hoping I could work around this by using rollup joins to check access after the pre-aggregation is built. But due to #3370 it isn’t possible to filter rollup joins on the joined pre-aggregation right now. Is there a way to work around this without having a pre-aggregation with data multiplication?

eg.

cube('EmployeeStoreAssociation', {
  sql: `select * from employee_store_association`,

  joins: {
    Store: {
      sql: `${CUBE.storeID} = ${Store.id}`
      relationship: `belongsTo`,
    },
    Employee: {
      sql: `${CUBE.employeeID} = ${Employee.id}`,
      relationship: `belongsTo`,
    }
  },

  dimensions: {
    employeeID: {
      type: `number`,
      sql: `employee_id`,
    },
    storeID: {
      type: `number`,
      sql: `store_id`,
    }
  }
};

cube('Store', {
  sql: `select * from store`,
  joins: {
    EmployeeStoreAssociation: {
      sql: `${CUBE.id} = ${EmployeeStoreAssociation.storeID}`,
      relationship: `hasMany`
  },
  dimensions: {
    name: {
      type: `string`,
      sql: `name`,
    }
  }
};

cube('Employee', {
  sql: `select * from employee`,
  joins: {
    EmployeeStoreAssociation: {
      sql: `${CUBE.id} = ${EmployeeStoreAssociation.employeeID}`
      relationship: `hasMany`,
    }
  },
    
  dimensions: {
    email: {
      type: `string`,
      sql: `name`,
    }
  }
};

cube('Order', {
  sql: `select * from order`,
  joins: {
    Store: {
      sql: `${CUBE.storeID} = ${Store.id}`,
      relationship: `belongsTo`,
    }
  },
  measures: {
    totalPriceSum: {
      sql: `${CUBE.totalPrice}`,
      type: `sum`,
    },
  },
  dimensions: {
    storeID: {
      sql: `store_id`,
    },
    totalPrice: {
      sql: `total_price`,
      type: `number`,
    },
  },
  preAggregations: {
    storeTotals: {
      measures: [CUBE.totalPriceSum],
      dimensions: [
        Store.id,
        Employee.email,
      ],
    }
  }
}

The storeTotals preaggregation will need to create a row per store, per employee with access to that store. This is not ideal since it can cause very large build times for pre-aggregations to the point where they will just fail to build due to timing out. Is there a way to work around this without using rollup joins?

Hi @tchell ,
How large your dataset is? Which database do you use? If you use a data warehouse then at least export/batching can be enabled