Monday, 7 July 2014

Planning – setting the cell retrieval threshold and interesting hack

If you have been working with planning then no doubt you have experienced the web application JVM crashing at some point which can be caused by a number of different factors, the usual likely candidates are large forms and maybe the most deadly of all is ad-hoc analysis.

When a large form or ad-hoc retrieval is run then there is a possibility that an out of memory error may occur in the JVM, the common errors to be generated in the logs are:

<Critical> <WorkManager> <BEA-002911> <WorkManager weblogic.kernel.Default failed to schedule a request due to java.lang.OutOfMemoryError: getNewTla

<Critical> <WorkManager> <BEA-002911> <WorkManager weblogic.kernel.System failed to schedule a request due to java.lang.OutOfMemoryError


You only need to monitor the web application once a single large ad-hoc query has been run to see the hit on resources.


It it possible to alleviate this issue with JVM tuning, optimal form design, improved access permissions, limit ad-hoc provisioning and educating users that planning ad-hoc should not be used as a download engine as it has not been designed to be used like that, if faster and larger queries are required then look towards the Essbase provider.

Even after implementing some of the above suggestions there is no guarantee that planning won’t be pushed too far and if it is a recurring issue then finding the root cause is definitely a number one priority.

A useful way of delving into the reason is to analyse the heap dump taken when the crash occurs, what is a heap dump you may well ask….

“A heap dump is a snapshot of the memory of a Java process at a certain point of time. There are different formats for persisting this data, and depending on the format it may contain different pieces of information, but in general the snapshot contains information about the java objects and classes in the heap at the moment the snapshot was triggered. Usually a full garbage collection is triggered before the heap dump is written so it contains information about the remaining objects.”

By default a heap dump is not created by default when an out of memory occurs so a few additional parameters can be added to the java options for the web application in question, they can be added in the registry editor for windows or setCustomParamsManagedServerName.sh for *nix systems.

The two parameters I usually use are:
-XX:+HeapDumpOnOutOfMemoryError and -XX:HeapDumpPath


Once the parameters have been applied and the web application restarted then the next time an out of memory error occurs a heap dump should be generated.


Now you have the output then you require software to analyse the heap and a very useful piece of open source software is eclipse memory analyser.

The installation is quick and simple and once the heap dump has been opened the Memory Analyzer can inspect the heap dump for leak suspects, e.g. objects or set of objects which are suspiciously big.


Once the leak suspects report is selected a pie chart is generated displaying the size of the heap and any problem suspects.


Selecting the details will allow you to drill down into the suspect classes which were active when the out of memory occurred.


Without needing to understand the classes in great detail you are able to spot that it was potentially an ad-hoc grid that caused the out of memory issue, selecting the attributes tab provides usual information such as the application and cube name plus the number of rows/column in the ad-hoc grid.

Selecting the xmlAdhocGrid2 class provides further information such as it was run from Smart View, the user id which can be easily converted to a username in the planning tables and some of the options selected at retrieval time.


The attributes also show that all the suppression options were turned off at the time of retrieval which is also good indication why such as large hit on the JVM took place.


If the out of memory occurred due to a large form and not an ad-hoc analysis the form in question can also be found in the heap dump.


With this simple investigation it is possible to ascertain what was happening in planning and the user that instigated the crash.

This is all well and good but wouldn’t it be nice just to be able to limit the amount of cells that can be retrieved thus protecting a possible outage on the web application.

Prior to 11.1.2.2 the options available are pretty limited and the following message just doesn’t cut it as a safeguard.


Recently I noticed a patch which was listed for release 11.1.2.3 but reading the description it clearly states it is for 11.1.2.2, I have no idea what it is doing with the 11.1.2.3 patches and I suppose it was mistaken put there and the reason why it has not had many downloads.


There is one defect addressed with the patch:

18259065 - When retrieving data through Smart View Ad-Hoc Analysis, any number of rows or columns is retrieved, despite the preferences which were set.

After applying this patch, the below mentioned property need to be set in Administrator -> Application -> Properties screen ERROR_THRESHOLD_NUM_OF_CELLS -> Value for this property will be the maximum number of cells for which the error messages is shown to the user during Ad-hoc Grid operation.

Note 1: This fix applies to Planning data simple forms, Smart View Ad-hoc and Financial Reports against Planning application.

