A Deep Dive into FlexibleDataGridDefinitionBuilder and DataGridDefinitionBuilder!

In the world of Oracle EPM Cloud, data grids serve as the backbone for interacting with multidimensional datasets. Whether you're querying data or preparing it for analysis, the choice of grid definition builder can significantly impact your productivity and script efficiency. Oracle EPM Cloud provides two main tools for constructing these grids: DataGridDefinitionBuilder and FlexibleDataGridDefinitionBuilder.

In this blog, we’ll explore these two builders, dissect their features, and help you decide which one suits your needs. Along the way, we’ll cover best practices, showcase example scripts, and highlight why the flexibility of the FlexibleDataGridDefinitionBuilder often makes it the preferred choice for modern Oracle EPM applications.

---

What Are Data Grid Definitions?

A data grid definition is a blueprint that specifies a slice of data in a multidimensional cube. Think of it as defining the axes of a table: rows, columns, and a fixed context (POV). This definition is essential for both querying and writing data in Oracle EPM.

Understanding

1. DataGridDefinitionBuilder

This is the traditional tool for constructing data grids. It requires explicit specifications for all dimensions in the POV, rows, and columns. Here’s a summary of its key characteristics:

• Explicit Dimension Naming: You must explicitly define which dimensions belong in the POV, columns, and rows.
• Detailed Configuration: Requires precise member selections for each dimension, making it slightly verbose for complex grids.
• Limited Features: Lacks advanced features like member exclusion.
• Best Use Cases:
• When working with small, simple cubes.
• When explicit control over every dimension is essential.

 DataGridDefinitionBuilder: Suppression Methods

 The DataGridDefinitionBuilder class offers several methods to suppress unwanted data during grid construction:

 

setSuppressMissingBlocks(boolean suppressMissingBlocks): Suppresses blocks that are entirely missing, preventing the retrieval of empty data blocks.

 

setSuppressMissingRows(boolean suppressMissingRows): Suppresses rows with missing data, ensuring only populated rows are included.

 

setSuppressMissingColumns(boolean suppressMissingColumns): Suppresses columns with missing data, including only those with actual data.

 

setSuppressInvalidRows(boolean suppressInvalidRows): Suppresses rows containing invalid data, filtering out rows that don't meet validation criteria.

 

setSuppressInvalidColumns(boolean suppressInvalidColumns): Suppresses columns with invalid data, ensuring only valid data columns are retrieved.

 

setSuppressInvalidScenarioTps(boolean suppressInvalidScenarioTps): Suppresses invalid scenario time periods, excluding data from invalid time periods.

 

setSuppressMissingRowsNative(boolean suppressMissingRowsNative): Suppresses missing rows at the native level, enhancing performance by excluding missing rows earlier in the data retrieval process.

 

setSuppressMissingSuppressesZero(boolean suppressMissingSuppressesZero): Determines whether suppressing missing data also suppresses zeros, allowing for more refined data suppression based on specific requirements.

 

 

2. FlexibleDataGridDefinitionBuilder

As its name suggests, this builder offers more flexibility. It simplifies grid construction and introduces advanced capabilities:

• Dimension Deduction: Automatically deduces dimension names based on provided members.
• Streamlined Syntax: Supports variable arguments (varargs) for defining rows and columns, resulting in more concise and readable code.
• Exclusion Features: Allows excluding specific members or sets, making it ideal for dynamic data handling.
• Enhanced Usability: Provides more intuitive methods and supports modern scripting best practices.
• Best Use Cases:
• Large, complex cubes with many dimensions.
• Scenarios requiring dynamic member exclusions or inclusions.

 

FlexibleDataGridDefinitionBuilder: Suppression Methods

The FlexibleDataGridDefinitionBuilder class provides similar suppression capabilities, allowing for efficient data retrieval:

 

setSuppressMissingBlocks(boolean suppressMissingBlocks): Suppresses entirely missing blocks, preventing the retrieval of empty data blocks.

 

setSuppressMissingRows(boolean suppressMissingRows): Suppresses rows with missing data, ensuring only populated rows are included.

 

setSuppressMissingColumns(boolean suppressMissingColumns): Suppresses columns with missing data, including only those with actual data.

 

setSuppressInvalidRows(boolean suppressInvalidRows): Suppresses rows containing invalid data, filtering out rows that don't meet validation criteria.

 

setSuppressInvalidColumns(boolean suppressInvalidColumns): Suppresses columns with invalid data, ensuring only valid data columns are retrieved.

 

setSuppressInvalidScenarioTps(boolean suppressInvalidScenarioTps): Suppresses invalid scenario time periods, excluding data from invalid time periods.

 

setSuppressMissingRowsNative(boolean suppressMissingRowsNative): Suppresses missing rows at the native level, enhancing performance by excluding missing rows earlier in the data retrieval process.

 

