⚡ Chapter II · Lesson 1 · 125 XP

📋 Cell API & operation.grid

Chapter II · EPM Cloud API · Lesson 1 of 7

The Grid Awakens

Every Groovy form script is handed one object at runtime: operation.grid. It is the in-memory snapshot of the form's cell data — your read/write interface to everything the planner entered. Understanding every property on a Cell object and every way to navigate the grid is the foundation of professional EPM scripting.

operation.grid — Complete API

Grid Navigation Methods

.getCellAt(row, col)

Cell | null

Zero-based row/col. Fastest lookup when position is known statically.

.getCellByIntersection(List)

Cell | null

Member names in POV → row → col dimension order. Returns null for invalid/suppressed intersections.

.dataCellIterator()

Iterator<Cell>

Iterates all writable cells. Skips headers/labels. EPM 21.05+. O(n) — preferred over nested getCellAt loops.

.getRowCount()

int

Total data rows. Drive dynamic getCellAt loops safely.

.getColCount()

int

Total data columns.

Cell Object — Every Property

Property	R/W	Type	Key Behaviour
`cell.data`	R/W	Number\|String\|null	The new user-entered value. Write here to transform before commit. null = #Missing — never assign 0 when you mean missing.
`cell.edited`	R	Boolean	True ONLY if user explicitly changed this cell in the current save. Without this guard, ALL cells refire your logic on every save.
`cell.readOnly`	R	Boolean	Writing .data on a readOnly cell silently fails — no error, no exception. Always guard.
`cell.priorValue`	R	Number\|null	Last committed Essbase value. null if previously #Missing. Use for delta = (cell.data ?: 0) − (cell.priorValue ?: 0).
`cell.noData`	R	Boolean	True if current value is #Missing. Never assume null == 0 — missing and zero are semantically different.
`cell.essbaseType`	R	String	Values: 'NUMERIC', 'TEXT', 'SMART_LIST'. Guard text cells before any math — ClassCastException on .data as Number.
`cell.memberNames`	R	List<String>	Full intersection in POV→row→col dimension order. Use to dynamically build getCellByIntersection POVs.
`cell.row / cell.col`	R	int	Zero-based position. Navigate to adjacent cells: grid.getCellAt(cell.row + 1, cell.col).

Production Audit Trail — cell.priorValue Pattern

Q8 Hard — Audit trail implementation

// Production audit trail — Vision Corporation budget change log
def user = operation.getUserName()
def ts   = new Date().format("yyyy-MM-dd HH:mm:ss")

operation.grid.dataCellIterator().each { cell ->
    // Guard chain — correct order
    if (cell == null || cell.readOnly || cell.noData || !cell.edited) return
    if (cell.essbaseType != "NUMERIC") return     // skip text/smart-list cells

    def newVal   = (cell.data       ?: 0G) as BigDecimal
    def oldVal   = (cell.priorValue ?: 0G) as BigDecimal
    def delta    = newVal - oldVal
    if (delta == 0) return                             // no real change — skip phantom audit

    // Write prior value to Audit_Prior scenario (same cube)
    def auditPov = cell.memberNames.toList()
    auditPov[auditPov.indexOf(cell.memberNames.find { it == "Budget" || it == "Forecast" })] = "Audit_Prior"
    def auditCell = operation.grid.getCellByIntersection(auditPov)
    if (auditCell && !auditCell.readOnly) auditCell.data = oldVal

    println "AUDIT|${ts}|${user}|${cell.memberNames.join('|')}|old=${oldVal}|new=${newVal}|delta=${delta}"
}

⚠getCellByIntersection null diagnoses: (1) Wrong dimension order — use cell.memberNames to get the correct order. (2) Member outside form range — only loaded cells can be found. (3) Suppressed #Missing row — suppression removes cells from the grid entirely. (4) Alias vs member name — API resolves member names only, never aliases.

🎯 Interview Q5 [Easy] + Q7 [Medium] + Q8 [Hard]

cell.data vs cell.priorValue / getCellByIntersection returned null / Design a production audit trail

Q5: data = new value (R/W), priorValue = last committed (R, can be null). Q7: 4 causes — dim order, member range, suppressed row, alias name. Q8: atomicity (same-save write), dataCellIterator, store priorValue (not new), getUserName for identity, guard audit cell for null.

