Groovy DataGrid Builders Cheatsheet for Oracle EPM Cloud

Published: November 10, 2024 | By WeRun Consulting

In Oracle EPM Cloud, Groovy scripting offers robust tools to interact with data grids—virtual representations of cube data. This cheatsheet dives into DataGridDefinitionBuilder, DataGridBuilder, and FlexibleDataGridDefinitionBuilder, providing detailed explanations and unique examples. Building on our getEval tutorial, these builders let you read, write, and filter data with precision.

DataGridDefinitionBuilder: Fetching Forecast Data

The DataGridDefinitionBuilder constructs a read-only grid definition for retrieving data from an Essbase cube. Per Oracle’s Groovy API, it’s a programmatic equivalent to a Smart View retrieve, requiring a Point of View (POV), rows, and columns. Key methods include:

• addPov(dimensions, members): Sets fixed dimensions (e.g., Year) and their member combinations as Lists of Lists.
• addRow(dimension, members): Defines row headers with a dimension and specific or evaluated members.
• addColumn(dimension, members): Sets column headers similarly.
• build(): Returns a DataGridDefinition object, which cube.loadGrid(definition, expand) then populates with data (expand=true for dynamic members).

The resulting DataGrid object provides iterators like dataCellIterator() to access cell data and metadata.

Cube cube = operation.application.getCube("Forecast")
DataGridDefinitionBuilder builder = cube.dataGridDefinitionBuilder()
builder.addPov(['Year', 'Scenario', 'Version'], [['FY24'], ['Forecast', 'Actual'], ['Final']])
builder.addColumn(['Measure'], [['Variance']])
builder.addRow(['Region'], [['North', 'South']])
DataGrid grid = cube.loadGrid(builder.build(), false)
grid.dataCellIterator().each { cell ->
    println "Region: ${cell.getMemberName('Region')}, Variance: ${cell.data}"
}
grid.close()
                

DataGridBuilder: Distributing Expenses

The DataGridBuilder creates a writable grid to push data into a cube, akin to a Smart View submit. It’s instantiated with a date format (e.g., "MM/DD/YYYY") for timestamped operations. Key methods include:

• addPov(member1, member2, ...): Sets POV as individual member strings, not Lists.
• addColumn(member1, member2, ...): Defines column headers as strings (typically Periods).
• addRow(headerMembers, data): Adds a row with header members (List) and data values (List of Numbers).
• build(status): Constructs a DataGrid, with a Status object tracking accepted/rejected cells.
• cube.saveGrid(grid): Commits the grid to the cube.

It’s designed for precision updates, with Status providing feedback on save success.

Cube cube = operation.application.getCube("Expenses")
DataGridBuilder builder = cube.dataGridBuilder("MM/DD/YYYY")
builder.addPov('FY24', 'Plan', 'USD', 'Working')
builder.addColumn('Apr', 'May', 'Jun')
def weights = [0.3, 0.3, 0.4] // 30%, 30%, 40%
def totalExpense = 15000
def expenseData = weights.collect { it * totalExpense }
builder.addRow(['Travel'], expenseData)
DataGridBuilder.Status status = new DataGridBuilder.Status()
builder.build(status).withCloseable { grid ->
    println "Cells saved: ${status.numAcceptedCells}"
    cube.saveGrid(grid)
}
                

FlexibleDataGridDefinitionBuilder: Targeted Revenue Analysis

The FlexibleDataGridDefinitionBuilder extends DataGridDefinitionBuilder with dynamic dimension handling and exclusion capabilities. It simplifies complex queries by inferring dimensions from members. Key methods include:

• setPov(member1, member2, ...): Sets POV as strings, order matching cube dimensions.
• setRowDimensions(dim) and setColumnDimensions(dim): Assigns dimensions to axes dynamically.
• addRow(members) and addColumn(members): Adds members or Essbase expressions (e.g., "ILvl0Descendants").
• setExclude(memberSpec): Excludes specific members from the grid.
• build(): Creates a flexible DataGridDefinition.

It’s ideal for grids with variable or filtered data needs.

Cube cube = operation.application.getCube("Revenue")
def builder = cube.flexibleDataGridDefinitionBuilder()
builder.setPov('FY24', 'Actual', 'USD', 'Final')
builder.setColumnDimensions('Customer')
builder.addColumn('ILvl0Descendants("AllCustomers")')
builder.setRowDimensions('Account')
builder.addRow('NetRevenue').setExclude('[Customer].[DormantCustomers]')
cube.loadGrid(builder.build(), false).withCloseable { grid ->
    grid.dataCellIterator().each { cell ->
        println "Customer: ${cell.getMemberName('Customer')}, Revenue: ${cell.data}"
    }
}
                

Key Differences

- DataGridDefinitionBuilder: Structured reads with explicit dimension-member pairs, best for static grids.
- DataGridBuilder: Write operations with direct member inputs, suited for data submission.
- FlexibleDataGridDefinitionBuilder: Dynamic reads with inferred dimensions and exclusions, ideal for complex queries.

Tips and Tricks

cube.loadGrid(builder.build(), false).withCloseable { grid ->
    // Process grid here
}
                

if (status.numRejectedCells > 0) {
    println "Rejected: ${status.numRejectedCells}, Reason: ${status.rejectedCells}"
}