Wednesday, 1 October 2014

11.1.2.3 LCM – Classic planning dimension import changes

I know 11.1.2.3 has been around for a while now but I have been asked a few times about the differences with  how LCM loads dimensions between 11.1.2.3 and earlier.

I have meant to write a quick blog post on the subject for ages and never got round so here it is and then I can forget about it ?

Prior to 11.1.2.3 if you load dimension metadata using LCM to a classic planning application then the hierarchy between the source and target application will match, so basically it is replacing instead of merging, this goes against other artifacts being loaded into planning with LCM as they will follow the update/insert method and never delete.

I have always wished for an option to delete on target with LCM and Planning but that is a different story.

So let’s take an example in 11.1.2.2 and is true for earlier versions, say we have the following entity dimension which is identical between a source and target application.


The shared member “E03_310_1000” in the source application is deleted and use LCM to extract dimension.

If you take a look at the LCM output the dimension metadata is contained in an xml file.


Examing the xml file before the member was deleted it contained the following.


Obviously after the member was deleted the above information is removed from the LCM xml output.

After using LCM to load the Entity dimension into the target application the source/target should match and member “E03_310_1000” will have been deleted.


Moving on to 11.1.2.3 the method LCM uses changes and unless I have missed something in the mass of documentation I don’t see it mentioned, please let me know if I have missed it and I will update.

Running the same LCM example in 11.1.2.3 the source/target will not be matched and members in the target will not be deleted, members will only be updated/inserted.

Examining the LCM output in 11.1.2.3 you will notice that the dimension files are no longer xml and are csv.


Opening up the csv reveals the changes, now there is some xml embedded in a header block which relates to the dimension properties.


After the header block there is something very familiar if you have used the planning outline load utility, yes the format looks exactly the same.


In the planning documentation there is further information to back up the fact that the outline load utility might be used in 11.1.2.3, the admin doc has information about the header block and is reserved for internal use, I doubt you would have seen anything about HEADERBLOCK if you have been using the utility before.


The theory also holds true for the utility being used because the default load operation is update which means members will be added, updated or moved.

To confirm the utility is being used I updated the csv file to delete one member.


I added in the operation field which does not exist in the LCM export, the field has the following options:


The LCM import completed successfully.


Checking the hierarchy before and after the LCM import you will see it successfully deleted the shared member “E03_310_1000”



That pretty much confirms that LCM does use the outline load utility, it is also worth pointing out that a planning refresh will automatically happen with LCM, this is an additional parameter if the outline load utility is being used directly.


So what are the options for deleting members, well you can update the LCM csv file, use the outline load utility or the planning web version or even manually delete.

I still believe it would be nice to have an option in Shared Services to select whether it should be merge or replace import mode like that is available for EPMA.


If you intend on using LCM to migrate a planning application from a previous version then look at updating to the new format otherwise it may be painful ?

Thursday, 18 September 2014

APS 11.1.2.3.502 diagnostic report failure

If you are running 11.1.2.3 and a Provider Services version before patch 502 then if you run a diagnostics report you should hopefully see a pass for the Essbase web services.


If you decide to apply patch 502 or later then there are some changes that have been made to the web services.

After applying 502 the diagnostics report should report two failures with the Essbase web services, one for the web application and one for the http server.


If you use the web services or are interested in why the report has suddenly started displaying failures then within the readme there is a hint under the documentation updates.

WSDL URLs for Essbase Web Services
The WSDL (Web Service Description Language) URLs for Essbase Web Services are disabled by default. Before using Web Services, enable WSDL using the Oracle Enterprise Manager user interface. See the Oracle Enterprise Manager documentation.


So it looks like there has been a change and the WSDL URLs which were previously enabled by default allowing the Essbase web services to be used  are now set to be disabled.

If you would like to enable the web services then this can be done in Enterprise Manager which is deployed by default in 11.1.2.3, start up the WebLogic admin server and go to:
http:// <server>:7001/em



Select the Provider Services WebLogic managed server and then Web Services



Select the “Oracle Infrastructure Web Services” tab, under the Web Service Name click “AdminServicePortType” Endpoint Name and then the configuration tab.


The WSDL will be set to false which was previously set to true, set the value to true.

