Adventures in Groovy – Part 54: Collections Are Your Best Friend

I received a number of questions when I attended KScope. A lot of people had a tough time wrapping their head around the fact that using Groovy could actually reduce cost and development time, and significantly decrease the effort to maintain an application. The feedback I get on my lightning lessons has been overwhelmingly positive. The basis of the concept of the use of Groovy making things simpler stems from understanding, using, and becoming confident using collections. I thought I would share some examples, use cases that you all use, and some samples to get those started. If you doubt Groovy can mean simpler and faster, I HOPE this will get you to at least think about coming over to the dark side and provide some perspective that will help.

I would like to create more of these and potentially move my training to be free. Before I can do that I need everybody to watch what I post AND subscribe! If I can generate enough of that, I can create enough add revenue to fun my effort and time. So subscribe, watch, and we will see what happens.

In the mean time, let me know what you think of having videos like this rather than traditional written content. If you would like to learn more about dynamic scripts for data maps, forms, and improving performance with Esssbase scripts by removing serial mode and threading the right way, head over to in2hyperion.podia.com. I have more lightening lessons ready for you to take advantage of!




Adventures in Groovy – Part 44: Don’t Waste User Time and System Resources

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.




Adventures in Groovy – Part 39: First Common Parent

I can’t tell you how many times I have been at a client and they wanted to replicate eliminations at the first common parent like HFM, or some other consolidations system.  Maybe there is a good way to do this, but I could never find an efficient and effective way to accomplish it with Essbase calculations.  Well, that is history.  This is yet another example of how to solve an historically complex problem very simply.

What Is The First Common Parent

Eliminations functionality addresses the posting of inter-company eliminations in scenarios where a full legal consolidation model is not required, such as within a standard financial model.  An example would be eliminating sales to another entity inside the organization so the total sales of the organization is not inflating the real sales of the organization.  This is typically done at the first node that consolidates the two entities, or first common parent.  In the example below, which will be used in the code below, we need to find the first common parent to do the eliminations for entity S253 and S592.  The hierarchy below shows that the first parent of these two members is Mountain Division.  This is the entity that will calculate and hold the eliminated sales.

Surprisingly Easy With Groovy

The Groovy classes available to us have the ability to query metadata.  This allows a calculation to return all kind of things, like the ancestors of members.  Groovy takes over the rest by comparing the arrays returned.

For this example, the calculation prompts for two members.  In an real-world example, these would likely be defined in the calculation, or maybe with UDAs or attributes.  The prompts in this example, C1 and C2, are run time prompts connected to the hierarchy above.  Once the members are defined, the next step is to query the ancestors of each of the two members.

First, a connection to the application that has the dimension and members is defined.  Once that is done, a dimension object is created that is used to execute the queries.  Since we need ancestors, we use IAncestors.

/*RTPS:{C1} {C2}*/
Cube cube = operation.application.getCube("Fin")
Dimension companyDim = operation.application.getDimension("Company")
List companyOne = companyDim.getEvaluatedMembers("IAncestors(${rtps.C1.toString()})", cube) 
List companyTwo = companyDim.getEvaluatedMembers("IAncestors(${rtps.C2.toString()})", cube)

companyOne returns an array with the following values.

[S253, AZ_Stores, Mountain_Div, West_Reg, US_Stores, Tot_Company]

companyTwo returns an array with these values.

[S592, MT_Stores, Mountain_Div, West_Reg, US_Stores, Tot_Company]

The hard part, if you consider that hard, is over.  Now that the two arrays are defined, a snazzy Groovy method are used.  The intersect method will return the common elements of two lists.

The order of the elements returned by the PBCS classes is ordered from the bottom of the hierarchy to the top.  The first element would be the first common parent!  This example doesn’t illustrate it but this would work for staggered hierarchies just the same.

List commonParents = companyOne.intersect(companyTwo)
println "First common parent for ${rtps.C1.toString()} and ${rtps.C2.toString()} is ${commonParents[0]}"

The println results in the following message to the job console.

First common parent for "S253" and "S592" is Mountain_Div

That is it boys and girls.  In 6 lines of scripting (and it could be less as some variables are introduced to clearly articulate the process and methods), the first common parent is identified.  The most difficult part would be the business logic to accomplish the actual business requirement.

All Done

Now that you know how to get the first common parent, this can be used to dynamically create the appropriate Essbase calculations to provide all the functionality that is needed.  If you like this, share it with the community.  Post comments if you have any questions.  I love getting feedback.




Adventures in Groovy – Part 36: Manipulating Dates

Manipulating dates is not something you may think is that useful, but there are a number of things that we have done in the past that are slow performing in Essbase, and others that were not possible or you may not have ever thought of.  This is going to walk visitors through ways to manipulate dates for all kinds of uses.  Hopefully it inspires some ideas for you to make your application a little more polished.

This Is Nice, But When Would It Be Used?

First, if logic is done in Groovy for things like WFP, comparing dates is frequently used to calculate benefits.  How many months has the person been employed?  Did they get a promotion in the last 12 months?  Calculating differences in dates is extremely simple in Groovy and applying more logic around it is often quicker to write and faster to process in Groovy than it is in Essbase.

Capex is also a place that is date dependent to calculate depreciation and other asset related accounts.  There are also some things we can do to make the user experience better, like giving the option to just enter a number of months rather than get the end date based on the start date.

I can’t say you will use it in every build you are involved in, but when it is required, these techniques will undoubtedly make your life a little easier.

Creating And Formatting Dates

