EPM - Did you know? #4

Back again with the fourth instalment of the “did you know” series and the random topic for today is:

Did you know that you can alter the frequency or disable the essbase OPMN ping?

Personally I think OPMN was a bad choice to inflict on essbase and has been more trouble that it is worth, it only seems a viable solution for clustering on *nix type systems and the rest of the time it is a waste of space, it would have been nice to be given the option of not having to deploy it with essbase, anyway it is an old technology and looks like it is not going to be in Oracle’s strategic future.

In case you are wondering what the OPMN ping is all about I will give a little bit of background information.

If you look in the essbase product bin directory you will see the OPMN ping executable:

What it basically does is log into essbase with the admin account and then log out this indicates whether essbase is alive or not which is a requirement when clustering.

By default this happens every 20 seconds and if you take a look at the essbase log you will see it constantly polluted with something like:

[Tue Dec 30 10:12:29 2014]Local/ESSBASE0///964/Info(1042059)
Connected from [192.168.1.115]

[Tue Dec 30 10:12:49 2014]Local/ESSBASE0///964/Info(1042059)
Connected from [192.168.1.115]

As you can imagine this amounts up to many entries over time and doesn’t serve much purpose in the log.

There are also other logs that get written to under the OPMN logs directory.

I seem to find that at many clients this log folder is not part of any housekeeping solution and the essbase ping and agent log can become large in size.

Unfortunately there does not seem to be any options to rotate these logs even though there is a setting in the opmn.xml

The rotation size works with the opmn log but not does not seem to work with the ping or agent log, I remember a enhancement request back in 2012 to be able to add the option to but I am not sure it got anywhere.

14556989 - ABILITY TO DO LOG ROTATIONS ON ESSBASE. OPMN, AND ESSBASE STUDIO

If you look in the EssbasePing.log then you will see the following messages repeating every 20 seconds.

[2014-12-30T11:02:15.272-18:02] Initializing Ping Request
PM-PingUtility INFO: Connection is done
PM-PingUtility INFO: Received Response from the Essbase Agent
PM-PingUtility INFO: Essbase Message - PING_OK
PM-PingUtility INFO: PING SUCCESS

If there is a problem connecting or logging into Essbase then you will see a similar message to:   

[2014-12-30T11:25:13.539-18:58] Initializing Ping Request
PM-PingUtility: Couldn't connect to Essbase with Essbase ERR# 1042006
PM-PingUtility: Fails to Ping the Essbase Agent 1042006

In a non-clustered essbase environment this is not really providing much benefit as the message just repeats and unless the log is being monitored it would go unnoticed, if required the same solution could be easily created with a Maxl script.

Luckily the opmn ping log does not have a lock on it so it can be removed with stopping services.

So how about altering the frequency of the ping and how can it be done.

Well it is back to the opmn.xml file located in:

<EPM_ORACLE_INSTANCE>\diagnostics\logs\OPMN\opmn

There is an element called ping and an attribute called interval that can be added to the XML file

For example <ping interval="60"/>

The interval is the number of seconds between each ping so in the above example after a restart to OPMN the ping will have gone from 20 seconds to 60 seconds.

After making any changes to the xml file I was always like to use the validate command to check the file is still valid.

The maximum value is 7200 seconds and if you try and set it any higher then you should see the following error message:

The maximum value is defined by the XML schema definition file:

It is possible to change the value in the XSD file so I tested increasing the maximum value to 10800 and the ping interval matched the value.

So how about disabling the ping process well it couldn’t be simpler just set the interval value to 0.

If you set it to 0 and restart OPMN then you will no longer be bothered by the ping.

EPM - Did you know? #3

Today I am going to be continuing the “did you know” series with the third instalment.

This time the randomiser has picked:

Did you know where the EAS console picks the version/patch number from?

I previously wrote a blog on understanding product versions in workspace, in the post I did not cover EAS and I know sometimes there can be confusion on the versioning in the console so here is a brief overview on how it works.

In the console if you go to “Help” and select “About Essbase Administration Services” then the following dialog box will open.

As you can see it displays three product version numbers, I have no idea why Business Rules is still included as it was removed in 11.1.2.2 and this causes the version to default to 4.1.1.

The first question is it the server version for EAS and APS and the answer is no it is only the version of the files being used in the console.

You can tell that by the MSI console version as you don’t even need to connect to the EAS server for it return the version and even when you connect it does not make any difference.

With the web console it downloads the latest versions of the java files from the EAS server so usually the console and server will be in sync.

So where is the version generated from, well both the web and MSI console use a java archive called eas_client.jar, the MSI version is located in

<MIDDLEWARE_HOME>\EPMSystem11R1\products\Essbase\eas\console\lib

The web console downloads the file from the EAS server and the file is contained in the EAS web application file:

<MIDDLEWARE_HOME>\EPMSystem11R1\products\Essbase\eas\server\AppServer\InstallableApps\Common\eas.ear

If you open the java archive there is a directory which contains property files and the AboutDialog.properties has some of the information we are after.
 

The VersionNo property contains the information that it is displayed in the console.

To prove it I updated the version and then loaded up the console again.

So that covers the EAS version so how about the APS version.

This is generated from a different java archive ess_es_gui.jar which is also used by the console:

The version 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.

Once again I updated the version number.

Loaded up the console again and the version had changed.

You may sometimes find that with patch releases that the jar files are not updated so the version number does not match to the patched server version.

Well at least you should now understand how the versioning works in the console.

EPM - Did you know? #2

If you read yesterday you will know I am putting together a series of quick posts with EPM information which you may or may not know.

The randomiser today has picked:

Did you know you can simply automate the refreshing of the Shared Services cache using the Java API?

In the Shared Services under the user directory configuration there is the option to set the cache refresh interval which the default is 60 minutes.

In some cases with large corporate directories the interval may be set to higher value to reduce performance degradation.

You may be wondering what the cache is for anyway, well here is Oracle’s definition:

"Interval (in minutes) for refreshing the Shared Services cache of groups to users relationship data. Default is 60 minutes.

Shared Services caches information about new external user directory groups and new users added to existing groups only after the next cache refresh. Users provisioned through a newly created external user directory group do not get their provisioned roles until the cache is refreshed."

Now say there have been changes in the external directory such as users have been added or removed from groups then these will not be picked up until the next cache refresh, if these changes are required to be pushed through straight away or maybe changes should be applied at a set time then having to log into Shared Services to click the refresh button is not the ideal option.

Luckily there is an option available using the API and a simple piece of code will allow the refresh to occur on demand.

Here is an example piece of code I wrote to basically accept a username and password which is then authenticated and if successful the cache is refreshed.

Apologies to master coders out there if you are appalled by my attempt :)

Right, let’s demonstrate refreshing the cache with an example scenario, in Shared Services I have an active directory group called EPM_ADMINISTRATORS which has already been provisioned with roles.

At present the group only has only user called epm_admin, in the AD I add myself to the group

Checking the properties of the group in Shared Services still only shows one member.

This will be the case until the next refresh of the cache so time to give my code a quick test.

Running the java can easily be done from command line with the following:

• Set the classpath to include the required java classes, to simplify I am using epm_j2se.jar which references many packages
• Set the epm instance location
• Call the java class passing in the epm instance and user credentials.

So for my environment I used:

set classpath=.;E:\Oracle\Middleware\EPMSystem11R1\common\jlib\11.1.2.0\epm_j2se.jar;

set EPM_ORACLE_INSTANCE=E:\Oracle\Middleware\user_projects\epmsystem1

java -DEPM_ORACLE_INSTANCE=%EPM_ORACLE_INSTANCE% refreshCache  admin password

The warnings can be ignored as they not important, I would recommend adding some encryption though as clear text credentials are not for the security conscious.

Checking the properties of the group in Shared Services confirms the refresh has happened with myself now displaying as a member.

If you are using essbase and run the above code the caching system essbase uses will not automatically trigger an update until a user logs in, so you could update the code to include a login using the essbase JAPI or Maxl and then you should see entries in the SharedServices_Security_Client.log to confirm the cache refresh did happen.

Well that completes #2, until next time..

EPM - Did you know? #1

I thought I would try something different over the festive period and as it is a time of sharing I am going to put together a series of very quick posts on EPM related information which you may not know..

I have created a list of topics and for each blog I will randomly pick one from the list, now they may be of no interest to you or you may find you already know them but I am sure some will find them useful, I am not sure how many I will get through as it is all time dependent but I am aiming for five.

Today the randomiser has chosen:

Did you know there is an undocumented tool that allows to change the data location for essbase databases?

The tool is command line driven and can change the location without the need to export/import or restructure the database.

It can be used to quickly change the data location on any database, it can be used in migrations where the drive structure is different between source and target and if done correctly can be part of an upgrade process from 9.3 onwards.

If you have ever worked with the essbase staging tool then you will know there is an archive file available in the EPM installation structure.

The tool can be found in the Migration folder and is used as part of the upgrade process for essbase.