⚡ Chapter II · Lesson 1 Tasks

List all 8 Cell properties with types and their key gotchas

Diagnose: getCellByIntersection returns null even though cell exists — 4 root causes

Implement audit trail: dataCellIterator → delta check → write priorValue to Audit_Prior

Explain why writing to a readOnly cell silently fails and how to guard

🔭

The Oracle

Cell API Expert

← Chapter I

⚡ Chapter II · Lesson 2 · 175 XP

🏗 DataGridBuilder — Full API

Chapter II · EPM Cloud API · Lesson 2 of 7

The Single Retrieve

DataGridBuilder is the most important API for production-scale Groovy scripting in EPM Cloud. It is the difference between a rule that takes 2 seconds and one that times out at 30 minutes. One .build() call retrieves everything. One .save() commits everything. Everything in between is pure Groovy in memory.

DataGridBuilder — Complete Method Reference

Builder Methods (fluent — each returns DataGridBuilder)

DataGridBuilder.create(operation)

DataGridBuilder

Factory method. Always pass operation — binds to current Essbase connection.

.pov(Map<String,String>)

DataGridBuilder

Fix sparse dimensions to one member. Each fixed dim reduces block search space. Add all non-varying dims.

.rows(Map<String,List<String>>)

DataGridBuilder

Sparse dimensions to iterate as rows. One row per unique member combination.

.columns(Map<String,List<String>>)

DataGridBuilder

Dense dimensions here. They're free — already loaded with the block at no extra I/O.

.build()

DataGrid

Executes ONE Essbase MDP retrieve. Never call inside a loop. Returns in-memory snapshot.

Grid Iteration & Mutation

grid.eachRow(closure)

void

Iterate every row. Row object has .getCellAt(memberName) and .getCellByIntersection(list).

row.getCellAt(memberName)

Cell

Get cell by its column member name. Faster than getCellByIntersection for column lookups.

grid.save()

void

Commits ALL in-memory mutations in ONE atomic write. Call AFTER all cell.data assignments. Never inside eachRow.

Dense vs Sparse Placement — The Why

❌ Dense dim in .rows() — multiple block reads per entity

DataGridBuilder.create(operation)
  .rows([
    Entity: entities,
    Account: accounts  // ← WRONG: Account is dense
  ]).build()
// Forces partial block retrieval per Account member

✅ Dense dim in .columns() — reads from same block, free

DataGridBuilder.create(operation)
  .rows([Entity: entities])
  .columns([Account: accounts])  // ← correct
  .build()
// One block per Entity — all Accounts free

Vision Scenario — Step 2: DataGridBuilder Integration

✓ S1

Null-safe read

Grid builder

Seasonality

Allocations

Peak rule

STEP 2 of 5 — DataGridBuilder

Vision Corporation — Revenue Spread with DataGridBuilder

// STEP 2: Replace individual getCell() calls with DataGridBuilder
// Reads ALL entities × ALL months in ONE Essbase call

def scenario = operation.parameter("Scenario") ?: "Budget"
def year     = operation.parameter("Year") ?: "FY2025"

def months = ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]
def essbase = operation.getEssbaseConnection()
def entities = essbase.getMember("Entity", "Total_Operating_Entities").getLevelZeroMembers()

// ONE retrieve — current year Budget and prior year Actual in same grid
def grid = DataGridBuilder.create(operation)
    .pov([Version: "Working"])
    .rows([Entity: entities.collect{it.name}, Scenario: [scenario, "Actual"]])
    .columns([Account: ["Total_Revenue"], Period: months + ["Full_Year"]])
    .build()

println "Grid built: ${grid.rowCount} rows × ${grid.colCount} cols"

// Phase 2: compute in memory — zero Essbase calls
def writes = []
grid.eachRow { row ->
    def rowScenario = row.getCellAt("Scenario")?.memberNames?.find { it == scenario || it == "Actual" }
    // ... spread logic added in Step 3
}
// grid.save() — one atomic commit in Step 3

🎯 Interview Q13 [Easy] + Q14 [Medium] — DataGridBuilder

