how can i use a javascript function

[aravindravva]

Problem

I am pretty new to the cube,.js , we are doing a POC for with cubejs for our analytics page
we have a usecase where we need to transform data based on category , the categories size can be very big can range upto 1000 records which we are thinking to store in a javascript map.

To get you the scenario I have tried to simulate a scenario with genders .
Tried with chatGPT it showed to use case but coming to the practicality with 1000 + categories the performance will be degraded . So can you guys please suggest probably the computationally less expensive way .

Tried like this getting sex as value instead of the db value

Related Cube.js schema

const map = new Map([
  ["M", "Male"],
  ["F", "Female"],
]);

const generateCaseStatement2 = (sex) => {
  return map.get(sex)
};


cube(`olympics_history`, {
  sql_table: `public.olympics_history`,
  
  data_source: `default`,
  

  joins: {
    
  },
  
  dimensions: {
    id: {
      sql: `id`,
      type: `number`,
      primary_key: true
    },
    
    sex: {
      sql: `sex`,
      type: `string`
    },

    age: {
      sql: `age`,
      type: `string`
    },
    
    height: {
      sql: `height`,
      type: `string`
    },
    
    weight: {
      sql: `weight`,
      type: `string`
    },
    gender: {
      type: `string`,
      sql: `${generateCaseStatement2(sex)}`,
    },
    
    team: {
      sql: `team`,
      type: `string`
    },
    
    noc: {
      sql: `noc`,
      type: `string`
    },
    
    games: {
      sql: `games`,
      type: `string`
    },
    
    season: {
      sql: `season`,
      type: `string`
    },
    
    city: {
      sql: `city`,
      type: `string`
    },
    
    sport: {
      sql: `sport`,
      type: `string`
    },
    
    event: {
      sql: `event`,
      type: `string`
    },
    
    medal: {
      sql: `medal`,
      type: `string`
    },
    
    name: {
      sql: `name`,
      type: `string`
    }
    },
  
  measures: {
    count: {
      sql: `count(*)`,
      type: `number`,
    },
    countryCount:{
      sql:`count(distinct noc)`,
      type: `number`
    },
    gamesCount:{
      sql:`count(distinct games)`,
      type: `number`
    }
  },
  
  pre_aggregations: {
    // Pre-aggregation definitions go here.
    // Learn more in the documentation: https://cube.dev/docs/caching/pre-aggregations/getting-started
  }
});

})

Related Cube.js generated SQL

SELECT
  "olympics_history".**undefined** "olympics_history__gender",
  count(*) "olympics_history__count"
FROM
  public.olympics_history AS "olympics_history"
GROUP BY
  1
ORDER BY
  2 DESC
LIMIT
  5000

Also tried using like this '${generateCaseStatement2(${CUBE}.sex)}' getting the same issue

[igorlukanin]

Hi @aravindravva 👋

I would suggest two ways to implement this:

• Either apply the transformation on the consumption side (i.e., after you receive the data from the API). There you can map the data in any way you want, and JavaScript should be performant enough for that.
• Or apply the transformation on the database side. In this case, you can have a separate table for your mappings, and maybe a separate Cube that is joined to the existing one. Then, you can rely on the database to perform the join and send you already transformed data.

I hope this helps.

[aravindravva]

Thanks for the quick reply
How will be the performance in case of another cube , base collection records can range to 10-20M and the static cube can have 1000 records.

can you point the implementation so i can refer :)

[igorlukanin]

The performance would depend on the database that you use but I don't think that joining to tables with just 1000 records should be an issue fr any database. Moreover, when you implement pre-aggregations in Cube, no joins will be needed at the query time, the data will be joined when the pre-aggregations are built.

As for an example, you can take a look at the docs: https://cube.dev/docs/product/data-modeling/concepts/working-with-joins