Back to Blog

Oracle EPM MDX Queries Explained: A Beginner’s Guide

9 min read
Arun Raj
Oracle EPM MDX Queries Explained: A Beginner’s Guide

When I explain MDX, I start with a business question. What were Actual Sales for California and Nevada in January, February and March?

Think of a spreadsheet with months across the top, entities down the side and sales at the intersections. MDX, short for Multidimensional Expressions, describes that grid and the context behind it. Which fiscal year, version and currency do you mean? A query can run successfully and still answer a different question if those selections are wrong.

This is Part 1 of a two-part series. You can follow along if you know a few dimension and member names in your application. The examples use an illustrative cube and sample values. They’re based on Oracle documentation and should be validated against your own test cube.

What changed in Oracle EPM 26.11?

Planning and FreeForm now have an enhanced MDX Query Editor for form design. You can generate a starting query from an existing Classic form layout and use MDX for data-driven member selection, ranking, filtering and query-time calculations. Oracle also added a REST API that runs a custom MDX query against a selected cube and returns a JSON grid. Existing Essbase queries may need changes for the target cube and editor. Oracle’s 26.11 MDX release note

Oracle scheduled the update for test environments on November 6 and production on November 20, subject to the qualifying maintenance window and any update exceptions. Check your environment’s version before following the form or API examples. Oracle’s monthly update schedule

For the rest of the release, see Newarc’s November 2026 Oracle EPM update.

A cube gives every number an address

A dimension is a category such as Account or Entity. A member is a selection within it, such as Sales or CA. A cell represents the intersection of one member from each dimension in the cube.

Dimension Sample member Question it answers
Account Sales What are we measuring?
Entity CA Which business unit?
Period Jan Which month?
Scenario Actual Which scenario?
Version Working Which version?
Years FY26 Which fiscal year?
Currency USD Which currency?

That gives us one cell, Sales / CA / Jan / Actual / Working / FY26 / USD. Your cube may also include Product, Channel, View or other dimensions. Start by writing down the full address of one cell you can check in a trusted form or Smart View.

A hierarchy organizes members. In our Entity hierarchy, West is the parent of CA and NV. California is the display alias for CA. I use verified member identifiers for reusable queries because aliases can vary by table or language. If a name is ambiguous, qualify it with the dimension or a distinguishing ancestor, as in [Account].[Sales] or [West].[CA]. For duplicate or shared members, use the identifier the member selector or generated query provides. Oracle’s MDX member reference

Members, tuples and sets

Construct Example How to read it
Member [Jan] One selection in a dimension
Tuple ([Jan], [Actual]) One coordinate made from members of different dimensions
Set {[Jan], [Feb], [Mar]} Three alternatives for Period, each a one-member tuple
Set of tuples {([Jan], [Actual]), ([Feb], [Actual])} Two coordinates with the same dimensions in the same order

January and February belong to one dimension, so ([Jan], [Feb]) is an invalid coordinate. To request both months, use the set {[Jan], [Feb]}. In a set of tuples, keep the dimensionality consistent. {([Jan], [Actual]), ([Feb], [Sales])} mixes Scenario and Account in the second position.

Read your first MDX query

Start with one period and one entity. Replace the illustrative application, cube and members with values from your outline.

SELECT
  {[Jan]} ON COLUMNS,
  {[West].[CA]} ON ROWS
FROM [SampleApp].[Plan1]
WHERE ([Account].[Sales], [Actual], [Working], [FY26], [USD])

Read it like a grid. Put January across the top and California down the side. Query the Plan1 cube in SampleApp and use Sales, Actual, Working, FY26 and USD for the remaining dimensions. That gives you one displayed data cell. Oracle’s Planning MDX form example

Now use {[Jan], [Feb], [Mar]} ON COLUMNS and {[West].[CA], [West].[NV]} ON ROWS. With the same point of view, you get two rows by three columns. These are illustrative teaching values.

Entity Jan Feb Mar
CA 100 110 120
NV 80 85 90

Use the slicer to set the point of view

In MDX, WHERE sets the slicer, which supplies context for the visible grid. With Actual in the slicer, every displayed cell uses Actual. To compare Actual and Budget, place Scenario on a visible axis and remove Actual from the slicer.

Put each dimension on one axis or in the slicer. Putting Sales in WHERE while listing Sales and Expenses on rows assigns Account twice. For repeatable reporting, explicitly set the intended member for each dimension that isn’t on a visible axis. Omitted dimensions can pick up cube defaults.