What is DataGridBuilder and why use it instead of getCellByIntersection in a loop? / Why should dense dimensions be in .columns()?

Q13: N getCellByIntersection calls = N block retrievals. DataGridBuilder.build() = 1 MDP call regardless of entity count. Q14: Dense dims are free in .columns() — already loaded with the block. In .rows() they force partial block retrieval per member = wasted I/O.

🏗 Chapter II · Lesson 2 Tasks

Write a DataGridBuilder for Vision: entities in rows, accounts+months in columns

Explain why Account must be in .columns() and not .rows()

Use grid.eachRow{} and row.getCellAt("Revenue") to iterate the result

Implement Step 2 — DataGridBuilder replacing getCell() loop for Vision spread

🔭

The Oracle

DataGridBuilder Expert

⚡ Chapter II · Lesson 3 · 150 XP

🗂 Member & Metadata API

Chapter II · EPM Cloud API · Lesson 3 of 7

The Dimension Whisperer

Production Groovy rules never hardcode member lists. When Vision Corporation adds a new entity or account, rules that hardcode names silently exclude it. Dynamic metadata queries via the Member API make rules self-maintaining — a new entity tagged with the right UDA is automatically included in every allocation and validation on the next run.

Member API — Complete Reference

All Member API patterns

def conn = operation.getEssbaseConnection()   // or getEssbaseDimension("Account")

// ── Fetch and inspect a member ──────────────────────────────────
def mbr = conn.getMember("NetIncome")
println mbr.name           // "NetIncome"
println mbr.alias          // "Net Income" — may be null
println mbr.consolidation  // "+", "-", "~", "^"
println mbr.dataStorage    // "Store", "Dynamic Calc", "Never Share", etc.

// ── Test member properties ───────────────────────────────────────
if (mbr.isDynCalc()) { /* skip — no stored value */  }
if (mbr.isShared())  { /* handle shared member */   }

// ── Hierarchy traversal ─────────────────────────────────────────
def leaves   = conn.getMember("Total_Vision").getLevelZeroMembers()
def children = conn.getMember("Total_Revenue").getChildren()
def ancestors = conn.getMember("Vision_US").getAncestors()   // parent → root

// ── UDA filtering — the driver-based budgeting pattern ──────────
def headcountAccounts = conn.getMembersByUDA("Account", "HEADCOUNT_DRIVER")
def allocTargets      = conn.getMembersByUDA("Entity", "ALLOCATION_TARGET")

// ── Alias → member name resolution ──────────────────────────────
def memberName = conn.getMemberFromAlias("Account", "Net Income", "Default")?.name

// ── Level and generation ─────────────────────────────────────────
println mbr.getLevel()       // 0 = leaf, 1 = parent of leaves
println mbr.getGeneration()  // 1 = root, 2 = first level below root

Vision Scenario — Step 3: Seasonality with Dynamic Members

✓ S1

Null-safe

✓ S2

Grid builder

Seasonality

Allocations

Peak rule

STEP 3 of 5 — Seasonality with Dynamic Members

Revenue Spread — Seasonality weights from prior year, dynamic entities via UDA

// STEP 3: Add seasonality + UDA-based entity selection
def conn    = operation.getEssbaseConnection()
def months  = ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]

// Dynamic: entities tagged SPREAD_TARGET — no hardcoded lists
def entities = conn.getMembersByUDA("Entity", "SPREAD_TARGET").collect{it.name}
assert !entities.isEmpty() : "No entities tagged SPREAD_TARGET — check UDA configuration"

// ONE build: current Budget and prior year Actual
def grid = DataGridBuilder.create(operation)
    .pov([Version: "Working", Account: "Total_Revenue"])
    .rows([Entity: entities])
    .columns([Period: months + ["Full_Year"], Scenario: ["Budget", "Actual"]])
    .build()

def writes = []

