The problem this solves

Oracle EPM Planning's metadata — the dimension hierarchies, member properties, aliases, data storage types, and parent-child relationships — is typically only accessible to EPM administrators with direct access to the Oracle Enterprise Data Management (EDM) module or the Planning metadata console. A finance analyst who wants to understand why a number looks different from what they expected has no self-service way to answer the question: "Is this account a Dynamic Calc? What's its parent? Is it in the right hierarchy?"

The result is a constant stream of metadata questions to the EPM team. "Which store is under EMEA?" "What accounts roll up to Net Revenue?" "Is the Store Occupancy account a leaf or a parent?" These are 2-minute questions that take 2 days to get answered through a ticket queue.

Master Data NLQ gives finance and operations users direct natural-language access to the EPM dimension metadata — without needing admin rights, without reading FDMEE documentation, and without waiting for the EPM team. The 12-dimension hierarchy of a full Planning cube is fully traversable in plain English.

Audience

Finance analysts who need to understand the EPM data model to interpret their reports. EPM business analysts performing metadata audits or impact analysis. EPM architects documenting a Planning application's dimension structure. Finance managers onboarding to a new Planning application and needing to understand the account hierarchy.

Natural language metadata queries

Input is a plain-text question about the EPM Planning dimension structure. Three query modes are supported:

The parser also accepts cross-dimension queries: "Show all leaf members in APAC with their currency settings" — entity hierarchy + currency dimension combined.

Searchable dimension metadata in the browser

Three distinct output formats depending on query mode:

• Search results table: Member name · Dimension · Parent · Data storage type · Alias · Description — sortable columns, highlight matched search term
• Hierarchy tree: Expandable parent-child tree with indentation levels, member count, and data storage type badge per node. Leaf members show in italic; Dynamic Calc members show with a ⚡ badge.
• Dimension statistics panel: For each queried dimension: total member count, breakdown by data storage type (Store Data / Dynamic Calc / Label Only) with a doughnut chart, and level-by-level member counts.

In the EPM governance workflow

Master Data NLQ sits alongside Oracle Enterprise Data Management (EDM) and the Planning metadata console, but it serves a different audience. EDM is for EPM administrators who manage the hierarchy. The metadata console is for technical users who need full control. Master Data NLQ is for business users who need to understand the hierarchy without changing it.

• Report interpretation: Analyst sees an unexpected number, asks "What accounts roll up to Total OpEx?" to verify the hierarchy is configured as expected
• Impact analysis: Business analyst asks "How many accounts are Dynamic Calc in the Account dimension?" before proposing a metadata change
• New hire onboarding: New FP&A hire asks "Show me all EMEA entities" to understand the regional structure without needing admin access
• Audit support: Internal audit team searches for all members with a specific alias or description pattern, across all 12 dimensions, in seconds

The numbers

The honest checklist

• ✓You have Oracle EPM Planning with a multi-dimension structure that non-admin users struggle to understand
• ✓Your EPM team receives repeated metadata questions from finance users ("what accounts are under X?", "is Y a leaf member?")
• ✓You want self-service dimension exploration without giving finance users EPM admin rights
• ✓You are planning an EPM metadata audit and want a searchable corpus of your dimension members
• ✗You need writeback — EDM writeback (adding/moving members) is out of scope for NLQ; this is read-only metadata exploration
• ✗You need real-time metadata from your live EPM instance — the demo uses a pre-loaded corpus; production requires the EPM REST API metadata endpoint
• ✗You need access control per dimension or member — the demo shows all members to all authenticated users

Try it now

The live demo indexes 10,500+ EPM Planning members across 12 dimensions. Search, explore hierarchies, and view dimension statistics — all client-side, zero latency once loaded.

Sample queries to try

• "Find all revenue accounts"
• "Show stores under APAC"
• "What members are Dynamic Calc in Account?"
• "How many dimensions does this cube have?"

The metadata question queue

In a typical Oracle EPM Planning deployment, the EPM team receives 10–20 metadata questions per week from finance users. "Which account does headcount roll up to?" "Is there a Currency dimension and what are the valid values?" "We're getting a data load error for entity 'Netherlands' — what's the exact member name?" Each question takes 5–10 minutes for the EPM analyst to look up and respond to — and the queue grows every time a finance team member is onboarded or the account hierarchy changes.

