Introduction

One of the huge frustrations I have with Planning is the fact that you haven’t been able to stop a user from saving data that didn’t validate since Smart View was released and Data Forms could be opened in Excel.  Prior to that, the forms could be customized with JavaScript and the form save could be interrupted and cells flagged.  Well, thanks to Groovy Calculations, it is back, and every bit as flexible.

Example

The following Data Form allows users to add new products and enter financial information.  In this form, 3 rules exist.

1. The GP Level 2 % has to be between -10% and 30%.
2. The Regular Cases CANNOT have one month that is more than 50% of the total cases.
3. If Regular Cases is entered, a corresponding Average Price per Case is required.

When a user fills out the form and violates these rules, the cell background is changed and a tool tip is added.  If violations exists, the form does NOT save any changes to the database.  Before any changes can be committed, all errors have to be corrected.  In this example, all 3 validation rules are violated and noted.  If the user hovers the mouse over the cell with a violation, the tool tip is displayed with the row and column members, and an error message explains to the user what the issue is with the data that is entered.

The Code

The significance of this is huge, but the implementation is rather simple.  It is probably be one of the more basic things created with a Groovy Calculation.  Quite simply, to add a validation error and stop the form from saving, all that has to be done is to add a validation error to the cell.

cell.addValidationError(0xFF0000, “Customer Error Message“,false)

This method accepts 3 parameters.

1. The first is the color you want the background to change to.  This is the integer value of any color.  Most people are familiar with the RGB code, and this can be retrieved in almost any image editor (even Windows Paint).  There are many free options, like the free option at https://www.shodor.org/ to convert that to a value that can be interpreted in Groovy.
2. The second parameter is the error message to be displayed in the tool tip.
3. The third is optional, and defaults to false.  False that it will identify the cell as an error and stop the form from saving.

This will likely becused in a grid iterator, which is how this example was constructed to get the screenshot above.  If the grid iterator object is foreign to  you, read Adventures in Groovy – Part 3: Acting On Edited Cells.  The one function that is void from that article is the crossDimCell method.  This acts like a cross dim (->) in a calculation.  So, it references the POV of the cell and overrides the dimension of the member specified as a parameter.  If multiple differences exist, separate the names with a comma.

def BackErrColor = 16755370 // Light Red
//Loop through the cells on the Regular Cases row
operation.grid.dataCellIterator('Regular_Cases','Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec').each { 
  // Set a variable equal to all 12 months
  def CaseTotal = it.crossDimCell('Jan').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 
  // Check the cell value to see if it is larger than 50% of the total year
  if(it.data / CaseTotal > 0.5 ) 
    it.addValidationError(BackErrColor, "Cases for a single month can't be more than 50% of the total year cases.", false) 
  // If cases are entered, make sure there is a corresponding price
  if(it.data != 0 && (it.crossDimCell("Avg_Price/Case_Inp").data == 0 || it.crossDimCell("Avg_Price/Case_Inp").data == '#Missing'))
    it.crossDimCell("Avg_Price/Case_Inp").addValidationError(BackErrColor, "A price is required when cases are entered.", false) 
}

// Loop throught the GP input cells and validate the % is in the valid range
operation.grid.dataCellIterator('GP_2_%_Inp','Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec').each { 
  println "$it.MemberNames $it.data"
  if(it.data > 0.3 || it.data < -0.1 ) {
    it.addValidationError(BackErrColor, "GP2 has to be between -10% and 30%.", false) 
 }
}

Form Settings

The one gotcha is that this needs to run BEFORE SAVE.  It makes sense, but I was expecting a similar result as validating a RTP when the Business Rule runs on save, so it took me a good night sleep to recognize that error in judgement.

Why This Is Significant

You may not thing this is a big deal because you can check this in a Business Rule after the data is saved and return an error message requesting the user to change it.  However, the users are as busy, if not more busy, than you are.  There are last minute changes that get slammed in at the end of a forecast or budget cycle.  There is no design doc to go back to and say it is going to take longer and we need a change order.  The CFO won’t accept that as an answer, so things get forgotten or missed.  This example forces valid data (not necessarily accurate) to be entered, and all kinds of things can be checked to make sure human errors don’t turn into huge issues for financial reporting.  Think if you had a product and forgot to put a price.  You could be missing millions, and this type of proactive validation can prevent such things from happening.  Little things like this reduce, or eliminate, fire drills later on in the budget cycle.

Conclusion

There is an infinite number of things that can be accomplished.  Simple things like the above, to extremely complex validation can be added.  Think about ensuring allocated dollars are fully allocated(100%), forcing salaries to be in predefined pay bands for titles, and forcing the results of driver based planning to be within a logical margin.

If you have some examples, please share with the community by posting a comment below.

Introduction

Groovy has the ability to execute Data Maps and Smart Pushes.  Data Maps are objects that define the data movement between applications and are accessible globally.  Smart Pushes are Data Maps that are attached to a Data Form and can override the dimensions settings.  They are largely the same thing, but defined separately inside of PBCS.

So, why execute these in Groovy rather than assign to a form and call it a day?

1. The data push can be customized to push only data that has changed, improving performance.
2. When a form is saved, all calculations are executed first, and the Smart pushes are executed after all calculations are finished.  If Groovy is used and the data push is done inside a Business Rule, the order of operation can be a
   1. calculation
   2. data push
   3. calculation
   4. data push
   5. etc
3. Since the Smart Push has a global limit of memory that can be used, and multiple people are running them through form saves, it is critical to make it as small as possible to reduce the probability of hitting that limit and increasing the odds of the Smart Pushes failing.

