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 Store")
cube = session.create_cube(store, "FirstCube")

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.avg(
    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