The Master Data NLQ layer makes the full dimension corpus self-service. The finance user asks the question in plain English and gets the answer in under 2 seconds. The EPM team's metadata support burden drops to near zero for lookup questions — they can focus on actual metadata governance and hierarchy changes.

The secondary business case is audit readiness. When an external auditor asks "Show us all accounts with a Dynamic Calc data storage type and their parent members," the answer is a 10-second query rather than a multi-day EPM export project.

Query-to-metadata pipeline

The metadata engine (Layer 2) runs entirely client-side. Once the 10,500+ member corpus is loaded, all search and hierarchy operations are in-memory — sub-millisecond response time. Only the LLM intent parsing (Layer 1) requires a network call.

App UI — Component breakdown

The 12-dimension EPM Planning cube

The demo corpus models a full Oracle EPM Planning cube with the following dimensions:

Data storage types in the corpus: Store Data (leaf-level numeric data), Dynamic Calc (computed on-the-fly from children — never explicitly stored), Label Only (hierarchy grouping node, no data).

How member search works

The search runs client-side against the pre-loaded dimension corpus. No server round-trip after the initial page load.

Search ranking (descending priority)

1. Exact name match — member name equals search term (case-insensitive)
2. Name starts with — member name starts with search term
3. Name contains — search term appears anywhere in member name
4. Alias match — same three-tier ranking applied to the alias field
5. Description match — lowest priority, partial match in description text

Hierarchy traversal

Parent-child relationships are stored in a flat memberMap object keyed by member name, with children[] and parent fields. Hierarchy queries use depth-first traversal from the named parent, returning all descendants with their depth level. The tree renderer indents by 20px per level and collapses branches deeper than level 3 by default.

// Hierarchy traversal (simplified)
function getDescendants(memberName, depth = 0) {
  const member = memberMap[memberName];
  if (!member) return [];
  return [{ ...member, depth }].concat(
    (member.children || []).flatMap(
      c => getDescendants(c, depth + 1)
    )
  );
}

Metadata intent extraction

The Master Data NLQ prompt extracts the query mode and metadata parameters. It is the most intent-diverse prompt of the four use cases — three distinct query modes require three different output shapes:

// Output schema (Master Data variant)
{
  "mode": "search" | "hierarchy" | "stats",

  // search mode
  "searchTerm": "revenue",
  "dimension": "Account" | "Entity" | ... | "all",
  "dataStorageFilter": "Store" | "DynCalc" | "LabelOnly" | null,

  // hierarchy mode
  "parentMember": "EMEA",
  "dimension": "Entity",
  "direction": "children" | "parent" | "ancestors",

  // stats mode
  "dimension": "Account" | "all"
}

Mode detection rules (few-shot examples)

• "find", "search", "which members", "show me accounts named" → search
• "children of", "what's under", "show hierarchy", "parent of" → hierarchy
• "how many", "count", "statistics", "breakdown", "percentage" → stats

Cost per metadata query

The Master Data use case has the lowest per-query cost of the four because the system prompt is shorter (no dimension value enumerations — the corpus is searched client-side, not described to the LLM), and the output JSON is the most compact.

Cost model — At-scale projections

Master Data NLQ has the lowest at-scale cost of the four use cases — smaller prompt, compact output, and the search itself runs client-side at zero cost. The main scaling consideration at enterprise scale is the initial corpus load time (10,500+ members is ~120KB of JSON) — at 10,000+ members a production deployment would use a server-side EPM REST API metadata call and client-side caching.

Key files

The memberMap construction

On page load, epm-data.js DIMENSION_MEMBERS is flattened into a memberMap object for O(1) member lookup by name. The map is built once and used for all search, hierarchy, and stats queries. Building the map for 10,500+ members takes ~2ms on a modern browser — imperceptible to the user.

Highlight rendering

Search term matches are highlighted using a DOM-safe text splitter — the search term is never inserted via innerHTML. Instead, the member name text node is split at match positions and wrapped with a <mark> element created via document.createElement. This prevents XSS via injected member names.

Tech stack — Every tool in this build

Known attack surfaces

Guardrails — What prevents bad metadata responses

