Thursday, 30 April 2015

EPM 11.1.2.x out of place maintenance release

The title of this post may seem a little strange as you are probably saying to yourself there is no out of place maintenance release and a maintenance release has to be applied to an existing 11.1.2.x deployment.

If you have a look at the 11.1.2.4 installation documentation you will see the rules behind a maintenance release.


It seems crazy that say you are currently on 11.1.2.1 and want to move to 11.1.2.4 you have to first apply the maintenance release and go through the configuration before being able to move to a new environment.

There are so many different reasons that you would want to upgrade to a new version but not want to go down the maintenance release path, I went through a number of those in a previous blog

My main issues with a maintenance release are that it doesn't clean up the previous release so you can be left with lots of unneeded files and a messy system, if the EPM registry is polluted already then the maintenance release does no good to help this cause, to apply a maintenance release you have to apply to a live system and many customers cannot have that sort of downtime and risk as what happens If at any point the upgrade process fails.

What has always confused me is that most of the EPM products and application store everything in relational databases and the maintenance release surely just upgrades the database structure, I know they are some exceptions to this rule like essbase databases and the RAF RM1 directory but I will cover those later.

What makes it more baffling in previous releases you could point to 9.2, 9.3, 11.1.1.x databases and upgrade them, yet if you are on any release of 11.1.2.x this is not possible.

Looking at the EPM configuration utility in different 11.1.2 releases you can see the upgrade database options.

In 11.1.2.1 it was possible to upgrade from 9.2.1+, 9.3.3+ and 11.1.1.3+


In 11.1.2.2 the options were 9.3.3+ and 11.1.1.3+


In 11.1.2.3 the only option was 11.1.1.4+


In 11.1.2.4 there are no upgrade database options.


As you can see in all those releases there is nothing about 11.1.2.x and the likelihood that if you are currently on 11.1.2.1 and upgrading to 11.1.2.4 you will want to install on new hardware or a new OS so why is there no option to upgrade the databases, wouldn’t this make more sense that the route highlighted in the table above.

I like the way some of the other Oracle products work when a new release is to be installed you can install either to a different directory or a new machine and then run an upgrade assistant which will upgrade the existing database elements.

Anyway I wanted to find out if it was possible to go down the maintenance route on a new install so I spent a fair amount of time studying what actually happens in a maintenance release, surely if you are on 11.1.2 it doesn’t perform some kind of magic that is only possible when running on an already deployed environment.

Now let’s ignore that not supported statement for a while as maybe by the end of this post it might be clearer if this is a viable option.

So here is an example of the process I followed on a fresh install of 11.1.2.4 on new hardware but still trying to replicate a maintenance release from 11.1.2.3

The first step really is take an LCM extract of HSS provisioning from the 11.1.2.3 environment as in the 11.1.2.4 install the Shared Services database is going to be blank, you wouldn’t want to ever contemplate upgrading the database as it contains configuration information like will be only valid in 11.1.2.3 environment.

I have found that LCM provisioning extracts work pretty well across different versions of 11.1.2 and it is one area that is not too painful in migrations and upgrades.

I have taken a copy of all the other relational schemas e.g. Calculation Manager, EPMA, Planning, HFM, RAF, FDMEE and so on, also the RAF RM1 folder and the essbase app directory and sec file.

I also created a blank schema which will be only temporally be used and can be dropped at the end.

11.1.2.4 was then installed and just the HSS database configured.

After spending time investigating what happens in a maintenance release I narrowed it down to a few changes that occur within the EPM registry for each product component.

The problem at this point is if you run a registry report there is nothing in the EPM registry for the various products.

So basically I ran the EPM configuration utility and only selected “configure database” for each product.


I pointed all the database configurations to the temporary schema I created so I could populate the registry with the required product component information.


Running the configuration utility again you will see all the database sections are now configured.


Running a registry report now reveals sections for each product and the important Instance and Systems tasks.


