A specialty chemical products company created an ASO reporting cube in ePBCS.  This application housed detailed data that rolled up to a GL account.  The problem was the source didn’t always tie to the GL due to restatements, GL entries, and manual corrections.  Since they wanted the data to match to the book of record, but still have the level of reporting  at the detailed level, they wanted to load data from both systems, calculate the variance, and store that variance so users would see the difference between the two sources.

There were a number of challenges.  Procedural calculations were “too complex” and could not calculate at all the levels.  They didn’t want to engineer a new solution that included a BSO calculation where the data was replicated and calculated.  So, Groovy was selected to calculate the variance.

Dimension Explanation

To simplify the explanation, this following Smart View retrieve will help tell the story.

Organization dimension includes sub organizations from the ERP, as well as the official organization in HFM.  If Ohio was an organization in HFM, the hierarchy would look similar to this.

• Ohio_Total
  • Ohio
  • Cincinnati
  • Columbus
  • Cleveland

I would have preferred to use a source dimension for this, but the application didn’t include one.  So, we used version, and it look like this.

• Total Working
  • Working (where the actuals were loaded) – named working1 in the script below
  • Working Adjustment (where the adjustment to get back to the book of record was stored) – named Working1_LStat_Adj in the script below
• System Versions (hidden from users)
  • HFM Stat (where HFM was loaded) – named HFMLocalStat in the script below

Groovy Calculation Overview

The calculation prompted the user for a start and end month, and the year. The follow was done at the bottom of every other dimension in the application.

1. The first piece of the calculation queried the Org hierarchy and found all the level 1 members that ended in _Total (Ohio_Total in the example above).  These were stored in an array to be used later.
2. The second step was to retrieve the data from HFM (HFMLocalStat) and the data loaded from the ERP (Working1) at level 1.
3. The third step was to calculate the variance between x_Total member for the ERP and HFM data and store it at the HFM child member in Working1_LStat_Adj.
4. The last step was to submit the results

The application didn’t have a source dimension, which is normally where I would account for this, so we added a few additional versions.  The first was a statistical version that was only used to house the data imported.  Users didn’t have access to this version.  The GL data was loaded to a statistical version.  The working version was compared to the statistical version, and the difference was stored in working adjustments.  The two of these combined was used to report from

Groovy Calculation Deep Dive

The calculation would skip the months that had no data.  For 12 months, the calculation finished in less than 5 minutes.  The calculation was designed, built, system tested, and went through user validation in a few days.  The total development time was less than a day.  The administrators could use the existing architecture with no changes required (outside of creating a new member in the xxx dimension)

[membership level=”0″]

In2Hyperion has premium content that is only available to those who make a small annual donation.

[/membership]
[membership]

/*RTPS: {rtpMonth} {rtpYear} */
def rtpMonth = rtps.rtpMonth.getEnteredValue()
def rtpYear = rtps.rtpYear.getEssbaseValue().replaceAll('"', '')

def lastMonth = Calendar.instance.with {
time = new Date().parse("MMM", rtps.rtpMonth.getEnteredValue() )
it[ MONTH ]
}

// Setup connections
Cube cube = operation.application.getCube("Operations")

// Identify the orgs that are taken into account and need adjusted
// members with a parent equal to the child with a _Total suffix
Dimension orgDim = operation.application.getDimension("Org", cube)
def keyProp = "Parent"
def orgParentChild = [:]
def Orgs = orgDim.getEvaluatedMembers("ILvl0Descendants(All Org)", cube) as String[]
Orgs.each{
  Member orgMbr = orgDim.getMember(it)
  def memberProps = orgMbr.toMap()
  if(memberProps[keyProp] == it.toString() + "_Total"){
    orgParentChild[it] = it.toString() + "_Total"
  }
}
def orgParents = orgParentChild.collect{it.value}