Note 2: Unless overridden, default value of this property -ERROR_THRESHOLD_NUM_OF_CELLS is 250000 cells.


Interesting yet another planning property that can be applied to an application which should stop a retrieval being run where the number of cells is higher than the default value of 250,000 or higher than a custom value set using the property.

Also if the threshold is exceeded the retrieval is not executed so no memory is allocated which means no additional memory being consumed in the planning web application.

The following calculation can be used to determine the total number of cells:

Total number of cells = row * cols

Number of cols = Max (static columns (dimensions on page/pov), data columns) + Number of dimensions on the rows (each dimension takes one column)

Number of rows = (static rows (one for page/pov + one for col headers) + (data rows)


Going back to the patch it indicates that it needs to be applied on top of 11.1.2.306 so before patching I tested on 306 and there were definitely no messages being generated with large retrievals.

After patching I ran a large Planning Smart View ad-hoc retrieval.


Nice, the default 250,000 threshold had been exceeded so the retrieval was stopped and an error message displayed.

So how about overriding the default value, I added a new application property ERROR_THRESHOLD_NUM_OF_CELLS with a low value of 10,000


A restart of the planning web application is required to apply the changes; I wish it was possible to apply properties settings without having to restart as it can be long winded task if you need to play around with different values.

Another Smart View retrieval and the new value was working.


I also tested from within planning and the same functionality is applied and a warning pop up is displayed when the threshold is exceeded in forms or in ad-hoc mode.


A nice feature if you are on 11.1.2.2 but what about the latest and greatest 11.1.2.3.500 surely that must be there as standard, unfortunately it does not seem to be available yet as no error messages were being generated with testing.

I could have left and it there but I wanted to delve a little deeper to see if the code was actually deployed in 11.1.2.3.500

After looking around I could see the code was there but it was not firing and I finally found the reason why.

As you may well be aware that some 11.1.2.3 functionality is only available in Oracle’s planning and budgeting cloud service (PBCS) and in this case it looks like at the moment the threshold option is only available if in the cloud world.

Now the following steps I definitely don’t recommend but I wanted to see if the threshold governor could be enabled.

Planning checks the Shared Services registry to see if cloud mode is active, running a registry report clearly shows that there is an option available which by default is set to false.


After a quick update to the registry and restart of services I wondered if anything would have happened.


Oh look my EPM instance has now sort of turned into the planning cloud version :)

I gave a quick test of opening a large planning form and running a Smart View planning ad-hoc retrieve.




So the functionality is definitely iavailable but for some reason not for on-premise deployments yet, maybe it is coming soon if it available now in 11.1.2.2

If you have never seen PBCS then there a few diagnostics options available which are not available in the on-premise version.


A health check can be run on a planning application which will run through various artifacts such as forms, rules, dimensions and give them a traffic light type status.


Any artifacts that have been given a red status such as passing an error threshold will provide suggestions on how to resolve the problem.


The health check also provides basic suggestions such as outline order optimisation and stats like plan type block size and block density.


The threshold properties in the cloud service are set by Oracle and cannot be changed but the properties are all still available.

In the following example I set the block size to a low value of 1000 Bytes using the ERROR_THRESHOLD_BLOCK_SIZE property.

Running the health check again now highlighted a red status for the block size.


Anyway I must apologise to Oracle for my hack and don’t worry I have set the cloud mode back to the original value.

I do have one final message though :)


Saturday, 31 May 2014

Planning 11.1.2.3.500 and Smart View

Just a quick update from me which I was going to write when PSU 11.1.2.3.500 was released but I totally forgot, recently I have heard about this issue a couple of times so I thought I might as well post it in case it happens to anybody else.

When I first patched an environment with .500 everything looked good, well that was until I tested out a planning form in Smart View.


The colour formatting had been lost on retrievals and at first I instantly thought it was due to the patching so was going to rollback .500, the more I thought about it I came to the conclusion that it must be related to Smart View as there were no other noticeable problems with planning.


I checked the installed Smart View version which was 11.1.2.500 and read through the .500 readme and did not see any mention of the required version of Smart View.

I then searched for the latest version of Smart View and it was 11.1.2.5.200 at that time, I know it is currently at 11.1.2.5.210 which could even have changed by the time I post this.