Most of the methods require a date object.  It can be created by getting the current date and time or setting the date and time from a string or concatenation of variables.

// This will create a date object with the current time
def date = new Date()
// To create the object with a specific date/time, follow this logic
def date = new Date().parse('yyyy/MM/dd', '1973/07/09')

A real-world example might be using the members in the POV to create a date.  Since getting the member values from RTPs or looping through cells has already been covered, this will assume the method you use returns the following values.  The parse method can hold just a year, month, or year and month, or year and moth and day, for example.  The two parameters must match.

// drop the first two characters
def sYear = "FY18".drop(2)
def sMonth = "Jan"
def sDay = "1" // The day is likely irrelevant, so 1 will be used as a default
def date = new Date().parse('yyyy/MM/dd', "$sYear/$sMonth/$sDay")
// we are using yy because the date is only two years
// we are using MMM because we have a month abbreviation.  If we had a number, it would be MM
println date
def date1 = new Date().parse('yy/MMM', "$sYear/$sMonth")
println date1

// Both return 'Mon Jan 01 00:00:00 EST 2018'

println date1.format('M/d/yy')
// Returns 1/1/18

Here are some more formatting examples.  Depending on the need, some of these may be useful.  There are more, but I think these are the formats you might use most often.

Pattern Output
dd.MM.yy 30.06.09
yyyy.MM.dd G ‘at’ hh:mm:ss z 2009.06.30 AD at 08:29:36 PDT
EEE, MMM d,yy Tue, Jun 30, ’09
h:mm a 8:29 PM
H:mm 8:29
H:mm:ss:SSS 8:28:36:249
K:mm a,z 8:29 AM,PDT
yyyy.MMMMM.dd GGG hh:mm aaa 2009.June.30 AD 08:29 AM

Often times we need the month, day, or year of a date object.  This is another simple thing to do.

date = new Date().parse('yyyy/MM/dd', '1973/07/09')
println date[Calendar.YEAR]
// Returns 1973
println date[Calendar.MONTH]
// Returns 7 
println date.getAt(Calendar.DATE)
// Returns 9

Manipulating Dates

The first thing that is very common is the need to do is some basic changes to the year and month.

Often time the year we have in Planning is prefixed with FY.  A simple thing to remove the FY is to drop the first two characters.

def sYear = "FY18"
println sYear.drop(2)
// Returns 18

Next is months.  There are a couple of things that are typical requests.  One is to pad the number with a 0.  The other is to convert numbers to the month name, and visa versa

date = new Date()
println.date[Calendar.MONTH].padLeft(2,'0')
println date1.format('MM')
// These will return the numeric month
// It will also pad it with zeros and return 2 characters
// Often times we need a two digit string rather than an integer

//If we have the number and need the name
$sMonth = 1
def date = new Date().parse('M', $sMonth)
println date.format('MMM')
// Returns Jan
println date.format('MMMM')
// Returns January

Increasing or decreasing a date by a predefined number of days is also simple and often used.

date = new Date()
date.plus(1)  // Returns tomorrow
date.minus(1)  // Returns yesterday

Increasing months and years is a little more complicated, but still easy to accomplish.

d = new GregorianCalendar() 
d.setTime(new Date().parse('yyyy/MM/dd', '2018/07/09'))  // sets to the current date/time
d.add(Calendar.MONTH,5)  // Increases the date by 5 months
println d.getTime().format('MM/dd/yyyy')
// Returns 12/9/2018

Lastly, getting the difference between two days might be something required.

In days

date = new Date().parse('yyyy/MM', '2018/07')
date1 = new Date().parse('yyyy/MM', '2019/08')
println (date..<date1).size()  // Returns 31

In Months

date = new Date().parse('yyyy/MM', '2018/07')
date1 = new Date().parse('yyyy/MM', '2019/08')
println ((date1[Calendar.MONTH] - date[Calendar.MONTH]) + ((date1[Calendar.YEAR] - date[Calendar.YEAR])*12))
// Returns 13

Calling It A Day

I can see prompting a user when adding a new asset to just request a start date and how many months the useful life would be, rather than asking for two dates.  When adding seasonal resources, it would be nice to ask for a start date and the number of days they will be needed, in some cases.

The date manipulation is something we all do, and I can tell you, doing it in Groovy is way simpler than doing in in Essbase with all the concatenations required.

If you have a good example of use case, please share!

 




Adventures in Groovy – Part 35: Error Trapping Groovy Calculations

There has not been alot of troubleshooting discussed in the adventures series.  Just like with most coding languages, you can gracefully handle errors resulting from actions (like divide by 0) and return descriptive information to the users and administrators in the job console.  There are several benefits that I see.

  • As previously stated, since the error is accounted for, the user doesn’t get a message that shows a failure with no context.
  • The error object will provide more information about what happened and what should be done to fix it in the future.
  • Predefined actions can take place since the error doesn’t interrupt the script, like returning an error message that tells the user to contact the administrator with an action

Error Handling Introduction

Try / catch / finally is a concept most development languages have.  Conceptually, you “try” some group of commands and “catch” any errors that might happen.  If you “catch” an error, you account for it by doing something.  “Finally,” you perform any closing actions.

try {
  def arr = 1/0
} catch(Exception ex) {
  println ex.toString()
  println ex.getMessage()
  println ex.getStackTrace()
}finally {
   println "The final block"
}

In this case, ex.toString() prints

java.lang.ArithmeticException: Division by zero

ex.getMessage() prints