At this point it is no different than a standard configuration.

As you can see the versions in the registry are all at 11.1.2.4 and the relational configuration has a value of configured.

You may be asking well why couldn't I just have pointed to the copies of the 11.1.2.3 schemas and reuse them, well you can but unfortunately it will not upgrade the database structure and leave it at the previous version which would not be good.

Going back to a maintenance release upgrade if you look in the EPM registry at the correct point in time you will see changes are made.


I am not going to give away all the secrets of the changes that are made but by using the epmsys_registry tool it is possible to set the registry up in such a way that it believes it is part of a maintenance release.

The format for the updates are:

epmsys_registry updateproperty #<Object ID>/@<Property Name> <Property Value>

Once you have a full list the changes can be implemented for all products pretty quickly.

Running the EPM configuration again returns all the database sections to require configuration like in a maintenance release.


All the other required configuration sections are also pre populated and are going to be treated exactly like a new configuration.

In the configure database panel I can set all the databases to the ones copied from 11.1.2.3


Next you just run through the configuration like you would with any new configuration.

During the configuration all the databases will be upgraded just the same as in a typical maintenance release.

Even the RAF directory can be copied from the release that is being upgraded and both the database / RM1 folder are upgraded and in sync.

Once complete and up and running then the same sort of processes can be carried out for upgrading applications.

The planning wizard process is the same as a maintenance release except the data sources will require pointing to the new databases/schemas.


The applications can then be upgraded.


Once upgraded they will be registered in Shared Services under default application groups


For HFM the upgrade applications from an earlier release can be selected in the configuration utility.


The applications will also be registered in Shared Services, for more information about what happens in the upgrade then take a look at my previous 11.1.2.4 maintenance release post.

As usual EPMA doesn’t play ball but there is a simple solution, the problem with EPMA applications is that there status will be set as deployed but they have yet to be registered with Shared Services.

The diagnostics can be run for each application and then set back to ‘Not Deployed’ so this allows full functionality and the apps can be deployed as normal.

With essbase it is possible to copy app directory over and with a little clever manipulation of the sec file the apps will automatically register in Shared Services.


Though to be honest with essbase I would always export/clear/import data to bring it in line with the latest release plus there are a number of alternative methods to upgrade.

If the essbase index/data locations are going to be different from the source environment then you can use the trick I previously blogged about.

The remaining products shouldn’t need much attention and will be automatically upgraded.

Once completed you can then import all the provisioning with the LCM backup that was taken from the previous release.

Now you may not be convinced with this process and be saying that it is not supported and the database for each of the products is not a clean 11.1.2.4 version because it has been upgraded.

Well now that everything is up and running and using the 11.1.2.4 code line you could easily run the EPM clone utility to export all the artifacts out using LCM.

I wrote a comprehensive post on the utility which you can read all about here.

Basically by running a simple bit of command line you can get the utility to extract everything out.


Once completed you will have LCM extracts for all products in one directory.


The EPM configuration utility can be again and all of the configure database sections selected, when asked choose to drop and re-create tables.


This this should clear all the database tables and it will be like a clean install. (watch out for things like HFM app tables as it doesn't clear them out so well)

Now just delete the registered apps from Shared Services and use the LCM full export and import everything back in, hopefully it should run a lot smoother because the export will be at the same version.

So by using this technique you have a clean install and a full set of LCM artifacts at the same version without affecting the source environment.

The beauty of this method is that you can pick and choose the products you would like to apply the maintenance release to and there may be slightly more overhead in process time but I can tell you it is much easier than trying to convert all the LCM files from a previous release to the release they are to be imported into.

This process has been tested out on an upgrade from 11.1.2.1 to 11.1.2.4 and not using sample apps but a live environment and it worked pretty flawlessly though that doesn't mean I am certifying it for everyone.

If you would like to know more then get in touch but please don’t ask for a step by step guide :)

