One of the more surprisingly useful things that can be done in a Groovy calculation is querying metadata. This was discussed in Part 11: Accessing Metadata Properties, but I recently had a situation where users could load data at parent levels and have it allocated down to the bottom of the hierarchies.  Knowing if users selected a parent member in the hierarchy was critical because the business process was different based on whether it was a parent or a level 0 member.  This can obviously be checked in a business rule, but I had the need to alter several functions in the process if the selection was a parent.  Smart Pushes and data synchronizations, as well as the business rules that were executed, were dependent on the level of the hierarchy selected.  This is possible with Groovy.

The Code Explained

Using a combination of methods, the children of any member can be returned in a list.  That list can be used for all kinds of things.  This focuses on getting the size of the list to identify if it has children.

// Normally getting the POV Selected, but this is hard coded for the example
def povProduct = '"' + "Tents" + '"' 
// Setup a connection to the cube
Cube cube = operation.application.getCube("GP")
// Create a dimension object
Dimension productDim = operation.application.getDimension("Product", cube)
// Create a member list - ILev0 of the member passed
def MemberList = productDim.getEvaluatedMembers("ILvl0Descendants($povProduct)", cube) 

println MemberList
println MemberList.size() == 1 // will print true or false

The MemberList variable now holds a list of all the members (level 0 members below tents), including the member passed in the function.  This will always have at least one item in the list, assuming the member exists in the dimension.  If there is more than 1, we know it has children, and therefore, is not a level 0 member.

if(MemberList.size() == 1)
  {
  //actions taken if the POV is a level 0 selection
  }
  else
  {
  //actions taken if the POV is NOT a level 0 selection
  }

Summary

In applications where top down planning is needed, different logic runs when data is entered at a parent verses when it is entered at a leaf (lev0) member.  This can be handled in an Essbase calculation using @ISLEV0, but with Groovy, the fix statement can be altered so that unnecessary logic isn’t executed and data pushes are limited to only what is impacted.

Do you have an idea of how you might benefit from the results of metadata queries?  Come up with your own and share with the group by commenting below.

Building on the previous post and in the spirit of reusing code, this will expand on the concept by walking through the creation and use of functions in Groovy calculations.  Functions have huge value in reducing your Groovy calculations and streamlining development. 

Functions allow access to processes that either do some action(s) or return a value to be used.  There are hundreds of cases I can think of where this is valuable.  Here are a few to get you started.

1. Getting the form POV, or a dimension in the POV
2. Creating and executing data maps (automatically using the form POV, for example)
3. Identifying if the account should be converted to a different currency (headcount will never be converted)
4. Logging repetative messages to the job console

Share your ideas with the community by posting a comment.  I am sure your thoughts will be valuable to the community!

These functions can be kept in a script that is reusable as discussed in Part 24.  Or, they can be created in individual Groovy calculations if they are unique to a specific calculation.

The Anatomy Of A Function

Functions can be dissected into three pieces.

1. A name (required)
2. The parameters (not required)
3. The logic (required)

The construction is below.

def name(parameter1, parameter2, ...)
{
  logic
}

The name is simple.  This is a reference to the function and the string you use to call it.  I would suggest making it meaningful, but not terribly long.  Typing long names gets annoying after a while!  Also, come up with a consistent case strategy.  I normally capitalize the first letter in all but the first word (getSomethingGood).

The function can have parameters.  Unless it is just doing some generic action, it will need something.  It can be a numeric value, a string, a boolean value, or really any other type of object, like collections.  Parameters are separated by commas and can have default values.

The logic is what is inside the function and the whole purpose to have a function.  Functions can return values, execute some action, or both.

Function Examples

Getting the POV

A lot of functions need members from the POV.  Sometimes surrounding them with quotes causes problems in the PBCS functions so this gives an option to include or exclude them.  This also will return the member as a list for functions that require a list, like data maps.

The second two parameters are not required unless quotes or a list is needed as the return value.

def getPovMbr(dimension, boolean quotes = false, boolean returnAsList = false) {
  if(returnAsList == true)
    // tokenize will split the value based on a delimiter and convert the string to 
    // a collection.  Since this is one value and we just need the one value converted
    // to a collection, I used ### as the delimiter since it will never occur in any
    // dimension name.
    return dimension.tokenize('###')
  else if(quotes == true)
    return '"' + operation.grid.pov.find{it.dimName==dimension}.essbaseMbrName + '"'
  else
    return operation.grid.pov.find{it.dimName==dimension}.essbaseMbrName
}

