MDX Query

You can execute MDX queries on running servers and get back the result as a pandas DataFrame.

Current session

[1]:
import activeviam as av

session = av.create_session()
store = session.read_csv("data/example.csv", keys=["ID"], store_name="store")
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]"""
df = cube.query_mdx(mdx)
df
[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.query import BasicAuthentication, Server

# Base URL of server with supported version (currently must have /pivot/rest/v4 endpoints).
url = "https://example.com"

# See BasicAuthentication's docstring to see how to use environment variables instead.
auth = BasicAuthentication("admin", "admin")

# Create your server.
# server = Server(url, auth)
[4]:
# You can use this MDX on the server sandbox.
mdx = """SELECT
  NON EMPTY [Currency].[Currency].[Currency].Members ON ROWS,
  [Measures].[pnl.SUM] ON COLUMNS
  FROM [EquityDerivativesCube]
"""

# And query it.
# df = server.query_mdx(mdx)
# df