I installed Smart View 11.1.2.5.200 and retrieved the same form again.


Issue resolved and just to be sure I rolled back to 11.1.2.5 and the issue was present again.

So the simple solution is if patching planning to 11.1.2.3.500 then make sure you are on Smart View 11.1.2.5.200+ which should be easy to implement if you have a small user community though more of a challenge if you have a large user base which deploys Smart View by package management.

Wednesday, 30 April 2014

Running EAS web console using different Java versions

Recently there was an OTN thread about invoking the EAS web console using different Java versions and it is a question that crops up time and time again.

Tim German wrote an excellent blog outlining the problem and a possible workaround, now I don’t want to discredit the method that Tim proposed in any way as it perfectly fine but I use a different technique and wanted to share as the way I see it the more options available the better.

For many different reasons it is possible that multiple versions of a JRE are installed on a client machine and probably the main reason is various software applications support different versions of Java.


It is possible to run multiple versions of Java without any issues but when using the EAS web console the user is usually forced down the route of the latest installed Java version being automatically invoked, yes it is possible to enable and disable the versions but sometimes this can be a pain when running multiple applications and in some cases an internal IT group policy will stop any changes being made in the Java control panel.

For example if I access the EAS web console then Java 8 which is not currently supported is automatically used as this is the latest version installed on my machine.



In theory you might think that starting up EAS by specifying the version of Java first would help but when accessed the JNLP file controls which version of Java will be invoked.


"C:\Program Files\Java\jre6\bin\javaws.exe" http://fusion11:10080/easconsole/easconsole.jnlp

In the above example Java 6 web start is used and the EAS JNLP is called, you will see the Java 6 flash screen appear but then the version of Java which will be used to start the console is defined by the contents of the JNLP which I will get onto shortly

 

So in my case Java 8 is invoked for running the EAS web console.

To get around this I use the following method which seems to work well for me.

First I want to download the JNLP to my machine instead of calling it each time from the EAS server where you have no control over it.

I create a test file with a jnlp extension and if you take a look at the properties you will notice by default it will open using Java Web Start Launcher.



I updated the property so it opens using notepad.


Now when I access the EAS web console the JNLP file is downloaded and I can it to my desktop.


If the easconole.jnlp file is edited you will see the following line in the xml code:

      <j2se initial-heap-size="32m" max-heap-size="256m" version="1.6+"/>

This basically means that the version of Java that will be invoked will be the latest version available that is greater or equal to 6 so in my case the web console will be run using Java 8

Once the jnlp file has been downloaded the property can be set back to opening with Java Web Start Launcher.


So to be able to run EAS using Java 6 I copied the jnlp file and edited to set the version to 1.6 instead of 1.6+


Then to start EAS I used the following syntax which runs Java Web Start 6 and also calls the updated JNLP file.


"C:\Program Files\Java\jre6\bin\javaws.exe" C:\EASConsoleTesting\easconsole16.jnlp

Taking a look at the Java Console and the properties of the Java process which EAS is running against it clearly indicates that Java 6 is correctly being used.



To use Java 7 it is a simple as copying and creating a new JNLP file then updating the version to 1.7


The syntax is the same but this time calling Java Web Start 7


Once again looking at the properties confirms the correct version of Java is being used.



It is also possible to target an exact update version so in my case I have both Java 7 update 45 and 55 installed but I want to open EAS using update 45.

This time in the JNLP file the version is set as “1.7.0_45” though to get it work I also had to add:

href=”http://java.sun.com/products/autodl/j2se”

 

The command to run is exactly using the same format.


The correct version of Java being run is confirmed.



So that is my take on running EAS web console using different Java versions, enjoy!

Thursday, 10 April 2014

11.1.2.3.500 Sample Planning application and more

I have always found the sample application that comes with planning to be a bit of a disappointment and it does not really showcase planning functionality very well, there is not much information on what it is about and it has not been updated since the early days of version 9 plus you have to go through the cumbersome process of creating, initialising and then loading the data.

If you are looking to learn planning it does not really give you much to go on and personally I have only really used the application to prove Planning and Essbase are working as they should.

With the release of Oracle’s Planning and Budgeting Cloud Service (PBCS) I think they must have looked at the sample application and thought that is just not good enough so have invested time in giving the application an overhaul.