setSuppressMissingSuppressesZero(boolean suppressMissingSuppressesZero): Determines whether suppressing missing data also suppresses zeros, allowing for more refined data suppression based on specific requirements.

 

Key Differences

Feature	DataGridDefinitionBuilder	FlexibleDataGridDefinitionBuilder
Dimension Handling	Requires explicit dimension names	Deduces dimensions automatically
Syntax Complexity	More verbose	Cleaner and more concise
Exclusion Support	Not supported	Allows exclusion of specific members
Usability	Requires detailed knowledge of cube	Easier to use with dynamic datasets
Use Cases	Static, simple grids	Dynamic, complex grids

 

Examples in Action

Example 1: Using DataGridDefinitionBuilder

 

Cube cube = operation.application.getCube('Plan1')

 

// Create a DataGridDefinitionBuilder

DataGridDefinitionBuilder builder = cube.dataGridDefinitionBuilder()

 

// Set the dimensions for POV, rows, and columns

builder.setPovDimensions(['Scenario', 'Version', 'Currency'])

builder.setRowDimensions(['Entity'])

builder.setColumnDimensions(['Account', 'Period'])

 

// Define the POV, rows, and columns

builder.setPov(['Actual', 'Working', 'USD'])

builder.addRow(['Total Entities'])

builder.addColumn(['Net Income', 'Jan'])

 

// Add suppression options

builder.setSuppressMissingBlocks(true)        // Exclude missing blocks

builder.setSuppressMissingRows(true)         // Exclude rows with no data

builder.setSuppressMissingColumns(true)      // Exclude columns with no data

builder.setSuppressInvalidRows(true)         // Exclude invalid rows

builder.setSuppressInvalidColumns(true)      // Exclude invalid columns

builder.setSuppressInvalidScenarioTps(true)  // Exclude invalid scenario time periods

 

// Load and process the grid

cube.loadGrid(builder.build(), false).withCloseable { grid ->

    grid.dataCellIterator().each { cell ->

        // Print the data in each cell

        println "Data: ${cell.data}"

    }

}

• Pros: Offers granular control.
• Cons: Verbose and rigid, especially for large cubes.

 

Example 2: Using FlexibleDataGridDefinitionBuilder

Cube cube = operation.application.getCube('Plan1')

 

// Create a FlexibleDataGridDefinitionBuilder

FlexibleDataGridDefinitionBuilder builder = cube.flexibleDataGridDefinitionBuilder()

 

// Set the Point of View (POV) for the grid

builder.setPov('Actual', 'USD', 'BegBalance', 'BU Version_1', 'FY22')

 

// Add columns and rows for the grid

builder.addColumn('ILvl0Descendants(Employee)')

builder.addRow('ILvl0Descendants(USA)', 'Price', 'Units Sold')

 

// Exclude specific members

builder.setExclude('Units Sold')

 

// Add suppression options

builder.setSuppressMissingBlocks(true)        // Exclude entirely missing blocks

builder.setSuppressMissingRows(true)         // Exclude rows with no data

builder.setSuppressMissingColumns(true)      // Exclude columns with no data

builder.setSuppressInvalidRows(true)         // Exclude invalid rows

builder.setSuppressInvalidColumns(true)      // Exclude invalid columns

builder.setSuppressInvalidScenarioTps(true)  // Exclude invalid scenario time periods

 

// Load and process the grid

cube.loadGrid(builder.build(), false).withCloseable { grid ->

    grid.dataCellIterator().each { cell ->

        // Print the data in each cell

        println "Data: ${cell.data}"

    }

}

 

• Pros: Simplified syntax, exclusion support.
• Cons: Slight learning curve for advanced features.

Best Practices

1. Prefer FlexibleDataGridDefinitionBuilder: Its flexibility and advanced features make it ideal for most scenarios.
2. Suppress Missing Data: Use suppression methods to exclude missing or irrelevant data blocks, improving efficiency.
3. Streamline Syntax: Leverage varargs and dimension deduction in FlexibleDataGridDefinitionBuilder for cleaner scripts.
4. Handle Errors Gracefully: Always implement robust error handling using throwVetoException() or similar methods.

Conclusion

While both DataGridDefinitionBuilder and FlexibleDataGridDefinitionBuilder serve the same foundational purpose, the latter's modern features and streamlined syntax make it a superior choice for most use cases. Whether you’re dealing with large, complex cubes or need dynamic data handling, FlexibleDataGridDefinitionBuilder is your go-to tool.

By understanding their differences and leveraging their strengths, you can master data grid definitions in Oracle EPM Cloud, streamline your workflows, and enhance your business rule capabilities.

Ready to elevate your EPM scripting game? Start exploring FlexibleDataGridDefinitionBuilder today and experience its power firsthand!