There are a lot of reasons one might loop through children in a Groovy Calculation.  On my journeys with Groovy, I have run into a few roadblocks where this has been helpful.  Some of these were related to limits in PBCS.  Looping through sets of members allowed us to get around some of the limitations.

• Running Data Maps and Smart Pushes have limits on the amount of data they can push when executed from a business rule (100MB).
• Using the clear option on Data Maps and Smart Pushes has limits on the length of the string it can pass to do the ASO clear (100000 bytes).
• The Data Grid Builders have limits on the size of the data grid that can be created (500,000 cells before suppression).

All 3 of these situations create challenges and it is necessary to break large requests into smaller chunks.  An example would be running the methods on one entity at a time, no more than x products, or even splitting the accounts into separate actions.

Possibilities

Before going into the guts of how to do this, be aware that member functions in PBCS are available.  Any of the following can be used to create a list of members that can be iterated through.

• Ancestors (i)
• Children (i)
• Descendants (i)
• Children (i)
• Siblings (i)
• Parents (i)
• Level 0 Descendants

More complex logic could be developed to isolate members.  For example, all level 0 descendants of member Entity that have a UDA of IgnoreCurrencyConversion could be created.  It would require additional logic that was covered in Part 11, but very possible.

Global Process

In this example, Company was used to make the data movement small enough that both the clear and the push were under the limits stated above.  The following loops through every Company (Entity dimension) and executes a Data Map for each currency (local and USD).

// Setup the query on the metadata
Cube cube = operation.application.getCube("GP")
Dimension companyDim = operation.application.getDimension("Company", cube)
// Store the list of companies into a collection
def Companies = companyDim.getEvaluatedMembers("ILvl0Descendants(Company)", cube) as String[]

// Create a collection of the currencies 
def Currencies = ["Local","USD"] 

