The Essbase Python API

The Essbase Python API, included in the oracle-data-studio package, brings a modern Python interface to Oracle Essbase 21c automation. With essbase.connect(), typed resource collections, context managers, and familiar Python patterns, developers can script common Essbase tasks without working directly against REST endpoints.

Whether you are an Essbase administrator automating routine tasks, a data engineer building integration pipelines, or a developer embedding Essbase operations in larger workflows, the API gives you a clean, production-ready interface to get the job done.

Why a Python API for Essbase?

Essbase is one of Oracle’s most powerful multidimensional analytics engines, trusted by enterprises worldwide for planning, budgeting, forecasting, and financial consolidation. Traditionally, Essbase administration has relied on graphical tools like the Essbase web interface or Smart View for Excel. While these tools are excellent for interactive use, they present challenges when it comes to automation, repeatability, and integration with modern DevOps practices.

The Essbase Python API addresses these challenges by enabling:

Scripted automation of routine administrative tasks such as starting and stopping applications, running calculations, and loading data.
CI/CD integration for outline changes, script deployments, and environment promotion using familiar tools like Git and Jenkins.
Enterprise scheduling through centralized orchestration platforms, allowing Essbase jobs to run as part of broader data workflows.
Programmatic security management for users, groups, roles, and security filters across complex multi-application environments.
Data extraction and analysis using MDX queries directly from Python, feeding results into pandas DataFrames, dashboards, or downstream systems.

Installation

The Essbase module is included in the oracle-data-studio package. Pandas is now an optional dependency, keeping the base install lean. Choose the install option that fits your needs:

pip install oracle-data-studio        # lean install
pip install oracle-data-studio[mcp]   # includes MCP server
pip install oracle-data-studio[pandas] # includes pandas for DataFrames

The package requires Python 3.10 or later. No mandatory pandas dependency—the API returns plain lists of dicts by default, so you can use any DataFrame library or none at all.

Getting Started

Connecting to Essbase

Use essbase.connect() with a context manager for automatic cleanup:

import essbase

with essbase.connect('https://essbase-host:443', 'admin', 'password') as ess:
    for app in ess.applications:
        print(app.name, app.type, app.is_started)

Token authentication is also supported:

# Bearer Token (IDCS / IAM)
with essbase.connect(url, token='eyJ...') as ess:
    ...

On connect, the client verifies connectivity by calling the Essbase /about endpoint. If authentication fails, an EssbaseException is raised immediately, so you know right away if something is wrong.

Listing Applications

The API exposes typed resource collections with Pythonic iteration, membership testing, and dictionary-style access:

with essbase.connect(url, user, password) as ess:
    for app in ess.applications:
        print(f"{app.name} - {app.type} - {'Running' if app.is_started else 'Stopped'}")

    # Pythonic collection operations
    if 'Sample' in ess.applications:
        app = ess.applications['Sample']
        print(app.settings())

Running a Calculation

One of the most common Essbase operations is running a calc script. The API makes this a one-liner:

with essbase.connect(url, user, password) as ess:
    db = ess.applications['Sample'].databases['Basic']

    # One-line: submit and wait
    job = db.scripts['CalcAll'].run(wait=True)
    print(f"Status: {job.status_message}")

    # Or via the jobs collection
    job = ess.jobs.run_calc('Sample', 'Basic', 'CalcAll', wait=True)

Querying Data with MDX

MDX queries return flattened records by default—no pandas dependency required. Use any DataFrame library or process records directly:

with essbase.connect(url, user, password) as ess:
    db = ess.applications['Sample'].databases['Basic']

    # Flattened records — no pandas dependency
    records = db.mdx_records(
        'SELECT {[Measures].Members} ON COLUMNS, '
        '{[Product].Children} ON ROWS '
        'FROM Basic'
    )

    # Works with any DataFrame library
    import pandas as pd
    df = pd.DataFrame(records)

    # Or polars, csv, json — it's just a list of dicts
    import json
    print(json.dumps(records[:5], indent=2))