Assuming the Plan is selected in the POV,

• getPovMbr(“Scenario”) will return OEP_Plan
• getPovMbr(“Scenario”,true) will return  “OEP_Plan”
• getPovMbr(“Scenario”,false,true) will return [OEP_Plan]

Print To The Log

Monitoring processing times of actions inside a calculation is helpful to diagnose issues and manage performance.  This function will accept a message and return the duration from the previous execution of the function.

def startTime = currentTimeMillis()
def procTime = currentTimeMillis()
def elapsed=(currentTimeMillis()-startTime)/1000

def printPerformance(message, boolean printTotalTime = false)
{
  // Gets the elapsed time
  elapsed=(currentTimeMillis()-procTime)/1000
  // Sets the new start time
  procTime = currentTimeMillis()
  // Print to log
  println "****************************************************"
  println "$message = $elapsed secs"
  if (printTotalTime = true)
    println "Total Time = " + (currentTimeMillis()-startTime)/1000 + " secs"
  println "***************************************
}

printPerformance(“The Data Map processed”,true) would write the following message in the log.

****************************************************
The Data Map Processed = .204 secs
Total Time = 1.44 secs
****************************************************

This can obviously be customized to whatever you want to show.  As it is something used often, having a function saves a lot of time, reduces the non-business related code in the rule, and makes reading the business rule more digestible.

Rather than repeat all this,

time elapsed=(currentTimeMillis()-procTime)/1000
procTime = currentTimeMillis()
println “****************************************************”
println “$message = $elapsed secs”
println “Total Time = ” + (currentTimeMillis()-startTime)/1000 + ” secs”
println “***************************************

you simply call the function.

printPerformance(“The Data Map processed”,true)

Convert Account To USD

Groovy calculations don’t need to run Essbase calculations to execute business logic.  The logic can be executed in Groovy.  For a presentation in Orlando, I wanted to prove this.  I replicated a calculation that calculates revenue, gross profit, and margins.  It also needs to produce converted currencies.  As you know, accounts like units, headcount, and rates don’t get converted even if the local currency is something other than USD.  In the example I showed at KScope, a gridbuilder was used and every account needed to be identified as an account that would be converted, or not converted.  The following function returns a true/false for an account based on whether that account has a UDA of IgnoreCurrencyConversion.

boolean convertCurrency(def account)
{
  Dimension AccountDim = operation.application.getDimension("Account")
  Member AccountMbr = AccountDim.getMember(account)
  def memberProps = AccountMbr.toMap()
  if(memberProps['UDA'].toString().contains('IgnoreCurrencyConversion'))
    return false
  else 
    return true
}

convertCurrency(“Units”) would return a false
convertCurrency(“Revenue”) would return a true

In the code, as it is looping through the accounts, it applies the conversion only if it needs to.

if(convertCurrency(account)){
  sValuesUSD.add(currencyRates[cMonth].toString().toDouble() * setValue)
  addcellsUSD << currencyRates[cMonth].toString().toDouble() * setValue
}
else
{
  sValuesUSD.add(setValue)
  addcellsUSD << setValue
}

The full example of this is near the end of the ePBCS Gridbuilder Deep Dive – Last Minute KScope Souvenirs in my Kscope Wrap Up.

Conclusion

There are significant benefits to functions.  As your growth in this space grows, you will likely develop more of these function and reuse them.  Got an idea?  Share it by posting a comment!

Now that you are knee deep in Groovy, help yourself out and reuse common code.  The more you learn, the more efficient you will be and will laugh at some of your initial attempts at your Groovy calculations.  If you are like me, you get excited about all the possibilities and learn as you go.  When you find better ways to do something, or even preferable ways, you end up with an application with inconsistent representations of the same logic.  You are too busy to go back and update all the snippets you have improved, so it stays in a somewhat messy state.

Do yourself a favor and start reusing some of the things you need in every script.  When you start doing more in Groovy calculations, the more you can replicate the better.  Making some of the code business rule agnostic will make you more productive and will create a consistent approach to calculation strategy.  An example of this would be variables for the dimensions in the POV.  Or, maybe a common map used to push data from WFP to the P&L.  Regardless of the use, as soon as you see things that will be duplicated, put them in a script.  Scripts can be embedded in Groovy calculations just like regular Business Rules.

Header Script

This is an example of something that I find useful for every Groovy calculation I am writing.  It accomplished a couple things.

