Reporting & Analysis Framework Fun

Sometimes the simple becomes complex, and you spend more time fixing the little things than expected.  Recently, a client was changing domain names in an 11.1.2.x environment, and issues were encountered with Reporting and Analysis Framework.  This issue will not pop-up in the new 11.2 versions, since Framework has gone the way of the Dodo.  But there are a LOT of clients still on earlier releases, and so this one is probably of some interest.

The EPM toolkit provides a couple of ways to update host values.  In particular, the EPMsys_Registry.bat application offers triggers to update both the hostname, and individual parameter values.  While it can be involved (since the update host change does not seem to update any localhost_name values), the process is straight-forward and well documented by Oracle.

Where an issue was encountered was in updating the V8 tables under Reporting and Analysis.  These values are set during the initial configuration of the system, and they are locked by various table/DB constraints, so editing them in SQL Plus or the Microsoft SQL Management Console is not easy.  Also, a re-configuration prior to the steps below did not update the domain/host values listed in the tables.

Instead, the process to update them requires deleting values in the Shared Services database, and then the BI/RA database.  In both cases, it is recommended that the contents be backed-up before proceeding with any changes.  

Once you have the backups in hand, you can follow the process below:

1. Backup the RM1 folder (this is the file structure for Reporting and Analysis)
2. Stop all services and run following two SQL statements against EPM registry database and RA/BIPLUS database

Execute the following statement against the EPM Registry (Shared Services/Foundation) DB/schema:

delete b from hss_component_property_values b where b.component_id in (select a.component_id from hss_component a where a.component_type_id in(49, 50,51,52,53,54,55,56,68))

Execute the following statement against the EPM Registry (Shared Services/Foundation) DB/schema:

delete a from hss_component as a where a.component_type_id in(49, 50,51,52,53,54,55,56,68) 

Execute the following statements against RA/BIPLUS DB/schema:

delete from v8_sa_props

delete from v8_serviceagent

delete from v8_host

Re-run the EPM configuration on hosts running the raframework agents, and select the following tasks:

• Configure Database (note: select to reuse the tables instead of dropping and recreating)
• Configure Framework Services
• Configure Reporting and Analysis Services

Once the above is complete, you will now be able to start the various RA Framework services and successfully explore and execute reports.