Financial Management (HFM) Performance Problems When Using SQL Server Database

Is your Hyperion Financial Management System running "slow?"  That's good, because now you have a chance to "catch it" and maybe speed it up!!  If you have more than one HFM application server running against a Microsoft SQL Server backend for your database, then please follow these steps to either fix the issue or confirm that this is not causing the issue.The standard procedure when using Microsoft SQL Server database as a repository for any Oracle Hyperion product is to run the following SQL query for all databases prior to configuring your software:

Set READ_COMMITTED_SNAPSHOT = ONSet ALLOW_SNAPSHOT_ISOLATION = ON

This pre-requisite requirement can be found in the Oracle Enterprise Performance Management System Installation and Configuration Guide.However, it has been recently discovered, that this database setting has potential negative performance impact on certain HFM Application tasks, such as consolidations.Run the following SQL query against all existing HFM databases within your environment(s) to confirm the state of the current database setting.  If your results display a “1” or “on” then please proceed to our offered solution below.

select name, is_read_committed_snapshot_on, snapshot_isolation_state_desc from sys.databases

 Solution:First, confirm that all Oracle EPM Service Software has been stopped.Second, if possible, perform a restart of the Microsoft SQL Server Windows service to clear any orphaned processes.Third, perform a cold back up of your HFM database.  ***This is a critical step***Last, you will run the following SQL query against the HFM database.

alter database <HFM_DB>

set allow_snapshot_isolation off;

alter database <HFM_DB>

set read_committed_snapshot off;

 In our example we changed the <HFM_DB> within the SQL query to EPM_FM, the name of our Financial Management database.Now you can go back and run this query again to confirm the change has taken hold:

select name, is_read_committed_snapshot_on, snapshot_isolation_state_desc from sys.databases

 Please note the change in the EPM_FM state to “0” and “off.” When this process is complete, please go ahead and restart your Oracle EPM Service Software.  We always suggest you perform your standard validation to confirm that your environment is still functioning properly.  While there is no guarantee of the amount of performance improvement, through our own testing, we were able to see a client's Financial Management consolidation process reduced from over 2 hours to approximately 15 minutes.We at iArch Solutions hope you found this blog helpful. Is there a question you want answered in a blog?  Send us your questions, comments and feedback.APPLICABLE VERSIONS: 11.1.2.4