It seems to be Oracle’s strategy is to preview in the cloud and then release to the on-premise world and this looks like what has happened with the 11.1.2.3.500 release.

If you have applied the latest patch set update and create a sample application you will instantly notice something has changed.


First of all it is not possible to name the sample application anymore and it is set as Vision.


When creating the application you will see that it is automatically initialising and creating the dimensionality and loading data, so already a much simpler process than what has come before.


When you open the application you will find that it blows the old sample application out of the water and actually uses lots of the functionality that is now available.

The application includes:
  • Three plan types
  • Four task lists
  • Over thirty forms including composite and charts
  • Calculation Manager rules and rule sets.
  • Two planning unit hierarchies
  • Provisioning applied using two groups.
Now if you are thinking that is all well and good but what is the all about well there is a video available going through the Vision application which was originally created for PBCS but there is nothing stopping you watching and learning if you are starting out with planning, the video can found here.

If you look into how the sample is initialised then you will find out there is a lot more than just the planning application.

After the application has been created if you take a look a migration status report in Shared Services the clue is there that Lifecycle Management has been used.


The report indicates not only is there the Vision planning application but artifacts loaded for Shared Services provisioning, Reporting and Analysis, Calculation Manager and FDMEE.

The status of the LCM import displays as failed and this is because that four users are provisioned against two planning application groups but the users are not the import LCM files.


 These users can be easily added and provisioned.

Depending on what products have been installed the import could fail for other reasons so instance say FDMEE has not installed then the import will fail, though it is worth pointing out this does not stop the other LCM artifacts from being loaded which means the planning application should be created successfully.

I had a search around to see if the LCM import files are available and the answer is yes they can be found in:
<MIDDLEWARE_HOME>\products\Planning\config\DemoApp


This means the zip file can be simply copied over to the LCM import/export folder and then will be accessible from Shared Services.


So basically it is possible to create the sample planning application directly using LCM or import any of the available artifacts in again.


Much more impressive than what has previously been available.

If Financial Reporting has been installed and the planning sample application created then financial reports and books should be available from Workspace in the Vision folder.


The reports are all using a planning connection to the vision application.


All the rules that are part of the sample application can be easily accessed through Calculation Manager.


You will also see a piece of functionality that is now available in Calculation Manager that has been passed down from the Oracle cloud offering.


As there is no EAS available in the cloud there needed to be a way to manage some aspect of the Essbase databases like caches, dense/sparse settings so these were built into Calculation Manager.

If FDMEE has been installed then the sample planning application also comes preconfigured and with a file data load set up.


Unfortunately I couldn’t find a load file but it only took a few minutes to create one and then load to FDMEE and push into the planning application.

I created a quick planning data form to test the drill-through functionality.


Now it is possible to drill-through from planning to FDMEE and view the data that was loaded.


It is also possible to take advantage of the new tablet functionality in planning if you have one following:
  • iOS7, tablet only, Safari and Chrome browsers
  • Android 4.1, 4.2, 4.3, tablet only, Chrome browser
  • Windows 8 Standard, Pro and Enterprise Editions, tablet, Chrome and Internet Explorer 10 browsers
Within the planning application all you need to do is go to Administration > Manage > Tablet Access


Add the forms/tasks/rules you want to be accessible from tablets.

Point the tablet web browser to:
http://<web_server>:<port>/HyperionPlanning



The Forms, Task Lists and Rules that were enabled in the planning should now be available.

To access Financial Reports snapshot reports have to be created first if you want to view them through the tablet.


I must admit the charts do look quite impressive on an iPad.


I am not sure if I would want to go through the pain of entering data through a tablet but I am sure it will impress lots of people.

It doesn’t look like drill-through to FDMEE is possible yet from a tablet.


Now you are able to run rules while on the move, is life complete ;)

If you want to test out the approval and workflow functionality using the new EPM mobile app then just head off to the either the Apple app store or Google Play store and download.



The EPM mobile app is currently supported on:
  • iOS7, phone and tablet
  • Android 4.1, 4.2, 4.3 phone and tablet
So lots of reasons to move to 11.1.2.3.500 and for me personally makes life much easier for testing out functionality is working as expected and integration between products is in order.

Also if you are new to the EPM world and are looking to learn then now is the perfect opportunity.

Enjoy!