Getting Jedox data into Microsoft Fabric (and any other server)

· Jedox

Every practical way to move cube data out of Jedox and land it in Microsoft Fabric, a SQL warehouse, Postgres or Snowflake, with working Python for each route and the gotchas that bite in production.

Sooner or later your Jedox numbers need to live somewhere else: a Microsoft Fabric Lakehouse, a SQL Server or Snowflake warehouse, a Postgres mart feeding Power BI. This post is the companion to pulling data from Jedox with Python - but where that one is about getting data out, this one is about the full journey: out of Jedox, and into the destination, with the Python for each leg.

One update since this was written: as of the September 2026 update to Jedox 2026.1, Integrator has a native Microsoft Fabric connection, which handles the Fabric case directly and makes several of the routes below unnecessary for that one destination. I have written it up separately in the Fabric connector setup template. The rest of this post still applies to Fabric on older releases, and to every other destination.

A reality check first, because it saves a lot of wasted effort. Jedox cubes are multidimensional, not relational. So a couple of things people expect simply don't exist:

What you actually have are three clean ways out, and I'll go through each, then show where the data lands.

Getting data out of Jedox

Route 1 - the OData Hub (the clean one, for cloud)

If you're on Jedox Cloud (21.3+), the OData Hub is the nicest read API: OData v4, JSON responses, HTTP Basic auth. Read a whole 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

def odata_all(path, **params):
    """Read a whole OData collection, following @odata.nextLink paging."""
    rows, url, send = [], f"{BASE}/{path}", dict(params)
    headers = {"Accept": "application/json", "Prefer": "odata.maxpagesize=50000"}
    while url:
        body = requests.get(url, auth=AUTH, headers=headers, params=send or None, timeout=300).json()
        rows += body.get("value", [])                            # OData wraps the collection in "value"
        nl = body.get("@odata.nextLink")                         # the server hands you the next page
        url = nl if (not nl or nl.startswith("http")) else f"{BASE}/{nl.lstrip('/')}"
        send = None                                              # the nextLink already carries the query
    return rows

# Find the database + cube ids from /Databases and /Databases(<id>)/Cubes, then read its cells.
# Each cell comes back with Path (its coordinates), Value (numeric) and StringValue (text).
cells = odata_all("Databases(1)/Cubes(10)/Cells", userules="true")
df = pd.json_normalize(cells)                                    # columns: Path, Value, StringValue
print(df.head())

Two URL styles - and the "database name at the end" question. The OData Hub comes in two flavours. The snippet above uses the Hierarchical API: the instance is the service root and you navigate entity sets - Databases(1)/Cubes(13)/Cells, Databases(1)/Views(<guid>)/Cells. There's also a Flat API, where you put the database name (or id) at the end of the base URL - https://odata.<instance>.cloud.jedox.com/Biker - and it exposes all of that database's cubes, dimensions and stored Views as one flat list. The Flat API is the one Jedox recommends for Power BI, Tableau and Excel (it's exactly what you paste into Power BI's OData feed), so for those tools, yes - you append the database name. For scripted pulls either works; I use the hierarchical form here because addressing a specific View by its GUID is unambiguous.

The Cells feed returns the cube's stored base cells (its FilledCellCount) - consolidated aggregates aren't listed, so it's base-only by design, and userules="true" adds the rule-calculated figures on top. A whole cube can be huge, so check FilledCellCount (on /Databases(<id>)/Cubes) first, or read a stored View for a slice - Databases(<id>)/Views(<guid>)/Cells, same shape. Mind the casing: the collection envelope is lowercase value, but each cell's fields are PascalCase - Path, Value, StringValue. (This is the bit that bites - read Value, not value.)

