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