Replace member lists with Children and Descendants

[West].Children ON ROWS returns West’s immediate children. In our example, those are CA and NV. The expression already returns a set, so it can go straight on the axis. A leaf member has no children, so using its children as an axis returns no data intersections.

For a deeper hierarchy, use Descendants([West], [Entity].Levels(0)) ON ROWS to select leaf-level descendants, assuming Entity is the dimension identifier and the target editor accepts the expression. Level zero starts at the leaves. A bare Descendants([West]) can include West itself. Validate hierarchy expressions individually in your EPM editor. Oracle’s MDX Descendants reference

Compare Actual and Budget with CrossJoin

Use CrossJoin to put Period and Scenario on the column axis. Remove Actual from WHERE because Scenario now comes from the column tuples.

SELECT
  CrossJoin(
    {[Jan], [Feb], [Mar]},
    {[Actual], [Budget]}
  ) ON COLUMNS,
  {[West].[CA], [West].[NV]} ON ROWS
FROM [SampleApp].[Plan1]
WHERE ([Account].[Sales], [Working], [FY26], [USD])

The columns appear as Jan/Actual, Jan/Budget, Feb/Actual, Feb/Budget, Mar/Actual and Mar/Budget. The two sets must use distinct dimensions. Count the candidate cells before running a larger query. Here, three periods × two scenarios × two entities gives 12 cells. Using 12 periods, 500 entities, 200 accounts and four scenarios gives 4.8 million candidate intersections. Oracle’s CrossJoin reference

Use NON EMPTY after checking the baseline

NON EMPTY {[West].[CA], [West].[NV]} ON ROWS suppresses a row when every selected cell in that row is #MISSING. A numeric zero counts as data, so a row of zeros stays visible. When I’m investigating missing data, I start with an explicit row list and leave suppression off so I can see which intersections I expected to populate. Oracle’s MDX axis reference

Choose between an MDX form, an export and a rule

What you need Starting point
Define a user-facing grid in Planning or FreeForm MDX Query Editor
Run a custom MDX query and receive a JSON grid Export MDX Query Data REST API
Retrieve an explicitly defined grid using JSON selections Export Data Slices REST API
Calculate or update stored EPM data An appropriate business rule or job
Move recurring bulk data between systems Data Integration or supported export jobs

The MDX export is a POST to /HyperionPlanning/rest/v3/applications/{appname}/plantypes/{plantype}/exportmdxdata. Send the SELECT statement in mdxQuery. The response includes pov, columns and rows, with headers and data for each row. Oracle requires the Service Administrator role for this endpoint. The separate Export Data Slices API takes a JSON gridDefinition and has its own access rules. Export MDX Query Data · Export Data Slices

For form authoring, work on a copy if you want to keep the Classic form. Saving the conversion removes the original Classic artifact. The MDX form grid retains member access filtering and cell attributes, so verify the saved form as the intended analyst as well as the designer. Planning form authoring · FreeForm form authoring

Troubleshoot one change at a time

Keep the last working query and compare the same known cell after each change. BSO, ASO and hybrid storage can affect query behavior and performance. Oracle’s 26.11 release note lists Planning and FreeForm, so validate your query constructs on the target cube. Oracle’s 26.11 feature scope

Symptom First check
Unknown member Cube, actual member identifier and current metadata
Empty grid Empty set, incorrect POV, missing data, suppression or access filtering
Unexpected number Year, scenario, version, currency and additional dimensions
Dimension conflict The same dimension appears on multiple axes or in both an axis and the slicer
Analyst sees fewer rows Member and form access under the analyst’s sign-in
Large or slow result CrossJoin size, broad descendant selections and query-time calculations
Classic view shows the old layout MDX changes appear when the form is opened in MDX mode

Five practice questions

Try each question before expanding the answer.

  1. How many displayed cells do three periods and two entities produce?
  2. Why is ([Jan], [Feb]) invalid as a coordinate?
  3. Where does Scenario go to show Actual and Budget side by side?
  4. Does NON EMPTY remove a row with three numeric zeros?
  5. What happens to the original Classic form when you save its conversion to MDX?
Show the answers
  1. Six before suppression. Fixed slicer members supply context for each cell.
  2. Both members are from Period. Use a set to request both.
  3. Put Scenario on the columns with Period and remove Actual from the slicer.
  4. The row remains because all three zeros are values.
  5. The Classic form is removed when the conversion is saved. Use a copy to retain it.

Part 2 will use this foundation to build and validate an MDX form, then call the export API from Bruno.