Division by zero

and ex.getStackTrace()

[java.math.BigDecimal.divide(Unknown Source), org.codehaus.groovy.runtime.typehandling.BigDecimalMath.divideImpl(BigDecimalMath.java:68), org.codehaus.groovy.runtime.typehandling.IntegerMath.divideImpl(IntegerMath.java:49), org.codehaus.groovy.runtime.dgmimpl.NumberNumberDiv$NumberNumber.invoke(NumberNumberDiv.java:323), org.codehaus.groovy.runtime.callsite.PojoMetaMethodSite.call(PojoMetaMethodSite.java:56), org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:48), org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:113), org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:125), ConsoleScript11.run(ConsoleScript11:2), groovy.lang.GroovyShell.runScriptOrMainOrTestOrRunnable(GroovyShell.java:263), groovy.lang.GroovyShell.run(GroovyShell.java:387), groovy.lang.GroovyShell.run(GroovyShell.java:366), groovy.lang.GroovyShell.run(GroovyShell.java:170), groovy.lang.GroovyShell$run$0.call(Unknown Source), groovy.ui.Console$_runScriptImpl_closure18.doCall(Console.groovy:1123), groovy.ui.Console$_runScriptImpl_closure18.doCall(Console.groovy), sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method), sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source), sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source), java.lang.reflect.Method.invoke(Unknown Source), org.codehaus.groovy.reflection.CachedMethod.invoke(CachedMethod.java:98), groovy.lang.MetaMethod.doMethodInvoke(MetaMethod.java:325), org.codehaus.groovy.runtime.metaclass.ClosureMetaClass.invokeMethod(ClosureMetaClass.java:294), groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:989), groovy.lang.Closure.call(Closure.java:415), groovy.lang.Closure.call(Closure.java:409), groovy.lang.Closure.run(Closure.java:496), java.lang.Thread.run(Unknown Source)]

The script in the final block is also written out.  It is intended for cleanup and tasks that run at the end of a script regardless of whether there is an error or not.

Handling Errors Uniquely

The catch command can be replicated to handle errors uniquely.  Let’s expand on the example above.  Assume the variable is coming from an RTP or cell value.  The following has a catch for a specific error.  The java.lang.ArithmeticException is equal to the output of ex.toString().  There are probably thousands of errors, if not more.  The easiest way for me to grab these is to use the ex.toString() and see what it produces.  I have no desire to remember or learn them all!

The following will do something different for the divide by zero error than all other errors.

try 
  {
  def denominator = 0
  println 1/denominator
  } 
catch(java.lang.ArithmeticException ex) 
  {
  println ex.getMessage()
  println "an action should be taken to account for the error"
  }
catch(Exception ex) 
  {
  println ex.toString()
  println ex.getMessage()
  println ex.getStackTrace()
  }
finally 
  {
  println "The final block"
  }

Finishing Up

This requires a little more effort, but once you get used to doing this, it can be reused.  I hear the argument that if you account for every possible situation, you don’t need to error trap.  That is true, and if you are smart enough to account for everything that can go wrong, don’t include this in your Groovy calculation.  I would argue that simple calculations probably don’t need this level of error handling, but more complex logic might be a candidate.  The example above could obviously be handled with an if statement, but put it in context.  It is used to illustrate the concept of try / catch / finally.




Planning Data Loads: com.hyperion.planning.InvalidMemberException vs. java.lang.RuntimeException

I had a very interesting thing happen today that tripped me up.  When loading data to a PBCS database through Planning (not as an Essbase file), I had two types of errors.  I have never seen this before and this could cause some serious heartburn for administrators and possibly waste a ton of time trying to resolve.  So, I am sharing for the sake of saving somebody some effort.

One Error, Two Messages

The error first is the typical error due to a member not being in the application.  com.hyperion.planning.InvalidMemberException: The member CTR_123 does not exist for the specified cube or you do not have access to it.  We have all seen this.  CTR_123 was not in the hierarchy.  Once it was added and the data was reloaded the issue was resolved.

The second issue was another error I have seen before, but I haven’t seen this in PBCS.  java.lang.RuntimeException: Not all dimensions were specified.  Normally, this is related to having a file correctly formatted in but having a member from one dimension in the wrong column, or having a column that is null.

As I often do, I created a Smart View retrieve and added the members in the load file one at a time.  When I found the member that caused a retrieve error, I went into the dimension editor to search for it.  To my surprise, it was there!  What?  But, when I looked at the properties, it was not valid for the application I was trying to load the data to.  This might have been overlooked by mere mortal (wink), but once enabled for the application in question, the load error was resolved.

Conclusion

So, why the two error types?  Why do we get two error types for the same error (the member doesn’t exist in the database)?  I can only assume since I loaded this through Planning, it tripped up on the fact that the member was in Planning, but not in the specific database I was trying to load.  If I loaded this as an Essbase file, as expected, I got the same error for both lines, member not found.

Hopefully this saves you some time.  If you have ever come across something similar, please share with the community.  These things are normally the things you find after a 12 hour day and you spend another 4 trying to figure it out.

Lastly, please enjoy a safe holiday and remind yourself how lucky you are and try to be thankful for the things you have and not be frustrated about the things you don’t.  Gobble Gobble!




Adventures in Groovy – Part 33: Mapping Members Between Plan Types

