EPM 11.1.2.2 configurator – host unreachable

I was recently working on an 11.1.2.2 Windows 2008R2 environment build and was hit with the following error in the configurator:

The error didn’t occur at first as I was able to successfully configure a number of products but when I started up the configurator again it started to warn that the database server was unreachable, at first I was hit with an anxious feeling that I had messed up but I was sure I had configured correctly and I knew the database server was definitely accessible. 

After clicking “OK” to the warning all the correct component configuration was displayed so there were no problems connecting to the database.

I did keep on ignoring the error message as there were no signs of problems with configuration and I put it down to a possible firewall issue with the database server but the back of my mind I did want to know the answer.

I recently stumbled up the following information:

“EPM System Configurator tests whether this address is reachable. A best effort is made to reach the host, but firewalls and server configuration may block requests, resulting in an unreachable status even though some specific ports may be accessible. The Java call typically uses an ICMP ECHO REQUEST if the privilege can be obtained; otherwise, it will try to establish a TCP connection on port 7 (Echo) of the destination host.

You can ignore the warning and continue, or you can open up port 7 and the warning will no longer be displayed.”

I thought I would have a look at confirming the information was correct and enabled the firewall on a 11.1.2.2 VM environment, starting up the configurator generated the same warning message.

 

 

I did enable ICMP in the inbound firewall rules on the database server and was able to ping it but unfortunately it did not resolve the issue for me in the configurator.

I ran a Wireshark trace when opening the configurator and you can clearly see the echo request of port 7 to the database server.

I then added a new firewall inbound rule on the database server firewall to allow all connections on TCP port 7.

I ended up rebooting everything just to be sure, started up the configurator and success the warning message was no more.

So if you do get hit by the warning don’t panic as it may just a firewall causing the pain.

Alternative methods for migrating Planning 9.3 Business Rules to 11.1.2.2 Calculation Manager

As you are probably well aware that Business Rules are no more in 11.1.2.2 and Calculation Manager is the product to manage rules, Hyperion Business Rules were always quite problematic and over the years I have seen so many issues and posts relating to them and to be honest the functionality never improved through the versions so I welcomed the end to HBR.

Now the supported upgrade path to 11.1.2.2 is either 9.3.3, 11.1.1.3/4 or 11.1.2.x though it is certainly possible to upgrade directly from 9.3.1 in the Planning/Essbase world as I have successfully achieved it on a number occasions but this is not the topic for today, if you are interested in upgrading from 9.3.1 to 11.1.2.2 then do get in touch.

I am going to go through a couple of alternative methods to migrate from 9.3.1 Business Rules to 11.1.2.2 Calculation Manager.

The first method requires access to a 9.3.1 EAS relational database; it does not have to be a live system and can be a backup of the database which personally I think would be the preferred method anyway.

I will keep the example nice and simple for demonstration purposes with a 9.3.1 environment containing just one business rule and associated variables.

There are three Global Variables (one of them is not associated with the rule) and there is one local variable “Scenario” used in the rule.

The location is set against the planning application, if the location is set to “All Locations” then when importing into Calculation Manager the planning application will be ignored and the rule can be imported into any application so depending on the number of rules it could end up being really messy if you don’t set the correct locations before the migration.

Right on to a preconfigured 11.1.2.2 environment, this doesn’t necessarily have to be a live environment and could easily be a personal VM just as long as it can access the 9.3.1 EAS database.

Start the EPM configurator and select “Configure Database” for EAS, the warning message about also deploying the web application can be ignored as once the migration has been completed the database will be reconfigured back to its original state.

Select “Upgrade existing database to the current release from 9.3.3+”, yes I know the version that I am connecting to is 9.3.1 but it doesn’t matter.

It is also possible to then configure the planning system database and select upgrade from 9.3.3+ but I am not going down that route today though before moving to the next stage planning should have been configured.

Log into Calculation Manager and select the application you wish to import the business rules into, selecting Migrate will execute a utility in the background that connects to the EAS database and export the entire HBR repository to an xml file and then imports into Calculation Manager.

Select The Planning application information and migrate options.

The import screen should contain all the rules and variables from the 9.3.1 environment and  if it doesn’t then the utility that is run in the background may have failed, it is possible to run it manually to find out what the issue is by carrying out the following steps.

If you go to:

<MIDDLEWARE_HOME>\EPMSystem11R1\upgrades\planning then there is a utility called upgrade_p2.bat

The syntax for the utility is

upgrade_ps2.bat EPM_ORACLE_HOME EPM_ORACLE_INSTANCE

So in my case it can be run from command line with the folllowing

upgrade_ps2.bat E:\Oracle\Middleware\EPMSystem11R1 E:\Oracle\Middleware\user_projects\epmsystem1

If the utility did not complete successfully there is a log generated at

<MIDDLEWARE_HOME>\user_projects\<instancename>\diagnostics\logs\upgrades\planning\upgrade_ps2.log

The utility will connect to the EAS database and export the entire HBR repository to a file called HBRrules.xml

The utility will create the EPMData\planning directory is it does not exist.

Once the file has been created you can go back into Calculation Manager and select the migrate option again.

In the system view for the planning you can see the business rule from the 9.3.1 environment has been imported.

Opening the rule confirms that it has been converted into the Calculation Manager format and the local and global variables are available.

If you wanted to import the rules into a different database you could edit the HBRrules.xml

Update the application name and plan type in the locations section to the application you wish to import into and then once again select migrate in Calculation Manager.

The rule and associated variables should be imported into the updated planning application.

Once the rules have been imported it is easy to move them to other 11.1.2.2 environment by either exporting from Calculation Manager or by using LCM.

Once happy with the migration you can run the EPM configurator again and point the database back to the original database, first-time configuration can be selected (only four database tables exist now in 11.1.2.2 EAS which are for the rarely used log analyser)

So that is the first method covered off so how about the next method, well I was looking at the format of the HBRrules.xml file and it looked remarkably like a standard xml export from Business Rules, it was slightly larger in size and the order of the xml was not exactly the same but it was close so I thought whether it would be possible to try and migrate using the xml.

For this you do need to have an operational 9.3.1 environment.

In 9.3.1 Business rules select the “Export Business Rules”

Select the required rules and dependents and then export.

Set the File name as “HBRrules.xml”

Copy the file across to the 11.1.2.2 environment.

Create the directory: <MIDDLEWARE_HOME>\EPMData\planning and place the “HBRrules.xml” file in there.

In Calculation Manager select the migrate option and the rules and dependents from the 9.3.1 exported xml file should be imported.

So there you have two alternative methods, the first one will import the full HBR repository and the second method allows you to select the objects you wish to migrate.

I have not had the chance to try it on prior versions to 9.3 but it is probably worth a go to see if it works.

Please note that these methods are probably not officially supported but sometimes you have to break to the rules to achieve your goals :)