Outline Browsing and Editing

The API provides rich outline operations for browsing members, searching, and batch editing:

with essbase.connect(url, user, password) as ess:
    db = ess.applications['Sample'].databases['Basic']

    # Browse members
    for member in db.outline.children('Product'):
        print(member.name, member.consolidation, member.number_of_children)

    # Search
    results = db.outline.search('Cola')

    # Batch outline edit
    db.outline.edit([
        {'operationType': 'addMember', 'parentName': 'Product',
         'memberName': 'P-2060'},
        {'operationType': 'setAlias', 'memberName': 'P-2060',
         'aliasTable': 'Default', 'alias': 'Ultra Widget'}
    ], restructure='PRESERVE_ALL_DATA')

Dimensions and Variables

Explore dimension metadata and manage substitution variables at server, application, and database scope:

with essbase.connect(url, user, password) as ess:
    db = ess.applications['Sample'].databases['Basic']

    # Explore dimensions
    for dim in db.dimensions:
        print(f"{dim.name}: {dim.type}, {dim.storage}")

    # Variables at three scopes
    ess.variables.create('GlobalMonth', 'Jan')       # server
    app.variables.create('AppMonth', 'Feb')           # application
    db.variables['CurMonth'].update('Mar')            # database

File Operations

Upload, download, and manage files in the Essbase file catalog:

with essbase.connect(url, user, password) as ess:
    # Upload a data file
    ess.files.upload('/applications/Sample/Basic/data.csv',
                     open('data.csv', 'rb').read())

    # Download and list
    content = ess.files.download('/applications/Sample/Basic/CalcAll.csc')
    entries = ess.files.list('/applications/Sample/')

Real-World Use Cases

Automated Nightly Data Loads

Many organizations run nightly ETL processes that load transactional data into Essbase cubes. You can script the entire sequence using context managers and typed resources:

with essbase.connect(url, user, password) as ess:
    # Upload data file
    with open('actuals_20260615.csv', 'rb') as f:
        ess.files.upload('/applications/Finance/Plan/actuals.csv', f.read())

    db = ess.applications['Finance'].databases['Plan']

    # Trigger data load and wait
    job = ess.jobs.run_dataload('Finance', 'Plan',
        rule='LoadActuals.rul', file='actuals.csv', wait=True)
    print(f"Load: {job.status_message}")

    # Run aggregation
    job = db.scripts['AggAll'].run(wait=True)
    print(f"Calc: {job.status_message}")

Bulk Security Provisioning

When onboarding teams or restructuring access, manually configuring security for dozens of users across multiple applications is tedious and error-prone. The Python API makes it scriptable:

with essbase.connect(url, user, password) as ess:
    # Create a group and add users
    ess.groups.create_group({'id': 'west_region',
                            'name': 'West Region Team'})
    ess.groups.add_users('west_region',
        {'users': ['jsmith', 'jdoe', 'mjones']})

    # Create a security filter
    ess.filters.create_filter('Finance', 'Plan', {
        'name': 'WestFilter',
        'rows': [{'access': 'read',
                  'member': '@IDESCENDANTS("West")'}]
    })

    # Assign the filter to the group
    ess.filters.add_permissions('Finance', 'Plan',
        'WestFilter', {'id': 'west_region'})

Outline Management and DevOps

The outline editing API lets you programmatically add members, set aliases, and restructure dimensions—ideal for promoting changes from development to production:

with essbase.connect(url, user, password) as ess:
    db = ess.applications['Sample'].databases['Basic']

    # Add new products from a master data source
    new_products = [
        {'name': 'P-2060', 'alias': 'Ultra Widget'},
        {'name': 'P-2061', 'alias': 'Mega Widget'},
    ]

    actions = []
    for p in new_products:
        actions.append({'operationType': 'addMember',
                        'parentName': 'Product',
                        'memberName': p['name']})
        actions.append({'operationType': 'setAlias',
                        'memberName': p['name'],
                        'aliasTable': 'Default',
                        'alias': p['alias']})

    db.outline.edit(actions,
        restructure='PRESERVE_ALL_DATA')

