Tag Archive for: Hyperion

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!

 

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. Read more

 

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. Read more

 

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. Read more

 

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.

 

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. Read more

 

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.

 

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. Read more

 

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. Read more

 

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.