Queries

You can execute queries and get back the result as a pandas DataFrame.

The queries can be made by passing an MDX string or by giving a list of measure and level objects.

You can even query already running sessions such as the ones started from a Java project.

New session

[1]:

import atoti as tt

session = tt.create_session()
store = session.read_csv("data/example.csv", keys=["ID"])
cube = session.create_cube(store, "Cube")

Cubes in your session can be queried like that:

[2]:

mdx = """SELECT
  {
    [Measures].[Price.AVG],
    [Measures].[Quantity.SUM]
  } ON COLUMNS,
  NON EMPTY Crossjoin(
    [Hierarchies].[Color].[Color].Members,
    [Hierarchies].[Continent].[Continent].Members
  ) ON ROWS
  FROM [Cube]"""
session.query_mdx(mdx)

[2]:
Price.AVG Quantity.SUM
Color Continent
blue Asia 405.000000 3500.0
Europe 450.000000 4500.0
green Asia 350.000000 4000.0
Europe 460.000000 3000.0
red Asia 360.000000 2200.0
Europe 466.666667 5500.0

Existing session

Queries can also be executed on existing sessions:

[3]:

from atoti.query import BasicAuthentication

existing_session = tt.open_query_session(
    session.url,  # Here you would put the base URL of the existing session.
    BasicAuthentication("admin", "admin"),
)
existing_session

[3]:
  • http://localhost:42369
    • Cubes
      • Cube
        • Dimensions
          • Hierarchies
            • City
              1. City
            • Color
              1. Color
            • Continent
              1. Continent
            • Country
              1. Country
            • Date
              1. Date
            • ID
              1. ID
        • Measures
          • Price.AVG
            • formatter: #,###.00
            • visible: True
          • Price.SUM
            • formatter: #,###.00
            • visible: True
          • Quantity.AVG
            • formatter: #,###.00
            • visible: True
          • Quantity.SUM
            • formatter: #,###.00
            • visible: True
          • contributors.COUNT
            • formatter: #,###;-#,###
            • visible: True
          • update.TIMESTAMP
            • formatter: None
            • visible: False

MDX query

[4]:

existing_session.query_mdx(mdx)

[4]:
Price.AVG Quantity.SUM
Color Continent
blue Asia 405.000000 3500.0
Europe 450.000000 4500.0
green Asia 350.000000 4000.0
Europe 460.000000 3000.0
red Asia 360.000000 2200.0
Europe 466.666667 5500.0

Object-based query

[5]:

existing_cube = existing_session.cubes["Cube"]
lvl = existing_cube.levels
m = existing_cube.measures
existing_cube.query(
    m["Price.AVG"], m["Quantity.SUM"], levels=[lvl["Color"], lvl["Continent"]],
)

[5]:
Price.AVG Quantity.SUM
Color Continent
blue Asia 405.000000 3500.0
Europe 450.000000 4500.0
green Asia 350.000000 4000.0
Europe 460.000000 3000.0
red Asia 360.000000 2200.0
Europe 466.666667 5500.0