Custom aggregations and result post-computations

[Stiivi]

There might be aggregates that might be computed from backend-computed aggregates, such as moving averages, rankings or ratios.

Requirements:

• list of common aggregations and other computations provided by cubes should be cubes-global
• there should be way to add custom aggregations
• there should be a way for checking existence of an aggregation function
• backends might have implementation of their own (for example: postgres window/analytical functions)

Implementation:

• backend should check for existence of such calculation when checking the aggregations list of a measure
• aggregation result should be wrapped or converted to a "computing aggregation result"

API

Suggested functions and objects:

• register_aggregation(name, decorated_callable)
• known_aggregations() for agg in known_aggregations()

To be defined

• specification of the decorated_callable
• where the computation is to be executed: in the result? as wrapped result?

Reference:

There is an example implementation by @Squarespace: e2ad5debdff2ac76a5c47d7468610bb1865fa1ae. Currently it is tied to the sql backend and is not easily extensible.

[marcinn]

Hi, @Stiivi.

I'm not sure what "computing aggregation" exactly mean, but I made very simple implementation of "calculated members" (in terms of Mondrian - http://mondrian.pentaho.com/documentation/schema.php#Calculated_members).

My code is incomplete - only sql backend is supporting calculated members, and the formula is almost a plain sql. Look at the changes at https://github.com/marcinn/cubes/commit/0ae1027cdc261b4939242c1e189b3496fe6b75a3 and feel free to grab and adapt this code if you wish. I have no time to do it more flexible, but this code gives me possibility to finish userstory.

Example of definition (model.json):

"measures": [{"name": "some"}],  # assuming default "sum" aggregation
"calculated_members": [{
    "name": "halfsome", 
    "formula": "case when [some_sum]<>0 then [some_sum]/2 else null end"
}]

[marcinn]

Oh, and I didn't tested the slicer. Please treat this as a some idea, not a target implementation.

[Stiivi]

Hi Marcin,

Nice addition.

I am currently working on a small framework called Expressions:

https://github.com/Stiivi/expressions

That parses simple arithmetic expressions and allows one to create a simple compiler. That will be used in the cubes for computed measures where backends such as SQL will use it to create proper constructs of computed expression and convert variable names into concrete objects (values, column references, ...).

Btw. why you introduced "calculated_members" instead adding {"name"="halfsome", "formula": '..."} to the "measures"?

Cheers,

Stefan

On Jul 31, 2013, at 9:55 AM, Marcin Nowak [email protected] wrote:

Hi, @Stiivi.

I'm not sure what "computing aggregation" exactly mean, but I made very simple implementation of "calculated members" (in terms of Mondrian - http://mondrian.pentaho.com/documentation/schema.php#Calculated_members).

My code is incomplete - only sql backend is supporting calculated members, and the formula is almost a plain sql. Look at the changes at marcinn@0ae1027 and feel free to grab and adapt this code if you wish. I have no time to do it more flexible, but this code gives me possibility to finish userstory.

Example of definition (model.json):

"measures": [{"name": "some"}], # assuming default "sum" aggregation "calculated_members": [{ "name": "halfsome", "formula": "case when [some_sum]<>0 then [some_sum]/2 else null end" }] — Reply to this email directly or view it on GitHub.

Stefan Urbanek

Twitter: @Stiivi Personal: stiivi.com Data Brewery: databrewery.org

[marcinn]

Nice addition.

I am currently working on a small framework called Expressions:

https://github.com/Stiivi/expressions

Great! I'll check it.

That parses simple arithmetic expressions and allows one to create a simple compiler. That will be used in the cubes for computed measures where backends such as SQL will use it to create proper constructs of computed expression and convert variable names into concrete objects (values, column references, ...).

Sounds good, I thougth about it the same way.

Btw. why you introduced "calculated_members" instead adding

{"name"="halfsome", "formula": '..."} to the "measures"?

This is an inconsistency. I thought about adding "formula" to measure definition, but at the begining I went another way - by creating CalculatedMember without inheritance of Attribute, just to minimize actual code changes. In fact the only one reason of that implementation was a lack of time. I'll try to find some time to improve it, maybe even using Expressions framework. I think that CalculatedMember should be a kind of Measure (Attribute) rather than a separate entity.

Cheers, Marcin