Groovy Data Map for Multiple Periods and Years

Data maps are one of the great features of EPM Planning. It is really easy to move data between cubes with data maps. When attached to a form and enabled for Smart Push, we are able to just push the data from the form members. But there are limitations to Smart Push and if you have too much data, Smart Push may not work.

With Groovy, we can execute a data map and define specific members to use instead of whichever members are defined in the data map. Using a combination of run-time-prompts (RTPS) and substitution variables, the script efficiently pushes only the data needed.

Our application is configured for a 18-period rolling forecast, which means that our period and year range can span over 3 years. We need to limit the data push to only the rolling forecast periods. We also need to push for one entity and multiple cost centres.

We start the script by identifying the RTPS variables and defining the cube and dimensions.

1. Identify entity and cost centre RTPS variables.
2. Define the full period range using the member names.
3. Define the source cube. Be sure to use a cube that the substitution variables (to be defined in the next step) are assigned to.
4. Define variables for the entity and cost centre dimensions. These variables will be referenced along with the RTPS variables.

Next is to define the period and year variables based on substitution variables.

1. Creating a variable for the function to retrieve substitution variables. This is not necessary, the function could be included in each line for defining the variables, but it makes for a little cleaner coding.
2. Using the variable defined above, define variables for each substitution variable.

Now we can define the periods for each of the rolling forecast years.

1. Create a string of forecast start year, forecast year 2, and forecast end year.
2. Define how many years. The function counts unique values, so when the string of years created above is ‘FY22,FY23,FY24’, then the number of years is 3. But if the years are ‘FY23,FY24,FY24’, the number of years is only 2.
3. Define period number based on forecast start month and end month. For example, if start month is Nov, pdNum is 10. If end month is Apr, EndpdNum is 3.
4. Using the period numbers just defined, define range of periods for first forecast year, second year, and third year. IF statements determine whether 2 or 3 forecast years are used.
5. Define string values for the period variables on each year.

Next is to create strings of the members from the entity and cost centre RTPS variables.

1. Define entity variable based on the RTPS. Then get the level0 descendants of the member as a string, and finally convert into a list.
2. Same as what was done for entity is done for cost centre.

Note: Our variable for entity is for a single member, cost centre is for multiple. We could just define entity as the RTPS value and stop, but we leave the full coding so this script can be reused in situations where multiple entity members are needed.

Almost done. Last steps are to define a couple more variables and launch the data map.

1. Checking that the data map exists before continuing.
2. Defining years and periods to be used in the execution of the data map.
3. Executing the data map for the entity, cost centres, years, and periods as defined previously.
4. Println statement so we can log the results.

Now we can run it and see the results. We are prompted for the RTPS variables.

Looks like a successful run.

We can check the Job Console in Planning to view details.

1. The RTPS values are what we selected at run-time.
2. The data map shows as 3 runs. This is because the Groovy script defined a range of periods and years for each of the forecast years.

Opening the log shows details of the println.

1. Data map run the first time for 2 periods in first forecast year.
2. Data map run the second time for all periods in second forecast year.
3. Data map run the third time for 4 periods in third forecast year.

There you have it, a Groovy script to efficiently run data maps using run-time-prompts and substitution variables over multiple periods and years.

As always, happy EPM’ng!

Here’s the full script.

/* RTPS: {Entity}, {varCCs} */

/* Data Map Push*/

// Setup the query on the metadata

List periodRange = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']
Cube cube = operation.application.getCube("OEP_FS")
Dimension entityDim = operation.application.getDimension("Entity", cube)
Dimension costcentreDim = operation.application.getDimension("Cost Centre", cube)

// Get values from substitution variables

Closure subVarValue = { String subVar -> operation.application.getSubstitutionVariable(subVar).value }
String fcstStartYear = subVarValue('WF_FCSTStartYr')
String fcstYear2 = subVarValue('WF_FCSTYear2')
String fcstEndYear = subVarValue('WF_FcstEndYr')
String fcstStartMonth = subVarValue('WF_FcstMnth')
String fcstEndMonth = subVarValue('WF_RollingFCSTEnd')

// Define periods for each of Rolling Forecast years

    def ttmYr = fcstStartYear + "," + fcstYear2 + "," + fcstEndYear
    String[] yrstolst = ttmYr.split(",")
    List<String> ttmYrList = Arrays.asList(yrstolst)
	def YrNum = ttmYrList.unique(false).size()
	def pdNum = periodRange.findIndexOf{ name -> name =~ (fcstStartMonth)}
	def EndpdNum = periodRange.findIndexOf{ name -> name =~ (fcstEndMonth)}
	def FirstYrPds = periodRange[pdNum..11].join(",")
	def SecondYrPds
    if(YrNum == 2){SecondYrPds = periodRange[0..EndpdNum].join(",")}
    if(YrNum == 3){SecondYrPds = periodRange.join(",")}
    def ThirdYrPds
    if(YrNum == 2){ThirdYrPds = periodRange[0..EndpdNum].join(",")}
    if(YrNum == 3){ThirdYrPds = periodRange[0..EndpdNum].join(",")}


def String FirstYrPds1 = FirstYrPds.toString().replaceAll("\"", "")
def String SecondYrPds1 = SecondYrPds.toString().replaceAll("\"", "")
def String ThirdYrPds1 = ThirdYrPds.toString().replaceAll("\"", "")

def ttmPddm = FirstYrPds1 + "-" + SecondYrPds1 + "-" + ThirdYrPds1
	String[] pdstolstdm = ttmPddm.split("-")
	List<String> ttmPdListdm = Arrays.asList(pdstolstdm)


// Store the list of Entities into a collection

def EntityVar = rtps.Entity.enteredValue
def EntityVar2 = entityDim.getEvaluatedMembers("""ILvl0Descendants($EntityVar)""" as String, cube)
def EntityList = EntityVar2.collect{ '"' + it + '"'}
def EntityVar3 = EntityList.join(",")

// store list cost centres into a collection
def ccVar = rtps.varCCs.enteredValue
def ccVar2 = costcentreDim.getEvaluatedMembers("""ILvl0Descendants($ccVar)""" as String, cube)
def ccList = ccVar2.collect{ '"' + it + '"'}
def ccVar3 = ccList.join(",")


// Execute Data Map passing Entity, CostCentre, Periods and Year parameters
// Check to see if the application has the data map

if (operation.application.hasDataMap("WF_SGA Expenses Push to WFRpt"))
for (int i = 0; i < YrNum; ++i)
{
def fyr = ttmYrList.unique(false).get(i);
def fspd = ttmPdListdm.get(i);

operation.application.getDataMap("WF_SGA Expenses Push to WFRpt").execute("Entity":EntityVar3,"Cost Centre":ccVar3,"Years":fyr,"Period":fspd,true)

println("Data Map WF_OEP_FS Push to WFRpt executed for Entity: $EntityVar Cost Centre: $ccVar Periods: $fspd Year: $fyr ")
}