• Mode fallback: If the LLM returns an unknown mode, the default is search with the raw user query as the search term — always produces a result, never errors silently.
• Zero-result handling: If a search returns zero members, the UI shows "No members found for '[term]' in [dimension]" with suggestions: check spelling, try a shorter term, or browse the dimension stats.
• Unknown parent guard: If the LLM returns a parentMember not found in the corpus, the hierarchy view shows an error: "Member '[name]' not found — check the exact member name in the search tab."
• Stats dimension guard: If stats mode is requested for an unrecognised dimension name, it defaults to showing stats for all dimensions rather than erroring.
• Depth cap: Trees deeper than 10 levels show a "…and N more members" expansion link rather than rendering all descendants at once.

Who is asking, and what are they allowed to see?

The demo answers neither question — it has a cookie gate and no notion of a user. In production these are the two questions everything else rests on, and they have different answers: authentication is who you are, authorization is what you may see. Corporate SSO settles the first. Only Oracle EPM Cloud can settle the second, and the single most important rule in this section is that this application must never become the place where that decision is made.

9.1 · The identity chain, end to end

9.2 · Federating the corporate identity provider

Oracle EPM Cloud does not replace your directory — it trusts it. The EPM Cloud identity domain is federated with the corporate IdP so authentication happens where it already happens, under policies security has already written.

For the browser application itself, use OIDC Authorization Code flow with PKCE. Not the implicit flow, which is deprecated and leaks tokens through the URL, and never a resource-owner password grant — a finance tool should not be capable of handling a password at all.

9.3 · From group membership to EPM roles

Roles are granted to groups, never to individuals, and the groups come from the directory. That one discipline is what makes joiner/mover/leaver work without anyone having to remember this application exists.

Entra ID group                    →  EPM role / entitlement
──────────────────────────────────────────────────────────────────
FIN-EPM-Analysts                  →  Planning User
FIN-EPM-Controllers-EMEA          →  Power User + EMEA data scope
FIN-EPM-Admins                    →  Service Administrator
──────────────────────────────────────────────────────────────────
Provisioned by SCIM. Remove the user from the group and the
entitlement disappears on the next sync — including here.

For this use case the relevant native entitlement is: EDMCS Data Manager or Viewer on the relevant viewpoints — browsing a dimension requires View on its viewpoint.

9.4 · The architectural decision: who enforces?

This is the choice that determines whether the deployment is defensible. Both patterns appear in the diagram above; the difference is where the security boundary actually sits.

The shortcut to refuse. Pattern B built with a Service Administrator account and no filtering at all is the most common way this gets delivered, because it works perfectly in UAT — testers are usually over-entitled, so nobody notices that everyone can see everything. It fails at the first access review, and by then it is in production with real users depending on it.

9.5 · Data-level security is the part that matters

Role membership decides whether a user can open the application. It does not decide which rows they get back, and confusing the two is the most expensive mistake available here.

• For this use case: Viewpoint and node-type permissions in EDMCS decide which dimensions, and which branches of a hierarchy, a user may even see.
• Apply it before aggregation, not after. Filtering a total that has already been computed across entities the user cannot see still leaks the total.
• The NLQ layer needs its own check. Layer 4 already validates that the resolved point of view uses approved members; production adds a second test — that the resolved POV sits inside this user’s scope — and it runs before the data call, not after. A natural-language interface is very good at asking for things politely; the authorization check must not care how the question was phrased.
• Fail closed. If entitlements cannot be resolved, return nothing and say so. An empty result is a support ticket; a permissive default is an incident.

9.6 · Provisioning, sessions and the leaver problem

• SCIM provisioning from Entra or Okta into the EPM Cloud identity domain (OCI IAM, formerly IDCS), covering joiner, mover and leaver. The mover is the case people forget — somebody changing region should lose the old scope, not accumulate both.
• No local user store. If this application keeps its own copy of who may do what, a leaver keeps access until somebody remembers to update it. Nobody ever does.
• Short-lived access tokens with refresh-token rotation; align session timeout with the data classification rather than with convenience.
• MFA and Conditional Access at the IdP — not reimplemented here. Device compliance and location policy come free with federation.
• Quarterly recertification of both the groups that grant access and the service account’s own entitlements, evidenced and signed.
• Break-glass access is a named, monitored, time-boxed account — never a shared credential in a password manager.

9.7 · What this means for Master Data NLQ

9.8 · Security configuration checklist

