The problem this solves

Oracle SmartView is the standard tool for EPM data retrieval in Excel. It is also one of the most under-used tools in the Oracle ecosystem — not because it lacks power, but because building a good ad-hoc grid from scratch takes 10–15 minutes even for a SmartView-proficient analyst: connect, select the cube, place dimensions on rows and columns, suppress zeros, set POV, zoom in, format.

Most finance users end up with the same 3–4 saved grids and never adapt them — they pull more data than they need and manually filter in Excel. The result is stale, over-wide reports that answer a slightly different question than the one being asked.

The SmartView NLQ builder generates a ready-to-use grid template in natural language. The analyst types "Revenue accounts for Berlin Mitte FY26 plan vs actual quarterly" and receives a pre-built, formatted grid — including Oracle HsGetValue formulas that connect to a live EPM instance the moment the analyst opens the file in Excel.

Audience

Finance analysts and controllers who use Oracle SmartView for period-end reporting, budget book preparation, and scenario comparison. EPM support teams who spend time building and distributing standard grid templates. Finance managers who want self-service EPM access without SmartView training overhead.

Natural language grid requests

The input is a plain-text description of the desired grid. The NLQ parser extracts five axes:

Interactive controls let the user modify the grid after NLQ generation: add/remove accounts via chips, switch between quarterly and monthly, toggle scenarios, and remove individual rows.

A live-ready Excel SmartView template

The primary output is a rendered grid in the browser — Account rows × Period × Scenario columns — styled to mirror Oracle SmartView output. The secondary output is the Excel file.

The Excel file contains

• Sheet 1 — Grid: Every data cell is a real HsGetValue formula pointing to the user's EPM connection. Cell B1 holds the connection name. Data cells reference $B$1 so changing the connection updates the whole sheet.
• Sheet 2 — Connection Guide: Step-by-step instructions to connect SmartView to an Oracle EPM instance, keyboard shortcuts (F9 refresh, Ctrl+Shift+J zoom in), and troubleshooting notes for the three most common SmartView errors.

HsGetValue formula example

=HsGetValue($B$1,
  "Account#Store Revenue",
  "Entity#Berlin Mitte",
  "Scenario#Plan",
  "Year#FY26",
  "Period#Q1",
  "Currency#USD")

In your EPM + Excel workflow

SmartView NLQ sits between the EPM data model (source) and the analyst's Excel working file (destination). It does not replace the EPM data entry process or Oracle Analytics dashboards — it replaces the manual "build an ad-hoc grid from scratch" step that every SmartView user repeats several times per period.

• Budget book prep: FP&A team generates 44 store-level revenue grids in one session, each pre-wired to live EPM data
• Variance triage: Controller builds a focused grid (COGS accounts, Berlin Mitte, Q3 FY26 actual vs plan) in 30 seconds instead of navigating SmartView manually
• Template distribution: EPM support team generates standard grids with HsGetValue formulas and distributes them — no SmartView client install needed on the recipient's machine until they want to refresh
• New user onboarding: Finance users who aren't SmartView-trained get useful, pre-wired grids without needing SmartView configuration knowledge

The numbers

The honest checklist

• ✓You have Oracle SmartView deployed and finance users who build ad-hoc grids regularly
• ✓Your users spend more than 10 minutes per period building standard grids they could describe in one sentence
• ✓You want self-service EPM data access in Excel without SmartView training overhead for casual users
• ✓You want live-data Excel files, not snapshots — HsGetValue connects directly to your EPM instance on F9
• ✗You need complex suppress-zeros, asymmetric grids, or member formula rows — NLQ generates symmetric grids only
• ✗You need writeback — HsGetValue is read-only; SmartView data form functionality is not replicated here
• ✗Your EPM cube has a non-standard dimension structure not reflected in the demo corpus

Try it now

The live demo generates real SmartView-ready Excel files with HsGetValue formulas. Download the file and open it in Excel with Oracle SmartView installed to see the formulas connect to a live EPM instance.

Sample queries to try

• "Revenue accounts for Tokyo Shibuya FY26 plan vs actual"
• "OpEx lines London Oxford St quarterly FY26"
• "Show both scenarios Mumbai BKC FY26 monthly"
• "APAC margin accounts FY25 budget vs forecast"

The template distribution problem

EPM support teams routinely spend 2–4 hours per period building and distributing standard SmartView grid templates for finance teams. Each template is a static Excel file — by the time it reaches analysts, the data is already stale. Analysts who want a slightly different view have to either request a new template (days of lead time) or build their own grid from scratch (20 minutes of SmartView configuration).