Requires a dedicated service account (the OData Hub doesn't do SSO), and the OData Hub is a cloud feature - on-prem usually goes through Route 2.

Route 2 - the OLAP HTTP API (low-level, works everywhere)

The Jedox/Palo OLAP engine speaks a REST-ish HTTP API whose endpoints live at the host root (the /api path you may have seen is just the in-browser docs reference, not the call prefix). It works the same on Jedox Cloud and on-premise - only the host and port differ, so check the port for your own instance: cloud is reached over HTTPS at https://olap.<instance>.cloud.jedox.com, while on-prem defaults to http://<host>:7777 but is often configured differently. Responses are semicolon-delimited CSV, not JSON. The pattern is: POST to /server/login for a session id, page through /cell/export, log out. (The /cube/* endpoints are metadata, not cell data; for a single defined slice rather than a streaming dump, /cell/area reads it in one call.)

import os, requests
import pandas as pd

# Endpoints live at the host ROOT. Cloud: https://olap.<instance>.cloud.jedox.com
# on-prem: http://<host>:7777   (override with JEDOX_OLAP if needed)
OLAP = os.environ.get("JEDOX_OLAP", f"https://olap.{os.environ['JEDOX_INSTANCE']}.cloud.jedox.com")
USER, PWD = os.environ["JEDOX_USER"], os.environ["JEDOX_PASSWORD"]

def call(ep, method="GET", **params):
    r = requests.request(method, f"{OLAP}{ep}", params=params, timeout=300)
    r.raise_for_status()
    # Every response is semicolon-delimited CSV, one record per line
    return [ln.split(";") for ln in r.text.splitlines() if ln]

# 1) Log in with POST -> session id (sid). extern_password is your plaintext password.
sid = call("/server/login", method="POST", user=USER, extern_password=PWD)[0][0]

# 2) Export a cube's base cells in batches, paging on the last cell's path
rows, path = [], None
while True:
    p = {"sid": sid, "name_database": "Finance", "name_cube": "P&L",
         "blocksize": 50000,
         "use_rules": 1,     # include rule-calculated figures
         "base_only": 1,     # base (non-consolidated) cells only
         "skip_empty": 1}    # omit empty cells
    if path is not None:
        p["path"] = path                      # continue AFTER the previous batch's last cell
    block = call("/cell/export", **p)
    cells = block[:-1]                        # last line is a progress counter -> drop it
    if not cells:
        break
    # CSV columns: type ; exists ; value ; path(comma-separated element IDs)
    rows += [(c[3], c[2]) for c in cells]
    path = cells[-1][3]
    if len(cells) < 50000:                    # last (partial) batch -> done
        break

call("/server/logout", sid=sid)

df = pd.DataFrame(rows, columns=["path", "value"])
# 'path' is element IDs in dimension order. Either split it, or resolve ids -> names via
# /cube/info (dimension ids in path order) + /database/dimensions + /dimension/elements:
df = df.join(df["path"].str.split(",", expand=True).add_prefix("dim_"))

The usual stumbling points: log in with POST, not GET; the response is semicolon-delimited (the path field is then comma-delimited inside a line - don't run a comma CSV reader over the whole thing); the last line of every block is a progress counter, not a cell; and paging is a cursor, not offset/limit - feed the last cell's path back in and stop when a batch comes back smaller than blocksize. Cell paths come back as element IDs, not names - resolve them via /cube/info, /database/dimensions and /dimension/elements.

Route 3 - let the Integrator do the heavy lifting

For anything large, recurring, or production, don't stream millions of cells through Python. Build a Jedox Integrator job: a Cube Extract as the source (or a CubeSlice Extract, which streams large cubes better - and there are Dimension and Rule extracts when you need member or rule metadata alongside the cells), and a Load that writes your target. Integrator writes natively to:

That means for many targets you write zero Python - the Relational Load drops straight into Snowflake or SQL Server, and the Parquet Load lands files in ADLS/OneLake for Fabric to pick up. Python's job is then just to trigger and orchestrate the job, via the Integrator REST v2 API:

import os, requests

BASE = os.environ["JEDOX_ETL"]          # e.g. https://your-host/etlserver/services/v2
PROJECT, JOB = "SalesETL", "ExportCubeToParquet"
s = requests.Session()

# Log in for an etlsession token (user/password, or reuse an OLAP sid via "olapSession").
# The exact login path varies by version - confirm against your instance's API reference.
login = s.post(f"{BASE}/login",
               json={"user": os.environ["JEDOX_USER"], "password": os.environ["JEDOX_PASSWORD"]},
               headers={"Content-type": "application/json", "accept": "application/json"})
login.raise_for_status()
etl_session = login.json()["sessionId"]    # token field name varies by version

# Run the job. The component is addressed as "<project>.jobs.<job>" - the ".jobs." matters.
run = s.post(f"{BASE}/components/{PROJECT}.jobs.{JOB}/executions/run",
             params={"waitForTermination": "false"},   # returns immediately; poll for final status
             headers={"etlsession": etl_session,
                      "Content-type": "application/json", "accept": "application/json"},
             json={"parameters": [{"name": "Year", "value": "2026"},
                                  {"name": "Region", "value": "EMEA"}]})
run.raise_for_status()
print(run.json())     # execution id or status object (shape varies) - then poll executions/status

Prefer not to call REST? There's a command-line client too - ./etlclient.sh -p SalesETL -j ExportCubeToParquet -u $USER -pw $PW -s $SERVER -c Year=2026 - and on cloud an OData trigger (Integrator('globalprojects')/Projects('SalesETL')/Jobs('ExportCubeToParquet')/Run). Skip the old SOAP API; it was deprecated in 2022.4.

Landing it in Microsoft Fabric

Three patterns, from most code to least. On Jedox 2026.1 with the September 2026 update, check the native Fabric connection before reaching for any of them: it is a licensed add-on, but it turns Fabric into an ordinary relational source and removes the plumbing these three patterns exist to work around.

Pattern A - a Fabric notebook: pull, then write a Delta table

The most flexible route. A Fabric notebook attached to a Lakehouse pulls from Jedox (Route 1 or 2), shapes it in pandas, and writes a managed Delta table that's immediately queryable in the Lakehouse SQL endpoint. Pull the credential from Key Vault rather than hard-coding it.

# Fabric notebook (PySpark). Needs Spark 3.4 / Runtime 1.2+.
import requests, pandas as pd

INSTANCE = "myinstance"
BASE = f"https://odata.{INSTANCE}.cloud.jedox.com"

# notebookutils is Fabric's helper (the renamed mssparkutils) - pull the password from Key Vault
pwd = notebookutils.credentials.getSecret("https://myvault.vault.azure.net/", "jedox-pw")
auth = ("svc_jedox", pwd)

r = requests.get(f"{BASE}/Databases(1)/Cubes(10)/Cells",   # or .../Views(<guid>)/Cells for a slice
                 auth=auth, params={"userules": "true"},
                 headers={"Accept": "application/json"}, timeout=300)
r.raise_for_status()

pdf = pd.json_normalize(r.json()["value"])             # columns: Path, Value, StringValue
# (for a big cube, page via @odata.nextLink - reuse the odata_all() reader from Route 1)
sdf = spark.createDataFrame(pdf)                       # 'spark' is predefined in Fabric

# Land as a managed Delta table in Tables/ of the default Lakehouse (auto-registered)
sdf.write.mode("overwrite").format("delta").saveAsTable("jedox_pnl")
# incremental instead: .mode("append").format("delta").saveAsTable("jedox_pnl")

spark.sql("SELECT COUNT(*) AS n FROM jedox_pnl").show()

Two things to keep straight: saveAsTable(name) writes a table under Tables/ and registers it; .save("Files/...") writes loose files under Files/ and does not create a queryable table. And inside a Fabric notebook, azure.identity's DefaultAzureCredential does not work - use notebookutils.credentials.getToken("storage") (or just the native Spark paths, which are already authenticated).

Pattern B - Integrator → Parquet in OneLake, then a shortcut

For scheduled, hands-off pipelines: an Integrator Parquet Load writes to ADLS/OneLake, and Fabric reads it via a Lakehouse shortcut or a quick load. If you'd rather push the Parquet from your own Python (an external app, not a notebook), OneLake uses the same SDK as ADLS Gen2 - just with the onelake.dfs.fabric.microsoft.com endpoint, the workspace name as the file system, and the item addressed as <lakehouse>.Lakehouse/Files/...:

# External Python (e.g. an orchestrator) dropping a Parquet file into a Lakehouse's Files area
from azure.identity import DefaultAzureCredential          # uses your 'az login' / Entra identity
from azure.storage.filedatalake import DataLakeServiceClient

svc = DataLakeServiceClient("https://onelake.dfs.fabric.microsoft.com",
                            credential=DefaultAzureCredential())
fs = svc.get_file_system_client("My Workspace")             # file system == workspace name
fc = fs.get_file_client("MyLakehouse.Lakehouse/Files/landing/pnl.parquet")

with open("pnl.parquet", "rb") as f:
    fc.upload_data(f, overwrite=True)

The endpoint is the giveaway here: it's onelake.dfs.fabric.microsoft.com, not dfs.core.windows.net - tools that hard-code the regular ADLS endpoint will break on OneLake.

Pattern C - low-code: Copy activity or Dataflow Gen2

No notebook at all. A Fabric Data pipeline Copy activity can read from a generic REST source (point it at the OData Hub cell endpoint), and a Dataflow Gen2 can pull from a Web connection and shape it in Power Query. Caveat: the REST Copy source only understands GET/POST returning JSON - which is exactly why the OData Hub (JSON), not the OLAP HTTP API (CSV), is the right source for this route.

Landing it in other servers

Same fork: either let Integrator write directly over JDBC (no Python), or pull to pandas and load it yourself when you want transforms or control. Here's the idiomatic Python for the three destinations I hit most.

import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy.engine import URL

# --- Microsoft SQL Server: sqlalchemy + pyodbc with fast_executemany ---------
odbc = ("DRIVER={ODBC Driver 18 for SQL Server};SERVER=myserver;DATABASE=mydb;"
        "UID=svc;PWD=secret;Encrypt=yes;TrustServerCertificate=no")
mssql = create_engine(URL.create("mssql+pyodbc", query={"odbc_connect": odbc}),
                      fast_executemany=True)          # the single biggest speed-up
df.to_sql("my_table", mssql, schema="dbo", if_exists="append",
          index=False, chunksize=1000, method=None)   # leave method=None with fast_executemany

# --- PostgreSQL: bulk COPY (far faster than row-by-row INSERT) ----------------
import csv
from io import StringIO

def psql_insert_copy(table, conn, keys, data_iter):     # verbatim from the pandas user guide
    dbapi = conn.connection
    with dbapi.cursor() as cur:
        buf = StringIO(); csv.writer(buf).writerows(data_iter); buf.seek(0)
        cols = ", ".join(f'"{k}"' for k in keys)
        name = f"{table.schema}.{table.name}" if table.schema else table.name
        cur.copy_expert(f"COPY {name} ({cols}) FROM STDIN WITH CSV", buf)

pg = create_engine("postgresql+psycopg2://svc:secret@host:5432/mydb")
df.to_sql("my_table", pg, schema="public", if_exists="append",
          index=False, method=psql_insert_copy)

# --- Snowflake: write_pandas (Parquet -> PUT to stage -> COPY INTO) -----------
import snowflake.connector
from snowflake.connector.pandas_tools import write_pandas

conn = snowflake.connector.connect(account="myorg-myacct", user="svc", password="secret",
                                   warehouse="MY_WH", database="MY_DB", schema="PUBLIC")
ok, n_chunks, n_rows, _ = write_pandas(conn, df, "MY_TABLE",   # UPPERCASE to match Snowflake
                                       auto_create_table=True, overwrite=False)
print(ok, n_rows)

The foot-guns, in order of how often they bite:

And the cloud-file path, for anything that ingests Parquet from a landing zone (Snowflake external stages, Databricks, Redshift):

# S3 needs s3fs; ADLS needs adlfs; GCS needs gcsfs - install the matching backend
df.to_parquet("s3://my-bucket/landing/pnl/data.parquet",
              engine="pyarrow", compression="snappy", index=False,
              storage_options={"key": "...", "secret": "..."})

Which route, when

Habits for production pipelines

Start with the OData Hub to explore, drop to the OLAP API when you need the raw cube, and graduate to the Integrator the moment the job becomes routine - then point its output at whichever Lakehouse or warehouse you're feeding.

Back to all posts