1. It stores the forecast months so data maps, smart pushes, Essbase calculations, and grid builder functions, can be dynamically execute on all months for a budget and the out months for the forecast based on the scenario selected.
2. It gets the numeric value for the current month to be used in other localized calculations.
3. It creates a calendar object to get the current time for logging performance.
4. The parameters are logged to the job console.

/*RTPS: {RTP_Month}*/

def Month = rtps.RTP_Month.toString().replaceAll("\"", "")
def MonthFix = rtps.RTP_Month.toString()
def months = ['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec']
def actualMonths = []
def forecastMonths = []

months.eachWithIndex {item, index ->
  if(index > months.findIndexOf{it == Month})
    forecastMonths <<  item 
  else
    actualMonths <<  item 
}

def actualMonthsFix = """\"${actualMonths.join('", "')}\""""
def forecastMonthsFix = """\"${forecastMonths.join('", "')}\""""
def useMonths = []

if(operation.grid.pov.find{it.dimName =='Scenario'}.essbaseMbrName.toString().toLowerCase().contains('forecast'))
  useMonths = forecastMonths
else
  useMonths = months

//Get time for logging perforance
Calendar calendar = Calendar.getInstance()
calendar.setTimeInMillis(currentTimeMillis())

// Get the numeric value for the month (Jan=0)
int monthNumber = Calendar.instance.with {
  time = new Date().parse( "MMM", Month )
  it[ MONTH ]
}

println "The current month number is $monthNumber"
println "The current month is $Month"
println "The current month (Fix) is $MonthFix"
println "The actual months (list) are ${actualMonths}"
println "The actual months (string) are ${actualMonthsFix}"
println "The forecast months (list) are ${forecastMonths}"
println "The forecast months (string) are ${forecastMonthsFix}"

Conversion Table

If the application moves data from more detailed plan types to a consolidated P&L, it likely has conversions.  This is an example of an account map from a Gross Profit plan type to a P&L plan type.  How it is used will be explained in a later submission, but it is used in every calculation that synchronizes data from the GP to the P&L cube.  Therefore, it is a great candidate to have in a shared library (a script) so it can be maintained in one place.

def acctMap = ['Cases':'Units',
               'Sales':'42001',
               'COGS':'50001',
               'Tax':'50015',
               'Chargeback':'56010',
               'Investment Gains':'50010',
               'Writeoffs':'56055',
               'Growth Attributed to New Products':'56300',
               'Samples':'56092'
                ]

Sharing Scripts

Just as scripts can be embedded in regular business rules, the exact same syntax is used in Groovy calculations.  Assume the header script above is called Common Groovy and sits in an application named FinPlan in the GP plan type.  The inclusion of the script into any  Groovy calculation would be done by inserting the following text.

%Script(name:=" Common Groovy ",application:="FinPlan",plantype:="GP")

Conclusion

These are just examples to get you thinking about how you can reduce the headaches down the road when your Groovy calculations need maintained.  It can easily be expanded to include common POV members and other common code.  Be mindful of how global this is as not all POV members are in all forms.  You might find it useful to have a script that is shared everywhere and a few others shared for like calculations or forms.  You aren’t limited to only including one shared script.

If you have a snippet you are using in all your Groovy calculations, share it with the community.  We always like to get feedback and learn from each other.

KScope has concluded, and what a fantastic week it was.  I love the years I get the feedback that I have an abstract selected so I can attend.  This year, I was awarded Oracle Ace, so it was really nice to be nominated and recognized for my contributions to the community. 

I tried to record the sessions and unfortunately, only 1 worked.  All of my presentations were recorded through the event and will be released in the coming months. When they are, I will share the links.  Until them, I hope the visual presentations will inspire you to take a look at Groovy, if I haven’t already pushed you in that direction.

Now that my time has been spent building these decks is over, look forward to more Groovy Adventures!

Top Down and BottomS Up Planning at Breakthru Beverage Group

Why Groovy is Game Changing

ePBCS Gridbuilder Deep Dive – Last Minute KScope Souvenirs

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

Yes, it is true that Groovy is available in on-premise and cloud (PBCS) versions of Hyperion Planning.  No, it is not true that the same flavor of Groovy exists in both.  Both have their advantages, and both have their drawbacks.  The likelihood that they will ever be the same is extremely low, and here is why.

The Difference Is

On-Premise gives developers the ability to write and use independent Groovy compiled applications.  These can be used in Business Rules as CDFs (custom defined functions).  Developers have complete functionality to make this do whatever they want.  It can return results to save to Essbase/Planning, it can interact with SQL, can run other programs, pretty much anything you can access that has a JAVA API.

