Add an HFM Data Source

You must add a Hyperion Financial Management (HFM) data source so that CDM users can use to create and refresh HFM formulas at the report object level.

Users do not create queries using an HFM data source. After an HFM server is defined, users can create and refresh HFM formulas. These formulas are resolved when a report is generated.

Supported HFM Formulas

These HFM formulas are supported:

• HsGetValue
  Retrieves data from a data source for the dimension members that you specify.
• HsGetText
  Retrieves the cell's text description from a data source for the dimension members that you specify.
• HsLabel
  Displays the default member label for a specified dimension member.
• HsCurrency
  Retrieves the currency value of the specified dimension member.
• HsDescription
  Displays the description of the specified dimension member.

Setup

A batch file (provided at CDM installation) on the HFM server must be run to allow CDM Data Source Connector (DSC) web applications to connect to it. If adding an HFM data source fails, check that this batch file has been run.

To connect to HFM 11.1.2.4 from CDM requires deploying the CDM Data Source Connector 3.0 on the HFM Server machine (not the foundation server). The DSC files are provided by Certent CDM Support upon request.

Prerequisites

• JDK 8 or higher, deployed on the HFM server
• Java certificate converter (recommended: openSSL)

Unsecured Connection Configuration

CDM Server Side

No configuration is required.

HFM Server Side

1. Run the config.bat file from CDM Data Source Connector 3.0 and enter this required data:
   • javaw.exe location (default location for JDK8: C:\Program Files\Java\jdk1.8.0_191\jre\bin\javaw.exe).
   • EPM Home directory (default: C:\Oracle\Middleware\EPMSystem11R1).
   • EPM Instance directory (default: C:\Oracle\Middleware\user_projects\epmsystem1).
   • Port the connector should listen on (default: 7911).
   • Type false when prompted for a secure connection.
2. After the configuration process is completed, run the start.bat file to start the Connector.

Secured Connection Configuration

CDM Server Side

1. Import the certificate you want to use for the connection in IIS in the Server Certificates area. Alternately, the certificate can be created in IIS directly using the IIS manager and exported to file.
2. In the CDM.config file from the connector web application (default location: C:\inetpub\wwwroot\CognosDM-Server\Connector\Config), modify the DataSourceConnector key as follows:

   <DataSourceConnector useSecureConnection="true" serverCertificateName="%certificateNamefromIIS%"/>

3. Call an IISReset.

HFM Server Side

The certificate file obtained from the CDM server-side configuration must be converted to a keystore file with a .jks extension. This can be done with any utility that supports the conversion. Certent recommends the openSSL freeware that has been used during internal testing.

To convert the certificate using Certent recommended method:

1. Copy the certificate from the CDM Server to the HFM server.
2. Run the certconvert.bat file from the CDM Data Source Connector 3.0 and enter the required data:
   • Certificate file path (for example: C:\Certificate.pfx).
   • Certificate password (the one it was exported with).
   • Path to the openSSL executable (default: C:\OpenSSL-Win64\bin\openssl.exe).
   • Path to the keytool executable from the deployed jre7 (default: C:\Program Files\Java\jdk1.8.0_191\jre\bin\keytool.exe).
   • Password for the resulting keystore.

After this operation is completed, an hfmconnect.jks keystore file is created in the CDM Data Source Connector 3.0 directory which is used to configure the Connector.

To configure and start up the connector:

1. Run the config.bat from the CDM Data Source Connector 3.0 directory and enter the required data:
   • Input javaw.exe location (default location for JDK 8: C:\Program Files\Java\jdk1.8.0_191\jre\bin\javaw.exe).
   • Input the EPM Home directory (default: C:\Oracle\Middleware\EPMSystem11R1).
   • Input the EPM Instance directory (default: C:\Oracle\Middleware\user_projects\epmsystem1).
   • Input the port the connector should listen on (default: 7911).
   • Type true when prompted for a secure connection.
   • Path to the jks keystore (default: <HFMConnectorPath>\hfmconnect.jks).
   • Password for the keystore.
2. After the configuration process is completed, run the start.bat file to start the Connector.

Add a HFM Data Source

1. In CDM, do either of the following to open the Data Source Connectivity tab in the work area:
   • Click the File tab, then navigate to Administration > External Data > Data Source Connectivity.
   • Click the Home tab, then in the Navigation Pane, click Administration and double-click Data Source Connectivity under External Data.
2. Click Home > Add > HFM Data Source, or right-click any existing data source in the work area and click Add > HFM Data Source.
3. Specify the connection parameters.
4. Click Test Connection.
   • If the connection succeeds, the Connection successful icon is displayed.
   • If the connection fails, the Connection test failed icon is displayed. Check with the database administrator to ensure that the database server is running and that you have the correct parameters. You can also expand the error message to see further details to help you troubleshoot the connection.
5. Click OK.

Users can now refresh HFM formulas that use the data source. These formulas are resolved when a report is generated.

Connection Parameters for HFM Data Source

To connect to HFM as one of your data sources in CDM, use the following parameters: