The HFM Express Adapter is a universal adapter for all versions of Oracle (Hyperion) Financial Management until version 11.1.2.4. The main difference with the existing HFM Adapter is that the HFM Express Adapter does not rely on the Data Extraction module of Financial Management (also known as Extended Analytics). Instead it extracts data and metadata by directly connecting to the relational database underlying HFM (SQL Server or Oracle). Also, the configuration and execution of the data extraction process has been simplified.

Prerequisites

If the relational database underlying HFM is an Oracle database, then ODAC must be installed on the CXO Cockpit Application Server. We tested version 11.2 Release 5 (11.2.0.3.20), but newer versions will also work.

Creation of a HFM Express Source System adapter

Creation can be part of the creation of a new CXO-Cockpit application or the creation of a Source System within an existing application. In both cases the following wizard will appear:

Select 'Use Backend System Adapter'
Select Adapter 'Hfm Express'
Enter a name (usually the name of the underlying HFM application)
Press Next / Next / Finish

After finishing, two more databases have been created:

cxo_fact_<name>: the database that will hold the extracted data and dimension information. At creation of the source system it will contain some sample data not related to the HFM application yet
cxo_hfmexpressstaging_<name>: a database that will be used to store the specifications of the data-extraction process. Also, during extraction it will act as a temporary storage for the raw data-extracts.

Also an Analysis Services OLAP Database will have been created (name = <name>), initially containing sample data.

Connection to HFM

Open the Source Sysem adapter console as follows (select the right adapter):

In the first tab, enter the connectivity details to the HFM database:

DB Type: We support 2 Database Types: SQL Server and Oracle.

SQL Server:
SQL Server: The SQL Server is the SQL Server instance where the HFM data are stored. Note: as a rule this is not the same database-server as used for the CXO-Cockpit databases.Consult your HFM System Administator to get these specs.
Database: The name of the HFM database (usually something like FM, HFM, HYPPROD, ...)
Application: The name of the HFM application from which we want to extract.
Authentication:
- Check the box if you want to access the HFM database by Windows Authetication: Note that not only the user of the Design Studio ('you') should be authenticated, but also the Windows account that is used as a Service account for the CXO-Cockpit services. Data extractions are carried out by our Agent Service:
  
  The Windows account(s) must have read-rights on the HFM database.
- If you want to use a SQL Server account: enter a username / password. Also this account can be a read-only user.
Oracle:

For an Oracle connection a TSN configuration is required on the CXO Cockpit application server:

Consult your Oracle DBA for more details.

ICP Specifications

In this dialog you can also set two parameters related to HFM Intercompany (ICP) transactions:

Extract ICP Details: check this box if you want to report on ICP transactions. Note that this may result in a lot more data records and will slow down the extraction and transformation process.
Aggregate ICP Entities: this is a reflection of a setting within HFM named ICPEntitiesAggregationWeight. Checking the box is equivalent to an weight of 1.0 (this is most widely used). Un-checking means 0.0.

Retrieve HFM Meta-data

In order to setup the extraction (creating Load Sets and Subsets) we first need to retrieve a local copy the HFM Meta-data, mostly the HFM Dimensions.

This can be done by pressing the button 'Get Metadata' and then wait (few seconds to couple of minutes) until the following dialog appears:

Note that copying the metadata has to be done each time the dimensionality of HFM has changed (added / removed / moved members of a dimension)

Load Sets

The Load Sets determine the combination of Scenarios, Years and Periods that will be extracted from HFM.

There are two types of Load Sets:

Load Set for a full data refresh. When executing such load set (of which you can only have one!) first all data and dimensions in the CXO Cockpit databases are cleared and then refilled with new data. This only needs to be done when:
1. No extractions have been done yet
2. Data in the past have changed
3. New HFM metadata have been loaded
4. You want to make an alternative selection from the HFM dimensions (more / less details from the Entity dimension, Account dimension, more Scenarios. I.e., any action that require rebuilding of the dimensions in the CXO-Cockpit application.
Incremental Load Sets: these can be used to quickly replace a slice of data usually one Scenario, one Year and one Period. The CXO dimensions are not refreshed. Incremental Load Sets can be executed manually but can also be used for Real-Time Synchronization.

Base Set and Subsets

A Base Load Set (type 1) looks like:

A Base Load Set can have several Subsets. During an extraction these Subsets will be executed sequentially in the order in which they are entered. Each Subset has its own combination of Scenario(s), Year(s) and Periods. Since they are all done within one data-extraction action they should not overlap.

Usually, the first Subset (named 'Base' in this example, but you can use any name) extracts the history of the Actual numbers. In this case we extract the Actual numbers for all months of 2006 (note that 2007 is the current reporting-year in this Comma application).

An important rule for the Period dimension is that you only select periods that belong to the native frequency of the selected Scenario: The Actual scenario is a monthly scenario, hence we only select months.

Let's add another Subset named CurAct (the actuals for current year):

Press the '+' and then rename the Subset in the Edit box:

Now for this Subset we select Scenario = Actual, Year = 2007 and Period = Jan, Feb:

This perfectly illustrates why Subsets are useful: for 2007 we are only interested in two months, while for 2006 (the history) we want all periods.

We also make a special Subset for the Budget scenario: two full years:

Other HFM dimensions

To complete a Base Load Set we must also select at least one member from each of the other HFM dimensions.

Value

A typical selection from the Value dimension is:

This will extract each Entity in it's own currency and all Entities in USD (provided the Entities are translated into USD within HFM).

Include the [None] member if you want to report on / use Currency Conversion rates.

Note that we will not extract 'XXX Total' itself: always the underlying 'XXX' and 'XXX Adjs' members are extracted. In the example of 'USD Total', in CXO we will see:

Entity

When selecting members from the Entity dimensions you should expand the tree to the level you want to include. For example:

In this example, in HFM more levels exist, but we deliberately decided to leave out details. It is not needed to expand to a fixed level. We could have decided to only expand EastRegion further.

It is also possible to skip a parent node. Suppose we don't want to have UnitedStates as an intermediate node in CXO, then we can de-select it:

This would move EastRegion and WestRegion one level up, making them children of Geographical.

From a functional perspective you should not do this is you selected [Contribution Total] in the Value dimension, because that selection only reports correct numbers if the parent-child structure is equal to the original.

To get Currency rates in CXO, also include Entity [None] in the selection.

Account

Selecting members from the Account dimension is done slightly different:

Like with Entity, the members that you do not select will not be included in the corresponding Account dimension in CXO. A major difference is, however, that all the levels below a selected member will automatically be included in the extraction and the dimension build-up in the CXO-Cockpit application. So, even though we only selected 'NetProfit', within CXO we will include all details of the underlying members all the way down to the base-level:

Best practice is to avoid multiple occurrences of members in the selection. This will slow-down the Processing of the OLAP database and/or the report queries. In doing that you should always realize that we extract all details under a certain parent node: even if you did not select duplicate members they can be there implicitly among the descendants of selected nodes.