grid.eachRow { row ->
    def entity     = row.getCellAt("Full_Year")?.memberNames?.find{it in entities}
    def annualBudg = (row.getCellAt("Full_Year Budget")?.data ?: 0G) as BigDecimal
    if (annualBudg == 0) return

    def pyTotal = (row.getCellAt("Full_Year Actual")?.data ?: 0G) as BigDecimal
    def fallback = pyTotal == 0  // new entity — equal spread

    months.eachWithIndex { m, i ->
        def pyMth   = fallback ? (annualBudg / 12) : ((row.getCellAt("${m} Actual")?.data ?: 0G) as BigDecimal)
        def weight  = fallback ? (1G/12G) : (pyTotal > 0 ? pyMth/pyTotal : 1G/12G)
        def spread  = (annualBudg * weight).setScale(0, BigDecimal.ROUND_HALF_UP)
        writes << [entity, "Total_Revenue", m, "FY2025", "Budget", "Working", spread]
    }
    // Rounding reconciliation — assign diff to December
    def sumMths = writes.takeLast(12).sum{it[6]}
    writes[-1][6] += (annualBudg - sumMths)
}

operation.grid.setDataCellValues(writes)   // ONE batch write for all entities × 12 months
println "Vision Spread Step 3: ${entities.size()} entities, ${writes.size()} cells written"

🗂 Chapter II · Lesson 3 Tasks

Use getMembersByUDA to get SPREAD_TARGET entities dynamically

Traverse getAncestors() to log full hierarchy path for an entity

Implement seasonality fallback for new entities with no prior year data

Add the rounding reconciliation to ensure months sum exactly to annual

🔭

The Oracle

Metadata & UDA Expert

⚡ Chapter II · Lesson 4 · 125 XP

🎛 RTPs — Multi-Select, Chains & Control Flow

Chapter II · EPM Cloud API · Lesson 4 of 7

The Runtime Orchestrator

Runtime Prompts are the user's input into a Groovy rule at execution time. Multi-select RTPs arrive as a comma-delimited string with edge cases that bite every developer eventually: trailing commas, blank tokens, null for skipped optional prompts. And chaining rules with executeRule() carries a case-sensitivity trap that has caused many a failed budget cycle overnight batch.

Multi-Select RTP — Complete Safe Parse

Q10 Medium — Multi-select entity RTP parsing

// EPM delivers multi-select as: "Vision_US,Vision_EU,Vision_APAC"
// Edge cases: null (skipped), trailing comma "Vision_US,", duplicates

def raw = operation.parameter("Entity") ?: ""   // ?: "" guards null before tokenize

def entities = raw
    .tokenize(",")        // split on comma
    *.trim()               // remove whitespace from each
    .findAll { it }        // drop empty tokens (trailing comma produces "")
    .unique()              // deduplicate

// Apply fallback if nothing came through
if (entities.isEmpty()) {
    entities = conn.getMember("Entity", "Total_Operating_Entities")
        .getLevelZeroMembers().collect{it.name}
    println "INFO: No entities in RTP — defaulting to all operating entities (${entities.size()})"
}

// Validate after parsing — don't trust user input
def validEntities = conn.getMember("Entity","Total_Vision")
    .getLevelZeroMembers().collect{it.name}.toSet()
def invalid = entities.findAll { !(it in validEntities) }
assert invalid.isEmpty() : "Invalid entities in RTP: ${invalid.join(', ')}"

executeRule — Chaining Rules

Q12 Hard — executeRule with RTP forwarding

// operation.executeRule() is SYNCHRONOUS — blocks until downstream completes
// Key: map keys must EXACTLY match the downstream rule's RTP names (case-sensitive)

try {
    // Forward RTPs — key must match EXACTLY as defined in Calculation Manager
    operation.executeRule("Vision_Aggregate_All", [
        "Scenario": scenario,           // capital S — must match CM RTP name
        "Year"    : "FY${year}",          // capital Y — String value always
        "Period"  : period               // 'period' != 'Period' → RTP not found
    ])
    println "Aggregation complete for ${scenario}/FY${year}"
} catch (Exception e) {
    println "ERROR: Downstream rule failed — ${e.message}"
    // Decision: cancel primary save OR log and continue?
    operation.cancelled = true
    operation.cancellationMessage = "Aggregation failed: ${e.message}"
    return
}

// ⚠ Recursive loop danger: Rule A calls Rule B which calls Rule A
// EPM does NOT detect this — will exhaust thread stack

operation.cancelled — The Correct Pattern

Q3 Medium — cancellationMessage must always be set