Tuesday, 7 April 2015

EPM 11.1.2.4 workspace integration with OBIEE

In the past I have covered in detail workspace integration with OBIEE so today I am not going to go over old ground and only provide an update with integration in EPM 11.1.2.4

I suspect because OBIEE is still at 11.1.1.7.x and there not been too many changes relating to configuration with EPM 11.1.2.4 that the process should still be the same but I feel it is worth testing out to be sure.

The blogs outlining the steps in more detail are available at:

OBIEE 11.1.1.7 integration with EPM 11.1.2.2

OBIEE 11.1.1.7 integration with EPM 11.1.2.3

Issues with OBIEE/Workspace integration when using SQL Server

If you are new to the process then it is definitely worth reading through them to get a good understanding on what is required.

For today I am going to be using OBIEE 11.1.1.7.150120 with a newly built EPM 11.1.2.4 environment and using Oracle as the database repository.

Here is a high level summary of the steps that are being carried out, for a detailed breakdown of the steps please visit the blogs mentioned above:

  • Configure same MSAD configuration in EPM Shared Services and OBIEE WebLogic Admin Server

  • Extract regSyncUtil_OBIEE-TO-EPM.zip

  • Update reg.properties with version from EPM environment

  • Run utility to share encryption key between EPM and OBIEE

  • Remove applicationID from OBIEE EPM registry

  • Copy css.jar, interop-sdk.jar and registry-api.jar from 11.1.2.4 EPM environment to equivalent OBIEE environment locations

  • Register OBIEE in workspace by updating registration.properties and running HSSRegistration tool

  • Reconfigure EPM web server to add in OBIEE proxy information

  • Enable SSO authentication using fusion middleware control

  • Update instanceconfig.xml to add the HyperionCSS authentication schema

  • Update bridgeconfig.properties to enable Hyperion CSS authentication

  • Restart EPM and OBIEE

  • Test
The process is exactly the same as I carried out with 11.1.2.3 and I didn’t hit any new issues.

The good news is that once the configuration was complete and restarted the OBIEE roles were available in Shared Services.


After provisioning an AD user with OBIEE roles in Shared Services the OBIEE was available for the user in Workspace.


Selecting one of the OBIEE menu options in workspace correctly passed across the SSO token and logged into OBIEE analytics without any issues. 


This is fine is you are accessing OBIEE reports that are not using Essbase or HFM as a data source, if you are there are a few additional steps that are required to allow the full SSO which I have not covered previously.

To enable SSO each Essbase/HFM connection pool requires to be updated using the BI admin tool.


The available options are to use a fixed user name to open reports or by using the variable route of :USER and :PASSWORD which will pass the username/password of the user that is currently logged in to analytics to Essbase/HFM but this is not really a true SSO.

To use the same SSO token which has been used in the workspace integration then the SSO option should be selected and saved.

If you then try and open a report which has an essbase data source you will be confronted with an unfriendly error message.


Taking a look at the essbase log you can see that it is passing in incorrect credentials

Local/ESSBASE0///6972/Error(1051293)
Login fails due to invalid login credentials

Local/ESSBASE0///6972/Warning(1051003)
Error 1051293 processing request [LoginEx] – disconnecting


This doesn’t really provide much information but if you increase the logging levels for essbase then the shared services client security log provides further info.

