How to pull data from Jedox with Python

· Jedox

Three practical ways to get cube data out of Jedox and into a pandas DataFrame - the OData Hub, the OLAP HTTP API, and the Integrator - with working request patterns and the gotchas to watch for.

Jedox is excellent at planning and consolidation, but sooner or later you want its numbers somewhere else: in a pandas notebook for analysis, in a reconciliation script, or feeding a Power BI or Tableau model. Here are the three ways I reach for, from quickest to most robust, plus the practical details that trip people up.

One thing to clear up first, because it saves wasted effort: Jedox cubes are multidimensional, not relational. There's no SQL/ODBC driver to SELECT from a cube, and MDX is only a legacy Palo-era add-on, not the native path. What you actually have is a clean JSON read API (the OData Hub), a lower-level CSV one (the OLAP HTTP API), and the Integrator. Exact routes and field names differ between versions (Cloud, on-premise, older Palo-era installs), so treat the snippets as the pattern and confirm against your own instance's /api reference. For the full, version-checked detail, see getting Jedox data into Microsoft Fabric and other servers.

Option 1: the OData Hub (quickest, JSON)

On Jedox Cloud the OData Hub is the friendliest read API: OData v4, JSON responses, HTTP Basic auth. Read a cube - or a stored View for a slice - straight into pandas.

import os
import requests
import pandas as pd

INSTANCE = os.environ["JEDOX_INSTANCE"]                 # your cloud instance id
BASE = f"https://odata.{INSTANCE}.cloud.jedox.com"
AUTH = (os.environ["JEDOX_USER"], os.environ["JEDOX_PASSWORD"])  # Basic auth; SSO not supported

resp = requests.get(
    f"{BASE}/Databases(1)/Views(<your-view-guid>)/Cells",   # or .../Cubes(<id>)/Cells for a whole cube
    auth=AUTH, params={"userules": "true"},
    headers={"Accept": "application/json"}, timeout=120,
)
resp.raise_for_status()
df = pd.json_normalize(resp.json()["value"])            # columns: Path, Value, StringValue (PascalCase!)
print(df.head())

For BI tools like Power BI or Tableau, use the "flat" form with the database name at the end of the URL (https://odata.<instance>.cloud.jedox.com/<DatabaseName>), which lists every cube, dimension and View at once.

Two details that often break a first attempt: the cell fields are PascalCase - read Value, StringValue, Path, not lowercase - and a whole cube pages via @odata.nextLink, so follow that link rather than $top/disablepaging. The detailed post has a ready-made paged reader.

Option 2: the OLAP HTTP API, when you need the raw cube

When you want the cube itself rather than a View, talk to the OLAP server directly - this works on Jedox Cloud and on-premise alike. Its endpoints sit at the host root (the /api path is just the in-browser docs reference). Only the host and port differ, so check the port for your instance: cloud is reached over HTTPS at https://olap.<instance>.cloud.jedox.com, on-prem defaults to http://<host>:7777 but is often configured differently. Responses are semicolon-delimited CSV, not JSON: POST to /server/login for a session id, page through /cell/export, log out.

import os, requests
import pandas as pd

# Endpoints are at the host root. On-prem: http://<host>:7777 | cloud: https://olap.<instance>.cloud.jedox.com
BASE = os.environ.get("JEDOX_OLAP", "http://localhost:7777")
USER, PWD = os.environ["JEDOX_USER"], os.environ["JEDOX_PASSWORD"]

def call(ep, method="GET", **params):
    r = requests.request(method, f"{BASE}{ep}", params=params, timeout=120)
    r.raise_for_status()
    return [ln.split(";") for ln in r.text.splitlines() if ln]

sid = call("/server/login", method="POST", user=USER, extern_password=PWD)[0][0]   # POST! -> sid;ttl
lines = call("/cell/export", sid=sid, name_database="Finance", name_cube="P&L",
             use_rules=1, base_only=1, skip_empty=1, blocksize=50000)
call("/server/logout", sid=sid)

# CSV columns: type ; exists ; value ; path  - and the last line is a progress counter, so drop it
df = pd.DataFrame([(c[3], c[2]) for c in lines[:-1]], columns=["path", "value"])
print(df.head())

This is the workhorse for large or on-prem pulls - just remember to log in with POST, that it's CSV, that the final line is a progress counter, and that big exports page on the last returned cell path. (The full paging loop is in the detailed post.)

Option 3: let the Integrator do the heavy lifting

For anything large or scheduled, do not stream millions of cells through Python. Use the Jedox Integrator to export the cube to a staging database or a file, and let Python read from there:

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine(os.environ["STAGING_DB_URL"])
df = pd.read_sql("SELECT * FROM stg_pnl_actual", engine)

This decouples extraction from analysis, runs on a schedule, and is far kinder to the OLAP server. It is the route I would use in production.

A few good habits

My usual path: explore with the OData Hub, switch to the OLAP HTTP API when the raw cube is needed, and hand the job over to the Integrator once it runs on a schedule.

Back to all posts