API Coverage

The API exposes Essbase functionality through typed resource classes. The following table summarizes the available resources:

Resource	Access	Key Operations
Connection	`essbase.connect()`	Context manager, close
Applications	`ess.applications`	iter, in, [name], create, start, stop
Databases	`app.databases`	iter, in, [name], start, stop, settings
Scripts	`db.scripts`	iter, create, run, validate, content
Jobs	`ess.jobs`	[id], run_calc, run_dataload, wait
Dimensions	`db.dimensions`	iter, [name], generations, levels
Outline	`db.outline`	children, search, member, edit
MDX	`db.mdx_records()`	Flattened records, raw mdx, grid
Files	`ess.files`	upload, download, list, copy, move
Variables	`ess/app/db.variables`	create, update, delete, iter
Groups	`ess.groups`	create, add/remove users, delete
Filters	`ess.filters`	create, assign permissions, manage access

Architecture and Design

The Essbase Python API is built around typed resource classes and context managers, sharing the same underlying REST client for authentication, session management, and HTTP communication.

The module structure:

essbase/
├── __init__.py           # connect() entry point
├── rest_client.py        # HTTP transport
├── connection.py         # Connection, context manager
├── applications.py       # Application, ApplicationCollection
├── databases.py          # Database, DatabaseCollection
├── scripts.py            # Script, ScriptCollection
├── jobs.py               # Job, JobCollection
├── dimensions.py         # Dimension, Generation, Level
├── outline.py            # Outline, Member
├── files.py              # Files
├── variables.py          # Variable collections
└── mdx.py                # mdx_to_records()

Error handling is consistent throughout: all API errors raise EssbaseException with the HTTP status code and server error message, making it easy to handle failures in automation scripts.

MCP Server Integration

The oracle-data-studio package also includes a Model Context Protocol (MCP) server that exposes Essbase operations as 30 composite tools for AI assistants and agents. A separate blog post covers the MCP server in detail.

What’s Next?

This blog covered the Essbase Python API. In upcoming posts, we will dive deeper into specific scenarios:

Building a complete CI/CD pipeline for Essbase outline changes with Git and the Python API
Advanced MDX querying and integrating results with pandas and visualization libraries
Monitoring Essbase performance programmatically using database statistics and query tracking
Leveraging the MCP server to build AI-powered Essbase management assistants

Resources

Install: pip install oracle-data-studio[mcp]
Essbase REST API Documentation: https://docs.oracle.com/en/database/other-databases/essbase/21/essrt/
Oracle Data Studio Documentation: https://docs.oracle.com/en/database/oracle/sql-developer-web/sdwad/

The Essbase Python API

Why a Python API for Essbase?

Installation

Getting Started

Connecting to Essbase

Listing Applications

Running a Calculation

Querying Data with MDX

Outline Browsing and Editing

Dimensions and Variables

File Operations

Real-World Use Cases

Automated Nightly Data Loads

Bulk Security Provisioning

Outline Management and DevOps

API Coverage

Architecture and Design

MCP Server Integration

What’s Next?

Resources

Alexander Tsikinovsky

Director, Data Systems Engineering

Query Databricks Delta tables from Oracle Autonomous Database using UniForm

Essbase MCP Server

The Essbase Python API

Why a Python API for Essbase?

Installation

Getting Started

Connecting to Essbase

Listing Applications

Running a Calculation

Querying Data with MDX

Outline Browsing and Editing

Dimensions and Variables

File Operations

Real-World Use Cases

Automated Nightly Data Loads

Bulk Security Provisioning

Outline Management and DevOps

API Coverage

Architecture and Design

MCP Server Integration

What’s Next?

Resources

Authors

Alexander Tsikinovsky

Director, Data Systems Engineering

Query Databricks Delta tables from Oracle Autonomous Database using UniForm

Essbase MCP Server