Groovy collections are used all throughout the ePBCS API.  If you are not familiar with collections, you may want to take a look at Adventures in Groovy – Part 27: Understanding Collections before you continue.  Maps, which are a type of collection, are very useful when moving data between different applications that have different member names representing the same data.  In a the example below, data is moving from a product revenue cube to a financial cube.  In the detailed cube, the member names are more descriptive, like Net Sales.  In the financial application, the same data is a true account number from the GL, and names 42001.  Mapping data between these two can easily be done with Groovy maps.

Introduction

There are two components to understanding the use of these maps.  First, the map must be defined for use.  The construction of the map is a delimited list of items.  Each of the items is made up of an key and a value.  These are separated by a colon.

//set account map
def acctMap = ['Units':'Units',
               '42001-Product Sales':'Net Sales',
               '50001-Cost of Sales':'COGS',
               '50015-Write-offs':'Write-offs',
               '56010-Chargebacks':'Customer Satisfaction Discount',
               '50010-Sales and Discounts':'Sales and Discounts',
               '56055-Overstock Discount':'Overstock Discount',
               '56300-Customer Satisfaction Discount':'Customer Satisfaction Discount',
               '56092-Multi-Purchase Discount':'Multi-Purchase Discount',
               '56230-Open Box Discount':'Open Box Discount',
               '56200-Damage Container Discount':'Damage Container Discount',
               '56205-Damaged Box Discount':'Damaged Box Discount',
               '56090-Group Purchase Discount':'Group Purchase Discount']

The second piece is retrieving the mapped value.  The value on the left of the colon is referenced and the value on the right will be returned.  The following would return 56230.

[acctMap.get("56230-Open Box Discount")]

A fully vetted example follows of moving data from one database to several others.  The function’s use is embedded in a loop, so rather than a hard coded value, the member of the account dimension is used as the accounts (rows in the form) are being iterated.  It looks like this.

[acctMap.get(it.getMemberName('Account'))]

Working Use Case

The map above is used in several places for several reasons.  First, the map is created.  Second, the map is iterated and the key is used to create a data grid for all the values that will be copied, or synchronized, to the destination cube.  Third, the map is used to lookup the converted value to create the grid connected to the destination.  this is a complete working example.  The items in red are specific to the map and its use.

//Dimension employeeDim = operation.application.getDimension("Account")

//****************************************************************************
// Data Movement between Apps
//****************************************************************************

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

def lstProducts = []
operation.grid.dataCellIterator({DataCell cell -> cell.edited}).each{ 
 lstProducts.add(it.getMemberName("Product"))
}

String strProducts = """\"${lstProducts.unique().join('","')}\""""
println "data push running for " + strProducts

if(operation.grid.hasSmartPush("Prod_SmartPush") && lstProducts)
 operation.grid.getSmartPush("Prod_SmartPush").execute(["Product":strProducts,"Currency":'"USD","Local"'])

//set account map
def acctMap = ['Units':'Units',
               '42001-Product Sales':'Net Sales',
               '50001-Cost of Sales':'COGS',
               '50015-Write-offs':'Write-offs',
               '56010-Chargebacks':'Customer Satisfaction Discount',
               '50010-Sales and Discounts':'Sales and Discounts',
               '56055-Overstock Discount':'Overstock Discount',
               '56300-Customer Satisfaction Discount':'Customer Satisfaction Discount',
               '56092-Multi-Purchase Discount':'Multi-Purchase Discount',
               '56230-Open Box Discount':'Open Box Discount',
               '56200-Damage Container Discount':'Damage Container Discount',
               '56205-Damaged Box Discount':'Damaged Box Discount',
               '56090-Group Purchase Discount':'Group Purchase Discount']


Cube lookupCube = operation.application.getCube("rProd")
DataGridDefinitionBuilder builder = lookupCube.dataGridDefinitionBuilder()
builder.addPov(['Years', 'Scenario', 'Currency', 'Version', 'Company','Store_Type','Department','Source','Product','View'], [['&v_PlanYear'], ['OEP_Plan'], ['Local'], ['OEP_Working'], [sCompany],['Store_Type'],['Total_Department'],['Tot_Source'],['Tot_Product'],['MTD']])
builder.addColumn(['Period'], [ ['ILvl0Descendants("YearTotal")'] ])
for ( e in acctMap ) {
 builder.addRow(['Account'], [ [e.key] ]) 
}
DataGridDefinition gridDefinition = builder.build()

// Load the data grid from the lookup cube 
DataGrid dataGrid = lookupCube.loadGrid(gridDefinition, false) 
def povmbrs = dataGrid.pov
def rowmbrs = dataGrid.rows
def colmbrs = dataGrid.columns
def tmpColMbrs = []

//Fin Grid Setup
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")
finGrid.addPov('&v_PlanYear','OEP_Plan','Local','OEP_Working',sCompany,'Prod_Model')
rfinGrid.addPov('&v_PlanYear','OEP_Plan','Local','OEP_Working',sCompany,'Prod_Model','MTD')
def colnames = colmbrs[0]*.essbaseMbrName

String scolmbrs = "'" + colnames.join("', '") + "'"
finGrid.addColumn(colmbrs[0]*.essbaseMbrName as String[])
rfinGrid.addColumn(colmbrs[0]*.essbaseMbrName as String[])

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)
}
DataGridBuilder.Status status = new DataGridBuilder.Status()
DataGridBuilder.Status rstatus = new DataGridBuilder.Status()
DataGrid grid = finGrid.build(status)
DataGrid rgrid = rfinGrid.build(rstatus)

finCube.saveGrid(grid)
rfinCube.saveGrid(rgrid)

Finishing Up