// Loop through months selected
(0..lastMonth).each{ iMonth ->
  def runMonth = new java.text.DateFormatSymbols().months[iMonth]
  def runDay = "${iMonth+1}-1"
  println "Executing for ${runMonth}"

  // retrieve the ERP and HFM data points
  DataGridDefinitionBuilder EntityBuilder = cube.dataGridDefinitionBuilder()
  EntityBuilder.addPov(['Market','Project','Scenario','View','Years','Currency','Product','BT_Customer','Customer'],
                       [['Total_Market'],['Total Project'],['Actual'],['PTD'],[rtpYear],['USD'],['Total Product'],['BT_Customer'],['TotalCustomer']])
  EntityBuilder.addColumn(['Period','Version'], [[runMonth] , ['Working1','HFMLocalStat','Working1_LStat_Adj']]) 
  EntityBuilder.addRow(['Entity','Org','Account'], [ ['ILvl0Descendants("Total Entity")'],orgParents,['ILvl0Descendants("NetIncome")'] ]) 
  EntityBuilder.setSuppressMissingBlocks(true)
  DataGridDefinition EntityDefinition = EntityBuilder.build()

  // Calculate and submit the adjustments
  // Build the grid based on the rows in the previous grid
  cube.loadGrid(EntityDefinition, false).withCloseable { entityGrid ->
    if(entityGrid.size() > 0){
      DataGridBuilder builderSubmit = cube.dataGridBuilder("MM/DD/YYYY")
      builderSubmit.addPov('No Market','No Project','Actual','PTD',rtpYear,'USD','No Product','BT_No Customer','No Customer','Working1_LStat_Adj')
      builderSubmit.addColumn(runDay)
      entityGrid.dataCellIterator("Working1").each{ it -> 
        builderSubmit.addRow([it.getMemberName("Entity"), it.getMemberName("Org").toString().reverse().drop(6).reverse(), it.getMemberName("Account")], [it.crossDimCell("HFMLocalStat").data - it.data])// it.crossDimCell("HFMLocalStat").data
      }
      DataGridBuilder.Status status = new DataGridBuilder.Status()
      DataGrid gridx = builderSubmit.build(status)
      cube.saveGrid(gridx)
      println(" Total number of cells accepted: " + status.numAcceptedCells)
      if(status.cellsRejected.size() != 0){
        println(" Total number of cells rejected: " + status.numRejectedCells)
        println(" First 100 rejected cells: " + status.cellsRejected)
      }
      gridx.close()
    }
    else{
      println "No data available."
    }
  }
}
println "Finished"
return

[/membership]

That’s A Wrap

This is a great example of how we can inject new and creative ways to solve problems with speed in processing, speed in developing, and introducing no additional maintenance.  Look for more of these real world wins in the future.

One thing that I have spent a lot of time on is making calculations independent of forms so that they can be used on any form.  For example, rather than hard coding a script to look at a form with one column header and one row header, I am now building things to be dynamic so that the POV, the rows, and columns all are read dynamically and identifying the edited cells is independent of the source it is looking at.  This will be a multi-post article because there is a lot to cover.

Think Different

I have not talked about some of the, what might seem like, less functional classes and methods in the API.  Most of my examples don’t go into their use.  The reason is solely trying to break concepts apart and try to not overload people with everything at once.  What if I told you you could eliminate most of your substitution variables?  What if I said you only need one data map?  What if I told you that you could use rule properties like never before?  Spoiler alert, you can!

Building The Foundation

The first concept I want to share is a simple one, but it is the start of making your scripts dynamic.  When working with grid builders, a reference has to be made to the plan type.  This is a very simple thing but has to be changed for different plan types.  You might first think to put this in a script and embed the script, but there is an easier way that makes it completely independent of the plan type or application.

Everything I have shown you with grid builders starts with this.  This means that every rule has to have a hard-coded plan type in it.

Cube cube = operation.application.getCube("plantypename")

Well, this isn’t the case.  It can be done without hard coding the play type name.   The cube variable can be set by getting the cube that the rule is created in so the rule will work on any plan type in any application.

Cube cube = rule.getCube()

I can take that one step further and eliminate the cube variable all together.

DataGridBuilder builderSubmit = rule.getCube().dataGridBuilder("MM/DD/YYYY")
//or
DataGridBuilder builderSubmit = rule.cube.dataGridBuilder("MM/DD/YYYY")

Grid builders aren’t the only class that uses this.  If you are doing anything with metadata, this will also benefit those scripts.

Dimension productDim = operation.application.getDimension("Period", cube)
// can be changed to
Dimension productDim = operation.application.getDimension("Period", rule.cube)

That’s A Wrap

We have access to all kinds of things that we can make use of now through these classes. The application class exposes the currency mode and the default currency.  We have access to the smart lists and can access those.  Could we use those in calculations?  Maybe it is used as a map, where label and description is an account conversion between plan types?  We can get the dimensions, so a calculation could see if a dimension exists.  Maybe we can dynamically create fix statements based on the dimensions in the cube (aggregate everything that is sparse).  User variables can be set, so maybe if a calculation runs and the user variables aren’t set, we ask the user to set it with an RTP, then continue the calculation?

The rule class has methods to get the description, the name, and the rule properties.  I can’t say I have done it, but maybe we used the description as a variable?  Maybe we have the name in a convention that specific characters mean something and are brought into the rule, like a the scenario name?

Some of these are just thoughts, and some of them are things that we could implement and use.  My point is that there are all kinds of things we have access to dynamically that we didn’t before.  The apps I am building don’t have variables for the open periods anymore because I can get them dynamically in the calculation based on the scenario being calculated.  No more start and end month.  No more current month.  And, if they are needed for reports or forms, have the calculation set them if they are wrong.

So, what are you thinking?  Do you have something you have done that you couldn’t do before Groovy?  Share it by commenting.

