Difficulty Adding Computed Metrics in Cube.js

[jhorlima]

Hello,

I am encountering an issue while trying to add computed metrics in my Cube.js schema. I currently have a functioning schema that looks like this:

cube(`fct_users_progress_statuses`, {
  sql: `SELECT * FROM users_progress`,
  preAggregations: {
    main: {
      measures: [
        fct_users_progress_statuses.sum_total_lessons_completed,
        fct_users_progress_statuses.sum_total_lessons,
        fct_users_progress_statuses.total_users,
      ],
      dimensions: [
        fct_users_progress_statuses.sk_dim_organizations,
        fct_users_progress_statuses.sk_dim_courses,
        fct_users_progress_statuses.sk_dim_users,
      ],
      timeDimension: fct_users_progress_statuses.datetime_created_at,
      granularity: `day`,
      partitionGranularity: `month`
    },
  },
  joins: {
    organizations: {
      sql: `${CUBE}.sk_organizations = {organizations}.sk_organizations`,
      relationship: `many_to_one`,
    },
    users: {
      sql: `${CUBE}.sk_users = {users}.sk_users`,
      relationship: `many_to_one`,
    },
    courses: {
      sql: `${CUBE}.sk_courses = {courses}.sk_courses`,
      relationship: `many_to_one`,
    },
  },
  measures: {
    sum_total_lessons_completed: {
      sql: `value_total_lessons_completed`,
      type: `sum`,
    },
    sum_total_lessons: {
      sql: `value_total_lessons`,
      type: `sum`,
    },
    percentage_progress_employees: {
      sql: `(${sum_total_lessons_completed} / CAST(NULLIF(${sum_total_lessons}, 0) AS DOUBLE)) * 100`,
      type: `number`,
    },
    total_users: {
      sql: `(${sk_users_progress})`,
      type: `count`,
    },
  },
  dimensions: {
    sk_users_progress: {
      sql: `sk_users_progress`,
      type: `string`,
      primaryKey: true,
    },
    sk_dim_users: {
      sql: `sk_dim_users`,
      type: `string`,
    },
    sk_dim_organizations: {
      sql: `sk_dim_organizations`,
      type: `string`,
    },
    sk_dim_courses: {
      sql: `sk_dim_courses`,
      type: `string`,
    },
    datetime_created_at: {
      sql: `datetime_created_at`,
      type: `time`,
    },
  },
});

This schema works well when I execute the following cube.load:

cubeApi.load({
  measures: ['fct_users_progress_statuses.percentage_progress_employees'],
  dimensions: ['fct_users_progress_statuses.sk_dim_users'],
  timeDimensions: [{
    dimension: 'fct_users_progress_statuses.datetime_created_at',
    dateRange: ['2024-01-01', '2024-06-30'],
  }]
});

However, I would like to add three new metrics that are calculated based on the progress:

• percentage_complete_contents
• percentage_in_progress_contents
• percentage_unstarted_contents

I implemented them as follows:

total_complete_contents: {
  title: `Total complete contents`,
  type: `count`,
  filters: [
    {
      sql: (CUBE) => `${CUBE.percentage_progress_employees} = 100`,
    },
  ],
},
percentage_complete_contents: {
  title: `Percentage complete contents`,
  type: `number`,
  format: `percent`,
  sql: (CUBE) => `ROUND(${CUBE.total_complete_contents} / ${CUBE.total_users} * 100.0, 2)`,
},
total_in_progress_contents: {
  title: `Total in progress contents`,
  type: `count`,
  filters: [
    {
      sql: (CUBE) => `${CUBE.percentage_progress_employees} > 0 AND ${CUBE.percentage_progress_employees} < 100`,
    },
  ],
},
percentage_in_progress_contents: {
  title: `Percentage in progress contents`,
  type: `number`,
  format: `percent`,
  sql: (CUBE) => `ROUND(${CUBE.total_in_progress_contents} / ${CUBE.total_users} * 100.0, 2)`,
},
total_unstarted_contents: {
  title: `Total unstarted contents`,
  type: `count`,
  filters: [
    {
      sql: (CUBE) => `${CUBE.percentage_progress_employees} = 0`,
    },
  ],
},
percentage_unstarted_contents: {
  title: `Percentage unstarted contents`,
  type: `number`,
  format: `percent`,
  sql: (CUBE) => `ROUND(${CUBE.total_unstarted_contents} / ${CUBE.total_users} * 100.0, 2)`,
},

Unfortunately, I encounter an error: Type.Error: sql.match is not a function.

Currently, I am calculating these metrics on the backend by executing cube.load, retrieving the data, and then returning the three measures. However, I am facing a limitation of 50k records and I would prefer not to proceed in this manner. I would like suggestions on how to add these metrics directly in Cube.js. I attempted using the asyncModule, but it was not clear how to implement it correctly.

I want to add these three measures by grouping the sk_dim_courses to calculate progress based on the completion of all courses by the user. Subsequently, I aim to count the percentage of users who have completed all courses, the percentage of users who have completed at least one lesson, and the percentage of users who have not started any lessons.

Thank you for your assistance!