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)
}
}



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s