Copy Data from Dynamic Member to Store Member with Groovy

One of the great things about hybrid EPM Planning is that the BSO cubes can have all the dimensions configured with dynamic parents thus eliminating the need for aggregation rules. But this presented a problem the other day when we needed to use the data from a parent in a prediction. Auto predict does not allow dynamic members as the source. Luckily, a colleague shared a Groovy solution with me that makes it very easy.

Our Entity dimension hierarchy is structured by region, each with a dynamic parent (e.g. Region1, Region2, etc.). We want to use the region totals as the source for the prediction model. To do this, we need to copy each dynamic member to a store member. We created alternate members for each region.

Dynamic Member Store Member

Region1 ALT_Region1

Region2 ALT_Region2

Region3 ALT_Region3

Region4 ALT_Region4

Region5 ALT_Region5

Region6 ALT_Region6

We also needed Total Product Line data in our prediction model, so a store member ALT_Product Line was created.

The Groovy script begins with defining the application, cube, and the period range. We only want to copy January thru current month, the range can be whatever periods are needed.

Then we create a grid with the dynamic members. This is what was new to me, I had not realized I could use dynamic members in my source grid. As explained before, we have both entity and product line dynamic members with data from our prediction model.

Next is to define the mapping of dynamic to store member.

Last step is to create the grid where the data will be copied and complete the copy.

Now we have the data from dynamic parents copied to store members. Those store members can be used in our prediction model for the source.

As always, happy EPM’ng!

Many thanks to Ronnie Tafoya for her help and teaching me yet another cool thing that can be done with Groovy.

Here’s the full script:

/*RTPS:  */  

/**************************************************************************************/
/* Use groovy to copy dynamic members to stored members               
/**************************************************************************************/

 Application app = operation.application
 Cube cube = app.getCube("OEP_FS")

/*  Get list of Periods from Jan - Actual Month  */
List periodRange = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']
def actPd = operation.application.getSubstitutionVariableValue("OEP_CurMnth")
def pdNum = periodRange.findIndexOf{ name -> name =~ actPd}
def actPds = periodRange[0..pdNum]
String actPdsStr = actPds.join(", ")

println "actPdsStr: $actPdsStr \n"



/**************************************************************************************/
/* Step 1: This section creates a grid with the dynamic members 
/**************************************************************************************/

DataGridDefinitionBuilder sourceBuilder = cube.dataGridDefinitionBuilder()

sourceBuilder.setSuppressMissingRowsNative(true)

sourceBuilder.addPov([ 'Version', 'Scenario', 'Plan Element' ],
                    [  ['OEP_Working'], ['OEP_Actual'], ['OFS_Load']  ] )

sourceBuilder.addColumn(['Years'], [ ["&OEP_CurYr"] ])


sourceBuilder.addRow([ 'Period' ,'Account', 'Entity', 'Currency', 'Cost Centre', 'Product Line'],
                       [
                       [actPdsStr],
                       ['ILvl0Descendants("WF_Operating Costs")'],
                       ['Region1', 'Region2', 'Region3', 'Region4', 'Region5', 'Region6'],
                       ['ILvl0Descendants("Input Currencies")'],
                       ['ILvl0Descendants("SGA_CostCentres")'],
                       ['Total Product Line'] 
                       
                     ])
                     
DataGridDefinition sourceGridDef = sourceBuilder.build()
def targetRows = []

/**************************************************************************************/
/* Step 2: This step applies the mapping of dynamic member to store member            */
/**************************************************************************************/
cube.loadGrid(sourceGridDef, false).withCloseable { sourceGrid ->

  Map mappings = [
				/* Copy dynamic member to store member for auto predict */
					'Region1' : 'ALT_Region1',  
					'Region2' : 'ALT_Region2',
					'Region3' : 'ALT_Region3',
					'Region4' : 'ALT_Region4', 
					'Region5' : 'ALT_Region5',
					'Region6' : 'ALT_Region6',
					'Total Product Line' : 'ALT_Product Line'
				]
                

  sourceGrid.rows.each { row ->
  	List<String> rowHeaders = row.headers.collect{mappings.containsKey(it.mbrName) ? mappings[it.mbrName] : it.mbrName}
    def rowData = []
    rowData = row.data.collect{it.missing ? '#Missing' : it.data}
    targetRows << [rowHeaders, rowData]
}
}

/******************************************************************************************/
/* Step 3: This step creates the grid where the data will be copied to and copies the data                                 
/******************************************************************************************/

/* If the source grid (targetRows) contains data, continue, else go to the end of the rule without error */
if(targetRows){

DataGridBuilder targetGrid = cube.dataGridBuilder("MM/DD/YYYY") 
targetGrid.addPov( 'OEP_Working', 'Prediction Base Case', 'OFS_Load')
targetGrid.addColumn ("&OEP_CurYr")

targetRows.each {  row ->

   List listRow = (List) row
   targetGrid.addRow((List< String>) listRow[0] , (List<>) listRow[1])

}

DataGridBuilder.Status status = new DataGridBuilder.Status()


targetGrid.build(status).withCloseable { grid ->
  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 to the cube

  cube.saveGrid(grid)
}
}