Arithmetic expressions – proof of concept

[Stefan Urbanek]

Hi,

Preliminary implementation of support for arithmetic expressions is available in the branch `sqlrefactor`. It is a proof of concept. See the examples/hello_world for a trivial example:

https://github.com/DataBrewery/cubes/blob/sqlrefactor/examples/hello_world/model.json#L32

With this feature you can specify any attribute to be derived from other attribute(s) and for aggregate to have custom aggregation function.

Requirements:

* expressions (latest 0.2.2) from https://github.com/DataBrewery/expressions

* grako from https://pypi.python.org/pypi/grako/3.5.1 (pip installable)

USE AND EXAMPLE

In model, any attribute (dimension attribute, measure, aggregate, cube detail) can have an “expreesion” property set. Attributes used in the expressions MUST be other logical attributes. Only base attributes (those without expressions) require to have physical column mappings.

Example:

{“name”: “price_with_vat”, “expression”: “price * 1.2”}

{“name”: “price_with_discount”, “expression”: “price * (1 - discount / 100)”}

The expressions currently support basic arithmetics and few SQL functions. The expression language and operators are inspired (and will very likely follow) the Postgres SQL dialect, but is not going to be 100% compatible. Language will be extended gently, with regard to other backends or SQL dialects. (Note that the expression language is meant to be shared with other, non-Cubes tools)

HELPERS

Few helper functions and methods were added to ease use of the expressions in the model. Just to name a few:

* Cube.base_attributes - returns all attributes that don’t have expressions and are very likely represented by a physical column

* Cube.attribute_dependencies - returns a dictionary saying which attribute directly depends on which other attributes

* Cube.collect_dependencies - dictionary of all, deep dependencies (whole attribute dependency tree is expanded)

in SQL there is a whole new simple module sql/expressions.py that compiles the expression into the SQLAlchemy object graph. The whole evaluation is safe, no python `eval()` is used in the process.

This addition of expressions was possible due to the redesigned SQL query generator (will post more information later, once structure is stabilized).

SUMMARY

The branch sqlrefactor should be considered unstable. Please report any bugs here or file them at:

https://github.com/databrewery/cubes/issues

More unittests are needed. Any contributions to the test suite is more than welcome.

Please, let me know what do you think and how it works for you.

Cheers,

Stefan

—

I brew data

Home: www.stiivi.com

Twitter: @Stiivi