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