Copy HFM Applications Using The Import Application Feature

In this post I document the steps required to configure the Import Application feature introduced in HFM 11.1.2.4.200. I recommend reading my earlier post about the Patch Set Update if you haven't done that already. Note that the configuration is database vendor specific and I used MS SQL Server – if you need guidance for setting things up with an Oracle database you can see Larry Lapp's recent blog post about the same topic.

Prerequisites

• HFM with Patch Set Update 11.1.2.4.200 (patch number 21225611) installed 
• Access to SQL Server Management Studio with sysadmin privileges
• SYSADMIN role granted to the SQL login used by HFM (Yes – the almighty sysadmin role. This requirement is documented as known issue #21032270 in the PSU .200 readme)

Create Linked Server

The Import Application feature introduces a way to copy HFM applications from a pre-defined source database to the current HFM environment (not the other way round – hence the term import). To use the feature you need to add the source database as a linked server on the destination SQL Server. Following the server diagram shown below you would add the Test server as a linked server on the Production SQL database in order to be able to import applications from Test.

The following MS SQL specific statements can be used to create a new linked server. Note that the commands are also documented in the updated HFM Admin Guide but the current version of the document contain several syntax errors!

In this example the source server is called "hvtest2.local" and the SQL user/login accessing the database is "hfmdbo". The identifier "TESTSQL" can be chosen freely.

exec sp_addlinkedserver N'TESTSQL',@srvproduct=N'',@provider=N'SQLNCLI',@datasrc=N'hvtest2.local'

exec sp_addlinkedsrvlogin N'TESTSQL',@useself=N'FALSE',@rmtuser=N'hfmdbo',@rmtpassword=N'SekretPasswd'

exec sp_serveroption @server=N'TESTSQL',@optname=N'rpc',@optvalue=N'true'

exec sp_serveroption @server=N'TESTSQL',@optname=N'rpc out',@optvalue=N'true'

Execute the commands and verify that you receive a successful status. Now you should be able to test the linked server using a query such as:

select * from [TESTSQL].[HFMDB].[dbo].[HSX_DATASOURCES]

The linked server should also appear in the server browser in SQL Management Studio.

After creating the link there is one more thing to finalize the SQL Server configuration. Patch Set Update .200 includes a SQL script called SQL_Server_Create_ImportApp_sprocs.sql that is supposed to create stored procedures for use by the import process. The script is not mentioned in the HFM documentation so I assume it should be executed automatically by OPatch... But that didn't happen on my install. Therefore it's a good idea to run the script against the HFM database before attempting to use import feature. The script file can be found in the HFM server installation path, e.g. D:\Oracle\Middleware\EPMSystem11R1\products\FinancialManagement\Server\conf.

HFM Web Interface

Now that the SQL side is set up, you can log in to Workspace and navigate to Navigate > Administer > Consolidation Administration. Select Import Application, where you should now see the Linked Server and Source HFM Database menu items properly populated.

To copy an application, select the linked server, then the HFM database name and the source application. Then fill in the details for the destination application. The options on the bottom of the Import Application panel may be used to leave out HFM audit and data tables and also to copy only selected years and scenarios.

After clicking the Import Application button uou will receive a friendly warning.

Once started, you can observe the status of the import process in the Admin Tasks view in Consolidation Administration. It's a good idea to check the generated log file for errors or warnings.

The imported application is automatically registered to the selected HFM cluster so you can provision roles and start using the app right away.

To summarize:

• Import Application only works between databases of same type (SQL-to-SQL or Oracle-to-Oracle).
• The feature requires configuration on the database side – but only before using it for the first time.
• Copying/importing an application happens directly from database to database. Firewalls between database servers in Test and Production will prevent the usage of this feature (unless you can get the required ports opened). 
• Importing applications is a one-way process. To copy applications Test-to-Prod and Prod-to-Test you need to carry out the SQL configuration tasks separately in both environments' databases.