EPM on-the-go

Sharing my experience in Enterprise Performance Management (EPM) with fellow EPM consultants

By

Bosco Lam

Simple Groovy Part 5 – Cross-Cube Grids

So far everything has happened inside one cube. Part 3 read from it, Part 4 wrote back to it. But planning models are rarely one cube so how to move data from one cube to another?

You have a lot of options: Data Maps, Data Integration, xwrite… but what about creating two grids using groovy?

Imagine we want to bring Headcount data from the Workforce cube into Financials.

A cross-cube data movement is never a straight copy. You have to pick the specific member or parent member of the dimensions the target does not have.

For example, the Employee and Job dimensions will be read at the parent-member level, while the remaining dimensions will stay the same. In this example, we will map the Total members to the default members.

Let’s take a look at the code.

//Declare the cubes - wfp is the source and fs is the target
Cube wfp = operation.application.getCube("WFP")
Cube fs  = operation.application.getCube("FS")
//Read lvl0 headcount out of WFP
def src = wfp.flexibleDataGridDefinitionBuilder()
src.setSuppressMissingBlocks(true)
src.setSuppressMissingRows(true)
src.setPovDimensions('Scenario','Version','Years','Employee','Job','Centre')
src.setPov('Forecast','Working','FY27','Total Employees','Total Jobs','Total Center')
src.setColumnDimensions('Period')
src.addColumn('ILvl0Descendants("YearTotal")')
src.setRowDimensions('Entity','Account')
src.addRow('ILvl0Descendants("Total Entity")','ILvl0Descendants("Headcount")')

Now the write builder. Same as Part 4, except the POV is carrying four dimensions that did not exist on the source side.

//Write into FS
DataGridBuilder tgt = fs.dataGridBuilder("MM/DD/YYYY")
tgt.addPov('Forecast','Working','FY27','No Plan Element','No Adjustment',
           'No Intercompany','No Centre','No Currency')
tgt.addColumn('ILvl0Descendants("YearTotal")')

Then read the source grid and feed each row into the target.

int rows = 0
wfp.loadGrid(src.build(), false).withCloseable { grid ->
    grid.rows.each { row ->
        //Read the header & value
        def header  = row.headers*.essbaseMbrName
        def value = row.data.collect { cell -> cell.missing ? 0.0d : cell.data }
        //Generate each row with the header & value
        tgt.addRow(header, value)
        rows++
    }
}

Lastly, build the status and save the grid.

DataGridBuilder.Status status = new DataGridBuilder.Status()
tgt.build(status).withCloseable { DataGrid grid ->
    println "Accepted : ${status.numAcceptedCells}"
    println "Rejected : ${status.numRejectedCells}"
    if (status.numRejectedCells > 0) {
        println "First rejects: ${status.cellsRejected.take(3)}"
        throwVetoException("Push rejected ${status.numRejectedCells} cells. Nothing was saved")
    }
    fs.saveGrid(grid)
    println "Pushed ${rows} rows x ${months.size()} months"
}

Here is the result.

Leave a comment