Thursday, 3 January 2013

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

6 comments:

  1. Great article!! I have been following some of your posts and they are too good. Thanks alot for sharing information.
    I was hoping to see a post from you on the new HFM database structure for Custom dimensions as now HFM can have more than 4 custom dimensions because of which the architecture has changed in you see physical columns for only Custom 1 and Custom2 and no columns for Custom 3 and Custom 4 dimensions. Do you know where is teh data for Custom3 and Custom 4 stored in HFM database

    ReplyDelete
  2. HI John,
    hope everything good at your end, can you throw some more light on 2nd approch i have the BR export file , but when i goto migrate option in Calcm i dont see any option to select the .xml file
    regards
    Vinodh Kannan

    ReplyDelete
  3. Thanks for the article. I had one question. You mention towards the beginning that you should upgrade under the configuration utility in 11.1.2.2 under EAS you should upgrade the database. I thought EAS was not available under 11.1.2.2

    ReplyDelete
  4. Hi Vinodh,
    If you have placed the xml in the correct place then you should only have to use the migrate option in Calc Manager to automatically pick up the xml.

    ReplyDelete
  5. Hi DM,
    EAS is definitely still around in 11.1.2.2 but Business Rules are not.

    ReplyDelete
  6. Hi John,

    We are doing Hyperion migration from 11.1.1.3 to 11.1.2. we are finding difficulty in migrating the user/groups and security as the utilities and LCM wouldn't work. Also need some pointers for Planning application migration.

    Thanks in Advance!
    -LVV

    ReplyDelete