Data Querying

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

There are two ways to query data:

  • with cube.query by passing a list of measure and level objects

  • with session.query_mdx by passing an MDX string

Querying current session

[1]:

import atoti as tt

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

Querying a cube

[2]:

m = cube.measures
lvl = cube.levels
cube.query(m["Amount.SUM"], m["Quantity.SUM"], levels=[lvl["Product"]])

[2]:
Amount.SUM Quantity.SUM
Product
BED_24 20,100.00 134.00
BED_25 39,600.00 132.00
BED_26 51,350.00 130.00
BED_27 58,080.00 132.00
BED_3 19,800.00 132.00
... ... ...
TSH_52 3,120.00 130.00
TSH_53 3,168.00 132.00
TSH_7 2,620.00 131.00
TSH_8 2,640.00 132.00
TSH_9 2,860.00 130.00

61 rows × 2 columns

MDX Query

[3]:

mdx_query = """
SELECT
  NON EMPTY [Measures].[Amount.SUM] ON COLUMNS,
  NON EMPTY [Hierarchies].[Product].[Product].Members ON ROWS
  FROM (
    SELECT
    {
      [Hierarchies].[Date].[ALL].[AllMember].[2020-05-14],
      [Hierarchies].[Date].[ALL].[AllMember].[2020-05-15],
      [Hierarchies].[Date].[ALL].[AllMember].[2020-05-16]
    } ON COLUMNS
    FROM [Sales]
  )
"""

[4]:

session.query_mdx(mdx_query)

[4]:
Amount.SUM
Product
BED_24 2,400.00
BED_25 4,800.00
BED_26 5,530.00
BED_27 7,040.00
BED_3 1,800.00
... ...
TSH_52 336.00
TSH_53 360.00
TSH_7 260.00
TSH_8 280.00
TSH_9 352.00

61 rows × 1 columns

Querying remote cube

It is possible to connect to an existing cube and query it. This cube can be started by another atoti process or even a classic ActivePivot (version >=5.7).

To do that you need to login and open a query session that will only let you query the cubes but not modify them.

In this example we will connect to the session created before with atoti:

[5]:

existing_session = tt.open_query_session(
    session.url,  # Here you would put the base URL of the existing session.
    # if we needed authentication we could pass it like that
    # auth=tt.query.create_basic_authentication("admin", "admin"),
)
existing_session

[5]:
  • http://localhost:42277
    • Cubes
      • Sales
        • Dimensions
          • Hierarchies
            • Date
              1. Date
            • Product
              1. Product
            • Sale ID
              1. Sale ID
            • Shop
              1. Shop
        • Measures
          • Amount.MEAN
            • formatter: None
          • Amount.SUM
            • formatter: None
          • Quantity.MEAN
            • formatter: None
          • Quantity.SUM
            • formatter: None
          • Unit price.MEAN
            • formatter: None
          • Unit price.SUM
            • formatter: None
          • contributors.COUNT
            • formatter: None

Querying a remote cube

You can retrieve the cubes of the remote session to access their levels and measures and query it.

[6]:

existing_cube = existing_session.cubes["Sales"]
lvl = existing_cube.levels
m = existing_cube.measures
existing_cube.query(m["Amount.SUM"], m["Quantity.SUM"], levels=[lvl["Product"]])

[6]:
Amount.SUM Quantity.SUM
Product
BED_24 20,100.00 134.00
BED_25 39,600.00 132.00
BED_26 51,350.00 130.00
BED_27 58,080.00 132.00
BED_3 19,800.00 132.00
... ... ...
TSH_52 3,120.00 130.00
TSH_53 3,168.00 132.00
TSH_7 2,620.00 131.00
TSH_8 2,640.00 132.00
TSH_9 2,860.00 130.00

61 rows × 2 columns

Remote MDX query

[7]:

existing_session.query_mdx(mdx_query)

[7]:
Amount.SUM
Product
BED_24 2,400.00
BED_25 4,800.00
BED_26 5,530.00
BED_27 7,040.00
BED_3 1,800.00
... ...
TSH_52 336.00
TSH_53 360.00
TSH_7 260.00
TSH_8 280.00
TSH_9 352.00

61 rows × 1 columns