This is a relatively simple concept and not terribly difficult to implement.  It is also something most will benefit from when synchronizing data with the dataGridBuilder.  Have something to add?  Post a comment and I will get back to you promptly.




Adventures in Groovy – Part 28: The DataGridBuilders

Gridbuilders are the link to moving data from BSO to ASO, and ASO to ASO.  Gridbuilders can also be used to save calculated results that are executed directly in Groovy.  If it jumped into your head, yes, you can completely bypass the Essbase calculation engine.  I have a coworker that has done exactly that – everything in Groovy and had all the forms connected directly to ASO!  There is an example of this in my presentation at KScope, Last Minute ODTUG Planning Sourvenirs.  Before we get too far ahead, back to the basics.

Welcome to Grids

Creating grids is the same as creating a Smart View report.  They have POVs, column and row headers, and of course, the data grid.

Gridbuilders come in two forms.

  1. The DataGridDefinitionBuilder is used to read data.
  2. The DataGridBuilder is used to submit data.

The later can use functions like ILvl0Descendants and can suppress missing rows.  Both can return status information about the volume of data the grid retrieves/submits.


The DataGridDefinitionBuilder

The first grid discussed will be the reader grid.  This grid can be used in processes to get currency tables, read data to be used in calculations, or make decisions on the logic to execute.  It can also be used to be the source of data moved to another location or plan type.  Try not to make this too complicated.  It really is exactly the same as a Smart View adhoc retrieve.

The first step is to create a connection to the cube, or plan type, and initiate the GridDefinitionBuilder object.

// Setup the connection to the plan type
Cube cube = operation.application.getCube("Plan1")
//Create the grid definition builder
DataGridDefinitionBuilder builder = cube.dataGridDefinitionBuilder

Now that the object is created and connected to a source, the next step is to setup the grid.  This can be done in any order that makes sense to you because it isn’t used until the grid is built.  Personally, I start with the POV.  If you understand collections, particularly lists, this will seem pretty simple.  If you don’t know what Groovy lists are, read Part 27: Understanding Collections.  Creating the POV requires two parameters.  The first is the list of dimensions.  The second is the list of members in the corresponding dimensions.  The first parameter is one list (a comma delimited list inside brackets).  The second parameter is a list of lists.  I will explain why a little later.  Each sub-list has only one member, but it is still a list.  The format of this is [[member1],[member2]].  So, it is a comma delimited list of embedded lists.  It is important to understand this.  If you are just replicating this example, you are doing yourself an injustice because you will struggle when you start throwing in variables and trying to create efficient ways to create these based off of a POV of the grid the user interacts with.

// Add the POV – Dimensions and a collection of the members in those dimensions
builder.addPov(['Years', 'Scenario', 'Currency', 'Period', 'Version', 'Entity’], [['FY16'], ['Current'], ['Local'], ['BegBalance'], ['BU Version_1'], ['No Entity’]])

The next thing I do is create the columns.  Again, understanding lists is critical to become efficient with these classes and methods.  I will sound like a broken record, but I wish I had somebody tell me this 6 months ago.  The column requires the same two parameters in the same formats.  The first is a list of the dimensions.  The second is a list of the lists of members associated to those dimensions.  The difference from the POV is that multiple members can be added to each dimension.  It might be more clear why the second parameter is a list of lists, now.  If it wasn’t, distinguishing which members related to which dimensions would be a jumbled mess with just a comma delimited list of members.  Just like with Smart View, columns can have multiple headers.