PBCS doesn’t have the same flexibility.  Custom defined functions can’t be compiled and stored on the server.  PBCS, rather, has “Groovy Calculations.”  This gives developers the flexibility to interact with the Data Forms that on-premise doesn’t have.  Developers can iterate through the cells and act accordingly.  It can stop the form from saving, calculate and override data entered, color code cells, customize Data Maps, Smart Pushes, dynamically generate calculations, move data between databases, all with access to much of the Groovy functionality.

PBCS also supports the REST API, so Groovy can be used to access that and do everything, even more, that EPM Automate can do.

Why They Will Never Be The Same

This is just an opinion.  Technology changes so rapidly that this may change.  Corporate strategy changes almost as rapidly.

If PBCS had to ability to do what on-premise does, the ability for Oracle to support the instance would be a challenge.  CDFs can delete all the files on a server, for instance, and I don’t see a cloud provider giving developers this much control in a shared environment.

I also don’t see on-premise to have the same proactive interaction that PBCS has with Groovy Calculations purely because Oracle is pushing the cloud, and they want the most current functionality to exist in the platform they are pushing clients to use.

My Two Cents

I understand why there is a difference, and I don’t expect it to change in the near future.  3 years ago I didn’t expect that I would tell you that I would rather do a cloud implementation than on prem, either.  I do think as people get more comfortable with the cloud, and security improves, there will be advances.  I think there will be a future state where the cloud offerings will be closer to having the flexibility to the on-premise implementations.

Introduction

Chris Hull has been kind enough to partner with us to present how the methods available in Groovy calculations have made a huge impact in their budgeting and reporting process using PBCS.

One of the biggest user complaints about their first budgeting process in PBCS was that they had to wait to get their consolidated reporting until an admin ran the process or a scheduled consolidation ran.  Thanks to what Groovy offers, this is no longer going an issue.

For those of you that participated, I have added some additional content.  I know we were limited on time.  I skipped a few examples and rushed through diving into the actual steps and Groovy processes.  I included additional examples and more information on the process below.  If you didn’t attend, I would encourage you to walk through the items in the order they are presented.

If any of this is of interest to you and you would like to speak further about the capabilities, please send an email.

Thanks for everybody that participated and supported this event.

Presentation Recording

Additional Questions

There were a few questions after we signed off.  I will do my best to answer them below.

Question: How can internal IT team support this solution? Does Huron train them on how to to write Groovy, change code in Groovy?  Demo shows only 3 cells were changed, how is performance if all products were changed? Doesn’t it hit MDX error limit anyways?  Does Oracle SR team support issues/bugs related to Groovy?

Answer: Most of our solutions are not maintained by IT, but by finance.  Huron certainly can train administrators on how to maintain the Groovy calculations.  As far as performance, I changed 75 products and it took 14 seconds to push the data from GP to the GP reporting application, and .4 seconds to synchronize it to the Fin cubes.  MDX isn’t used to do the synchronization so the MDX text limit is irrelevant.  Oracle does support the product and they support issues related to the API, which Groovy uses.  I have seen some issues resolved in weeks, and others that are still being addressed months later.

Question: What capabilities does the tool have to track changes to the budget?

Answer: PBCS, when turned on, has auditing at the cell level.  Every time data is changed, the user, date/time, old value, and new value are recorded.  At any point a user can look at that and see the change history.

Expanding On The Groovy Form Save Process

Additional Examples

Presentation PowerPoint

Conclusion

Thanks again.  I hope to do more of these in the future and will make sure  you are included in the invitation.

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.

No, But Can It Solve Yours?

I received a lot of positive feedback on the Groovy Series and have been asked a many great questions.  People are excited about the improvements but are still a little hesitant to buy in to the hype.  They question, and rightfully so, 

1. are the performance gains really as positive as I have stated, and
2. is the functionality that can be added to improve a user’s experience really available, and
3. can it improve the validity of the data input as much as I have said it does?

Challenge Accepted!

If you have a challenge, performance issues, or missing functionality that you desperately need in Data Forms, post a quick comment with a summary about what you are facing.  Please enter a valid email so I can contact you directly with any questions.  Don’t worry, your email will remain confidential!

I am going to try to pick one situation every week or two and provide some alternatives with Groovy that will solve, or improve the problem you are facing.

I encourage you to

Join the Party!

Good luck on your quest to make Hyperion Planning a better experience for all of your users!