Queries

You can execute queries on cubes 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 remote servers such as the ones started from a Java project.

Local session

[1]:
import activeviam as av

local_session = av.create_session()
local_store = local_session.read_csv("data/example.csv", keys=["ID"])
local_cube = local_session.create_cube(local_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]"""
local_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

Remote server

Queries can also be executed on remote servers:

[3]:
from activeviam.remote import BasicAuthentication

# Here you would put the base URL of the remote server.
url = local_session.url

# Set up the auth protocol
auth = BasicAuthentication("admin", "admin")

# Create your server.
remote_session = av.create_remote_session(url, auth=auth)
remote_session
[3]:
  • http://localhost:40773
    • Cubes
      • Cube
        • Dimensions
          • Continent
            • Hierarchies
              1. Continent
          • Color
            • Hierarchies
              1. Color
          • Country
            • Hierarchies
              1. Country
          • City
            • Hierarchies
              1. City
          • ID
            • Hierarchies
              1. ID
          • Date
            • Hierarchies
              1. Date
        • Measures
          • Quantity.AVG
          • Price.AVG
          • Quantity.SUM
          • Price.SUM
          • contributors.COUNT
          • update.TIMESTAMP

MDX query

[4]:
remote_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]:
remote_cube = remote_session.cubes["Cube"]
lvl = remote_cube.levels
m = remote_cube.measures
remote_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