// Add the columns – 2 parameters, collection of dimensions and 
// collection of collections of members in those dimensions
builder.addColumn(['Years', 'Account’],
[['Children(Years)'],['Min Salary','Max Salary','Min Bonus','Max Bonus']])

The rows are identical to the columns, so I won’t repeat the narrative above.

// Add rows no data - in this class as it is retrieving data
builder.addRow(['Grades'], [ ['ILvl0Descendants("Grades")']])

Before we proceed, the addColumn and addRow methods can be replicated multiple times (you know this as segments in developing data forms).

Now that the grid is constructed, execute the build method to create the object with all its properties.

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

Finally, load the grid, which is the equivalent of refreshing a Smart View template.

// Load a data grid from the specified grid definition and cube
DataGrid dataGrid = cube.loadGrid(gridDefinition, false)

At this point, the grid acts just like a data form.  All the methods and classes are available just as they are on forms.  These grids can be iterated, just like web forms.

The DataGridBuilder

The DataGridBuilder is the class used to submit data to the database.  The construction and use is similar to the DefinitionBuilder, except the use of functions is not relevant, and rather than loading data to the grid, the data is saved to the database.

// Setup the connection to the plan type
Cube cube = operation.application.getCube("Plan1") 
DataGridBuilder builder = cube.dataGridBuilder("MM/DD/YYYY")

What we are creating below would look like this if it was done in Smart View.

Setting up the POV is similar to above, except this time there is no requirement to pass a list.  Also, the identification of the dimensions is not necessary.  All that is needed is a comma delimited list of members.

// Setup the grid POV, Rows, and Columns
builder.addPov('Salary', 'Current', 'Local', 'BU Version_1’)

Setting up the columns is also slightly different.  The dimensions are again missing and the members are all that is required.  Also, there is a line for each dimension rather than passing it in lists as multiple parameters.  This is exactly the same as setting up a Smart View template.  Each column has to be set.  The example below has 3 columns made up of the year and period dimensions.

builder.addColumn('FY16', 'FY16', 'FY16’)
builder.addColumn('Jan', 'Feb', 'Mar’)

The rows are similar in that there is a slight difference from the DefinitionBuilder, but it does require two parameters.  Each is a list.  The first includes the members of the dimensions in the row headers.  The second are the corresponding values to the column headers above.  The following adds the members from the department and employee, and sets the values for Jan through March of FY16.

// Add rows to the grid
builder.addRow(['Department 1', 'Employee 1'], [30000, 30000, 30000]) 
builder.addRow(['Department 5', 'Employee 2'], [40000, 40000, 40000])
builder.addRow(['Department 1', 'Employee 3'], [30000, 30000, 30000])

Once the grid is laid out, and this can be done before the grid is defined, a status object is created to hold the results of the submission of data.

// Create a status class to hold the results
DataGridBuilder.Status status = new DataGridBuilder.Status()

At this point, the grid can be built.  The difference with this object is that the status object is passed.  That status object can be used to evaluate if records will be submitted and if any will be rejected.

// Build the grid – basically a refresh/retrieve
DataGrid grid = builder.build(status)
println("Total number of cells accepted: status.numAcceptedCells")
println("Total number of cells rejected: status.numRejectedCells")
println("First 100 rejected cells: status.cellsRejected")

Finally, save the grid to submit the data to the database.

// Save the data to the cube
cube.saveGrid(grid)

Finish Up

This all seems pretty simple.  I can’t say it enough.  Spend some time understanding Groovy collections, especially how to manipulate them.  I guarantee the time you spend will be time well spent.  I will be posting another article on how to diagnose issues and troubleshoot grid errors, whether it be grids that won’t return results, or grids that won’t save data.  Hope this was valuable!

 




Adventures in Groovy – Part 27: Understanding Collections

Because so many methods in PBCS require parameters that are maps and lists, it is very important to your productivity and effectiveness to understand, use, and be able to manipulate collections.  Collections include lists, maps, and ranges.  These are based on Java collection types and include a ton of useful methods.  This article will cover lists and maps.

  • Lists are objects that are embedded in brackets and separated by commas, like [Jan, Feb, Mar].
  • Maps are also known as associative arrays and are similar to lists.  Each element is separated by a colon, like  [Revenue:678979, Expense:34387].

Not to jump too far ahead, but these objects aren’t limited to holding strings.  They can hold numeric values, lists inside of lists, maps inside of lists, or really almost any combination of these objects.

Time Well Spent

There is a lot of information in this post.  Before you page down, or read a little and get bored, I highly recommend you invest the time and understand the content.  The feedback from everybody that I gave this information to prior to this post said it made a huge impact and really cleared up a lot of questions they had.

A Little About Lists and Maps And Why Understanding It Is Important

Lists and Maps are used in a number of places that are frequently used in Groovy calculations.  Gridbuilder and DataMap objects are dependent on these objects.  You may be building the POV by embedding strings into what is actually a list without even knowing it. I have done this in previous examples to keep it simple and explainable.

String sYear = '"' + operation.grid.pov.find{it.dimName =='Years'}.essbaseMbrName + '"'
String sScenario = '"' + operation.grid.pov.find{it.dimName =='Scenario'}.essbaseMbrName + '"'
String sCompany = operation.grid.getCellWithMembers().getMemberName("Company")
String sChannel = '"' + operation.grid.pov.find{it.dimName =='Channel'}.essbaseMbrName + '"'
String sMaterialGroup = '"' + operation.grid.pov.find{it.dimName =='Material_Group'}.essbaseMbrName + '"'

operation.application.getDataMap("[name of map]").execute(["Company":sCompany,"Channel":sChannel,"Material_Group":sMaterialGroup,"Vendor":sVendor,"Scenario":sScenario,"Version":"OEP_Working","Years":sYear,"Source":"NBF_Source","Currency":"Local,USD","Account":accountPush],true)

builder.addPov(['Years', 'Scenario', 'Version', 'Company','Channel','Material_Group','Source','Vendor','View'], [sYear.tokenize(), sScenario.tokenize(), ['OEP_Working'], [sCompany],['Tot_Channel'],['Total_Material_Group'],['Tot_Source'],['Tot_Vendor'],['MTD']])

Since the POV and DataMap include lists and maps, building them can also be done by pulling the grid POV, which is a map.   Lists can be extracted from the map.  There are some additional members that are required that are not in the Web Form, so a few dimensions have to be added.

Hopefully the following example will emphasize the importance of understanding these objects and the methods associated to them.  The above is not a ton of work, but replicating this hundreds of times and having the foresight to think about how functions can be reused  highlights why understanding lists, and understanding Groovy, will help you provide solutions that are more manageable to maintain.  The following replicates what was above in a more effective and readable way, in my opinion.  It may seem more complex, but give it a chance.  I think you will find it easier once you understand it, and less work to develop Groovy calculations.

Example Setup

Assume the user changes Units for Jan, Feb, and Mar for NBF1 in the Data Form below.  Net Sales is impacted as it is calculated.  The business rule that runs calculates currency, so that will also have to be included in the DataMap and GridBuilder.

The POV for the form looks like this.

Replicating The DataMap Using A Groovy Map Object

Executing a Data Map uses a Groovy map built from the Data Form POV.  Currency has to be altered.  The edited rows and columns are appended.  For a complete explanation of how to get the edited cells, read Part 3: Acting On Edited Cells.

// Create a map from the data form POV
def povMap = [:]
operation.grid.pov.each{povMap[$it.dimName] = $it.essbaseMbrName}
// Update Currency to include Local AND USD 
povMap['Currency'] = 'Local,USD' 
// Add the vendor and accounts that have been changed 
povMap2.put('Period', sPeriods)
povMap2.put('Account', accountPush)
povMap2.put ('Vendor', sVendor)
// Execute the data map with all the dimensional overrides
operation.application.getDataMap("[name of generic map]").execute(povMap,true)

Let’s walk through the results of the script.  The povMap when created from the Data Form starts with

  • [Version:OEP_Working,
  • Source:NBF_Source,
  • Currency:Local,
  • Company:BILB,
  • Years:FY18,
  • Scenario:OEP_Plan,
  • Material_Group:mI03,
  • Channel:c01]

Once this map is created, there are some edits made.

  1. Currency is changed from Local to Local, USD.
  2. Periods is added as Jan, Feb, Mar.
  3. Account is added as Units, Net_Sales.
  4. Vendors is added as NBF1.

This method requires fewer lines, is much easier to repeat, is easier to read, and can be reused for other functions.

Replicating The GridBuilder POV Using A Groovy Map Object

Assume the data is now going to be replicated to another cube using a GridBuilder.  In the example available in Part 21: Real Time Data Movement (Getting REALLY Groovy), the data is moved to a P&L cube at a summary level and doesn’t include the channel, material group, and vendor dimensions.  A new Groovy map can be created (which we will do here), or we can alter the existing one above.  The source grid would then need to pull data at the total of all these dimensions.  Currency and Period need to be removed as they are in the columns.  Account will be removed and placed in the rows.

def povGridMap = [:]
operation.grid.pov.each{povGridMap[it.dimName] = it.essbaseMbrName}

// Remove dimensions that will be in the rows and columns
povGridMap.remove('Currency')

// Add View dimiension
povGridMap['View'] = 'MTD'

// Change the dimensions that need to pull totals
povGridMap['Channel'] = 'Total_Channel'
povGridMap['Material_Group'] = 'Total_Material_Group' 
povGridMap['Vendor'] = 'Total_Vendor' // Another way to add a new element to the map

builder.addPov(povGridMap.keySet() as List, povGridMap.values().collect{[it]} as List)
// the reason this requires the as List notation is because the addPOV method requires 
// the parameter to be a list type
// From the Java Docs:
// void setPov(java.util.List<java.lang.String> dimensions,
//             java.util.List<java.util.List<java.lang.String>> members)

Let’s walk through the results of the script.  The povMap when created from the Data Form starts with

  • [Version:OEP_Working,
  • Source:NBF_Source,
  • Currency:Local,
  • Company:BILB,
  • Years:FY18,
  • Scenario:OEP_Plan,
  • Material_Group:mI03,
  • Channel:c01]

The edits are then made and the result is

  • [Version:OEP_Working,
  • Source:NBF_Source,
  • Company:BILB,
  • Years:FY18,
  • Scenario:OEP_Plan,
  • Material_Group:Total_Material_Group,
  • Channel:Total_Channel,
  • Vendor:Total_Vendor]

Moving On

Hopefully the difference in the two strategies inspires you to learn a little more about the objects and methods below.

The remainder of this article shows examples that will be extremely useful, and some that will just give you some insight to what is possible.

Lists

Creating And Editing Lists

Lists can be created and referenced a number of ways.  Empty lists can be created.

def emptyList = []

Lists can be created by adding the elements.

Def q1List = [‘Jan’,’Feb’,’Mar’]

Lists can be altered by adding new elements.

def q2List = [‘Apr’]
q2List << (‘May’)
q2List.add(‘Jun’)

or

q2List.addAll(['Apr', 'May', 'Jun'])

They can even be added at specific places.

def q2List = ['Apr']
q2List << ('Jun')
q2List.put(1,'May') // which places it after Apr and before Jun

Elements can be edited based on location.

q2List.set(1,'May') // second item
q2List[-2]'May' // go back 2 from end
q2List[1]'May' / second item

Elements can be removed from lists.

q2List << (‘Jul’)
q2List.remove('Jul')

or

q2List.removeIf{it == 'Jul'}

or

 ['a','b','c','b','b'] - ['b','c'] // result is ['a']
Iterating Lists

Iterating on elements of a list is usually done by using one of the following.

  • each
  • eachWithIndex (same as each but include the index of the element)
['Jan','Feb','Mar'].each { month ->
    println "Item: $month"
}
// With the index of the element
['Jan','Feb','Mar'].eachWithIndex { month, index ->
    println "Item: $month is index $index"
}

The list can be altered while iterating, too.  This doesn’t update the list.  It only produces a new list.

println [1, 2, 3].collect { it * 2 }  //would produce [2, 4, 6]
Manipulating lists
Filtering and searching

There are tons of filtering and searching options.  The following are examples, but it isn’t even close to an exhaustive list.  Hopefully it will inspire some thought as to how you can use these methods.

// find 1st element matching criteria
[1, 2, 3].find { it > 1 } // results in 2

// find all elements matching criteria
[1, 2, 3].findAll { it > 1 } // results in [2, 3]

// find index of 1st element matching criteria
['a', 'b', 'c', 'd', 'e'].findIndexOf {it in ['c', 'e', 'g']} // results in 2

['a', 'b', 'c', 'd', 'c'].indexOf('c') // index returned (2)
['a', 'b', 'c', 'd', 'c'].indexOf('z') // index -1 means value not in list
['a', 'b', 'c', 'd', 'c'].lastIndexOf('c') // 4 is returned

[1, 2, 3].every { it < 5 }               // returns true if all elements match the predicate

![1, 2, 3].every { it < 3 }

[1, 2, 3].any { it > 2 }                 // returns true if any element matches the predicate

![1, 2, 3].any { it > 3 }

[1, 2, 3, 4, 5, 6].sum() == 21                // sum anything with a plus() method
['a', 'b', 'c', 'd', 'e'].sum() // 'abcde'
[['a', 'b'], ['c', 'd']].sum() // ['a', 'b', 'c', 'd']

// an initial value can be provided
[].sum(1000) //  1000

[1, 2, 3].sum(1000) //  1006

[1, 2, 3].join('-') //  '1-2-3'

def list = [9, 4, 2, 10, 5]
list.max() // 10
list.min() // 2

// we can also compare single characters, as anything comparable
assert ['x', 'y', 'a', 'z'].min() // 'a'

// we can use a closure to specify the sorting behavior
def list2 = ['abc', 'z', 'xyzuvw', 'Hello', '321']
list2.max { it.size() } //  'xyzuvw'
list2.min { it.size() } //  'z'

['a','b','c'].contains('a')      // true

[1,3,4].containsAll([1,4])       // true

[1,2,3,3,3,3,4,5].count(3)  // 4
Sorting

Groovy offers a variety of options to sort lists, from using closures to comparators.

[6, 3, 9, 2, 7, 1, 5].sort() // [1, 2, 3, 5, 6, 7, 9]
def list = ['abc', 'z', 'xyzuvw', 'Hello', '321']
list.sort {it.size()} // ['z', 'abc', '321', 'Hello', 'xyzuvw']

def list2 = [7, 4, -6, -1, 11, 2, 3, -9, 5, -13]

Maps

Literals

In Groovy, maps (also known as associative arrays) can be created using the map literal syntax: [:].  Maps are use to create associations between two or more elements.  They can be used to lookup values like currency rates, map accounts from one plan type to another, and a host of other things.  Many of the API methods require maps to be passed.

def map = [name: 'Gromit', likes: 'cheese', id: 1234]
map.get('name')   //  'Gromit'
map.get('id')   // 1234
map['name']   // 'Gromit'
map['id'] == 1234

def emptyMap = [:]
emptyMap.size()   // 0
emptyMap.put("foo", 5)
emptyMap.size()   // 1
emptyMap.get("foo")   // 5
Map Notation

Maps also act like joins so you can use the property notation to get/set items inside the Map as long as the keys are strings which are valid Groovy identifiers:

def map = [name: 'Gromit', likes: 'cheese', id: 1234]
map.name   // 'Gromit'  -    can be used instead of map.get('name')
map.id   // 1234

def emptyMap = [:]
emptyMap.size()   // 0
emptyMap.foo = 5
emptyMap.size()   // 1
emptyMap.foo   // 5
Iterating Maps

Maps makes use of the each and eachWithIndex methods to itarate through maps, just like Lists.

def map = [
        Bob  : 42,
        Alice: 54,
        Max  : 33
]

// Loop through each item
map.each { entry ->
    println "Name: $entry.key Age: $entry.value"
}

// add an index
map.eachWithIndex { entry, i ->
    println "$i - Name: $entry.key Age: $entry.value"
}

// Alternatively you can use key and value directly
map.each { key, value ->
    println "Name: $key Age: $value"
}

// Key, value and i as the index in the map
map.eachWithIndex { key, value, i ->
    println "$i - Name: $key Age: $value"

Manipulating Maps

Adding/Removing Elements

Adding an element to a map can be done either using the put method, the subscript operator or using putAll.

def defaults = [1: 'a', 2: 'b', 3: 'c', 4: 'd']
def overrides = [2: 'z', 5: 'x', 13: 'x']

def result = new LinkedHashMap(defaults)
result.put(15, 't')
result[17] = 'u'
result.putAll(overrides) // [1: 'a', 2: 'z', 3: 'c', 4: 'd', 5: 'x', 13: 'x', 15: 't', 17: 'u']

Removing all the elements of a map can be done by calling the clear method:

def m = [1:'a', 2:'b']
println m.get(1) //  'a'
m.clear()
Filtering and searching

It is useful to search and filter maps, and here are some examples of this functionality.

def people = [
    1: [name:'Bob', age: 32, gender: 'M'],
    2: [name:'Johnny', age: 36, gender: 'M'],
    3: [name:'Claire', age: 21, gender: 'F'],
    4: [name:'Amy', age: 54, gender:'F']
]

def bob = people.find { it.value.name == 'Bob' } // find a single entry
def females = people.findAll { it.value.gender == 'F' }

// both return entries, but you can use collect to retrieve the ages for example
def ageOfBob = bob.value.age
def agesOfFemales = females.collect {
    it.value.age
}
// ageOfBob == 32, agesOfFemales == [21,54]

def agesOfMales = people.findAll { id, person -> person.gender == 'M'}.collect { id, person ->
    person.age
}
// agesOfMales == [32, 36]

// `any` returns true if any entry matches the predicate
people.any { id, person -> person.age == 54 }

Conclusion

There is a lot of information and examples here.  What I hope you walk away with is an understanding of what a list and a map is, most importantly.  Understanding how to use these in the API is really important, and manipulating them will save you a ton of time and unnecessary code.




Adventures in Groovy – Part 20: Groovy On-Premise vs. Groovy Cloud

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.