To date, I see the advantage of running a Smart Push (a Data Map on a form) in that most of the dimensions are already overridden and less overrides are required in the Groovy logic.  There is no difference in performance or the size of what can be pushed between the two when executed from a Groovy calculation.  The advantage of using a generic Data Map is that there is less effort in defining the form level Smart Pushes, and once one Groovy calculation is written to override all dimensions from a Data Map, it can be replicated easily to all required forms.

To understand the memory issues and explanation of how it differs from Data Maps and Smart Pushes, see PBCS Data Map / Smart Push Has Data volume Limits.

Data Map

Executing a Data Map is very simple and can be done in one line of code.

operation.application.getDataMap("Data Map Name").execute(true)

Calling execute() on a DataMap would execute the named Data Map (with no customization) and clearing the target location before pushing data.  Changing the true to false, or removing it, would remove the clear and leave the data as is.

To override the the dimension settings and increase or decrease the scope of what is used, the member names need to be further defined.  Every dimension’s scope can be changed, or just the ones that require a change in scope can be altered.  The following changes the scope for the account, scenario, version, and Entity dimensions.

operation.application.getDataMap("Data Map Name").execute(["Account":"Net Income, Income, Expense", "Scenario":"Budget", "Version":"Working", "Entity":"East Region"], true)

Smart Push

The Smart Push works exactly the same, except the object referenced is a Smart Push, and is obtained through the grid object, not the application.  Again, the likelihood that the Smart Push is further scoped is high, so it is reasonable that the dimensional parameters would be fewer as many of them change based on the POV selected.

operation.grid.getSmartPush("Smart Push Name").execute(["Account":"Min Bonus, Min Salary"])

One additional option is to define a Smart Push from a Data Map in the Groovy script.

operation.application.getDataMap("Data Map Name").createSmartPush().execute(["Account":"Min Bonus, Min Salary"])

Error Trapping

When these are written, it is likely that the Smart Pushes and Data Maps exist.  One extra step to ensure the calculation doesn’t fail is to verify their existence.  For Smart Pushes, verify that it is attached to the form.

//Data Map Example
if(operation.application.hasDataMap("Data Map Name"))
  operation.application.getDataMap("Data Map Name").execute(true)
//Smart Push Example
if(operation.grid.hasSmartPush("Smart Push Name"))
  operation.grid.getSmartPush("Smart Push Name").execute()

Conclusion

Creating variables to use in these functions to change the scope to only the rows and columns that have been edited, the calculation would look like this.  This is where I see the biggest bang for  your buck.  To understand more about using the grid iterator, read Adventures in Groovy Part 3: Acting On Edited Cells.  When a grid has hundreds of rows, only pushing the data that has been edited can make a huge difference in performance.

