Add feature to dynamically define measures based on SQL commands in query

[gizmodus]

Add feature to dynamically define measures based on SQL commands in query

[paveltiunov]

@gizmodus I see. So the original idea behind Cube.js schema: it's just JS code and it can be dynamically generated using any external information including data in your DB.

To make it possible to do we still lack couple of things though:

1. Allow to import arbitrary Node.js modules in schema code.
2. API to trigger schema recompile.

@gizmodus Do you think it can solve your problem if this stuff will be in place?

[paveltiunov]

@gizmodus That's pretty interesting one! Could you please elaborate a little bit more on a business use case for that?

[gizmodus]

@paveltiunov Sure! We would like to show statistics to multiple organizational units in a hospital. Each organizational units has its own charts with different content. The definition of these charts is stored in a database in JSON format.
In many cases we can take the relevant data for statistics directly from the database. However, sometimes it would be handy to transform the raw data from the database with a few SQL commands and then use the output for our charts.

Example:
We would like to display a bar chart of the number of patients per week day. In MySQL this can be easily achieved with the following command: DAYNAME(createdAt)
If I haven't missed anything, to achieve this we have to add the following lines to our schema.js under 'dimensions':

createdatweekday: {
      sql: 'DAYNAME(patientCreatedAt)',
      type: 'string'
},

So, whenever an organizational unit comes with a new request for a chart, we have to change schema.js and ship a new build of our application.

However, if we could define these transformations directly in the query (which is part of our configuration for the individual organizational unit), we can add charts with special parameters without a change in the application code.

Hope you this is somewhat understandable, otherwise I'd be happy to elaborate more!

[paveltiunov]

@gizmodus Seems like the goal is to create custom dimensions based on some config stored in your DB?

[gizmodus]

@paveltiunov Excactly 👍

[gizmodus]

@paveltiunov I think it would kind of solve the problem, however result in more complexity as we would have to recreate the schema dynamically by crawling through all chart configs whenever the configuration of a customer changes.

Is it very complicated to implement? I know that is very simple to add a custom dimension in a MySQL query: SELECT DAYNAME(createdAt) as 'createdAtWeekDay' [...]

But then I am not familiar with the architecture of cube.js 😃

[paveltiunov]

@gizmodus it should be as simple as providing hash for your current configuration state:

CubejsServerCore.create({
  schemaVersion: () => sha1(JSON.stringify(config))
})

If schemaVersion changes recompile will be issued.

Does it solve your problem?

[Kylia669]

Are you going to add schemaVersion soon? or is it already in CubejsServerCore config?

[paveltiunov]

@Kylia669 just added. Will be shipped in 0.3.5.