• ✓Oracle EPM Cloud federated with the corporate IdP over SAML 2.0 or OIDC; the cookie gate removed entirely
• ✓Browser app uses OIDC Authorization Code + PKCE — no implicit flow, no password grant
• ✓MFA and Conditional Access enforced at the IdP, not reimplemented in the application
• ✓Roles granted to directory groups, never to individuals; SCIM covers joiner, mover and leaver
• ✓Enforcement pattern chosen deliberately — Pattern A where the API supports it, or Pattern B with filtering centralised and tested
• ✓Data-level security applied before aggregation, and the resolved POV checked against the user’s scope before the data call
• ✓No local user table and no local role table anywhere in the application
• ✓Every query logged against the real end user, even when a service account makes the call
• ✓Authorization failures fail closed and are logged as security events rather than swallowed
• ✓Quarterly recertification of access groups and of the service account’s own entitlements

From demo to a governed enterprise deployment

Everything above runs on synthetic data, a public LLM API key, a cookie gate, and no audit trail — deliberately, so the mechanics are inspectable. Taking Master Data NLQ to production is not a rewrite; the 4-layer pipeline and the data-layer contract survive intact. It is a controlled-change program across six workstreams: architecture, LLM platform, security, SOX/audit, environment promotion, and operations. This section is the checklist we run with clients.

10.1 · Production reference architecture

10.2 · Choosing the LLM platform

The demo’s DeepSeek call is a placeholder for a single adapter, callLLM(system, user), behind Layer 3. Swapping the provider changes one function and zero business logic. Pick the platform the organisation already governs — the security and procurement review is the long pole, not the integration.

Put a model gateway in front of whichever you choose (Azure API Management, OCI API Gateway, Kong AI Gateway, LiteLLM, or Portkey): it owns key custody, per-team spend caps, routing and fallback between models, prompt/response logging, and lets you retire a deprecated model without touching the application.

10.3 · Security controls

10.4 · SOX, audit, and model-risk controls

A read-only NLQ layer does not change a financial-reporting control, but it is an interface to a SOX-relevant system and lands squarely in ITGC scope. Treat prompts, schemas, and eval sets as code — that single decision satisfies most of what an auditor will ask for.

10.5 · Dev → Test → Prod promotion

• Promoted artifacts: application build, prompt templates (versioned), approved-member schema snapshot, eval set, infrastructure config (IaC) — all from the same Git tag.
• Pipeline: branch → PR review → CI (tests + evals) → deploy to Test → UAT sign-off → CAB → deploy to Prod → smoke test. Hosting can stay on Cloudflare Pages/Workers or move to OCI Functions + API Gateway or the org’s standard platform — the code does not care.
• Configuration: per-environment secrets and endpoints injected at deploy; the same build runs in every environment.
• Metadata sync: a scheduled job refreshes dimension metadata into Layer 2 grounding with change detection, so a new entity or account appears in the approved lists without a code release.
• Rollback: previous build and previous prompt version retained; rollback is a redeploy, and because prompts are versioned it also reverts a prompt regression.

10.6 · Operating it

• SLOs: p95 latency, availability of the read path (the deterministic fallback keeps it alive when the LLM is down — already built), fallback rate as a quality signal, eval pass rate per release.
• Cost governance: per-user and per-team token budgets at the gateway; alert on anomalies; the unit-cost model earlier in this kit is the baseline.
• Model lifecycle: providers retire models on a schedule — re-run the eval suite on the successor before switching, and record the switch as a change.
• Incident runbook: LLM outage → fallback parser; EPM API outage → cached metadata with a stale banner; guardrail spike → review logs for injection attempts.

10.7 · What changes for Master Data NLQ

10.8 · Production readiness checklist

• ✓LLM platform selected from the governed list, DPA / zero-retention terms on file, gateway in front of it
• ✓SSO integrated; authorisation derived from EPM security groups; cookie gate removed
• ✓Read-only EPM service account per pod, credential in a vault, rotation scheduled
• ✓Prompts, schema, and eval set version-controlled and under change management
• ✓Append-only audit log wired to the SIEM with the agreed retention
• ✓Golden-query eval suite passing in CI; results archived as release evidence
• ✓Dev / Test / Prod pipeline with gates, IaC, and a rehearsed rollback
• ✓Model-risk register entry and owner named; first re-validation date set
• ✓Metadata refresh job scheduled with change detection
• ✓SLOs, cost caps, and the incident runbook agreed with platform operations