// WRONG: cancelled without message — blank dialog or cryptic JVM error
operation.cancelled = true
// User sees nothing useful

// CORRECT: always set both, always return immediately after
operation.cancelled = true
operation.cancellationMessage = "Gross margin ${gm.setScale(1)}% is below the 10% minimum for ${entity}. " +
    "Increase revenue or reduce COGS before submitting."
return   // ALWAYS return immediately — no code should run after cancel

🎯 Interview Q3 [Med] + Q10 [Med] + Q12 [Hard] — RTPs & Control

What happens without cancellationMessage? / Multi-select RTP parse / executeRule mistakes?

Q3: blank or cryptic dialog — include entity+value+fix guidance in message. Q10: full chain — tokenize + trim + findAll + unique + null guard + fallback + validate. Q12: case-sensitive keys, String values, synchronous (can throw), recursive loop danger.

🎛 Chapter II · Lesson 4 Tasks

Write the 5-step multi-select RTP parse chain with null guard and fallback

Call executeRule() with correct case-sensitive RTP map and try-catch wrapper

Write a cancellationMessage that includes entity, value, and what to fix

Identify two ways to accidentally create an executeRule recursive loop

🔭

The Oracle

RTP & Control Flow Expert

⚡ Chapter II · Lesson 5 · 150 XP

⚡ Performance — The 9 Levers

Chapter II · EPM Cloud API · Lesson 5 of 7

The Speed Alchemist

A Groovy rule that was 3 minutes in UAT is 45 minutes in production. The data volume is 10x. The analysis: 96 individual setCell() calls in a nested loop. Fix: one DataGridBuilder + one save = 2 seconds. The 9 performance levers are ranked by impact — check them in order before optimising anything.

The 9 Performance Levers — Ranked

Impact

What to Do

Why It Matters

CRITICAL

Call .build() once, outside any loop

Eliminates N Essbase block retrievals. 100 entities = 0.2s vs 50s.

CRITICAL

Call grid.save() once, after all mutations

Single atomic write vs N lock+write+unlock cycles. Also: data integrity (partial write prevention).

HIGH

Tight POV — fix every constant sparse dim

Each fixed dim halves+ the block search space. Loose POV = full sparse index scan.

HIGH

Dense dims in .columns(), not .rows()

Dense dims in rows force partial block retrieval. In columns they're free.

MEDIUM

Cache member objects before loops

getMember() = metadata round-trip. Cache once before .each, reuse N times.

MEDIUM

Use dataCellIterator() over nested getCellAt loops

Iterator is O(n) over loaded cells. Nested getCellAt is O(rows×cols) with repeated index lookups.

LOW

Avoid println inside tight loops

String alloc + I/O flush per iteration. Log counts, not per-cell detail.

LOW

Filter in POV/rows spec, not in Groovy

Groovy-side filtering still retrieves the full slice from Essbase first.

CONSIDER

Hand off large dense calcs to Essbase calc scripts

Native Essbase engine is 10–100× faster for block-level aggregations vs JVM cell-by-cell.

Vision Scenario — Step 4: Allocation with all 9 Levers Applied

✓ S1

Null-safe

✓ S2

Grid builder

✓ S3

Seasonality

Allocation

Peak rule

STEP 4 of 5 — IT Allocation with all 9 Levers

Revenue-Proportional IT Shared Cost Allocation — Performance Correct

// STEP 4: Two-pass IT allocation — all 9 performance levers applied
def conn     = operation.getEssbaseConnection()
def t        = System.currentTimeMillis()
def lap      = { lbl -> def n=System.currentTimeMillis(); println "[PERF] ${lbl}: ${n-t}ms"; t=n }

// Lever 5: cache member objects BEFORE any loop
def entities = conn.getMembersByUDA("Entity", "ALLOCATION_TARGET").collect{it.name}

// Lever 1+3: ONE build call, tight POV (all constants fixed)
def grid = DataGridBuilder.create(operation)
    .pov([Scenario: "Budget", Version: "Working", Year: "FY2025"])
    .rows([Entity: entities + ["Corporate"]])              // include pool entity
    .columns([Account: ["Total_Revenue", "IT_Shared_Cost", "IT_Allocated"], Period: ["Full_Year"]])
    .build()