If you extract the archive and look in the bin directory there is an executable file called essmove (available on both Windows/*nix)

As I said there is no documentation available but I found out about it by accident and then had a play around with it, it is called as part of the staging tool but can be used in isolation.

To be able to use the tool you first must set a number of environment variables but don’t worry there is a script already available in the Essbase server bin directory, look for the setEssbaseEnv script.

Once the script has been run then execute essmove and follow the onscreen questions though make sure the intended database has been stopped first.

In the above example the drive location for the ind/pag files has been moved from E to F.

The changes are applied to the databases kernel file (*.esm)

If you check in EAS or with Maxl you will see the location has been updated.

Before starting up the database make sure the index and data files have been copied to the new location.

The tool can be used against ASO databases in the same way.

In the above example the default tablespace location has been updated to F:\Essdata\App

The changes are applied to the metadata tablespace file.

If you check in EAS or by Maxl then you should see the location has been changed.

I have used the tool on many occasions and have found it invaluable, it is also possible to pass values into the tool so it be part of a scripted process.

So that completes the first did you know, I hope you found it useful.

Planning 11.1.2.3 – setting the cell retrieval threshold update

In a previous blog I went through the reasons why setting the cell retrieval threshold was important if you want to maintain a healthy JVM and how to interrogate a heap dump using third party software, if you have not read the post then it is probably worth having a read through it to gain some background information.

In the blog I highlighted that a patch was available for 11.1.2.2 that allowed to set the maximum cell retrieval but unfortunately this had not made it into the on-premise 11.1.2.3 as of the .500 PSU even though it was available in the cloud version, I had this light hearted message for Oracle.

Now I know Oracle would have not taken any notice of it but luckily with the recent planning 11.1.2.3.501 PSU the following defect was addressed.

18663876, 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, SmartView 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.

So the functionality has finally made it to 11.1.2.3 and to be sure it was working I had to test it out.

After applying the .501 PSU I created and opened an extremely large form in planning that would pass the default threshold of 250,000 cells.

Good news the functionality has been implemented, so how about a large ad-hoc Smart View retrieve through the planning layer.

All good, it is working as expected.

The remaining test it to set the planning property ERROR_THRESHOLD_NUM_OF_CELLS to control the threshold.

It is shame you still have to restart the planning web application server to apply the changes.

Running a large form confirmed that the new threshold of 10,000 cells has been honoured.

The same positive results with a Smart View ad-hoc planning retrieve.

If you are running planning 11.1.2.3 and want to use this functionality then get patching to 501 or newer.

Essbase standalone mode is no more

I am sure some of you are wondering what the hell is Essbase standalone mode anyway, well before the days of System 9 and Shared Services there was only the Essbase native security mode to manage security, the days where each Hyperion product was a separate entity and then came along System 9 and the start of central security integration through Shared Services (yes there was the hub prior to that but in my experience it was not commonly used).

As the product set increased it made more sense to manage security in one location and over time Shared Services was the new home to do this for the core products.

This meant that Essbase standalone mode became more of a rarity and it was only really used if a customer had just purchased Essbase and had no interest in Shared Services.

Even now in 11.1.2.3 the option to deploy Essbase in standalone mode is still available though I expect that to be gone from 11.1.2.4

In a recent Essbase PSU 11.1.2.3.504 readme there was the following statement:

Essbase Native Security Mode Is No Longer Supported
Caution! Oracle strongly recommends not using Essbase native security mode because of security concerns. If you are currently using Essbase native security mode, you should convert Essbase Server to EPM System security mode and migrate users to EPM System security using Administration Services Console. See “Converting Essbase Server and Migrating Users to Shared Services” in the Oracle Essbase Administration Services Online Help. After you complete the conversion and migration tasks, Essbase security is managed as described in the Oracle Enterprise Performance Management System User Security Administration Guide.

So basically standalone mode is no more, I am finding more and more that Oracle seem to be releasing information in Readmes and I find it gets lost in all the noise, with the amount of different patches it is not easy to remember where you read the information and because it is a patch it doesn’t seem to make it to the documentation until the next major release.

Also I have noticed that Oracle are going back to patch readmes and updating the information in them, the native security is no longer is a prime example because now that has appeared in PSU 503, I checked the original readme and it was not in there, also you can see the date release in Oracle Support has changed.

The information is not in PSU 502 so I assume it came into force in 503.

It is annoying the fact that the readmes are being updated without being informed, I know patch 500 was a classic as it now contains much more information in the readme that it did when it was originally released, it would nice if Oracle provided some versioning on the readme so you can easily find out what has been added or changed, most people use versioning on documentation so I don’t see why Oracle can’t.

The moral is each time you patch go and have a look at the readme again in Oracle Support because it may have changed from one day to the next.

Anyway if you are using Essbase native security in 11.1.2.3 then it is time to change and if you currently using it and are going to upgrade then be aware it is no longer supported.

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 ?