Hierarchies

Cubes are structured in dimensions, hierarchies and levels.

[1]:
import atoti as tt

session = tt.create_session()
store = session.read_csv("data/example.csv", keys=["ID"], store_name="First")
cube = session.create_cube(store, "FirstCube")
Welcome to atoti 0.4.0!

By using this community edition, you agree with the license available at https://www.atoti.io/eula.
Browse the official documentation at https://docs.atoti.io.
Join the community at https://www.atoti.io/register.

You can hide this message by setting the ATOTI_HIDE_EULA_MESSAGE environment variable to True.

Evaluating cube.hierarchies will display the structure of the cube:

[2]:
lvl = cube.levels
h = cube.hierarchies
h
[2]:
  • Dimensions
    • Hierarchies
      • City
        1. City
      • Color
        1. Color
      • Continent
        1. Continent
      • Country
        1. Country
      • Date
        1. Date
      • ID
        1. ID

Change dimension name of the hierarchy

A dimension can be seen as a folder of hierarchies. By default, all the hiearchies are put in the same dimension called Hierarchies.

[3]:
h["Color"].dimension = "Physical attribute"
h
[3]:
  • Dimensions
    • Hierarchies
      • City
        1. City
      • Continent
        1. Continent
      • Country
        1. Country
      • Date
        1. Date
      • ID
        1. ID
    • Physical attribute
      • Color
        1. Color

Delete a hierarchy

Any hierarchy can be deleted using del

[4]:
del h["Date"]
h
[4]:
  • Dimensions
    • Hierarchies
      • City
        1. City
      • Continent
        1. Continent
      • Country
        1. Country
      • ID
        1. ID
    • Physical attribute
      • Color
        1. Color

Slicing or not

A slicing hierarchy will not aggregate the data on all its members. By default the hierarchies are not slicing.

[5]:
h["Color"].slicing = True
[6]:
h["Color"].slicing = False

Multi-level hierarchies

Levels can be grouped into a single hierarchy:

[7]:
h["Geography"] = [lvl["Continent"], lvl["Country"], lvl["City"]]
h
[7]:
  • Dimensions
    • Hierarchies
      • City
        1. City
      • Continent
        1. Continent
      • Country
        1. Country
      • Geography
        1. Continent
        2. Country
        3. City
      • ID
        1. ID
    • Physical attribute
      • Color
        1. Color

If several levels have the same name, you will need to specify the hierarchy to access it.

[8]:
geoContinent = lvl[("Geography", "Continent")]
geoContinent
[8]:
Level(_name='Continent', _column_name='Continent', _data_type='string', _hierarchy=Hierarchy(_name='Geography', _levels={'Continent': ..., 'Country': Level(_name='Country', _column_name='Country', _data_type='string', _hierarchy=..., _comparator=None), 'City': Level(_name='City', _column_name='City', _data_type='string', _hierarchy=..., _comparator=None)}, _dimension='Hierarchies', _slicing=False), _comparator=None)
[9]:
m = cube.measures
cube.query(m["Quantity.SUM"], levels=[lvl[("Geography", "Continent")]])
[9]:
Quantity.SUM
Continent
Asia 9700.0
Europe 13000.0

If you are in this configuration you can do a per using the level object

[10]:
m["Average per country"] = tt.agg.mean(
    m["Quantity.SUM"], scope=tt.scope.origin(lvl[("Country", "Country")]),
)
cube.query(m["Average per country"], levels=[lvl[("Country", "Country")]])
[10]:
Average per country
Country
China 6000.0
France 7500.0
India 3700.0
UK 5500.0
[11]:
lvl["Color"]
[11]:
Level(_name='Color', _column_name='Color', _data_type='string', _hierarchy=Hierarchy(_name='Color', _levels={'Color': ...}, _dimension='Physical attribute', _slicing=False), _comparator=None)

Level comparators

Levels member are ordered when they are displayed using a comparator.

ASC is the default comparator if None is specify, it is the natural ascendant order.

DESC is the opposite of ASC : the descendant natural order.

[12]:
lvl["ID"].comparator = tt.comparator.DESC

It is also possible to specify the first members of a level :

[13]:
lvl["Color"].comparator = tt.comparator.first_members(["green", "blue"])

Create level from numeric store column

Float columns are not converted to a level by default but it is possible to create a level from any store column

[14]:
store["Quantity"].name
[14]:
'Quantity'
[15]:
h["Quant"] = {"My Quantity": store["Quantity"]}
h
[15]:
  • Dimensions
    • Hierarchies
      • City
        1. City
      • Continent
        1. Continent
      • Country
        1. Country
      • Geography
        1. Continent
        2. Country
        3. City
      • ID
        1. ID
      • Quant
        1. My Quantity
    • Physical attribute
      • Color
        1. Color
[16]:
cube.query(cube.measures["Price.SUM"], levels=[cube.levels["My Quantity"]])
[16]:
Price.SUM
My Quantity
1000.0 500.0
1500.0 880.0
2000.0 810.0
2200.0 360.0
2500.0 500.0
3000.0 880.0
4000.0 350.0