lap("Retrieval")

// Pass 1: sum total revenue (lever 6: eachRow, not getCellAt in nested loop)
def totalRev = 0G
def revMap   = [:]
grid.eachRow { row ->
    def entity = row.getCellAt("Total_Revenue")?.memberNames?.find{ it in entities }
    if (!entity) return
    def rev = (row.getCellAt("Total_Revenue")?.data ?: 0G) as BigDecimal
    revMap[entity] = rev
    totalRev += rev
}
assert totalRev > 0 : "Guard: total revenue is zero — allocation aborted"

// Fetch pool value
def pool = grid.eachRow { row ->  // locate Corporate row
    row.getCellAt("IT_Shared_Cost")?.data ?: 0G
}.first() ?: 0G as BigDecimal

// Lever 2: ALL writes staged into one list
def writes = []
revMap.each { entity, rev ->
    def share = (pool * (rev / totalRev)).setScale(0, BigDecimal.ROUND_HALF_UP)
    writes << [entity, "IT_Allocated", "Full_Year", "FY2025", "Budget", "Working", share]
}

operation.grid.setDataCellValues(writes)   // Lever 2: ONE batch write
lap("Commit")
println "IT allocated: pool=${pool} → ${entities.size()} entities, ${writes.size()} writes"

⚡ Chapter II · Lesson 5 Tasks

Recite the 9 levers in ranked order from memory

Implement Step 4 allocation with lap() profiling — measure Retrieval vs Commit

Identify which levers apply if Computation phase is the bottleneck

When should you invoke a native Essbase calc script instead of Groovy?

🔭

The Oracle

Performance Expert

⚡ Chapter II · Lesson 6 · 150 XP

🌐 Cross-Cube & REST API Patterns

Chapter II · EPM Cloud API · Lesson 6 of 7

The Bridge Builder

Vision Corporation's Planning cube calculates the budget. Its Reporting cube drives the CFO dashboard. When a planner saves a form in Planning, the Reporting cube must immediately reflect the change. Cross-cube writes via getRestApiClient() and external REST calls via HttpURLConnection are the integration bridges that make EPM Cloud talk to the world.

getRestApiClient() — Scope & Decision Tree

Scenario	Use This	Why
Write to another cube in same EPM environment	`getRestApiClient().postDataSlice()`	Zero credential setup — session token injected automatically
Read data from another cube	`getRestApiClient().get()`	Same session token applies
Write to a different EPM environment	Manual HttpURLConnection + SubVar credentials	getRestApiClient() only targets current environment
Call external API (Salesforce, SAP, etc.)	Manual HttpURLConnection + SubVar credentials	Not in EPM ecosystem — needs own auth
Post-write aggregation needed	operation.executeRule() after write	Synchronous, guaranteed order

⚠postDataSlice() is ASYNC: It fires and returns immediately. If you call it then immediately read back written values, you will get stale data. Use executeRule() if you need to chain a calculation that reads the written values.

Q16 Hard — Cross-cube write with full error handling

// Step 1: Validate primary cells FIRST — then cross-cube write
// (If cross-cube write ran first and primary save cancelled → phantom data)
if (!primaryValidation()) {
    operation.cancelled = true
    operation.cancellationMessage = "Validation failed — see details above"
    return
}

// Step 2: Build cross-cube payload
// Member list must follow TARGET cube's stored dimension order — NOT form order
def client  = operation.getRestApiClient()
def payload = [
    data: entities.collect { entity ->
        [["Budget", "Working", "FY2025", "Full_Year", entity, "Total_Revenue"],
         getRevenue(entity)]
    }
]

try {
    client.postDataSlice("Vision_Reporting", payload)
    println "Cross-cube write queued for ${entities.size()} entities to Vision_Reporting"
} catch (Exception e) {
    // Business decision: log and continue, or cancel primary save?
    println "ERROR: Cross-cube write failed: ${e.message}"
    // Primary save still proceeds — Finance ops team investigates via job log
}

// For external REST (non-EPM API) — credentials from SubVars NEVER hardcoded
// def token = operation.getSubstitutionVariable("EXT_API_TOKEN")
// def conn = new URL("https://api.corp.com/data").openConnection()
// conn.setRequestProperty("Authorization", "Bearer ${token}")