[EPMCSS-99999] [oracle.EPMCSS.CSS] [tid: 26] [ecid: disabled,0] [SRC_CLASS: com.hyperion.css.facade.impl.CSSAbstractAuthenticator] [SRC_METHOD: getUserDetails] EXITING in [0]ms. RETURNING [essuser, , ldap://orclguid=11cf57e5e5b443378fc36b792d5974b2?USER, , essuser, , 1428403297072, , null|11cf57e5e5b443378fc36b792d5974b2|null|null|null|null|0|null|null|null|,|.|false|null|false|null, BnwdiigC51bzqFwG9zm41VcKutlDmFLun65iUpEpDxk=, ECID:1.fbb32e89d31ed03e:-76dffa80:14c936ca095:-8000-00000000000000b8;kXiClpjg1vC].


As you see it is trying to log in using the orclguid attribute which is the default for Oracle Internet Directory, for Microsoft it should be objectGUID.

There is a simple solution which is actually documented but I wanted to go through the error first.

In the OBIEE web logic domain an additional java system property should be added to the setDomainEnv script.

-Doracle.epm.css.identity.type=fusion


If you are on windows and using a windows service to start up OBIEE then you will also need to add the property into the registry.

Once added and OBIEE managed servers restarted then full SSO should be available from workspace.


There is also an alternative way of achieving the same result and that is by adding a new property to the OBIEE EPM registry.


I would probably stick with the first method though unless you are using a different login attribute to an external directory.

With the long overdue demise of products like Web Analysis and Interactive reporting, OBIEE is now becoming more of the norm for dashboard type reporting with EPM and it is definitely worth considering integrating with workspace.

Monday, 9 March 2015

EPM 11.1.2.4 Maintenance Release

I have finally got round to putting a post together around the 11.1.2.4 maintenance release, I would first like to stress this is not going to be a step by step process guide and to be honest the maintenance release is no different than applying in previous releases which I have already covered here and here.

There is also substantial documentation available here which should cover the full process.

Before diving into applying a maintenance release there are a number of important factors to consider like the supported paths to 11.1.2.4


Personally I am not a big fan of maintenance releases for reasons which I will try and cover and in many cases the end goal of running 11.1.2.4 with upgraded applications from a previous release can be achieved with a clean install.

Similar questions to the following examples should be asked first to ascertain whether a maintenance release versus a clean install is the best option:
  • Is the hardware that EPM is running on going to be fit for x number of years.
  • Is it time to rethink how your current release of EPM is distributed and whether it is the right fit for the next release.
  • Do you want to change operating system, for example windows 2012 server is now supported.
  • Is it time for a server rebuild required after accumulating years of patches and fixes at OS and Software level.
  • Do any additional products require bringing into the environment
  • Is there a requirement move to a different database type e.g. SQL Server to Oracle.
  • Is it a good time to perform a review of applications, reports, security before moving to the next release.
  • Is a clean install not a better option as a maintenance release pollutes rather than cleans.
  • Can you bring down your live system while a maintenance release is being applied.
  • Do you have time to fully test the system before bringing it back online.
  • What happens is something goes wrong in the maintenance release process.
There are many more of these types of questions and if whoever you have chosen to perform the upgrade is not asking them then you should really reconsider changing them before ending up in a bad place.

Maintenance releases do have there place but you have to be sure it is right for your deployment, I mean you may have recently had a clean install on new hardware of 11.1.2.3 and are an essbase/planning customer, as there have not been many infra changes in this area it could be a good fit for this in-place upgrade.

My main problem with maintenance releases is they are basically a bolt on to a previous version and many files that are no longer required are not removed, the Shared Services registry can be a mess and the registry cleaner does not really fix this, the system can be slower than a clean install and if it goes wrong it is not only a pain to fix but you are not sure if it is down to the maintenance release or a bug in the new release.

I prefer the way other Oracle products work where you can install a new release to a new Middleware home and then just run an upgrade assistant, once confirmed working the old Middleware home can be deleted.

The reason I am going to be trying out the maintenance release today from 11.1.2.3 is I want to see if I hit any issues, I am interested to know what happens with FDM as it is no longer an option in 11.1.2.4 with FDMEE as the replacement, also due to all the architectural changes with HFM I am keen to understand whether the maintenance release cleans up any of the existing components that are no longer required, I am pretty sure I know the answer but you never know I might be shocked.

It is very important to have a full rollback procedure in case the upgrade process goes wrong, I have seen many posts in the past where the person applying the release has totally overlooked this and ended up a complete mess.

I will also point out that housekeeping is definitely a good option before hitting the installer.

The maintenance release does not remove any client installations so these should really be removed before starting.

There is a bit of confusing statement in the documentation which has been there for the last few releases:
  • Apply any required PSEs before proceeding with maintenance the installation.
I have never been sure to what that is referring to as a maintenance release should drop down all the required files and files from a previous release should not be that important

It is a good time to run the registry cleaner utility before applying the maintenance to confirm whether the Shared Services registry is in good shape to proceed, it doesn’t always help much but it does no harm to run it.


All good, on to the install though as I mentioned this is not a step by step document and I would prefer to dig into what is happening when applying which may get a little technical.


So the installer has been started with the same user as the original installer and all the prerequisite checks have been met, at this point it is no different than a clean install but what always interests me is how the installer knows a version of EPM has been installed.


The middleware home setting is not updateable indicating a previous release exists.

The way the installer determines a previous version is there is by first checking two system variables.


If the EPM_ORACLE_HOME and HYPERION_HOME variables do not exist the Oracle inventory is then searched for EpmSystem home.


If none of the variables are found then it is deemed a clean install and the Middleware Home can be entered.


If any of the three variables are found then the assumption is a previous version of EPM has been installed.

If a valid version exists the only option will be to apply the maintenance release but how does the installer work out whether a valid version is installed, well this is where the file .oracle.products comes into play.


The file is basically in XML format and contains information about what products components have been installed and the version.


If any products are found that are in the supported upgrade path the apply maintenance reason will be the only available option as the maintenance has to be applied before any new installs.

The file also determines what list of products are displayed to be upgraded in the following screen:


There is an important note at this point

“You must apply the maintenance release to all EPM System products in the deployment. You cannot apply the maintenance release to only some products.”

To show how the file works I will go through a few changes.


In the above example I have set the version to 11.1.2.4 for Essbase Studio Server Samples.


The installer now believes the samples are now at 11.1.2.4 so need to apply the maintenance release.

In the next example I have removed all the children from the essbase node.


Run the installer again and it now thinks no Essbase related products have been installed.


If I remove .oracle.products then installer will be conned into thinking nothing has been installed and hence no option to apply the maintenance release.


Once a maintenance release has been installed this is what you are looking for before proceeding.


Just before completion you will notice the following in the status bar:


At this point not only is .oracle.products being updated but also the files under EPM_ORACLE_HOME\inventory are being updated to 11.1.2.4

An example of the components inventory after the maintenance install.


And if you take another look at .oracle.products the version been updated and the install type has been changed from NEW to MAINTENANCE.


It is interesting at this point to see if anything has happened with FDM in the file.


As expected there have been no changes to the FDM product components.

The maintenance install does not interact in any way with the Shared Services Registry or make any changes under EPM_ORACLE_INSTANCE.

The Shared Services registry comes into play the first time the configuration utility is started and in the background property values are being changed for all the installed components.

Here are some examples of the changes that happen the first time the utility is started but no configuration has taken place.


The utility has gone through the registry and set property values for each product to “PendingMaint

These changes now determine what components are automatically selected and the status in the next configuration screen.


The “PendingMaint” value is very important in a maintenance release as when a product is configured the utility is updating the database structure to bring it in line with PS4 release.

Once a configuration is complete you can see the updates that have been made to the registry.


The registry has been updated to change the status to "Configured" and version updated to "11.1.2.4.0"

There are also important updates being made on the file system under the EPM_ORACLE_INSTANCE directory during the configuration, I am not going to go into them but need to be taken into account in a rollback situation.

As I eluded to earlier a maintenance release does not clean up the previous release and an example is the OCM configuration which no longer exists in 11.1.2.4, after applying the maintenance you are still left with the OCM files, windows service and entries in the registry.



So if you add the products into the mix then you can see it is getting messy compared to a clean install.

If you take HFM as another example you will see registry entries that are no longer required if you compare a clean install to a maintenance.



As for FDM all Shared Services registry entries remain untouched in a 11.1.2.4 maintenance.

Taking a look at the file structure for HFM you will see how polluted a maintenance release gets.


Above is only an example of the exe files in the FM server directory after a maintenance compared to a clean install below.


In IIS you will see that nothing has been removed for HFM even though it is not required in 11.1.2.4


It is the same story with DCOM objects with the maintenance version with all the objects left.


In a clean install of 11.1.2.4 it is a different picture.


The windows services for HFM are cleaned up though with only two remaining.


Going back to the configuration process I know I have mentioned a rollback procedure a few times and I have good reason to as I got hit by an issue when configuring the foundation database.


Inspecting the logs highlighted the issue but not the reason why.

Error: {0}[[
com.hyperion.hit.registry.exceptions.RegistryException: Unable to create registry.
    at com.hyperion.hit.registry.RegistryConnection.createRegistry(RegistryConnection.java:358)
    at com.hyperion.hit.registry.RegistryUtils.initUpgradeRegistry(RegistryUtils.java:339)
    at com.hyperion.hit.registry.RegistryUtils.upgradeRegistry(RegistryUtils.java:141)
    at com.hyperion.foundation.config.FoundationDbConfigurator.configure(FoundationDbConfigurator.java:87)
    at com.hyperion.config.wizard.impl.RunAllTasks.executeDbConfigTask(RunAllTasks.java:685)
    at com.hyperion.config.wizard.impl.RunAllTasks.execute(RunAllTasks.java:306)
    at com.hyperion.config.wizard.impl.RunnAllTasksState.run(RunnAllTasksState.java:92)
    at java.lang.Thread.run(Thread.java:662)
Caused by: java.sql.BatchUpdateException: error occurred during batching: ORA-00001: unique constraint (FOUNDATION.PK_ROLES) violated


After a while of researching I did finally manage to find the cause and it was because a planning patch had been applied in 11.1.2.3 that made changes to Shared Services roles, the roles have not made it into 11.1.2.4 so caused a serious failure.

Luckily I could rollback remove the changes to the Shared Service database and then configure successfully.

The configuration utility does not automatically select to upgrade HFM application from an earlier release and has to be done manually.


Once again it is interesting to understand what is happening behind the scenes, I won’t go into full detail but basically a batch file is called which initiates a java class.

The Java builds up a command line to call the HFM application upgrade tool.


In 11.1.2.4 there is no GUI available for the tool and it has to be run silently using command line just like what the Java program generates from reading the Shared Services registry.

Here is an example of the command line that is generated:

E:\Oracle\Middleware\EPMSystem11R1\products\FinancialManagement\Server\HFM Application Upgrade_x64.exe
 -silent -dbname=FUSION -dbport=1521 -dbuser=HFM –dbpassword=HFMx3ds3s1 -dbtype=Oracle -dbserver=HFMTNS -eoi=E:\Oracle\Middleware\user_projects\epmsystem1


The HFM database is also updated


Logs are generated are in EPM_ORACLE_INSTANCE\diagnostics\logs\upgrades\hfm


Yes the password to the database is written to the log :)

Once the upgrade has been completed the Java program will register the application in Shared Services if it is not already registered.

The utility can be run multiple times from command line or the configuration utility by manipulating the database table.

On the FDM side of things it is still possible to log into the applications after the maintenance release and I even managed to load data from FDM to 11.1.2.4 essbase though I am not saying that it is supported and how long it will work for, it won’t obviously work with the HFM adaptors due to 11.1.2.4 HFM moving into the Java world.

I think I am going to wrap up the 11.1.2.4 maintenance post now as it has taken a fair bit of effort to put together even though I have removed a lot of content which was not only to stop boring the hell out of you but I also don’t want to give away too many secrets :)