// Execute a Data Map on each company/currency
for (def i = 0; i < Companies.size(); i++) { 
 def sCompanyItem = '"' + Companies[i] + '"' 
 for (def iCurrency = 0; iCurrency < Currencies.size(); iCurrency++){
  operation.application.getDataMap("GP Form Push").execute(["Company":Companies[i]

On Form Save

When there are large volumes of data that are accessed, it may not be realistic to loop through all the children.  In the case of a product dimension where there are 30,000 members, the overhead of looping through 30,000 grid builds will impact the performance.  However, including all the members might push the grid size over the maximum cells allowed.  In this case, the need to iterate is necessary, but the volume of products is not static from day to day.  Explicitly defining the number of products for each loop is not realistic.  Below creates a max product count and loops through all the products in predefined chunks until all 30,000 products are executed.

def slist = [1,2,3,4,5,6,7,8,9,10]
// Define the volume of members to be included for each process
int iRunCount = 4
// Get the total number of items in the Collection
int iTotal = slist.size()
// Identify the number of loops required to process everything
double dCount = (slist.size() / iRunCount)
int iCount = (int)dCount
// Identify the number of items in the last process (would be be <= iRunCount)
int iRemainder = (slist.size() % iRunCount)

//Run through each grouping
for (i = 0; i <iCount; i++) {
 // loop through each of the members up to the grouping (iRunCount) 
 for (ii = 0; ii < iRunCount; ii++) {
  // Do the action
  // slist[i * iRunCount + ii] will give the item in the list to use as needed
  print slist[i * iRunCount + ii] + " "
 }
}

// Run through the last group up to the number in the group
 for (i = 0; i < iRemainder; i++) {
  // Do the action 
  print slist[iCount * iRunCount + i] + " "
 }

A Wrap

So, the concept is pretty simple.  Now that we have the ability to do things like this outside of typical planning functions really opens up some possibilities.  This example ran post-save, but what about pre-save?  How about changing the color of cells with certain UDAs?  What about taking other properties managed in DRM that can be pushed to Planning that would be useful?  How about spotlighting specific products for specific regions that are key success factors in profitability?

Do you have an idea?  Take one, leave one!  Share it with us.

Introduction

Before we jump in, there are a number of questions you are going to have at the conclusion of this article.  Please post comments and I will answer them, but keep in mind, this is an example.  Are there different ways to accomplish this?  You bet.  Should the data sync directly to the rFin database?  Probably not, as there are calculations in the fin database that likely need to happen.  This could be so complicated that nobody would follow it, so some liberties have been taken to simplify the explanation.  The hope is that you can take this, as it has all the pieces required, and modify, add pieces, alter others, and be able to create something that meets your needs.  This is a continuation of Part 18.  Please read that before you continue.

A Visual

Before moving into the steps of the process, the following diagram is an overview.

Step 1: Validating User Input and Executing Business Logic

Step one is Groovy validation on the input that is not relevant to the actual data movement and has been discussed in other articles.  This also runs any business logic on the data that has been changed.  The only difference between a Groovy and non-Groovy calculation is that the logic is isolated to execute on only the rows and columns that have changed.  This has also been discussed in previous articles and is not relevant to the topic of real time synchronization.

Step 2a: Isolating The Edited Rows

Isolating the edited rows to synchronize is not required, but it will significantly reduce the amount of time the data map / smart push takes.  If this step is skipped, hundreds, if not thousands, of products will be synchronized for no reason.  This will certainly create performance problems where there doesn’t need to be.  So, even though it isn’t required, it is HIGHLY recommended.  This will be discussed at a high level.  Much more detail on this topic can be read in Part 3 of this series.

Although the POV in the GP form has dimensions that aren’t in Fin, Company is.  This is the one parameter that is used all the way through this example.  Year, Version, and Scenario are all fixed.  The form is only for Budget updates.  This is not common, so replicating this would likely require some additional variables to store Scenario and Year.

// Get POV
String sCompany = operation.grid.getCellWithMembers().getMemberName("Company")
def sMaterialGroup = operation.grid.getCellWithMembers().getMemberName("Material_Group")
String sChannel = operation.grid.getCellWithMembers().getMemberName("Channel")

//Get a collection of all the products that have been edited
def lstVendors = []
operation.grid.dataCellIterator({DataCell cell -> cell.edited}).each{ 
 lstVendors.add(it.getMemberName("Vendor"))
}
//Convert the collection to a delimited string with quotes around the member names
String strVendors = """\"${lstVendors.unique().join('","')}\""""

Step 2b: Pushing The Changes To The GP Reporting Database

The next step of the process is to push the data (hopefully only the changed data) from the BSO database to the reporting database (GP to rGP in the diagram above).  This step is basically pushing the same level of data from a BSO to an ASO database for the products that have changed.  This can be done with a Smart Push or Data Map.  It can also be done with a data grid builder, but this is a simpler way to accomplish it.  A deep dive into the data grid builder is scheduled later in the series.

If you only want to further customize a push already on a form, then use a Smart Push.  Otherwise, a Data map is required.  There is a detailed explanation in Part 8 of the series and is worth reading.

Smart Push is used in this example.  I prefer to use Data Maps but it is helpful to see both.  A step later in the process will use a Data Map (push from Fin to rFin).

With this example, the Smart Push on the data form has all the appropriate overrides.  The only thing needed to be customizes is the list of products that have been changed.

// Check to see if the data map is on the form and that at least one product was updated
if(operation.grid.hasSmartPush("GP_SmartPush") && lstVendors)
 operation.grid.getSmartPush("GP_SmartPush").execute(["Vendor":strVendors,"Currency":'"USD","Local"'])

Why use a Smart Push or Data Map here?  Could you use the grid builder?  Absolutely.  Quite honestly, I don’t know which is faster, but I am going to test this in a later post and compare the performance.

Step 3 & 4: Synchronizing With Fin and rFin

This is where new methods will be introduced, and honestly, the most complicated part.  It is also the piece that completely changes the landscape and completes the circle on being able build real time reporting.  Since data is moving from a BSO to an ASO, there isn’t a pre-built solution/method to do this.  But, Groovy does open up the ability to simulate a retrieve, with a customized POV, and a submit.  At a high level, that is what these steps accomplish.  The POV from the form is used as a starting point and changed to a total vendor/channel/material group and retrieve the data from rGP (ASO so no consolidation is required), create another retrieve that is connected to the fin cube, copy the data at a total vendor/channel/material group from rGP to the fin cube grid, and submit it.

The following is Groovy Map, or Groovy Collection, that simply holds the translation between the accounts in the GP database and the accounts in the Fin database.  This is nothing proprietary to PBCS or the PBCS libraries.  If you are unfamiliar with these, explanations are easy to find by searching Google for “Groovy data maps.”

//set account map
def acctMap = ['Regular_Cases':'Regular_Cases',
 'Net_Sales':'42001', 
 'Cost_of_Sales_without_Samples':'50001',
 'Gallonage_Tax':'50015', 
 'Depletion_Allowance_Manual_Chargeback':'56010', 
 'Gain_Loss_Inv_Reval':'50010',
 'Supplier_Commitments':'56055',
 'Supplier_Spend_Non_Committed':'56300',
 'Samples':'56092',
 'GP_NDF':'56230',
 'GP_BDF':'56200',
 'GP_Contract_Amortization':'56205',
 'Sample_Adjustment':'56090'
 ]

Now, let’s start in with the methods that have not been discussed in the Groovy Series.  The remaining process simply copies the data at total channel, total material group, and total vendor, to the Fin databases to No Cost Center, which is void in GP.

If you are familiar with creating Planning Data Forms, or you use Smart View to create adhoc reports, you will understand the concepts of creating grids with Groovy.  They include the page, column, and row definitions, all which have to be defined.  Once they are defined, well, that is all there is .  The script looks a little scary, but it is basically doing the things you do every day.

This first grid is our source grid.  It will connect to the rGP (ASO) database and retrieve the data to be moved to the Fin and rFin databases.

// Create variables that will hold the connection information
Cube lookupCube = operation.application.getCube("rGP")
DataGridDefinitionBuilder builder = lookupCube.dataGridDefinitionBuilder()

// Define the POV for the grid
builder.addPov(['Years', 'Scenario', 'Currency', 'Version', 'Company','Channel','Material_Group','Source','Vendor','View'], [['&v_PlanYear'], ['OEP_Plan'], ['Local'], ['OEP_Working'], [sCompany],['Tot_Channel'],['Total_Material_Group'],['Tot_Source'],['Tot_Vendor'],['MTD']])

// Define the columns
builder.addColumn(['Period'], [ ['ILvl0Descendants("YearTotal")'] ])

// Loop through the Groovy Map for the accounts to retrieve
for ( e in acctMap ) {
 builder.addRow(['Account'], [ [e.key] ]) 
}

// Initiate the grid
DataGridDefinition gridDefinition = builder.build()

// Load the data grid from the lookup cube 
DataGrid dataGrid = lookupCube.loadGrid(gridDefinition, false) 

// Store the source POV and rows to replicate in the destination grids (rFin and Fin) 
def povmbrs = dataGrid.pov 
def rowmbrs = dataGrid.rows 
def colmbrs = dataGrid.columns

Now that the source is ready to go, creating the objects/grids that connect to the destination databases is next, which are Fin and rFin.  It builds out the POV, columns, rows, and also loops through the cells in the source grid to get the data.  Almost every line is duplicated, so don’t get confused.  The reason is that the script is creating a grid to save to each of the fin databases.  To make it easier to see this, the duplicate items are in a different color.

// Create variables that will hold the connection information
Cube finCube = operation.application.getCube("Fin")
Cube rfinCube = operation.application.getCube("rFin")
DataGridBuilder finGrid = finCube.dataGridBuilder("MM/DD/YYYY")
DataGridBuilder rfinGrid = rfinCube.dataGridBuilder("MM/DD/YYYY")

// Define the POV for the grid
finGrid.addPov('&v_PlanYear','OEP_Plan','Local','OEP_Working',sCompany,'No_Center','GP_Model')
rfinGrid.addPov('&v_PlanYear','OEP_Plan','Local','OEP_Working',sCompany,'No_Center','GP_Model','MTD')

// Get the column from the source grid and define the column headers for the grid
def colnames = colmbrs[0]*.essbaseMbrName
String scolmbrs = "'" + colnames.join("', '") + "'"

finGrid.addColumn(colmbrs[0]*.essbaseMbrName as String[])
rfinGrid.addColumn(colmbrs[0]*.essbaseMbrName as String[])

// Build the rows by looping through the rows on the source grid, converting the accounts,
// and inserting the values from rGP (source)
dataGrid.dataCellIterator('Jan').each{ it ->
  def sAcct = "${acctMap.get(it.getMemberName('Account'))}"
  def sValues = []
  List addcells = new ArrayList()
  colmbrs[0].each{cName -> 
    sValues.add(it.crossDimCell(cName.essbaseMbrName).data) 
    addcells << it.crossDimCell(cName.essbaseMbrName).data
    }
  finGrid.addRow([acctMap.get(it.getMemberName('Account'))],addcells)
  rfinGrid.addRow([acctMap.get(it.getMemberName('Account'))],addcells)
  }

If you noticed slightly different methods (dataGridBuilder vs DataGridDefinitionBuilder), you have a keen eye.  Later discussions will go into detail on the differences, but the reason both are used in this example is because DataGridDefinitionBuilder allows the use of functions, like ILvl0Descendants, which was used so members were not hard coded.

The argument could be made that there is no reason to push the data to rFin since later in the process it will be replicated.  I would not argue with that rational.  However, for educational purposes, the push to rFin here will include USD and Local currency.  The push later will only include USD.  So, there is some replication that could be removed in a production application.

//Create a status object to hold the status of the operations
DataGridBuilder.Status status = new DataGridBuilder.Status()
DataGridBuilder.Status rstatus = new DataGridBuilder.Status()

//Initiate the grids connected to Fin and rFin with the status object
DataGrid grid = finGrid.build(status)
DataGrid rgrid = rfinGrid.build(rstatus)

// The print lines that send information to the log are not required, 
// but showing the status is helpful in troubleshooting and monitoring
// performance
println("Total number of cells accepted: $status.numAcceptedCells")
println("Total number of cells rejected: $status.numRejectedCells")
println("First 100 rejected cells: $status.cellsRejected")

// Save/Submit the form to Fin
finCube.saveGrid(grid)

// Additional information sent to the log
println("Total number of cells accepted: $rstatus.numAcceptedCells")
println("Total number of cells rejected: $rstatus.numRejectedCells")
println("First 100 rejected cells: $rstatus.cellsRejected")

// Save/Submit the form to rFin
rfinCube.saveGrid(rgrid)

Step 5: Executing and Synchronizing Fin Logic

This is by far the simplest part of the entire process.  This piece doesn’t have to be a Groovy calculation, honestly.  In this situation, the Company can be grabbed from the form POV.  That said, I like the ability to log things from a Groovy Calculation, so I have done so in this example.  Why is currency calculated here and not in GP?  Great question.  Ah…this is just an example.  This could be replaced with any logic.

This is the simple part...execute the business rules

String sCompany = operation.grid.getCellWithMembers().getMemberName("Company")
StringBuilder essCalc = StringBuilder.newInstance()
essCalc <<"""
FIX(&v_PlanYear,"OEP_Plan",$sCompany,"No_Center","GP_Model")
 %Script(name:="FIN_Currency",application:="BreakFin",plantype:="Fin")
ENDFIX
"""

println essCalc
return essCalc

After any specific business logic is executed, the last step is to push the data to rFin.  Rather than use a Smart Push like above, this time a Data Map will be used.  My preference is to use Data Maps.  Once the parameters are understood, I think it is easier just to pass all the overrides in a generic Data Map.  Otherwise, the overrides are managed in multiple.  I certainly can’t argue performance, simplicity, or other benefits for one method over another.  It is completely a preference.

//Declare string variables to house POV members
String sCompany = '"' + operation.grid.pov.find{it.dimName =='Company'}.essbaseMbrName + '"'
//Execute datamap
operation.application.getDataMap("Fin Form Push").execute(["Company":sCompany,"Scenario":"OEP_Plan","Version":"OEP_Working","Years":"&v_BudYear","Source":"GP_Model","Currency":"USD","Account":'ILvl0Descendants("Account")',"Cost_Center":"No_Center"],true)

Performance

I have presented this concept 3 times to about 300 people.  I always get this question.

OK, you change one product and it is fast.  What happens if you change all of them?

To be completely transparent, pieces of this are not much faster, but the move from the detailed cube to the summary cube (GP to fin/rFin in this example) is lightning fast and makes no difference whether 1 or 30K products are changes.  In a real world situation, planners don’t change every line every time.

Here is a summary of what I experienced.  The first 4 are changes made at a lev0 of channel and material group.  The second 4 are done at the top of those dimensions.  The calculation of the business logic changes for obvious reasons.  The push of the changed data changes for the same reason.  It is simply a question of volume.  The synchronization to the reporting consolidated cubes is not impacted.  It doesn’t matter whether 1 or 30k products are changed because the data moving from the rGP cube is the same because it is pushing at a total.

* All times are in seconds

Conclusion

The reason I looked into Groovy was because of this example/client.  The logic on the form was relatively complicated and included allocations, then based on the changes there were spreads through the months and additional adjustments to make sure impacts to revenue were impacted properly.  The form save was taking minutes, and was unacceptable, for obvious reasons. The Smart Push was taking too long and had to be run in background, and would error due to size periodically.  That gave me the idea of the push on save to the consolidated financial cube, and voila!

This introduces some incredible functionality that can change the landscape of what we can do with PBCS.  Much of this has been discussed in previous pieces of this series, but the addition of moving consolidated data (without running a consolidation) throughout the applications is brand new.  There are some more things to the script that could be done to even speed it up a little (only moving edited accounts and months, for example).  In the near future, we will jump into this code at a more granular level and explain, line by line, what everything is doing.  We will also optimize this example further.  Look forward to that in the coming weeks.  For now, a solid example is available for you to use.

What do you think?

Introduction

One of the challenges with Hyperion Planning is the ability to move data between applications in real time.  A classic example of this is a P&L application with other modules that have greater detail.  The following is an example.

• A Gross Profit specific database that includes a product, delivery channel, and product type dimension.
• A CapEx specific database with asset type, asset, and asset category
• A Workforce specific database with job type, union, and employee.
• A P&L application that includes income and expense with information fed from the detailed models at consolidated levels.

In June of 17, with the release of Groovy Calculations, the ability to update any of the detailed models and synchronize the consolidated data in real time to the P&L database became possible.  When a user saves data, within seconds, the data can be reflected in a database with different dimensional.

Setting The Stage

This is going to be a lengthy, multi part article.  Before we begin, the application architecture is going to be laid out so the calculations can be explained in detail.  The application will consist of 2 play types.  The first is the P&L and the second is a detailed product planning play type.  We won’t introduce a Capex and Workforce model.  It will only complicate the explanation and is redundant in the logic required.

The data flow and architecture looks like this.GP (Gross Profit Product Detail) databases

The initial plan type is called GP

Although this may not match with your model, the concept is the same.

• It has dimensions that are required to plan at a product level that don’t exist in the P&L application.
• It has specific logic that doesn’t apply to other databases.
• It has a unique account dimension that doesn’t mirror what is in the other applications.
• Consolidation takes a long time and is not optimal to be performed on a data form save.

As previously stated, the same differences will exist in other models, like Capex and Workforce.

Fin (Income Statement / Balance Sheet) databases

The Fin application is a typical consolidated reporting application that excludes details like product level revenue, employee level plans, and assets and their properties needed to calculate capital expense.

Dimensional Summary

For this example, the following shows the application dimensions and database associations

The Synchronization Process

The GP database includes 3 dimensions that don’t exist in the Fin model.  For this to be moved to the Fin model, 3 dimensions need to be consolidated.  The GP model also has a different account structure.  A translation between the two account structures has to occur before the synchronization can be completed.  The other piece that is not required, but highly encouraged, is to only work with the data that has changed.  So, this will dynamically select the data rows on the form that have been edited by the user.  Functionally, the following happens when a user saves a data form.

• Identify the members that need to be included in the synchronization
• Push the level zero data from the GP BSO database to the GP ASO database (only edited data)
• Retrieve the data from the GP ASO database at a total product, channel, and material group
• Submit the data from the above retrieve to the Fin BSO application and the rFin ASO application
• Execute any logic that needs to be completed in the Fin application (taxes, driver-based data, etc.)
• Push the level zero data from the Fin BSO database to the Fin ASO database

Groovy Methods Required

There is a lot going on here, so we are going to summarize and explain the Groovy methods that will be used to accomplish the synchronization.

DataGridIterator

To make this as efficient as possible, it is important to only execute the methods on the data that have been edited.  If you haven’t read Part 3 of this series, take a look before you continue.

DataMap / SmartPush

Once the POV is identified that needs to be included in the synchronization, the first operation is to push that data to the reporting cube.  This will be used a couple of times in this sequence.  Part 8 of the Groovy Series covers this in detail and an understanding is helpful before you continue.

DataGridBuilder / DataGridDefinitionBuilder

This has not been covered yet.  These methods give you complete control to simulate a retrieve and submit.  These two objects are the major pieces of the puzzle that have never really been exposed in any fashion.  These are the methods that really open up the possibilities for real time reporting.

Take A Breath

You may be a little overloaded with new information.  We will let this settle in and give you a chance to digest the concepts.  The next article will walk you through the code.  To satisfy your curiosity, watch this video, which takes you through the above example in a live environment.

Challenge Accepted

When I asked visitors to try to come up with a situation that Groovy Calculation might be able to solve, this was a good one.  One visitor asked if they could require a cell comment if certain parameters were not met.  It is actually relatively easy.

The following requirement exist in this example.  If any month holds more than 30% of the full year, that cell requires the user to enter a cell comment.  If no comment exists, the user won’t be able to save the form.

The User Experience

If any month is more than 30% of the full year, and the user doesn’t add a comment to a cell, the form will not save.  The following shows what happens when the above fails, and what happens after the user enters a comment into the cell.

The Code

def backErrColor = 16755370 //Red
def caseTotal = 0
def accountName = ""

// Loop through the months
operation.grid.dataCellIterator('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec').each { 
  // Get a total for all 12 months every time the row changes
  if(it.getAccountName() != accountName) {
    accountName = it.getAccountName();
    caseTotal = it.data + it.crossDimCell('Feb').data + it.crossDimCell('Mar').data + it.crossDimCell('Apr').data + it.crossDimCell('May').data + it.crossDimCell('Jun').data + it.crossDimCell('Jul').data + it.crossDimCell('Aug').data + it.crossDimCell('Sep').data + it.crossDimCell('Oct').data + it.crossDimCell('Nov').data + it.crossDimCell('Dec').data 
  }
  // If the value is greater than 30% of the total and the cell does NOT have a cell comment, interrupt the form save
  if(it.data > 0 && it.data / caseTotal > 0.3 && !it.hasCellNote() ) {
    it.addValidationError(backErrColor, "Cases for a single month can't be more than 30% of the total year without an assumption.", false) 
  }
}

Conclusion

Challenge accepted.  This one goes in the win column for Groovy Calculations!

Introduction

I know you can argue this is a user issue and a training issue, but the fact is, sometimes people will save a form without editing any data.  There are at least three negative issues as a result.  One, the business rules and smart pushes execute, consuming unnecessary resources.  Two, users may think they made changes and expect changes in the results.  Three, if the processes are time consuming (like applying allocations or currency rates globally), the user will have to wait to correct the issue.  There is a very simple way to stop all the processes from executing and inform the user that they haven’t made any changes.

The Code

The following will provide a template to use to accomplish the interruption of the form save.  The messageBundle can be altered to be whatever is required and in as many languages as needed.  If this is new to you, read Part 13 of this series.

//Setup Message Bundle
def mbUs = messageBundle( ["validation.NoDataChanged":"No data was altered so no business logic was executed."])
def mbl = messageBundleLoader(["en" : mbUs])

//Create the grid and iterator objects
DataGrid curgrid = operation.getGrid()
GridIterator itr = curgrid.getDataCellIterator(PredicateUtils.invokerPredicate("isEdited"))
// Throw an exception if no cells are edited
if(!curgrid.empty && !itr.hasNext() ){throwVetoException(mbl, "validation.NoDataChanged")}

A second option would be to count the cells that have been edited.  One reason to show this, as it is not my first choice, is that it could be altered for another purpose.  If the number of cells edited is required, this could be used to count them.

def iCount = 0 

//Setup Message Bundle 
def mbUs = messageBundle( ["validation.NoDataChanged":"No data was altered so no business logic was executed."])
def mbl = messageBundleLoader(["en" : mbUs]) 

//Iterate through the edited cells
operation.grid.dataCellIterator({DataCell cell -> cell.edited}).each { iCount +=1 }

// Throw an exception if no cells are edited
if(iCount == 0) { throwVetoException(mbl, "validation.NoDataChanged") }

Implementing On A Form

Once the code is save as a business rule, add it to any form and run it BEFORE SAVE.  This example assumes the code above is saved as GP – IsEdited.

As long as it is set to run before save and there are no cells on the form that have been altered, the user will get an error that displays whatever the message bundle represents.

Summary

This isn’t going to change your life, or make a drastic improvement for your users.  But, it is a simple thing that is easy to implement that will add some polish to your application.  If you have found other ways to use Groovy Calculations to add some polish to your application, please share with a comment.

Introduction

One of the huge benefits that available in Groovy Calculations is the ability to interact with a user, validate data, and act on the validation.  Now, we can interrupt form saves, stop Run Time Prompts from continuing, and communicate information back to the user.

This may sound repetitive if you have read part 13 and part 14, and you can skip to the code example to learn more about run time prompt validation.  If not, you must have an understanding of the validation functions and the components of the messageBundle.There are a number of functions for validation, and they can be categorized functionally. Although they all can be use somewhat interchangeably, the logical uses are

• Data Form validation functions
  • addValidationError
• RTP validation functions
  • validateRtp
• Validation functions that are more open ended and can be used just about anywhere
  • messageBundle
  • messageBundleLoader
  • throwVetoException

In this post, we will discuss one aspect of this, and probably the simplest application, validating Run Time Prompts (RTP).

The MessageBundle

Before a few of the methods can be used, one must first understand the MessageBundle and MessageBundleLoader methods.  To look at documentation, they might seem very complex, and a maybe a little intimidating.  The reality is that these are just objects that hold the error messages.  That is pretty much the long of short of it.  The messageBundle holds a map (basically a lookup table that is two columns and n rows) of the error ID and the description of the error you want to display.  If the application is consumed by users with multiple languages, a messageBundle can be created for each language.  The messageBundleLoader allows you to identify which bundle to use based on the user’s local.  The example below should answer any questions you have.

The Message Bundle

Think of this method as an array, or a table in Excel.  It has 2 columns (ID and message).  It can have an infinite amount of rows.  The syntax of this is “[id,message]”.  For multiple errors, this is duplicated, separated by a comma, like “[id,message],[id,message]”.  Here is an example of a messageBundle with one error.

def mbUs = messageBundle( ["validation.InvalidCharacters":"Only alphanumeric characters can be entered (a-z, 1-9)."] )

And with two errors.

def mbUs = messageBundle( ["validation.InvalidCharacters":"Only alphanumeric characters can be entered (a-z, 1-9)."],
["validation.Negative":"A positive number is required."])

And with two errors in Spanish.

def mbSpanish = messageBundle( ["validation.InvalidCharacters":"Sólo se pueden introducir caracteres alfanuméricos (a-z, 1-9)."],
["validation.Negative":"Se requiere un número positivo."])

This can be extended to hold all the error messages required for the scope of the calculation.

The Message Bundle Loader

The messageBundleLoader is the piece that pulls either a single, or multiple, messageBundles together to use in a call.  If only one language is required, it would look like this.

[def mbl = messageBundleLoader(["en" : mbUs])

For multiple languages, it would include multiple messageBundles

[def mbl = messageBundleLoader(["en" : mbUs],["en" : mbSpanish])

Validate The Input

When a validation error exists, the prompt window will not close, so it won’t let a user continue unless all the data entered validates.  Validations are only limited to your knowledge of how to validate the input.  Let Google be your friend.  You will be hard pressed to have a sitiation where you can’t find an example of what you are trying to do.  If you aren’t familiar with “regex,” it will likely be included in just about any Google search you do.  The examples below all use a regex string to validate the inputs.

To use a run time prompt in Groovy, they must be initiated.  This looks like a comment, but it acts differently when prefaced by RTPS:

/*RTPS: {EmployeeName} {EmployeePhone} {EmployeeEmail} */

Next, we will create a messageBundle.  Although it is simplier than above, it is more than enough to demonstrate its use in the validateRtp method.  This creates an error for each of the three validations in English.

def mbUs = messageBundle(["validation.invalidemail":"Email address is invalid: {0}", "validation.invalidphone":"Phone number is invalid: {0}", "validation.invalidnamelength":"Employee name must be 5 to 40 characters: {0}"]) 
def mbl = messageBundleLoader(["en" : mbUs])

Now, the actionable stuff.  The next 3 lines will validate the 3 run time prompts.  If any of them fail, the RTP window will remain open and the user can’t continue until they fix the errors or cancel the action.

// Validate the Rtp values
validateRtp(rtps.EmployeeName, {(5..40).contains(it.enteredValue.size()) }, mbl, "validation.invalidnamelength", rtps.EmployeeName)
validateRtp(rtps.EmployeeEmail, /^.+@.+/, mbl, "validation.invalidemail", rtps.EmployeeEmail.enteredValue)
validateRtp(rtps.EmployeePhone, /^(?:\+?1[- ]?)?\(?([0-9]{3})\)?[- ]?([0-9]{3})[- ]?([0-9]{4})$/, mbl, "validation.invalidphone", rtps.EmployeePhone)

Putting it all together, we have the following.

/*RTPS: {EmployeeName} {EmployeePhone} {EmployeeEmail} {Scenario} {Year} {Period} {Entity} {Version}*/
def mbUs = messageBundle(["validation.invalidemail":"Email address is invalid: {0}", "validation.invalidphone":"Phone number is invalid: {0}",
"validation.memberexists":"The member you have specified already exists and cannot be created: {0}.", "validation.invalidnamelength":"Employee name must be 5 to 40 characters: {0}"])
def mbl = messageBundleLoader(["en" : mbUs])

// Validate the Rtp values
validateRtp(rtps.EmployeeName, {(5..40).contains(it.enteredValue.size()) }, mbl, "validation.invalidnamelength", rtps.EmployeeName)
validateRtp(rtps.EmployeeEmail, /^.+@.+/, mbl, "validation.invalidemail", rtps.EmployeeEmail.enteredValue)
validateRtp(rtps.EmployeePhone, /^(?:\+?1[- ]?)?\(?([0-9]{3})\)?[- ]?([0-9]{3})[- ]?([0-9]{4})$/, mbl, "validation.invalidphone", rtps.EmployeePhone)

Introduction

One of the huge benefits that available in Groovy Calculations is the ability to interact with a user, validate data, and act on the validation.  Now, we can interrupt form saves, stop Run Time Prompts from continuing, and communicate information back to the user.There are a number of functions for validation, and they can be categorized functionally. Although they all can be use somewhat interchangeably, the logical uses are

• Data Form validation functions
  • addValidationError
• RTP validation functions
  • validateRtp
• Validation functions that are more open ended and can be used just about anywhere
  • messageBundle
  • messageBundleLoader
  • throwVetoException

In this post, we will discuss one aspect of this, and probably the simplest application, validating Run Time Prompts (RTP).

The MessageBundle

Before a few of the methods can be used, one must first understand the MessageBundle and MessageBundleLoader methods.  To look at documentation, they might seem very complex, and a maybe a little intimidating.  The reality is that these are just objects that hold the error messages.  That is pretty much the long of short of it.  The messageBundle holds a map (basically a lookup table that is two columns and n rows) of the error ID and the description of the error you want to display.  If the application is consumed by users with multiple languages, a messageBundle can be created for each language.  The messageBundleLoader allows you to identify which bundle to use based on the user’s local.  The example below should answer any questions you have.

The Message Bundle

Think of this method as an array, or a table in Excel.  It has 2 columns (ID and message).  It can have an infinite amount of rows.  The syntax of this is “[id:message]”.  For multiple errors, the id:message is duplicated, separated by a comma, like “[id,message,id:message]”.  Here is an example of a messageBundle with one error.

def mbUs = messageBundle( ["validation.InvalidCharacters":"Only alphanumeric characters can be entered (a-z, 1-9)."] )

And with two errors.

def mbUs = messageBundle( ["validation.InvalidCharacters":"Only alphanumeric characters can be entered (a-z, 1-9).",
"validation.Negative":"A positive number is required."])

And with two errors in Spanish.

def mbSpanish = messageBundle( ["validation.InvalidCharacters":"Sólo se pueden introducir caracteres alfanuméricos (a-z, 1-9)."],
["validation.Negative":"Se requiere un número positivo."])

This can be extended to hold all the error messages required for the scope of the calculation in all the locales required.

The Message Bundle Loader

The messageBundleLoader is the piece that pulls either a single, or multiple, messageBundles together to use in a call.  If only one language is required, it would look like this.

def mbl = messageBundleLoader(["en":mbUs])

For multiple languages, or multiple messageBundles, they would be concatenated together with commas.  View a valid list of locales to make sure the parameter in parenthesis is correctly linked to the correct locale.

def mbl = messageBundleLoader(["en":mbUs", "es":mbSpanish])

Throw an Exception (Interrupt Form Save)

Here is where the cool stuff happens.  see post about looping through cells

If a validation error exists, an exception can be generated to stop the form from saving.  To do this, simply use the throwVetoException method.  This accepts 2 parameters.  The first is the messageBundlerLoader, and the second is the id associated to the to be displayed.  Using the example above, and assuming the local is US, the following would stop the form from saving and display a message of  “Only alphanumeric characters can be entered (a-z, 1-9).”

throwVetoException(mbl, "validation.InvalidCharacters")

Consolidated Example

The following example creates two error messages in two languages.  On form save, this will loop through all the cells and throw an error if any value is negative.

def mbUs = messageBundle( ["validation.InvalidCharacters":"Only alphanumeric characters can be entered (a-z, 1-9).",
"validation.Negative":"A positive number is required."])

def mbSpanish = messageBundle( ["validation.InvalidCharacters":"Sólo se pueden introducir caracteres alfanuméricos (a-z, 1-9).",
"validation.Negative":"Se requiere un número positivo."])

def mbl = messageBundleLoader(["en" : mbUs,"es" : mbSpanish])

operation.grid.dataCellIterator.each {  
  if(it.data < 0)  
    throwVetoException(mbl, "validation.Negative")
  }

Introduction

For people that are new to Groovy/Java, testing functions that Groovy provides can be a tedious and time consuming process.  Learning anything is.  Trying to do this with the wrong tools compounds it.  I have seen some people give up and walk away from trying to improve applications because they struggle with the Groovy Calculations and the complexity it introduces to go beyond some of the basics, just because they are using a hammer when they need a screwdriver.  For example, it is simple to use a documented example and loop through the cells on a form, but to utilize the Groovy/Java objects and methods is the difference between using the default logic and taking Planning to a whole new level.  For those of us who are learning, testing simple functions can be very painful inside a Groovy Calculation.

I will by preface saying I am not a Groovy developer.  I am learning as I need functionality and I am trying to build a foundation to be as productive as possible.  Although Groovy in PBCS doesn’t give developers full access to all the Java libraries, much of the logic that is needed to develop new functionality can be tested outside of PBCS.  I have found that as I learn more and require more non PBCS related functionality, it is easier to test in the Groovy Console rather than in a PBCS calculation.  Some examples are

• string functions like replace, regex, concatenate
• mathematical functions
• other manipulation that require the use of collections and hash tables

These can be used in looping through grid cells or building evaluation rules on data entered.  Hopefully, this is helpful to those learning Groovy.

How To Get Started

Download Java SDK

Before Groovy can be used, Java has to be installed.  Most systems already have it.  If not, the Java Development Kit can be downloaded and installed.  There is information about which version of Groovy and Java are compatible at groovy-lang.org.  The Java SDK can be downloaded from Oracle.

Download An Editor

Groovy can be edited in many free and paid programs.  Some of them are more robust than others and provide things like automatic code completion, color coding, and more advanced features that aren’t likely required at this novice level.  They also increase the complexity for those that are completely new to writing JAVA or Groovy.  If you are interested in this or need a longer term solution, check out these editors.

• IntelliJ IDEA
• Netbeans IDE
• SlickEdit
• Groovy/Grails Tool Suite

For those who want to just get started with a simple and supported editor to test some basic code, try the Apache Groovy Console.  The Windows Installer, the documentation, and the SDK are available to download and install.  Once installed, you are ready to go!  If you go to your Start menu in Windows, you will see a folder for the version of Groovy installed.  In that folder click on Start GroovyConsole to open the editor.

Using The Groovy Console

Much of what is done in the Groovy calculations can’t be accessed here.  We don’t have grids, cells, or any of the PBCS  methods that we interact with in a Groovy Calculation.  Groovy can also access the REST API (outside of Groovy Calculations), which opens up the ability to manage PBCS like EPM Automate.  I recently looped through the product catalogue at BestBuy.com and built a hierarchy!  This is a whole other beast, but it is worth mentioning.

Before we jump into testing a script, here are a few things that will be helpful using the Groovy Console.

• The editor has two panes.  The top pane is where the script is developed and edited.  The bottom pane is where the results of the script are displayed when it is executed.
• The toolbar has some common functions.  You can open and save your scripts, redo/undo, and execute from icons in this area.

Examples

I find it very helpful as I am learning, to test the logic and the results in this console.  Once validated, it will be moved to the PBCS calculation and used appropriately.  Here are some examples where it might be useful, and hopefully the separation of where to test what is highlighted.

Regex Example

There was a requirement on a form at a recent client where they wanted to accept input.  They used this to setup properties in the HR system.  The HR system could not accept some characters, so the ask was to only allow alphanumeric characters, a space, an underscore, and a dash.  We had to add validation to the run time prompt, as well as when the data was updated in a form.  Not being an expert with regex, I didn’t want to test this in a calculation (update calc, run calc, open job console, expand status, toggle between windows, etc).

So, I opened the Groovy Console and tested there.  The end result is below, but it was much easier to tweak the regex syntax directly in the console, running it, and seeing the result immediately, in one step.  This was easy to see and verify the output was void of any characters that were not allowed.  The length could be compared, pre and post character removal, and was used to stop the save of the data.

String text = "This - text ! has \\ /allot # of % special % characters"
println text
println text.length()
println text.replaceAll("[^a-zA-Z0-9 _-]", "")
println text.replaceAll("[^a-zA-Z0-9 _-]", "").toString().length()
println text.length() == text.replaceAll("[^a-zA-Z0-9 _-]", "").toString().length()

At this point, I proved out the regex functionality.  I can now go back to the Groovy Calculation and use this logic on the variable returned from the PBCS function (whether it be an RTP or a cell value) and remove the invalid characters or test to see if there are any, and act accordingly.  This is what it would look like

String enteredValue = rtps.RTP_NewEmployee.getEnteredValue();
if(enteredValueAdj.length() == enteredValue.length())
{ 
def mbUs = messageBundle(["validation.InvalidChars":"You have entered invalid characters.  Only alphanumeric characters, spaces, dashes, and underscores are accepted."])
def mbl = messageBundleLoader(["en" : mbUs])
throwVetoException(mbl, "validation.InvalidChars", rtps.RTP_NewEmployee)
}

Converting Nested Collections

I was building a Data Map override from a POV, and it wasn’t validating because some of the variables were collections that included a nested collection.  This whole concept was completely new to me, and again, I didn’t want to have to go through 3-5 steps to see if the result was returning a delimited list of members that the Data Map would accept.  Since I had no initial idea how to accomplish this, I searched and found examples that might accomplish what I wanted to achieve.  It took 5 to 10 iterations of examples to get to what I wanted and understand how this worked.  Updating a script in the Groovy Console, running it, and seeing the results in the same window proved much quicker to find a solution.

In the solution below, I created a variable that replicated the variable that PBCS that was returning (a list).  I was able to build out a few lines to eliminate the nested collections and ported this over to my Groovy Calculation.

This proved out that the simple loop below would give me a list I could pass to the Data Map, and was much quicker to solve than trying to do this in PBCS.

def orig_list = [10, 20, [1, 2, [25, 50]], ['Groovy']]
def usable_list = []

orig_list.collectNested([]) { 
 usable_list << it
 } 
println usable_list 
println '"' + usable_list.join('","') + '"'

The result of the executed script created two lines.  At this point, I could use this function in the Groovy Calculation by replacing the orig_list with the object returned from the PBCS function.  I used the usable_list in the Data Map.

[10, 20, 1, 2, 25, 50, Groovy]
“10”,”20″,”1″,”2″,”25″,”50″,”Groovy”

Wrapping Up

These examples are great examples of how we can use a pair of tools to create business logic efficiently.  If you are a seasoned java developer, much of this might seem ridiculous to you and question why one would ever use something outside of PBCS.  I get it.  Now that I know how these two function work, I likely will not use the Groovy Console to write and test this.  But, as I continue to learn more and more, being able to do this in something outside of PBCS has proven invaluable, increased my productivity, and significantly reduced my frustration.

If you are learning, or are an experienced Groovy developer, please share your insights with the community and post a comment!

The GroovyDynamicLocalizedException Groovy method should return the message in the user selected local (language) from a messageBundle.  This can be used to share the bundles in other situations.  Currently, when used, it returns a validation error.

A validation error was received from the Planning server.

‘Error:The Groovy script failed to compile with internal error: Compile Error: [Static type checking] – Cannot find matching method Script29#GroovyDynamicLocalizedException(oracle.epm.api.GroovyResourceBundleLoader, java.lang.String). Please check if the declared type is right and if the method exists.

Oracle has created in internal bug to this issue.

Bug 27656951 – EPBCS – GROOVY FUNCTION ERRORING

An example of the use of this function that produces this error is below.

def mbUs = messageBundle(["validation.InvalidVendorDesc":"The Product name cannot exceed 20 characters in length. The description that you entered is characters too long.", "validation.InvalidCharacter":"You cannot use commas [ , ] or pipes [ | ] in Product names"])
def mbl = messageBundleLoader(["en" : mbUs])
println GroovyDynamicLocalizedException(mbl,"validation.InvalidVendorDesc").getMessage()

There is currently no time table on a resolution.

Introduction

Groovy opens up a lot of things above and beyond performance improvements and improving the user experience.  One example is the possibility to interact with the metadata.  Dimensions and members can be queried for all types of things which can be useful in many situations.  Is the POV at a level 0?  What is the parent of the current POV member?  Does the member exist in another application?  What about pushing data for specific UDAs and dynamically generating the Data Map?  How about dynamically generating the Data Map to ignore dynamic calculated members?  These are just some examples to get you thinking about where this could be useful.

Code Example

This article won’t get into the logic to accomplish the above examples once the property is identified but will explain how to extract properties for its use.  Below is an example of retrieving every property of an account named Regular_Cases.  This iterates through every metadata property and writes it to the log.

// Get the dimension of the member in question
Dimension AccountDim = operation.application.getDimension("Account")
// Get the member 
Member AccountMbr = AccountDim.getMember("Regular_Cases")
// Print the map to the log
println AccountMbr.toMap()
def memberProps = AccountMbr.toMap()
// Print the member name
println AccountMbr.toString()
// Print every property and corresponding property value
for ( e in memberProps ) {
  println "${e.key} = ${e.value}"
}

When this is executed, the following is sent to the log.

println AccountMbr.toMap() produces

{Formula (rGP)=<none>, Plan Type (GP)=true, Solve Order (rGP)=0, Formula (Fin)=<none>, Data Storage (OEP_WFSC)=never share, Time Balance=flow, Formula=<none>, UDA=HSP_NOLINK, Skip Value=none, Variance Reporting=non-expense, Data Storage (GP)=never share, Essbase Name=Regular_Cases, UUID=c842d186-6d83-4b90-8d1e-49474a6a8a1d, Member=Regular_Cases, Data Storage=never share, Data Storage (rFin)=never share, Formula (rFin)=<none>, Aggregation (rWFP)=+, Formula (GP)=<none>, Data Storage (rWFP)=never share, Data Storage (OEP_REP)=never share, Data Storage (rGP)=never share, Data Type=currency, Formula (OEP_WFP)=<none>, Plan Type (rFin)=true, Aggregation (OEP_WFP)=+, Data Storage (OEP_WFP)=never share, Parent=GP_Accts, Two Pass Calculation=false, Aggregation (GP)=+, Plan Type (rGP)=true, Process Management Enabled=true, Plan Type (rWFP)=false, Source Plan Type=GP, Aggregation (OEP_WFSC)=+, Exchange Rate Type=none, Plan Type (Fin)=true, Alias: English=Regular Cases, Plan Type (OEP_WFP)=false, Aggregation (OEP_REP)=+, Solve Order (rWFP)=0, Data Storage (Fin)=never share, Hierarchy Type=dynamic, Allow Upper Level Entity Input=false, Account Type=revenue, Formula (OEP_REP)=<none>, Aggregation (Fin)=+, Aggregation (rGP)=+, Plan Type (OEP_WFSC)=false, Formula (rWFP)=<none>, Formula Description=<none>, Aggregation (rFin)=+, Solve Order (rFin)=0, Formula (OEP_WFSC)=<none>, Solve Order (OEP_REP)=0, Valid For Consolidations=false, Plan Type (OEP_REP)=false}

for ( e in memberProps ) {println “${e.key} = ${e.value}”} produces

Regular_Cases
Formula (rGP) = <none>
Plan Type (GP) = true
Solve Order (rGP) = 0
Formula (Fin) = <none>
Data Storage (OEP_WFSC) = never share
Time Balance = flow
Formula = <none>
UDA = HSP_NOLINK
Skip Value = none
Variance Reporting = non-expense
Data Storage (GP) = never share
Essbase Name = Regular_Cases
UUID = c842d186-6d83-4b90-8d1e-49474a6a8a1d
Member = Regular_Cases
Data Storage = never share
Data Storage (rFin) = never share
Formula (rFin) = <none>
Aggregation (rWFP) = +
Formula (GP) = <none>
Data Storage (rWFP) = never share
Data Storage (OEP_REP) = never share
Data Storage (rGP) = never share
Data Type = currency
Formula (OEP_WFP) = <none>
Plan Type (rFin) = true
Aggregation (OEP_WFP) = +
Data Storage (OEP_WFP) = never share
Parent = GP_Accts
Two Pass Calculation = false
Aggregation (GP) = +
Plan Type (rGP) = true
Process Management Enabled = true
Plan Type (rWFP) = false
Source Plan Type = GP
Aggregation (OEP_WFSC) = +
Exchange Rate Type = none
Plan Type (Fin) = true
Alias: English = Regular Cases
Plan Type (OEP_WFP) = false
Aggregation (OEP_REP) = +
Solve Order (rWFP) = 0
Data Storage (Fin) = never share
Hierarchy Type = dynamic
Allow Upper Level Entity Input = false
Account Type = revenue
Formula (OEP_REP) = <none>
Aggregation (Fin) = +
Aggregation (rGP) = +
Plan Type (OEP_WFSC) = false
Formula (rWFP) = <none>
Formula Description = <none>
Aggregation (rFin) = +
Solve Order (rFin) = 0
Formula (OEP_WFSC) = <none>
Solve Order (OEP_REP) = 0
Valid For Consolidations = false
Plan Type (OEP_REP) = false
Data Storage (GP) = never share

Getting A Specific Property

Typically, there would not be a need to pull every property.  There might be times when having access to these, however, is useful in calculations.  If a currency calculation is being executed, for example, the rate applied is different if the member is a balance sheet account.  Getting one value can be retrieved by building on the above script.

def keyProp = "Account Type"
if(memberProps[keyProp] = "Revenue"
  {do something}
elseif(memberProps[keyProp] = "Balance Sheet"
  {do something}

Wrap Up

This may seem a little worthless at first, but if you think about all the BSO functions (getting UDAs, Account types for VAR functions, and member relation functions) that require this information, mimicking them in Groovy requires access to the metadata properties.  So, don’t underestimate its use for things like variance, currency, and other calculations, that are done outside of Essbase/Planning calculations and member formulas.