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!




Drill Through to Data Management and Stay In Excel

There is a new, and often requested, option added to Smart View.  If you use drill through to Data Management (PBCS) or FDMEE (On Prem), download the most recent release of Smart View.  We, as users, now have the option to change where the result of our drill through queries is returned.  Users can either be asked where the result should be displayed, have it displayed in your browser, or (drum roll) have another tab created that holds the results in Excel.

Change Your Option

This isn’t a complicated or drawn out explanation, but it is sexy!  To change where the results are displayed, go to your Smart View ribbon and click Options to open the dialogue.  Select the Advanced tab on the left and scroll down just a tad.  You will see an option for Drill-Through Launch.  The 3 options previously mentioned are available.  This removes one of the biggest user frustrations regarding drill-through reporting and will surely make a lot of people happy.

The Proof Is In The Pudding

It works just as you would expect, but here is the proof.  When you open a retrieve and connect to the application, right click on a cell.  Click on Drill-through as you always have.

If the data has more detailed data available, a new worksheet will be created in your workbook with the results if you have chosen the In New Sheet option.

Not Much Else To Cover

That is it.  There isn’t much else to say, but this is a great and frequently requested feature.  We can finally provide a good answer.

Absolutely you can drill through to the detail and have it returned in Excel.

As always, post a comment if you have something to share with the community or have additional questions about this topic.




Why The Name In2Hyperion, And Why It Is Changing

As the branding of Planning changes, so to am I.  It became obvious to me when I was at KScope18 and I got asked about the Hyperion name.  It was then I realized that I have been doing this too long.  I am now the old guy.  So, what is Hyperion and what does it mean?

For those of you that don’t know, Hyperion was the company that owned Planning prior to the acquisition by Oracle.  So, you will still hear people talk about Hyperion Planning, or HFR (Hyperion Financial Reporting), or Hyperion Essbase.  That said, Oracle is slowly phasing out the name.  Unless you are using the on premise version, you may have never heard of Hyperion.  As such, Hyperion is being used less and less in searches.

To help people find me, I am syndicating everything to www.in2Oracle.com and www.in2EPBCS.com.  I am still going to keep similar branding, but since people that come to in2Oracle get a logo reading in2Hyperion, there is some confusion.

For those of you that don’t know what Hyperion is, welcome to In2Hyperion.com!  For all you old timers, welcome to In2Oracle.com and In2ePBCS.com.

is now

 




Adventures in Groovy – Part 34: Getting Started With Groovy in ePBCS, Implementation Methods, an ODTUG Webinar

I was fortunate enough to speak for ODTUG a few weeks ago and really excited that my discussions around Groovy are getting some of the most attended and most interactive ODTUG webinars.  If you have put any of these presentations together, you know how much time it takes to do the research, consolidate the information, make it presentable, and spend the time to hopefully make it fluid.  So, when you provide feedback, I really appreciate it.

The Webinar

There were some questions I wasn’t able to answer, so here goes.

Can the dataCellIterator take functions like @IDESC, @CHLIDREN, etc?

The iterator iterates through the grid, so it doesn’t have the ability to do this directly.  I am not sure of the question, but you could iterate through the grid and for each cell use these functions in other classes/methods to do things like see if it has children, or check to see if it is a child of something.

Is there a way to have a groovy business rule to call a non-groovy business rule – for example if cells were edited then run BR1 else return?

Yes and no.  There is no way to execute another rule.  But, you can embed it into the script like you can in any other rule or script.  You can neither write the string or drag and drop the rule into the Groovy rule.  It doesn’t always put it where the cursor is, but you can cut and paste it to wherever you need it.  It basically is like an include and just embeds the script text, so it would need to be in a string builder.

Can We improve the Aggregations with Groovy?

Yes and no.  If you dynamically create an aggregation script that is the exact same as a normal rule, the same time would result.  Where you do get a benefit is that you can only consolidate the impacted members and dimensions based on what has been edited.  You can also move the data from the BSO to the ASO cube and eliminate the need to aggregate, which would obviously improve the perception of speed.

Does groovy interact with workflow, valid intersection, copying attached documents / supporting detail?

Workflow is in the roadmap.  I confirmed last week with development, so it is coming.  Attachments and supporting detail can be copied by executing smart pushes.

Can grids be generated on the fly using groovy?

They sure can, but they aren’t visible to the user.  There are two grid builders for retrieving and submitting data.

Is there any documentation available to give performance comparisons between business rule/calc and groovy?

Not that I know of, but as previously stated, Groovy doesn’t make Essbase faster.  The perception to users will be that it does, but it is only because we have the ability to isolate what we calculate more than we did before.  That said, if you use the grid builders to do the calculations and submit the results rather than use BSO calculations, you might see different results.  There are some things (allocations) that I think is faster in BSO.  I think using the grid builders on ASO – I do see improvements in performance using Groovy over procedural calculations.  But, I want to emphasize that the majority of the time the speed is improved because of the ability to calculate only what we need to.




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.