🎯 Interview Q15 [Medium] + Q16 [Hard] — REST API

getRestApiClient() scope limitations / Cross-cube write with full error handling

Q15: same-environment only; postDataSlice is async; SubVars for credentials in external calls. Q16: ordering critical — validate primary FIRST; payload dimension order = target cube stored order (not form order); try-catch around write; async means don't read back immediately.

🌐 Chapter II · Lesson 6 Tasks

Build a cross-cube write payload from Vision Planning → Vision_Reporting

Explain why validate-primary-FIRST ordering matters for cross-cube writes

Write an external REST call using HttpURLConnection with SubVar credential retrieval

Explain postDataSlice() async behaviour and how to handle post-write reads

🔭

The Oracle

REST & Integration Expert

⚡ Chapter II · Lesson 7 · 225 XP

👑 Peak: The Complete Vision Production Suite

Chapter II · EPM Cloud API · Lesson 7 of 7 · Mastery Complete!

The Summit

Every technique from both chapters — null safety, error wrapper, DataGridBuilder, UDA-based member selection, seasonality, allocation, cross-cube write, profiling — assembled into a single, production-grade Vision Corporation Groovy rule suite. This is what a senior EPM Groovy practitioner delivers.

✓ S1

Null-safe

✓ S2

Grid builder

✓ S3

Seasonality

✓ S4

Allocation

Peak rule

👑 Vision Corporation — Master Budget Rule Suite (Production Grade)

All 24 Interview Toolkit patterns demonstrated. All 9 Performance Levers applied. Vision Corporation $2.8B, 8 operating entities, 6 currencies.

// ═══════════════════════════════════════════════════════════════
// VISION CORPORATION — Master Budget Rule Suite
// Combines: Seasonal Spread + IT Allocation + Validation + Audit
// Interview coverage: Q1-Q24 all patterns demonstrated
// ═══════════════════════════════════════════════════════════════
import java.text.SimpleDateFormat

// ── Universal logger + profiler ─────────────────────────────────
def log = { l, m -> println "[${new SimpleDateFormat('HH:mm:ss').format(new Date())}][${l.padRight(5)}] ${m}" }
def t0 = System.currentTimeMillis()
def lap = { lbl -> def n=System.currentTimeMillis(); log("PERF","${lbl}: ${n-t0}ms"); t0=n }
def outcome = "UNKNOWN"