Often times, there is a need to lock cells from a user outside of the traditional dimensional security methods.  Maybe you don’t want to add dimensional security to accounts so your data push allows users to push data to another cube.  Or, maybe there is more complicated logic to identify who can and who can’t edit a cell.  The logic to do with is very simple.

Locking Cells

If you have read many of my articles, you likely have read about how to iterate though cells with the data grid iterator.  If you haven’t, starting with Part 3 would be a good start.  The grid iterator allows the script to loop through all cells, edited cells, or a filtered list of cells.  This is traditionally used to execute logic after a user edits a form, but it can be used to lock cells when the form is loaded.  Whether it is simply specific accounts, products, or a more complicated filter like products that have or don’t have a specific attribute, it quite easy to do.

Following are two ways to lock the cells.  The first is more dynamic.  This could do all kinds of things, like lock the cell if it was above a specific value, or if it had a specific attribute, or even something silly, like if it the user’s name was Kyle!

// identify the rows to be locked in a list collection
List<String> lockCells = ['Net Income']
// lock the cells
operation.grid.dataCellIterator().each {
     // Lock the cells if the measure member of the cell is in the lockCells list
     if(lockCells.contains(it.getMemberName("Measures"))){
          it.setForceReadOnly(true)
     }
}

The next example would do the same thing as above, but would filter the cells the iterator loops on and lock all the cells that meet the filter applied.

// identify the rows to be locked in a list collection 
List<String> lockCells = ['Net Income']
// lock the cells
operation.grid.dataCellIterator(lockCells.join(',')).each {
     it.setForceReadOnly(true)
}

Add To The Form

Now that the rule is built, the only thing left is to add this to the form in which it should run.  The key is to add this rule and check the Run After Load checkbox.  This will allow the form to load (or render), and then run the rule, which in this case, will change predefined cell properties so that it is not editable.

That’s A Wrap

This is not the longest post I have ever made, but it doesn’t mean it is less useful.  Locking cells based on criteria, criterial we never had access to before, is quite interesting.  When security is set to lock a cell from a user through dimensional security, it also means the users can’t use data pushes to move that calculated data to other cubes, which is an issue.  Think about adding an attribute or UDA to those accounts and then building a common script that runs on all forms that locks the cells with that attribute or UDA.  It wouldn’t be any more difficult to build, and it can be automated through metadata builds.  Hmmm, wouldn’t that be nice!

Data forms that have rules on them run when a user saves a form, regardless of whether the form has edited data.  Sometimes this is by design, to let a user run a calculation.  But, most of the time, it is an accident.  There is no reason to use system resources and have the user wait for a calculation to finish when it doesn’t need to be executed.

Check If Data Has Changed

There is a very simple way to check if a data form submitted has data that has been edited, or changed.  Several previous posts have talked about the data iterator.  The data iterator is used to loop through the cells in a form.  Predicates can be used to only loop through the cells that have been edited.  The documented example shows the code to identify and loop through the edited cells, printing each to the job console.

operation.grid.dataCellIterator({DataCell cell -> cell.edited}).each {
     println("$it.memberNames, cell data: $it.data")
 }

If you are starting to understand the methods and collections, you notice that this returns a list of data cell types.  From this, I can get all the properties of each cell.  Since this returns a list, I can also use the size method.  The size method returns the number of elements.

operation.grid.dataCellIterator({DataCell cell -> cell.edited}).size()

This will return the number of edited cells.  It isn’t a stretch to take this one step further and wrap it in an if statement to see if the number of edited cells is more than 0.  If it is 0, then no cells have been edited.

IF(operation.grid.dataCellIterator({DataCell cell -> cell.edited}).size() == 0){
   ...take action
}

Inform The User Or Not

This is where there is some administrator preference.  I can argue both ways, but I think it depends on the users and the expectations of what happens when a form is saved.  One one hand, I can argue that the rule should exit without notifying the user that nothing ran.  It is an extra click that they have to respond to that might be annoying.  Telling them that nothing changed, and no calculation ran may not be needed.  In this case, the calculation exits gracefully, and everybody moves on.  Using return exits the calculation.

IF(operation.grid.dataCellIterator({DataCell cell -> cell.edited}).size() == 0){
    return
}

On the other hand, the user be expecting something to happen.  They might need to know that they didn’t edit anything.  If this is the situation, I can throw an exception and prompt the user there was an issue.  Using throwVetoOperation will do just that.  It initiates an error and nothing further runs.

IF(operation.grid.dataCellIterator({DataCell cell -> cell.edited}).size() == 0){
     throwVetoException{"No data was edited and no business logic was executed."}
}

The throwVetoException method can also use the Groovy message bundle and bundle loader if you are using those classes.

That’s A Wrap

I have used both methods, exiting without notifying the user and existing and notifying the user.  My preference is to simply exit and not run or notify the user because normally they don’t need to know nothing ran because nothing changed.  Regardless, I think it is a good practice to add this to your Groovy calculations to account for a user saving a data form when no data was edited.