Groovy - Simple script to move data from planning cube to reporting cube..

Hello folks,

Most of you now might be aware of that Oracle has now provided a new scripting language for Business Rule - Groovy. You may go through the Link for Groovy - Hyperion

Oracle has provided a good documentation on this and today I am going to show a simple usage of Groovy script to move the data available on the data form to another cube.

Currently, the groovy is available by default only for EPBCS and PBCS + 1 module. We can configure Groovy for on-premise (though the below code is only for EPBCS / PBCS specific for now)

Below is the snippet of the same...

/*Create Reporting Data Grid*/
/*Replace RepCube with your Cube name*/
Cube driverCube = operation.application.getCube("RepCube") 
DataGridBuilder builder = driverCube.dataGridBuilder("MM/DD/YYYY")

/*Variable to add column and row to the target grid*/
def colheaders = [] as String[]
def defaultPOV = [] as String[]
List<String> rowheaders = new ArrayList<String>();
List<Double> rowdata = new ArrayList<Double>();

/*Capture current grid*/
DataGrid grid = operation.grid;

/*Hold the pov, column and row for the current grid*/
List<DataGrid.HeaderCell> povs = grid.getPov();
List<List<DataGrid.HeaderCell>> cols = grid.getColumns();
List<DataGrid.Row> rows = grid.getRows();

/*Add default POV*/
defaultPOV = [/*Comma separated values*/ ]
/*After adding the default POV un-comment the below section*/
/*builder.addPov(defaultPOV)*/

/*Add the pov from the data form*/
for (DataGrid.HeaderCell cell : povs) {
 builder.addPov(cell.getMbrName())
}

/*Add the column from the data form*/
for (List<DataGrid.HeaderCell> cells : cols) {
 colheaders = []
 for (DataGrid.HeaderCell cell : cells) {
 colheaders = colheaders + cell.getMbrName()
 }
 builder.addColumn( colheaders )
}

/*Add the row from the data form*/
for (DataGrid.Row row : rows) {
 List<DataGrid.HeaderCell> subRows = row.getHeaders();
 List<DataGrid.DataCell> subRowsData = row.getData();
 rowheaders = []
 rowdata = []
 for (DataGrid.HeaderCell cell : subRows) {
 rowheaders.add(cell.getMbrName());
 }
 for (DataGrid.DataCell cell : subRowsData) {
 rowdata.add(cell.getData())
 }
 builder.addRow(rowheaders , rowdata)
}

/*Build the data grid*/
DataGridBuilder.Status status = new DataGridBuilder.Status()
DataGrid gridDefinition = builder.build(status)

/*Print the accepted and rejected cell count*/
println("Total number of cells accepted: " +status.numAcceptedCells)
println("Total number of cells rejected: " +status.numRejectedCells)
println("First 100 rejected cells: " + status.cellsRejected)

/*Save the data grid*/
driverCube.saveGrid(gridDefinition)


Hope, the comments in the script are self explanatory.

I have largely borrowed the code from http://docs.oracle.com/cloud/latest/epm-common/GROOV/ and one of the groovy script which is available on cloud.

The above code is only for the members which are on the data form and need to move to reporting cube (any cube). One can also create a groovy rule to move other data as well (can be found in the link above)

Setup : Attached this to any form (just by changes to the cube name) and run it on SAVE.

It uses data form grid API, so the restriction of amount of data volume to be move is as same as that of  data form

Hope you all like it.  Comments are appreciated..

Cheers -☺