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

By

Save parent-level input to the lowest level

Have you ever had a client ask, “Why can’t I just enter data at the parent level?” Even after you explain the capability and the pros & cons, your client still doesn’t get it and keeps pushing for it.

The challenge is that most parent members are Dynamic Calc, especially now that we are mostly building hybrid BSO cubes. Essbase calculates Dynamic Calc on the fly and you can’t directly type into a Dynamic Calc member.

Instead of changing the parent members to Stored and use Standard Target version, you can use two Groovy rules to save the number to the lowest member under it.

One forces the cell to be editable, and the other finds the lowest-level member and saves to it.

Force editable

Very simple – you just need to use setForceEditable(true).

//The form only has parent-level rows so force editable on all cells
operation.grid.dataCellIterator().each { DataCell cell ->
cell.setForceEditable(true)
}

This opens every cell on the form, so build the form with parent-level rows only.

If it also has read-only cells you want to keep locked, like YearTotal or a closed month, add a check before setForceEditable by using an IF statement.

setForceEditable only opens the cell on the form. The parent member is still Dynamic Calc, so whatever the user types there doesn’t get stored. This is where the second rule comes in.

Find and save to the lowest member

//Find the first level 0 member under a member. A level 0 member returns itself
String getFirstLevel0(Dimension dim, Cube cube, String sMbr) {
List<String> lstLeaf = dim.getEvaluatedMembers('Lvl0Descendants("' + sMbr + '")', cube)*.name
return lstLeaf.isEmpty() ? sMbr : lstLeaf[0]
}
//Define Cube
Cube cube = operation.application.getCube("Plan1")
Dimension dimProduct = operation.application.getDimension("Product", cube)
DataGridBuilder builder = cube.dataGridBuilder("MM/DD/YYYY")
//Same POV and columns as the form
List<String> lstPov = operation.grid.pov*.essbaseMbrName
builder.addPov(lstPov.toArray(new String[0]))
List<String> lstCol = operation.grid.columns[0]*.essbaseMbrName
builder.addColumn(lstCol.toArray(new String[0]))
//Rows on this form are Entity and Product. Change Product to its first lvl 0 member
operation.grid.rows.each { DataGrid.Row row ->
//Only rows the user changed
if (!row.data.any { DataGrid.DataCell cell -> cell.edited }) {
return
}
String sEntity = row.headers[0].mbrName
String sProduct = getFirstLevel0(dimProduct, cube, row.headers[1].mbrName)
List<String> lstData = row.data.collect { DataGrid.DataCell cell -> cell.missing ? "#Missing" : cell.formattedValue }
builder.addRow([sEntity, sProduct], lstData)
}
//DataGridBuilder.Status
DataGridBuilder.Status status = new DataGridBuilder.Status()
builder.build(status).withCloseable { DataGrid grid ->
if (status.numRejectedCells > 0) {
println("Total number of cells accepted: $status.numAcceptedCells")
println("Total number of cells rejected: $status.numRejectedCells")
println("First 100 rejected cells: $status.cellsRejected")
}
cube.saveGrid(grid)
}

So if a user types 50,000 at Total Hardware and 12,000 at Total Software, it gets saved to the first product under it.

After save.

getFirstLevel0 is a function we created. It asks for the level 0 descendants of the row’s member and takes the first one. If the member is already level 0, the list comes back empty, and it returns the member itself.

The rest rebuilds the form. Same POV, same columns, same rows, only Product is changed. Then saveGrid writes it to essbase.

Hopefully this is helpful to you.

Leave a Reply