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")

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.

Cubes in your session can be queried like that:

[2]:

mdx = """SELECT
  {
    [Measures].[Price.MEAN],
    [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.MEAN 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.
    auth=BasicAuthentication("admin", "admin"),
)
existing_session

[3]:
  • http://localhost:42521
    • Cubes
      • Cube
        • Dimensions
          • Hierarchies
            • City
              1. City
            • Color
              1. Color
            • Continent
              1. Continent
            • Country
              1. Country
            • Date
              1. Date
            • ID
              1. ID
        • Measures
          • Price.MEAN
            • formatter: #,###.00
            • visible: True
          • Price.SUM
            • formatter: #,###.00
            • visible: True
          • Quantity.MEAN
            • 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.MEAN 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.MEAN"], m["Quantity.SUM"], levels=[lvl["Color"], lvl["Continent"]],
)

[5]:
Price.MEAN 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