cube icon indicating copy to clipboard operation
cube copied to clipboard

Additive measures with rounding

Open aaa-user opened this issue 1 year ago • 0 comments

Is your feature request related to a problem? Please describe. I want to use pre-aggregations, so I should use the following types: count, sum, min, max or count_distinct_approx. In my case it's sum:

planMeasureName: {
    sql: `plan_measure_name`,
    type: `sum`
}

To minimize accumulated error I should use ROUND at the latest moment, after aggregations by chosen granularity. There is no specific parameter to round measures. So, I've tried to add additional measure only for round:

planMeasureName: {
      sql: `ROUND(${planMeasureName}, 0)`,
      type: `number`
}

but it produces error:

Internal: Error during planning: Coercion from [Int64Decimal(5), Int64] to the signature Uniform(1, [Float64, Float32]) failed.

And clause ROUND(${planMeasureName}) produces the same error.

Describe the solution you'd like I don't want to create additional measure only for rounding, and I think there should be specific parameter to round measures. This parameter would influence only on queries responses. But under the hood all granulated measures should be storaged unrounded, because required granularity can be changed and we can't loose tails inside pre-aggregations until final aggregations is done.

Describe alternatives you've considered I haven't found solution in Cube yet and consider to round on front-end. But it's a bad practice for me, because I want to see all calculations inside cube file. I can remember nuance about rounding on front, but next generations of developers shouldn't know about this.

Cube version 0.34.55

aaa-user avatar Feb 15 '24 13:02 aaa-user