try {
    // ── Layer 1: RTP validation ──────────────────────────────────
    def scenario = operation.parameter("Scenario") ?: "Budget"
    def year     = operation.parameter("Year") ?: "FY2025"
    assert scenario in ["Budget","Forecast"] : "Invalid scenario: ${scenario}"
    log("INFO", "START | user=${operation.getUserName()} | ${scenario} ${year}")

    // ── Metadata: dynamic member selection via UDAs ─────────────
    def conn        = operation.getEssbaseConnection()
    def opEntities  = conn.getMembersByUDA("Entity", "ALLOCATION_TARGET").collect{it.name}
    def hcAccounts  = conn.getMembersByUDA("Account", "HEADCOUNT_DRIVER").collect{it.name}
    assert !opEntities.isEmpty() : "No ALLOCATION_TARGET entities — check UDA config"
    log("INFO", "Entities: ${opEntities.size()} | HC accounts: ${hcAccounts.size()}")

    // ── ONE DataGridBuilder call — all data in single Essbase retrieve ──
    def months = ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]
    def grid   = DataGridBuilder.create(operation)
        .pov([Version: "Working", Year: year])
        .rows([Entity: opEntities + ["Corporate"], Scenario: [scenario, "Actual"]])
        .columns([Account: ["Total_Revenue","IT_Shared_Cost","IT_Allocated","Total_Headcount"],
                  Period:  months + ["Full_Year"]])
        .build()
    lap("Retrieval")

    // ── Computation: pre-fetch all values in memory ─────────────
    def revenueMap = [:]; def headcountMap = [:]; def pool = 0G
    grid.eachRow { row ->
        def entity = row.getCellAt("Total_Revenue")?.memberNames?.find{ it in opEntities }
        if (entity) {
            revenueMap[entity]   = (row.getCellAt("Full_Year")?.data ?: 0G) as BigDecimal
            headcountMap[entity] = (row.getCellAt("Total_Headcount Full_Year")?.data ?: 0G) as BigDecimal
        } else {
            pool = (row.getCellAt("IT_Shared_Cost Full_Year")?.data ?: 0G) as BigDecimal
        }
    }
    def totalRev = revenueMap.values().sum() ?: 0G
    assert totalRev > 0 : "Guard: total revenue zero — allocation aborted"

    // ── Validation: collect ALL errors before throw ──────────────
    def errors = []
    opEntities.each { e ->
        def rev = revenueMap[e] ?: 0G
        def hc  = headcountMap[e] ?: 0G
        if (hc < 0) errors << "❌ ${e}: Headcount ${hc} is negative"
        if (rev > 0 && hc == 0) errors << "⚠ ${e}: Has revenue but zero headcount"
    }
    if (!errors.isEmpty()) throw new AssertionError(errors.join("\n"))

    // ── Write: ONE batch for all entities × all accounts ─────────
    def writes = []
    opEntities.each { e ->
        def rev   = revenueMap[e] ?: 0G
        def share = totalRev > 0 ? (pool * (rev / totalRev)).setScale(0, BigDecimal.ROUND_HALF_UP) : 0G
        writes << [e, "IT_Allocated", "Full_Year", year, scenario, "Working", share]
    }
    operation.grid.setDataCellValues(writes)
    lap("Commit")

    // ── Cross-cube: mirror results to Reporting cube ─────────────
    try {
        operation.getRestApiClient().postDataSlice("Vision_Reporting", [data: writes.collect{
            [[it[4], it[5], it[2], it[3], it[0], it[1]], it[6]]
        }])
        log("INFO", "Cross-cube write queued: Vision_Reporting")
    } catch (Exception ex) {
        log("ERROR", "Cross-cube write failed (primary save continues): ${ex.message}")
    }

    log("INFO", "COMPLETE: ${writes.size()} cells | pool=${pool} | entities=${opEntities.size()}")
    outcome = "SUCCESS"

} catch (AssertionError ae) {
    outcome = "VALIDATION_CANCEL"
    operation.cancelled = true
    operation.cancellationMessage = ae.message
    log("WARN", "Validation: ${ae.message}")
} catch (Exception e) {
    outcome = "SYSTEM_ERROR"
    log("ERROR", e.message)
    e.stackTrace.take(5).each{ log("ERROR", "  ${it}") }
    throw e
} finally {
    log("INFO", "=== OUTCOME: ${outcome} ===")
}

Interview Toolkit — Complete Coverage Map

Q#	Topic	Where Demonstrated
Q1, Q2, Q3	Architecture & hooks	Ch I Lesson 1 + cancellationMessage in skeleton
Q4	Timeout strategies	DataGridBuilder + lap() profiler (Performance Lever #1)
Q5, Q6	cell.data, cell.edited	Ch II Lesson 1 guard chain + audit trail
Q7, Q8	getCellByIntersection null / audit trail	Ch II Lesson 1
Q9, Q10, Q11, Q12	RTPs — cast, multi-select, closure return, executeRule	Ch I Lessons 2–3 + Ch II Lesson 4
Q13, Q14	DataGridBuilder / dense columns	Ch II Lesson 2 + Steps 2–5
Q15, Q16	getRestApiClient / cross-cube	Ch II Lesson 6 + Step 5
Q17, Q18, Q19, Q20	Performance — single change, member cache, calc script, profiling	Ch II Lesson 5 + lap() in Step 4–5
Q21, Q22, Q23, Q24	Logging, AssertionError, partial write, error wrapper	Ch I Lesson 5–6 + Step 5 try-catch-finally

👑 Chapter II · Completion — Groovy Mastery

Write the complete production suite from memory (skeleton + DataGridBuilder + error wrapper)

Explain how all 9 performance levers are demonstrated in the peak rule

Map all 24 Interview Toolkit questions to specific code patterns in both chapters

Adapt the suite for Vision's HR allocation pool (by headcount instead of revenue)

👑

The Oracle — Groovy Examiner

Validate your production-grade Groovy mastery

🏆 Quest Map