Also set WSDL to true for the following Endpoint names “DatasourceServicePortType” and “QueryServicePortType

Once set restart the provider services web application.

If you run the diagnostic report again this time it should pass for the Essbase web services.


If you don’t intend on using the web services then it is probably not worth enabling and putting up with the failures as they are harmless.

Sunday, 17 August 2014

Understanding product versions in workspace

Have you ever wondered how the display version operates in workspace or questioned why the version has not been updated after a patch has been applied, if so then this might be of interest to you.


I know I have been asked on many occasions by customers why the version number in workspace does not match the patch that has been applied in an environment, I usually say that workspace should be not be counted on as a true reflection but never gone into detail why not.

I am going to attempt to go through the main core products and show where the version number is populated from, you might be naïve and think that it is one standard for all but please remember this is EPM and still under the covers it is a mishmash of products all working in different ways.

This is going be based on 11.1.2.3 but it should be relevant for 11.1.2.2 and possibly earlier depending on product.

So let’s take the workspace and UI version first.


The above versions are contained in two property files within the workspace web application.

Located in
<MIDDLEWARE_HOME>\EPMSystem11R1\products\Foundation\workspace\InstallableApps 
is the enterprise archive file workspace.ear and contained within the packaged file is the web application archive file workspace.war

 

The two property files contain the current version number.


To prove this I updated the version number in the files and repackaged them.


 A restart of foundation is required to deploy the changes.


Ok so that shows where those versions are sourced from.

Now let’s move on to planning which is currently displaying the following version in workspace.


For the majority of java web applications the version number can be found in the Shared Services registry.


If you run a registry report and look at the logical web application entry then you will see a displayVersion property which workspace reads in when selecting the help > about menu.

This is fine but when you apply a patch there is usually no interaction with the registry so the version must originate from somewhere before the registry being updated.

For planning there is a property file located in the java archive file
<MIDDLEWARE_HOME>\EPMSystem11R1\products\Planning\lib\HspJS.jar


The PlanningConfig file contains the planning version.


Once again I will update the version and repackage the file.


When the planning web application is restarted the version in the file is checked against the display version in the Shared Services registry and if they don’t match the registry is updated.


Restart the foundation web application again and the version in workspace should be updated.


On to HFM which is again slightly different to where the version is sourced from.


The version is contained within the web application archive but this time it is derived from the manifest file in the META-INF directory.


The manifest is a special file that can contain information about the files packaged in an archive file and in this case the version is read from the Implementation-Version line.

To confirm this I updated the version in the manifest file.


Restarted the HFM java web tier service and checked the HSS registry.


The display version is updated in the FM LWA and ADF LWA entries but workspace only uses the version in the ADF entry.

Restart Foundation and the new version is displayed.


Now you should have got the concept I will quickly go through some of the other core products, each of them the version is read from the HSS registry in workspace but the version is updated in the registry from the following source files:

Provider Services – This also uses the manifest located in the ess_es_server java archive file



Financial Reporting - This also uses the manifest located in the HReports java archive file


FDMEE – Yet again a slightly different way of maintaining the release version and is stored nice and simply in an xml file.


Calculation Manager uses the build version within a property file in the calcmgrcommon java archive file


EPMA is always different and there is property file file within the structure of the java web application archive.


The version displayed is a combination of the awb.display.version and build.number property.

The Reporting and Analysis Framework version doesn’t look to me like it gets updated unless there is a direct change to the display version in the HSS registry for the RA_FRAMEWORK_LWA entry.

You may have noticed I have not mentioned Shared Services and this is because in 11.1.2.3 it is embedded in workspace and unless I am missing something there is no option to view the version in workspace.

In previous versions when Shared Services was separate the version was available by using the help > about option in the Shared Services console.


The version is not read from the HSS registry and is sourced from the manifest in the interop-sdk java archive file.


The version displayed is a combination of the Current-Version and Drop-Number properties.

I think that covers the majority of product components and if you find that a version is not updated in workspace when a patch has been applied then it could be down to the file that contains the versions is not part of the patch or the file has not been updated.

At least you should be able to trace it back to the source and impress others, just remember where you heard it from :)

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 :)