Groovy - Scoping down / Handling threshold issue for smartpush using Groovy and Aggregation

Hello,

This post is about smartpush threshold issue handling using Groovy smartpush calls. This post is based on our experience during the EPBCS implementation.

Initially we had the below smartpush calls using Groovy.

String Entity = 'Ilvl0Descendants(' +  rtps.vEntity + ' )'

['Local','USD'].each { String sCurrency ->
      operation.application.getDataMap("myDataMap").createSmartPush().execute(["Account":"Ilvl0Descendants(ParentAccount)", "Product":"Ilvl0Descendants(All Product)", "Scenario":rtps.Scenario, "Currency":sCurrency, "Entity":Entity, "Years":rtps.vYear,"Period":'Ilvl0Descendants(Yeartotal)', "Version":rtps.sVersion])
}

But this use to fail because of Threshold error. So we started thinking how to just push only the members which has data and include it in the push definition.

We achieved this with help of below;

1. Aggregating the data based on the user selected prompts - Entity, Scenario, Year, Version
2. Create a virtual grid in groovy using DataGridBuilder & navigation through the DataGrid for the members which has data. 

Aggregation Script:
FIX({vYear}, {vScenario}, {vVersion}, @Relative(ParentAccount,0)) /*ParentAccount is dynamic calc*/
@IDESCENDANTS(All Product);
@IDESCENDANTS({vEntity});
ENDFIX


Groovy Script:
/*RTPS: {KOProfitCenter} {vYear} {vScenario1} */
//if datamap is present in application then execute push
String sDataMap = 'myDataMap'

if(!operation.application.hasDataMap(sDataMap)) {
println 'Error : DataMap - ' + sDataMap + ' not found, Smart Push not executed...'
return
}

// String variables to capture rtps variables
String sEntity = rtps.KOProfitCenter
String sYear = rtps.vYear
String sVersion = rtps.vVersion
String sScenario = rtps.vScenario1

//String definitions of variable constants
String sEntityDef = 'Ilvl0Descendants(' + sEntity + ')'

//List declaration for capturing scoping dimension members
Set<String> accountList1 = []
Set<String> entityList = []
Set<String> prdList = []

/*Creating Virtual Grid to navigate through the members having data*/
Cube cube = operation.application.getCube("myCube")
DataGridDefinitionBuilder entityBuilder = cube.dataGridDefinitionBuilder()
entityBuilder.setSuppressMissingBlocks(true)
entityBuilder.addPov(['Years', 'Scenario', 'Currency', 'Version', 'Product'], [[sYear], [sScenario], ['LC'],  [sVersion], ['All Product']])
entityBuilder.addColumn([ 'Period','Account'], [ ['ILvl0Descendants("YearTotal")'], [ParentAccount] ])
entityBuilder.addRow(['Entity'], [[sEntityDef]])
DataGridDefinition entityGridDefinition = entityBuilder.build()

DataGrid entityDataGrid = cube.loadGrid(entityGridDefinition, true)
entityDataGrid.dataCellIterator().each { 
  if( !it.isMissing() ){ /*This will ensure that only the members with data are appended into the list*/
    entityList << it.getEntityName().toUpperCase()
  }
}

entityDataGrid.close()

DataGridDefinitionBuilder grp1AccountBuilder = cube.dataGridDefinitionBuilder()
grp1AccountBuilder.setSuppressMissingBlocks(true)
grp1AccountBuilder.addPov(['Years', 'Scenario', 'Currency', 'Version', 'Product'], [[sYear], [sScenario], ['LC'],  [sVersion], ['All Product']])
grp1AccountBuilder.addColumn(['Period','Entity'], [ ['ILvl0Descendants("YearTotal")'], [sEntity] ])
grp1AccountBuilder.addRow(['Account'], [ ['ILvl0Descendants(ParentAccount)'] ])
DataGridDefinition grp1AccountGridDefinition = grp1AccountBuilder.build()

DataGrid grp1AccountDataGrid = cube.loadGrid(grp1AccountGridDefinition, true)

grp1AccountDataGrid.dataCellIterator().each { 
  if( !it.isMissing() ){
    accountList1 << it.getAccountName().toUpperCase()
  }
}

grp1AccountDataGrid.close()

DataGridDefinitionBuilder grpPrdBuilder = cube.dataGridDefinitionBuilder()
grpPrdBuilder.setSuppressMissingBlocks(true)
grpPrdBuilder.addPov(['Years', 'Scenario', 'Currency', 'Version'], [[sYear], [sScenario], ['LC'],  [sVersion]])
grpPrdBuilder.addColumn(['Period', 'Entity'], [ ['YearTotal'], [sEntity] ])
grpPrdBuilder.addRow(['Product', 'Account'], [ ['ILvl0Descendants(All Product)'], ['TP8010 (FS_OP)', 'TP4000 (FS_OP)', 'TP3000 (FS_OP)', 'TP5000 (FS_OP)'] ])
DataGridDefinition grpPrdGridDefinition = grpPrdBuilder.build()

DataGrid grpPrdDataGrid = cube.loadGrid(grpPrdGridDefinition, true)

grpPrdDataGrid.dataCellIterator().each { 
  if( !it.isMissing() ){
prdList << it.getMemberName("Product").toUpperCase()
  }
}

grpPrdDataGrid.close()

String sGrp1Accounts  = """ "${accountList1.join('","')}" """
String sEntities = """ "${entityList.join('","')}" """
String sProds = """ "${prdList.join('","')}" """

println sYear
println sScenario
println sVersion
println sProducts
println '-----Group 1 Accounts-------'
println sGrp1Accounts
println '-----Entities-------'
println sEntities
println '-----Products-------'
println sProds
println '------------'

def smpush= operation.application.getDataMap(sDataMap).createSmartPush()
if(accountList1 && entityList){
  ['Children(Q1)','Children(Q2)','Children(Q3)','Children(Q4)'].each { String sPeriods ->
    ['Local','USD'].each { String sCurrency ->
      smpush.execute(["Account":sGrp1Accounts, "Product":sProds, "Scenario":sScenario, "Currency":sCurrency, "Entity":sEntities, "Years":sYear,"Period":sPeriods, "Version":sVersion])
      }
}
}


Hope this helps you all to solve the threshold issue in your projects.

Cheers.