The NLQ approach inverts this: every analyst generates their own live-data template on demand, in under 30 seconds, and the file refreshes against live EPM data when they press F9 in Excel. The EPM support team's template-building workload drops to near zero.

Secondary value: the HsGetValue formula output is a forcing function for correct EPM query construction. Every formula has an explicitly named entity, account, scenario, year, and period — no ambiguous POV, no stale snapshot, no copy-paste errors.

NLQ → Grid spec → Excel pipeline

App UI — Component breakdown

Account chips and the retail chart of accounts

The SmartView builder's default account chip set mirrors 7 of the 18 accounts in the shared FP&A income statement — the 3 revenue lines plus the four computed totals analysts ask for most often in ad-hoc grids:

Period granularity: Quarterly (Q1–Q4) or Monthly (Jan–Dec). Monthly mode generates 12 columns per scenario. Zoom-in from Q to Month is triggered by the period toggle — the account list is unchanged.

Grid spec extraction

The SmartView prompt differs from the FP&A prompt in one key way: it returns an array of accounts rather than a single statement type, and it supports two scenarios simultaneously.

// Output schema (SmartView variant)
{
  "accounts": ["Store Revenue", "Digital Revenue", ...],
  "accountGroup": "revenue" | "opex" | "margin" | "mixed",
  "entity": "Berlin Mitte" | "APAC" | ...,
  "scenarios": ["Plan", "Actual"],   // 1 or 2 elements
  "year": "FY26",
  "periods": ["Q1","Q2","Q3","Q4"],
  "periodGranularity": "quarterly" | "monthly",
  "chartType": "bar" | "line" | null
}

Key prompt engineering decisions

• Account group inference: "revenue accounts" → accounts array contains all revenue-type members, not just "Total Revenue"
• Two-scenario handling: "plan vs actual" → scenarios array has exactly two elements; "actual" alone → one element
• Period granularity: "monthly" or "month-by-month" → periodGranularity: "monthly"; default is quarterly

How the Excel export works

SheetJS (xlsx 0.18.5) supports formula cells via the cell object { t:'n', v:cachedValue, f:'HsGetValue(...)' }. The cached value v holds the demo data number — this makes the file usable immediately without an EPM connection. When the user opens it in Excel with SmartView and presses F9, the formula fires against their live EPM instance and replaces the cached value.

Cell structure

// SheetJS cell with HsGetValue formula
ws['D10'] = {
  t: 'n',
  v: 4230000,   // cached demo value (shows without EPM connection)
  f: 'HsGetValue($B$1,' +
     '"Account#Store Revenue",' +
     '"Entity#Berlin Mitte",' +
     '"Scenario#Plan",' +
     '"Year#FY26",' +
     '"Period#Q1",' +
     '"Currency#USD")'
};

POV header block (rows 1–7)

Rows 1–7 contain the POV setup: B1 = connection name (user-editable), B2 = Entity, B3 = Year, B4 = Currency, B5 = Cube name, B6 = Application name, B7 = Database name. All data formulas reference $B$1 for the connection — changing B1 re-points the entire sheet to a different EPM environment.

Freeze panes

Freeze at column C, row 9: account names stay visible while scrolling across 12 monthly columns; the POV block stays visible while scrolling down through account rows.

Cost per grid generation

Cost model — At-scale projections

Key files

The downloadExcel() function

The function builds the workbook in memory using SheetJS, iterating over account rows × period × scenario columns to construct each formula string. The connection name from the input box (or default "HFM" if blank) is placed in B1. The Connection Guide sheet is built from a static array of step objects — no external template file required. The workbook is serialised and downloaded as EPM-SmartView-[Entity]-[Year].xlsx.

Tech stack — Every tool in this build

Known attack surfaces

Guardrails — What prevents bad grids

• Account enum validation: Only accounts in the known DIMENSION_MEMBERS corpus are allowed in the output array. Unknown account names from the LLM are filtered out before grid rendering.
• Two-scenario limit: Even if the user asks for three scenarios, the parser returns a maximum of two (Plan + Actual, or the two most mentioned). More than two columns per period makes grids unreadable.
• Period granularity guard: Monthly mode is only available when the period set is full-year (all 12 months or Q1–Q4). Requesting "monthly Q3 only" is re-interpreted as "quarterly Q3 only".
• Empty result guard: If the account array resolves to zero valid accounts, the UI shows an error with suggested account group names — it does not render an empty grid.

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: Planning User, plus a SmartView connection pointed at the same identity domain.

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: Enforced twice: once when this tool builds the template, and again — authoritatively — when the user refreshes the workbook and SmartView retrieves under their own login.
• 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 SmartView Builder

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 SmartView Builder 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 SmartView Builder

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