// Setup the variables to store the list of edited vendors and periods
def lstVendors = []
def lstPeriods = []
// Iterate through only the cells that were changed and create a list of changed vendors and periods
operation.grid.dataCellIterator({DataCell cell -> cell.edited}).each{ 
 lstVendors.add(it.getMemberName("Vendor"))
 lstPeriods.add(it.getMemberName("Period"))
}
// Convert the lists to a comma delimited string with quotes surrounding the members
String strVendors = """\"${lstVendors.unique().join('","')}\""""
String strPeriods = """\"${lstPeriods.unique().join('","')}\""""
// Execute the Smart Push with a change in scope to Vendor and Period
if(operation.grid.hasSmartPush("GP_SmartPush") && lstVendors)
 operation.grid.getSmartPush("GP_SmartPush").execute(["Vendor":strVendors,"Period":strPeriods]

With any luck, you will see significant improvement, unless they change every month of every row!

I have been selected to speak at the OVOAUG on February 16, 2018.  I have been there before, and it is a very nice group of people to engage with.  If you are in the area, or would like to hear more about how Groovy in PBCS can change the landscape of performance, user interaction, improvement of data input, and reduced user frustration, please go to http://ohio.communities.oaug.org/ and register.  I would love to see as many of you there as possible.

Here is the agenda.  It is going to be a functional overview, but we will touch on how to start writing Groovy, and if you show up, I will be more than happy to talk before and/or after the session.

Introduction

When developing applications in PBCS, there are times when we need to request information from users.  This is especially useful in workforce application, but certainly not limited to them.  With Groovy, we can take validation to the next level.  We can compare inputs to each other, we can limit input, and we can now stop the execution of the calculation until the inputs are validated.  The difference now is that we have the ability to keep the prompts open, and force the user to either enter valid data, or cancel the request.

Validating Hire Date / Termination Date Use Case

Let’s start with a very simple example.  Let us assume we are adding a new employee, and we are asking for a hire and termination date.  A real-life example would require more options, like a name, title, union, and other required inputs.  To simplify this tutorial, we are only entering the hire and termination dates to prove out the validation and show functionality of the Groovy RTP validation logic.

When a user enters a termination date after a hire date and launches the rule, it validates and executes the rule.

When the job is opened in the job console, we see the RTPs and the values entered, and the Job Status is selected, the log shows the values returned to Essbase.

When a user enters a termination date prior to a hire date and launches the rule, it an error is returned and the RTP window doesn’t go away.  At this point, the user has to correct the error, or cancel the execution of the business rule.

In this case, the log shows the business rule failed.

Code

There are multiple objects that are used to accomplish RTP Validation.  The code that processed the above action is the following.

/*RTPS: {RTP_HireDate} {RTP_TermDate}*/
def mbUs = messageBundle(["validation.InvalidDate":"The termination date must be after the hire date."])
def mbl = messageBundleLoader(["en" : mbUs])

// Validate the Rtp values
if(rtps.RTP_HireDate.getEssbaseValue() > rtps.RTP_TermDate.getEssbaseValue())
   throwVetoException(mbl, "validation.InvalidDate", rtps.RTP_HireDate)

// Print the results to the log
println "Hire Date: " + rtps.RTP_HireDate.getEssbaseValue()
println "Term Date: " + rtps.RTP_TermDate.getEssbaseValue()

rtps object

Creating RTPs in Groovy was covered in the previous article.  If you haven’t read that, it would be a good time to take a look, as it explains the basic of this object.  Expanding on the use of the object, we are using some additional methods.  This object has many, including returning the input as boolean, double, strings, dates, members, and smart lists, to name a few.  In this example, we are using getEssbaseValue, which returns the value sent to Essbase and stored.  If there was a need to compare date ranges, we could have used the getDate, and expanded on this with the Groovy date functions to get the days, months, or years between the entered values.  In this simple example, we just want to make sure the term date is greater than the hire date.

messageBundle

The first thing that is requires is to create a messageBundle and messageBundleLoader.  These two objects work together to hold the errors, the error messages, and multiple languages, if required.

The messageBundle is a map that holds all the errors (name and description).  In this example, we only have one error, but more can be added and separated by commas.  The messageBundleLoader holds all the messageBundle objects that represent the different languages.

throwVetoException

When an exception is found, executing this method will initiate an error and cause the RTP validations to fail.  This method requires the messageBundleLoader, and the error to be returned to the user.

Other Use Cases

By now you are probably already thinking of other uses of this.  I can see limiting growth rates, confirming combinations of RTPs (like not allowing salaried people in a union), ensuring that a new employee doesn’t have a hire date prior to the current date, and probably hundreds of other ways to use this.

If you would like to share an idea, please post a comment!

Conclusion

Being able to validate user input prior to executing a calculation and returning the input request to the user is  huge step forward, and just another benefit of Groovy calculations.  We can reduce the number of user errors and improve the user experience.

Introduction

One of the fundamental features of a Groovy calculation is the ability to dynamically grab aspects of a grid, and getting the POV is something that is required to dynamically generate an Essbase calculation.  There are times when the entire POV is required, times when only members from specific dimensions are needed, and situations where only the rows and columns of the edited cells are used to construct effective fix statements.  All 3 of these situations will be discussed.

Use Case (Pulling POV Members)

Many times, the Fix statement of a calculation being built includes all members in a data grid POV.

Code

List<String> povMemberNames = operation.grid.pov*.essbaseMbrName 
String calcScript = """   
Fix("${povMemberNames.join('", "')}")
 [Calculation]
EndFix;"""

Breaking It Down

The operation.grid.pov provides access to all the dimensions in the POV.  From that, dimension names, member names, and other properties that are useful, can be accessed.  When followed by a *, it returns all the dimensions in the POV to a list.  Using the essbaseMbrName instructs the function to return every member in the POV.  The povMemberNames variable stores a list  of all those values.

When building the calcScript variable, ${povMemberNames.join(‘”, “‘)} will return the list, delineated with “,”.  This would return something like membername1″,”membername2″,”membername3.  This obviously is missing surrounding quotes, which is why it is embedded inside the quotes.

Use Case (Pulling Selective Dimension Members From The POV)

Pulling one, or all of the dimension values in a POV, in individual variables for later use, provides the ultimate flexibility.  The following pulls all the members and stores them in a unique variable.  Then, any dimension in the POV can be accessed.  An easy way to accomplish this is to use the find method of a List object to filter the elements.

Code

List<String> povmbrs = operation.grid.pov
String curYear = povmbrs.find {it.dimName =='Years'}.essbaseMbrName
String curCompany = povmbrs.find {it.dimName =='Company'}.essbaseMbrName
Fix((${fixValues(curYear, curCompany))
 [Calculation]
EndFix;"""

Breaking It Down

The first line stores the entire POV collection.  That variable can be accessed and used like any Groovy collection.  By using the find method, items in the collection can be filtered.  povmbrs.find {it.dimName ==’Years’} will return the Years dimension object and one of the properties of that object is the essbaseMbrName.  Using it will return an Essbase friendly member name.

The “fixValues” method converts strings to “Fix friendly” strings that can be used in an Essbase calc script.  Any of the following objects can be inserted into this method.

• AttributeDimension
• AttributeMember
• Dimension
• Member
• PeriodDimension
• RtpValue
• YearDimension

The result is the dimension member name inside a fix statement.

Rows/Columns

Some of the real efficiencies with Groovy stem from the fact that the edited cells can now be determined and a Fix statement can dynamically be generated to isolate longer running calculation on only the rows and columns that have changed.  In the following example, there are two methods to get the members.

Code

Set<String> periods = []  
Set<String> products = [] 
operation.grid.dataCellIterator({DataCell cell -> cell.edited}, MemberNameType.ESSBASE_NAME).each { DataCell cell ->         
  periods << cell.periodName       
  products << cell.getMemberName("Product")     
  } 

String calcScript = """   
Fix("${periods.join('", "')}", "${products.join('", "')}")
 [Calculation]
EndFix;"""

Breaking It Down

If you are unclear about how to iterate through a grid, read Adventures in Groovy Part 3: Acting On Edited Cells.  Inside the dataCellIterator, the example assigns two list objects with the respective members.  Since this only touches the cells that have been edited, only the periods and products that have been updated will be stored in the respective variables.

You may be wondering if this is truly the most isolated combination of data.  Theoretically, the same product may not have the same edited months.  You could further condense changes in the Fix statement by looping through the rows and creating a fix for every row.  In this example, Period is dense, so doing this would not change the number of blocks.  Depending on the application, taking this extra step might prove more efficient.  Rather than complicate this further, we are assuming this isn’t required.

Every required dimension (Account, Entity, Period, Scenario, Version, and Year) has its own method. cell.periodName returns the Period member of the cell.  All the dimensions have the same naming convention.

For custom dimensions, getMemberName can be used with the required dimension passed as a parameter.  If you want consistency, this method can also retrieve the 6 required dimensions.  cell.getmemberName(“DimensionName“), where DimensionName is an actual dimension, returns the respective member in that dimension.

Just like the previous example, add the variable to the Fix statement with a join and it returns the delimited list of members that have been edited.

• “${periods.join(‘”, “‘)}” returns the list of Periods that have edited cells, and
• “${products.join(‘”, “‘)}” returns the rows (or products) that have been edited.

Wrapping Up

One last step that can be added, and should be, is the check to see if any cells have been modified by evaluating the size of one of the lists that is created during the grid iteration.  After the iteration is completed, the following can be added to accomplish this.  If there are no cells edited, the calculation is stopped at the return line and nothing is sent back to Planning/Essbase to execute.

if(products.size() == 0) {
    println("No edited cells found!")
    return
}

Joining the examples above, the fix would look like this.

String calcScript = """  
 Fix("${povMemberNames.join('", "')}", "${periods.join('", "')}", "${products.join('", "')}", ${fixValues(curYear, curCompany))
   [Calculation]
EndFix;"""
return calcScript

Conclusion

If you implement this, you will likely see huge performance improvements.  In the client production applications, I have implemented this in, I see at least a 95% improvement in performance.  This same logic can be used for Data Maps and data movements from ASO to BSO (which we will cover later)

Introduction

Accessing Substitution Variables is critical in most calculations, and accessing them in Groovy is a little more complex than it needs to be with not having an API to get them.  Since the SubstitutionVariable is not available, there are a couple ways to get them.  The precursor to this post is three-fold.

1. Read the Bug Report: Groovy SubstitutionVariable Class Not Functioning  post on Jan 8, 2018 regarding the SubstitutionVariable class availability.
2. Thanks to Abhi for providing a great alternative.
3. It may be helpful to read Adventures in Groovy Part 4: Run Time Prompts to understand how to access RTPs in a Groovy calculation.

In my bug report above, I suggested grabbing them via a hidden column or row from a form.  A reader suggested a another way to do this, and I think it is a better way to accomplish it.  Rather than grabbing the substitution variable by adding it to the form and hiding the column/row from the user, Abhi provided a much cleaner approach to working around not having access to the SubstitutionVariable class by using hidden RTPs.

Create Run Time Prompts to Access Substitution Variables

Assume the following 3 variables are required in business rules.  Create a new RTP for each.  The naming convention is irrelevant, but should be considered and be consistent for easy reference in the business rules.  In this read, I have assumed there isn’t an existing RTP with the defaults set to a substitution variable.  Even if there is, it might be beneficial to create ones specifically for this need so future changes don’t impact the values.

Name: subVar_CurMonth
Type: Member
Dimension: Period
Default Value: &v_CurMonth
RTP Text: N/A

Name: subVar_CurYear
Type: Member
Dimension: Period
Default Value: &v_CurYear
RTP Text: N/A

Name: subVar_BudYear
Type: Member
Dimension: Period
Default Value: &v_BudYear
RTP Text: N/A

Business Rule Inclusion

Inside the business rule, the following convention is required to add the variables.

/*RTPS: {subVar_CurMonth subVar_CurYear subVar_BudYear}*/

Set all the RTPs in the Variables tab to set to hidden so the user isn’t prompted for these.  Now, the substitution variables can be referenced.

def varCurMonth = rtps.subVar_CurMonth.toString()
def varCurYear = rtps.subVar_CurYear.toString()
def varBudYear = rtps.subVar_BudYear.toString()

Conclusion

Since these are likely to be used in many rules, it would be beneficial to add these to a script and embed that script into the rules that need to access these.  Any new variable that needs to be included can be added to the script, and all the business rules would then have access to them.  There are a number of ways to do this with Groovy calculations, but the simplest way is to embed it like a non Groovy business rule.  This can be dragged from the left pane, or entered manually.  The syntax is

%Script(name:="script name",application:="application Name",plantype:="plantype name"

If and when Oracle releases the class that provides direct access to sub vars, expect it to be documented here.

Introduction

Groovy provides a very easy way to interact with the user via run time prompts, or RTPs.  These can be linked to dimensions, members, and input.  One of the huge benefits of getting RTPs in Groovy is that the result can be validated, and the calculation can be cancelled if they don’t validate (we will touch on this in a future post).

The Solution

This is one of the easier things to do with a Groovy calculation.  There are two things required.  First, the Groovy calculation must prompt the user to select a value.  This is done by doing the following.

/*RTPS: {RTP_Consolidate_Data}*/

At any point in the script after the above, the value can be used.  If it is going to be used multiple times, it might be easier to set a variable.  Regardless of the approach, the value can be referenced using the rtps object as follows.

 String sRTP
 sRTP = rtps.RTP_Consolidate_Data.toString()

That is all that is required!

Conclusion

Beyond the obvious uses of an RTP, I have started using these for a number of other reasons.

• On global forms where multiple values may be changed throughout a short period of time and execute long running calculations, like allocations, I have seen benefits of prompting a user with a yes/no smartlist RTP.  If the user has more changes, they may not need to execute the calculation after every save.  This gives them the option.
• If there is a requirement where some prompts are dependent on other prompts, using RTPs in Groovy gives you the flexibility to validate the combination.  For example, if an employee is set to hourly with a VP title, the prompts can be validated and returned to the user as invalid combinations before the prompts are removed from user view.

Introduction

With the introduction of Groovy Calculations this summer, one of the things I use most, especially for applications with data forms that include a large sparse dimension in the rows with suppression on, is the option to loop through cells and identify only the POV on the cells that have changed.  In applications like workforce planning, or product level applications that have hundreds, if not thousands, of possible blocks, isolating only the changed data can have significant impacts on performance.  Normally when a data form is saved, the fix includes all level 0 members of the employee dimension and must run the calculations on all of them, regardless of whether employee changed or not.  Being able to fix on only a row, or the handful that change, give us a significant advantage in user response.

This post will go into how this is executed, and a few use cases to get you thinking about the possibilities.  All of these I have developed and are in a production application.

The Code

Using a grid iterator, with the appropriate parameter, is an extremely easy way to deploy functionality that looks through ONLY the cells that have been changed.

 operation.grid.dataCellIterator({DataCell cell -> cell.edited}).each{
  [actions]
 }

The cell object, and all its parameters, are available inside the loop.  By adding {DataCell cell -> cell.edited}, the loop is isolated to only cells that have changed.  The representative member names, the data value, if it is locked, has attachments, and many other things can be accessed with the example above.

Use Cases

An infinite number of uses are possible, as you are probably already thinking about. If not, the following will probably spark some creativity.

Customizing an Essbase Fix Statement

One of the most significant benefits of this is the ability to be able to dynamically generate a fix statement and filter what is calculated.  Although the calculation on the Essbase side isn’t improved just by using Groovy, the ability to dynamically write the calculation on only what changed is significant, especially when allocations, data pushes, and other longer running processes are required.

Assuming the rows of a data grid include the dimension Product, and Period is in the columns, the following will create variables that will include only the periods and products that have been updated.  These can be used in the string builder that is passed to Essbase.  So, rather than @RELATIVE(“Product”,0) running on all possible products, it can be replaced with “Product 1″,”Product 2”.

The following creates list and string variable for Product and Period.  Every cell that is updated will add the relative product and period to the list variables.  After all the cell values have been read, the two string variables are set to include a unique list of the dimension members, surrounded by quotes, and separated by commas, which are immediately ready to include in the FIX statement.

def lstProducts = []
def lstPeriods = []
operation.grid.dataCellIterator({DataCell cell -> cell.edited}).each{
  lstProducts.add(it.getMemberName("Product"))
  lstPeriods.add(it.getMemberName("Period"))
  }
def strProducts = '"' + lstProducts.unique().join('","') + '"'
def strPeriods = '"' + lstPeriods.unique().join('","') + '"'

The string builder would look something like this.  In the following example, the other variables are pulled from the POV.

def sScenario=povmbrs.find {it.dimName =='Scenario'}.essbaseMbrName
def sCompany=povmbrs.find {it.dimName =='Company'}.essbaseMbrName
def sYear=povmbrs.find {it.dimName =='Year'}.essbaseMbrName

StringBuilder strEssCalc = StringBuilder.newInstance()
strEssCalc <<"""FIX($sScenario,
  $sCompany,
  $sYear,
  $strProducts,
  $strPeriods
)
 Revenue = units * price;
ENDFIX
"""

At this point, the strEssCalc value can be passed to Essbase and executed.  If only 2 products are changed in 1 month, only those 2 cells would be calculated.  If this data form included 12 months and 1,000 products, the calculation would take roughly 1/500th of the time.

Customizing Smart Push

Smart Pushes on forms, depending on the POV, can exceed a threshold of what a user perceives as acceptable performance.  In the 17.11 release, Data Maps and Smart Pushes can now embedded in the Groovy Calculations.  The 2 huge benefits to this are that

1. the data that is pushed can be filtered to only the data that changes, decreasing the time of the operation, and
2. the ability control the operation order of when a push runs (for example, calculation, push, calculation, push)

If a data form has a smart push associated to it, it can be accessed and further customized.  If not, data maps can also be accessed, customized, and executed.

One thing I learned from the Oracle development team is that the Smart Pushes have a max memory that can be accessed.  One Smart Push may never hit that limit if it is isolated enough, but we found issues when multiple Smart Pushes were executed a the same time.  We were seeing multiple, and intermediate, failures in the logs.  So, it is even more critical to make these pushes as small as possible to eliminate that issue.

If we reference the example above in the customized fix, we expand on that and apply the same filter to the Smart Push.  The only addition needed is to encapsulate the strProducts variable in quotes.  If nothing is passed, it runs the smart push as it is setup in the form, so operation.grid.getSmartPush(“appname”).execute() would simply execute the same thing as if the Smart Push was set to run on save.

strProducts = """ + strProducts + """
if(operation.grid.hasSmartPush("appname"))
  operation.grid.getSmartPush("appname").execute(["Product":strProducts,"Period":strPeriods])

Validate data

Having the ability to proactively perform data validation is another great addition with Groovy.  Rather than running a calculation, and after the Data Form save returning a message telling the user that they have to change something, or changing it for them, we now can interrupt the data form save and instruct the user to change it before it has any adverse impact on the data.  The following will change the cells that violate the validation to red, add a tooltip, stop the form save, and throw an error message.  Assume we don’t want the user to enter more than 50,000 in salary.  This threshold can point to data in the application, but is hard coded below for simplicity.

operation.grid.dataCellIterator({DataCell cell -> cell.edited}).each{ 
  if(it.data > 50000){
    it.addValidationError(0xFF0000, "Salary is outside of the allowed range.")
  }
}

Conclusion

This is just a taste of what can be done.  As you can see, with the ability to isolate actions on only the dirty cells, we now have opportunities we haven’t had since pre Smart View, and functions are completely new.  The impact on performance is game changing and the ability we now have to interact with a user pre and post save is ground breaking to the possibilities.

Introduction

We all know the Data Form validation rules are serviceable, but they are not robust.  When Smart View advanced and forms were opened in Excel, the validation logic developers had in JavaScript became useless.  Since then, we have really missed the ability to communicate with the user interactively with visual cues and validation rules that halted the saving of data.  Well, Groovy calculations to the rescue!

I will preface with the fact that I am encountering some odd behavior, so I am going to break this up into multiple articles.  It appears that Oracle is validating Groovy enhancements in Data Forms on the web, and not necessarily testing the full functionality in Smart View.  Currently, I have this working in a browser perfectly, but 3 of the 8 columns are failing in Smart View.  I am hoping to get closure to a ticket on this in the near future.  When I get a resolution, I will amend this article with some clarity on either what I am doing wrong, or when it will be resolved.

High Level requirement

At a high level, the planners want to see any seeded value that was changed with a different background color to single out the lines that have been edited.

The Details

We have a form that provides the users the ability to override seeded data.  In this example, a planner can change the Average Price/Case, Net Sales, and/or GP Level 2 at any level of the hierarchy and gets allocated down to level 0 on a % to Total.  This form has the accounts in question for 3 sources.  The override columns are a separate version that is set to top down so security doesn’t prevent them from entering at a non-level 0 member.  This is only used to enter the 3 values, is used to calculate the Input source, and is cleared.

The Initialized source is seeded from prior year growth.  This, in essence, is the basline seeded amount.  At initialization, the Input source is a duplicate of Initialized source.

The Initialized source is also on the form.  When overrides are entered, it is applied to the input source.  At this point in the process, the Input is different from the Initialized source, as shown by the orange color in the previous image.

Why Not Validation Rules?

First, there is limited functionality in the Data Form validation rules.  In this case, the functionality is there, but has an issue with the precision of the data.  Even though Input equals Initialized (or appears to),  validation fails and shows a different background color.  I have seen this before with decimals with large precision.

How Groovy Solves This

Groovy calculations have the ability to traverse through the cells of a Data Form.  The 8 cells that can be impacted by the 3 overrides can be checked against their counterpart in subsequent columns (comparing the same account in the Input source to the Initialized source).  This is for another discussion, but Groovy can actually create temp grids and pull data directly from Essbase that doesn’t exist in the grid, too.

To simplify this, the following only loops through the first column – Avg Price / Case.  This can be replicated easily for all subsequent columns by changing the account in question.

This example uses several Groovy methods/functions.  First, the data grid is stored in a variable, as it will be referenced throughout.  Next, we are using the dataCellIterator, which is the same in the previous post on Groovy.  If you didn’t read that, or don’t understand the iterator, check that out.

At this point, the calculation is requesting to loop through all the cells with Avg Price/Case AND Input in the POV.  Inside the loop, lDestMembers is set to a list equal to all the members in the POV for the relative cell.  memberNames returns every member in the POV in a Groovy list.

The next step is getting the value for the corresponding cell in the Initialized source.  getCellWithMembers accomplishes this with the appropriate parameters passed.  This function accepts member names, so all the members in the Input cell’s POV are used, excluding the source dimension.  This is changed to Initialize.

Lastly, the comparison is made between the two cells.  If they are not identical, setBgColor is executed on the Input source cell to identify it as something that has changed due to an override.

The Calculation

// Initialize a grid
DataGrid curDataGrid = operation.grid
// Set the color to be used if the values are not identical
def iColor = 16746496
// Loop through the cells in column that has
//  Average Price/Case and Input in the POV
operation.grid.dataCellIterator('Avg_Price/Case','Input').each
  {
    // Get the POV for the cell
    def lDestMembers = it.memberNames
    // get the value in the Initialized source that is equivalent to
    // the cell in the Input Source.  The POV form the Input source
    // is used with the exception of the source is changed to Initialize
    def dValue = operation.grid.getCellWithMembers(lDestMembers[0].toString(),
    lDestMembers[1].toString(),lDestMembers[2].toString(),
    lDestMembers.toString(),lDestMembers[4].toString(),
    lDestMembers[5].toString(),"Initialize",lDestMembers[7].toString(),
    lDestMembers[8].toString(),lDestMembers[9].toString(),
    lDestMembers[10].toString()).data
    // if the value is different between the Input and Initialized source,
    // change the background color
    if(it.data != dValue)
    {
      it.setBgColor(iColor)
    }
  }

Data Form Changes

This new Groovy Business Rule should be added to the form and executed on load and save.  This will ensure that the accounts that have been changed are identified both before, and after, the user makes any changes.  One more note that might save you hours of frustration – make sure this rule runs last when other rules are also executed!

Conclusion

This opens up a lot of options that far surpass the default form validations.  Other options are available.

• Tool-tips can also be assigned to a cell instructing the user how to resolve a validation error, if one exists.
• The form save can be interrupted, stopping the user from saving data on a form (or even saving only parts of the form) when validation errors exist.
• Data can be altered to force validation prior to saving.
• Detailed messages can be displayed with instructions and other communication to the user.
• Have specific calculations executed based on the data entered.

This is not an exhaustive list.  We, as developers and architects, literally can do anything we want and have complete control over what happens and what doesn’t happen.  This is exciting because we have nearly complete control over what happens on save.  If you have other ideas, or questions, please share them with comments.

What Is Groovy

Recently, Groovy scripting was added to ePBCS business rules as an option instead of the GUI, or the go-to scripting for you old-timers who still refuse to change.  These are defined in the Business Rule editor as Groovy calculations.  So, what is Groovy?

“Apache Groovy is an object-oriented programming language for the Java platform. It is a dynamic language with features similar to those of Python, Ruby, Perl, and Smalltalk. It can be used as a scripting language for the Java Platform, is dynamically compiled to Java virtual machine (JVM) bytecode, and interoperates with other Java code and libraries. Groovy uses a Java-like curly-bracket syntax. Most Java code is also syntactically valid Groovy, although semantics may be different.”

If you haven’t heard of Groovy, you may want to do some research.  Oracle is using more and more Groovy in applications as administrative options and a communication method between applications.  Groovy is a standard and can be used with millions of applications and websites with the REST API.

What Groovy Script/Calculations Are Not

Groovy calculations are not java-based calculations.  It is not a new calculation language.  It does provide a way to interact with a Data Form in ePBCS and build a calculation script dynamically.  So, Groovy, in the context of Groovy Calculation Scripts, does not connect to Essbase via Groovy Business Rules. It simply builds a string that is sent to Essbase as a calculation.  It does, however, interact with Planning and that is where the power starts.  With the ability to have all the Groovy functionality to manipulate strings and now the ability to interact with the data form, dynamic calculations can be built.  The calculation script sent to Essbase is no different, but the script can now be dynamically generated based on things like, the POV, the text value of a Smart List, whether the values in the grid were updated, whether the data entered meets validation criteria, and other similar things.

If you are experienced with Hyperion Planning, you may have dabbled with JavaScript to do data validation, calculate data prior to the user submitting it, or prevent users from submitting data.  It was a great option to provide feedback to users, but that basically was useless when Smart View allowed users to open Data Forms in Excel. The JavaScript did nothing unless the form was opened in an internet browser.

Getting Started

The first step in creating a Groovy Calculation Script is to, well, create one.  To do that, create a new business rule.  Change the view from Designer to Edit Script.  If you haven’t noticed this before, it provides a way to toggle the GUI to a script view.

Next, find the drop-down box in the toolbar to the far right named Script Type.  This option will read Calc Script.  Change it to Groovy Script.

AAt this point, the script window is now set to validate Groovy script, not Essbase syntax. Even though it doesn’t do anything yet, you have just created your first Groovy Business Rule!

Use Cases

There is a lot of potential in this functionality.  To get you thinking, here are some examples:

1. Execute calculations on large sparse dimensions on ONLY the members that changed on the form.
2. Access the Smart List text to do validation, use in calculations, and store for later use in Essbase (maybe save a member name in a member that is numeric, like employee ID, Cost Center, or account).
3. Perform validation before the calculation is built and sent to Essbase. For example, if the sum of a column used to allocate dollars doesn’t sum to 100, send a calculation that ONLY returns a message and doesn’t perform the allocation.
4. Perform text manipulation previously done in Essbase with functions.  Concatenating member names and truncating member name prefixes and date formats are some of the few I use regularly.  Many of these functions are extremely slow and force the calculation to execute in serial mode, so to be able to do them outside the script is now an option.

Real World Example

The Problem

I am working with a client who wants to override the result of driver-based calculations based on historical trends.  In this example, the volume of cases can be changed and the profit rate can be adjusted.  Once the form is saved, the overrides need to be removed.

Here-in lies the challenge.  If the overrides are removed and the calculation runs on all members in the form, the results would revert back to what they were prior to the override because the override values no longer exist in the database or Data Form.  So, rather than perform the calculation on the override, it would use #missing  or zero, and take the results right back to what the drivers dictated.  The most obvious way around this issue is to execute the calculation on ONLY the rows (vendors in this example) that were edited. In other words, dynamically generate the FIX statement on the vendors that were updated.

The Non-Groovy FIX Statement

Without Groovy, the FIX statement would include @RELATIVE(“Vendor”,0) to run the calculation on all venders on the Data Form.  This has 2 issues.  One, it calculates all the vendors and will change the vendors back to the pre-override values.  Two, every time the user saves the form, the fix is traversing through 30,000 possible vendors.  Although most companies have less than 8,000 active vendors, it still poses a performance issue calculating 8,000 blocks when only a few typically change.

The only aspect of the calculation that is going to change in this situation is the FIX statement, so that will be the only piece shown in the comparison between a Groovy script and a non-Groovy script.

FIX(&vScenario,
    &vVersion,
    &vCompany,
    &vYear,
    "Local",
    "Input",
    @RELATIVE(“Vendor”,0),
    "Jan":"Dec",
    "Regular_Cases")

The Groovy FIX Statement

Since Groovy can dynamically create the calculation script, it looks more like the example below. The sPov will be a string variable in Groovy that holds all the members in the data form’s POV.  The sVendors Groovy variable will hold the list of vendors that have been edited.

FIX($sPOV
    $sVendors,
    "Jan":"Dec",
    "Regular_Cases")

@RELATIVE(“Vendor”,0), which would produce a list of every vendor in the hierarchy, is replaced with “V300000300040003”, “V300000300060001”, “V300000300070002”.

The issue of running the calculation on vendors that have not been edited has now been solved.  An added benefit is that the calculation runs on 3 of the 8,000 blocks, so what took 30 seconds now completes in under a second.

Now, The Interesting Part

Let’s dissect the Groovy calculation script piece by piece.

Setting The Stage

For Groovy to perform operations, there are a few housekeeping items that need to be addressed.  First, a few string builders need to be created to store some variables of strings that grow through the process and are concatenated to Essbase calculation before it is submitted for processing.

There are some variables used to interact with the form’s data grid.  For easy reference to the grid through the script, the grid object is stored in a variable (curgrid).  Next, a variable is created to hold the result of the cells that have been edited (itr).    The likelihood that these variables would exist in most of the scripts is high, so it might make sense to get familiar with these objects and their parameters.

//Get current Data Form
DataGrid curgrid = operation.getGrid()

// Construct a string builder
// Holds the calculation script sent to Essbase
StringBuilder scriptBldr = StringBuilder.newInstance()

// Holds the value for the venders that have changed
StringBuilder vendorList = StringBuilder.newInstance()
String sVendors

// Iterater which gives you only the edited cells
GridIterator itr = curgrid.getDataCellIterator(PredicateUtils.invokerPredicate("isEdited"))

// Holds the list of members from the POV – the function returns an array, so this
// parsed the array and places quotes around each member and separates them with a comma
String sPov = '"' + curgrid.getPov().essbaseMbrName.join(',').replaceAll(',','","') + '"'

At this point the values of the variables are as follows.

Find the Vendors That Have Changed

We know the users will enter overrides in this Data Form (Case Growth and Average Price).  The following piece of the Groovy script will build a delimited list of those vendors based on the rows that have been edited.  It will include quotes around the member names to account for any member names that are numeric or have special characters and will be separated by a comma.  Groovy provides the ability to append to a string with <<”””, and close it with “””.  The if statement ensures that a vendor will not be appended to the string if multiple columns are changed.

// Loop through each cell that was edited and build the vendor list
// If multiple cells on the same row are edited, only add vendor once
itr.each{ DataCell cell ->
  sVendors = cell.getMemberName("Vendor")
  if(vendorList.indexOf(sVendors) < 0){
    vendorList <<"""
   ,"$sVendors"
   """
  }
}

At this point, only a few variables have changed. The bulk of the Groovy functionality is finished.  We now have the POV and the list of vendors that need to be in the FIX statement.

The Essbase Calculation

The next section will append text to the scriptBldr string.  This string will ultimately be sent to Essbase as the calculation to be performed.  Groovy variables are embedded and replaced with the value that they were set to previously.  The two used in this calculation are $vendorList and $sPOV.  Other than those two pieces, everything else is pulled from the original Business Rule and highlighted in red below.

// Add the calculation defined in a business rule to the string variable
// the POV and Vendor List will be used to dynamically set the FIX statement
scriptBldr <<"""
VAR v_Price;

FIX($sPOV
    $vendorList,
    "Jan":"Dec",
    "Regular_Cases")

  /* Calculate Overrides */
  "OEP_Working"(
  v_Price = "Avg_Price/Case"->"YearTotal";

  "Regular_Cases" = (1 + "Case_Growth_Rate"->"BegBalance") * 
                    ("Regular_Cases"->"FY16"->"Final");
  IF("Avg_Price/Case_Inp"->"BegBalance" == #Missing)
    "Net_Sales" = (v_Price) * (1 + "Case_Growth_Rate"->"BegBalance") * 
                  "Regular_Cases"->"FY16"->"Final";
  ELSE
    "Net_Sales" = ("Avg_Price/Case_Inp"->"BegBalance") * 
                  (1 + "Case_Growth_Rate"->"BegBalance") *
                  "Regular_Cases"->"FY16"->"Final" ;
  ENDIF

  IF("GP_2_%_Inp"->"BegBalance" == #Missing)
    "GP_Level_2" = ("GP_Level_2_%"->"YearTotal"->"FY16"->"Final") * "Net_Sales" ;
  ELSE
    "GP_Level_2" = ("GP_2_%_Inp"->"BegBalance") * "Net_Sales" ;
  ENDIF
  )
ENDFIX

FIX($sPOV
    $vendorList)

  CLEARDATA "Avg_Price/Case_Inp"->"BegBalance";
  CLEARDATA "GP_2_%_Inp"->"BegBalance";
  CLEARDATA "Case_Growth_Rate"->"BegBalance";
ENDFIX
"""

At this point, the scriptBldr variable is a complete Essbase calculation that can be validated in any Business Rule.

Finishing UP

The last thing required is to send the calculation text built above to Essbase.

println scriptBldr // Sends the script to the log
return scriptBldr // Sends the script to Essbase

Verifying What Was Sent To Essbase

When the Data Form is saved, the results in the form can be validated back to the logic to verify that the calculation worked as expected.  Regardless of whether the calculation executes with or without failing, the value of scriptBldr ( calculation sent to Essbase) is captured in the Job console.

In the Job console, click the Job Status link.  This includes the value of the scriptBldr variable.  The text can be copied from this window, and if it failed to execute, can be copied into a Business Rule and validated there to find the issue.

Wrapping Up

I will admit that I am not a Java programmer, so I am still educating myself on the potential this affords developers.  I am struggling to digest the API documentation and to truly understand the depth of the possibilities. I do know this opens up a whole world we didn’t have with Hyperion Planning. I plan on learning and using Groovy calculations more and more because of the possibilities it provides.  Look for more examples and knowledge sharing as I get my hands around the API and integrate this into more delivery solutions.  To get future publications, sign up to be notified about new posts and articles at www.in2hyperion.com.