The HFM Express Adapter is a universal adapter for extracting meta-data and data from all versions of Oracle (Hyperion) Financial Management until version 11.2. The Adapter can be managed by the Source System Manager. You need to be an Admin user within CXO.

Prerequisites

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

Creation of a HFM Express Source System adapter

Creation can be part of the creation of a new CXO 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), then press Next
If SQL Server Analysis Services runs on another server than the SQL Server used for the CXO Databases, then in the next screen you can override this:

This option is rarely used
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.

Connecting to HFM

You can open the Source System adapter console by pressing the button Integration Settings in the above window.

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

You can check the connectivity by pressing the Check button.

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

Settings for 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.
Windows Authentication:
- Check the box if you want to access the HFM database by Windows Authentication: 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.
Settings for Oracle:

For an Oracle connection a TNS configuration is required on the CXO 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 a weight of 1.0 (this is most widely used). Un-checking means 0.0.

*turning ICP aggregation off will load the ICP hierarchy.

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 (the dimension specifications).

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

This process has to be repeated each time the HFM application has changed: new dimension members added, members removed, aliases changed, etc.
After retrieving the HFM meta-data you should check the selected Dimension Members (see below).

Manual update of CXO dimensions

After retrieving the latest version of the HFM meta-data, usually a full (base) extraction is done (see details further in this document) to make sure that all current and (restated) historic data is retrieved taking the updated dimensions into account. A full (base) extract automatically implies that also the CXO dimensions are updated.

Sometimes this is not feasible because the phase of the reporting cycle does not permit the waiting times associated with a full data-refresh. In that case you can apply a shortcut by pressing the button 'Update CXO Dimensions':

Now you can skip a full data-refresh and start an incremental load or a real-time synchronization.

Create a Calendar

After retrieving the meta-data for the first time, we still have to perform one manual step: fill a table in the cxo_hfmexressstaging_... database with the specifications of the calendar.

We have prepared the script in a Stored Procedure sp_create_calendar. Select the stored procedure:
, then do a right mouse click and select Modify:

The following script will appear:

This script can be used to define various calendars. To generate a calendar with a Half Year / Quarter / Month layout:

select the first block ('truncate ...') and press F5. This will clear the current calendar table.
then select all lines in the 2nd block and press F5
you can then close the window. Ignore the requests for saving.

In the comment-area you can see an alternative calendar: only a Quarter level and the months abbreviated to 3 characters. Of course, you are free to change the layout, for example to accommodate a broken fiscal year.

To create the right calendar, check the structure of the Period dimension in the HFM application. For example, for the COMMA application it is:

There are a few rules attached to this script:

The Period ID should be a continuous series 1, 2, 3, ...
The Parent ID for the root level (Year) is always -1. For the other members it should define the hierarchy (e.g., all months in Quarter 1 have Parent ID = 4)
The Frequency for the Year level is always 2, and for each additional level the frequency increases by 1. That's why the Frequency for the months is 5 for a layout that includes a Half Year level and 4 for a layout with Quarters only.
The Unit of Time: Y = year, H = Half Year, T = Trimester, Q = Quarter, M = Month, W = Week, D = Day.

Note that creation of the calendar is a one-time action and does not require any maintenance.

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 this 'Base' load set (of which you can only have one!):
1. All data in the CXO fact-database is cleared;
2. All dimension tables are cleared;
3. The dimension tables in the fact-database are built up again
4. The requested data-selection is extracted from HFM;
5. This new data is transformed and copied to the fact-database in portions of one Scenario / Year / Period combination.
  
  A full extraction 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. In fact any action that requires rebuilding of the dimensions in the CXO 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: Then press Save:

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 (February being the current reporting month), 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:

In CXO the post-fix 'Total' will be omitted
<Entity Curr Total> will not appear in CXO. To work with local currency a Cube Calculation has to be defined.

Note that - opposite to the older adapter versions - the HFM Value dimension is not stored in the VAL dimension of CXO anymore but in the GAP dimension. The reason is that the VAL dimension is a flat dimension not allowing parent-child structures. This should be kept in mind in migrations.

Avoid selecting [Contribution Total]

If you do not need [Contribution Total] or one of it's components, it is highly recommended to not select this Value. It will almost duplicate the number of records in the database

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 can be done in two different ways:

Selecting a node automatically means that you select the entire sub-tree below that node;
WYSIWYG ('What You See Is What You Get ') mode. That means if you select a parent node but you do not expand it, then we will consider that node as a base-node in the CXO Account dimension.

The selection mode is governed by a System Variable (see section System Variables)

Tree selection mode (mode 1)

You can recognize this mode by the red font of the selected entities. In this example, the selection of NetProfit implies that we get this account as well as all it's descendants all the way down to the base level.

Note that NetProfit is selected twice (green circles). In this case it doesn't make sense, but it can be that you implicitly select it twice because NetProfit is copied under one of the other selected nodes. The way how this kind of duplication is handled is explained below where we describe the System Variable PreAggregateAccounts

Further note that OtherInfo is expanded because we do not want all members under that node. Unchecked members, like Rates, are not included.

.

A fragment of the resulting dimension in CXO looks like:

Tree-selection mode is the preferred mode because it requires the least amount of maintenance. For instance, if in HFM accounts are added or deleted they are automatically selected for CXO if their root node is selected.

WYSIWYG mode (mode 2)

In this mode we use black font. Similar to the Entity dimension, the resulting Account dimension in CXO exactly looks the way you expanded the selection.

This then would be an unrealistic selection:

because it would mean that in CXO we e.g. only select NetProfit and none of the accounts determining this account. A more realistic selection (for NetProfit) would be:

resulting in:

General

Irrespective of the selected mode, 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.

Opposite to the Entity dimension:

It is not allowed to skip parent-nodes
It is risky to skip certain sibling nodes: unlike with Entities, the aggregation of child accounts into parent accounts is done within the OLAP layer of CXO. If you deliberately skip siblings then the numbers on higher levels may be incorrect.

In this example, the Sales account is excluded, which means that TotalRevenues doesn't have a correct value. Sometimes you can use your knowledge of the underlying system to de-select a child. For example, it could be that you know for sure that SalesReturns will never contain any amount. In that case it's permitted to skip it.

Another condition that allows for skipping siblings is that he parent (and grandparents) is either not used or is a so-called Group Label. For example, 'Otherinfo' is a group label:

Custom Dimensions

The same logic as the Account dimension applies to selecting members from Custom dimensions:

In this case, we selected. Closingbalance and the entire tree below it.

We deliberately skipped the [None] member in the root because we know that it is also present in the ClosingBalance tree.

Running extractions

After specifying the Base Load Set, including the selection of the dimension members, the extraction can be started:

The trigger for an extraction will be picked up by the CXO Agent Service. To monitor the progress, you can (repeatedly) press the button 'Show Progress':

After finishing the extraction you can press the button 'Process OLAP' to feed the extracted data into the OLAP cube.

Incremental Extractions

Running a full Base extraction is only required after an update of the HFM meta-data, when the history of Actuals is restated or if a new Scenario / Year / Period combination is added.

During the closing cycle it is sufficient to run incremental extractions: only one or a few combinations of Scenario / Year / Period will be extracted and replaced in the over-all facts.

You can add an Incremental Load Set by pressing the '+' button and then Edit the name of the Load Set. By definition, each Load Set added to the Base is considered to be an Incremental Load Set. Note that an Incremental Load Set cannot be split into Subsets anymore.

To run an incremental extraction, select the Incremental Load Set and press 'Extract'.

Real-time synchronization

An Incremental Load Set can also be synchronized with a fixed interval. First Enable the Synchronization by checking the box:

Then set the required synchronization interval. Press Save to save the settings.

To start the Real Time Synchronization press the 'Synchronize' button. The system will now check each time-interval if something has changed within HFM (given the dimension context of the Load Set) and automatically retrieve the updated data. The cube (a special incremental partition) will be processed automatically.

System Variables

In table application_variables table of the cxo_hfmexpressstaging_... database a number of settings is stored that determine some aspects of the process of data-extraction and dimension build-up.

Some values in this table can be changed by IT and / or the Dba

Before changing anything in this table:

It is advised to only change an application_variable if there is a very good reason for that
Never change any cell of the av_name column
Strictly follow the description below
Don't touch the application_variable fields that are not listed below (ask the CXO team for their meaning).

Name	Description and options	Possible values
FilterSourceData	To get the raw data from HFM, queries are constructed that get the data and dimension information from the right tables of the HFM database. If FilterSourceData is set to 0, we only apply a simple filter for including or excluding ICP details, but no filter for the Entities. Entities are then filtered once the raw data is stored in the CXO staging database. When FilterSourceData = 1 then we explicitly filter by Entity already within the raw-data query (WHERE EntityId in ....). In general, if more than ~30% of the entities is selected for inclusion ion CXO it is better to set FilterSourceData = 0 because the huge WHERE clause will slow-down the query. If less than ~30% is to be included then set FilterSourceData = 1.	1 = True 0 = False
PreAggregateAccounts	As explained earlier, selections from the Account Dimension are usually done on a high level (the root node, or a few levels lower). All descendants of a selected node are then selected as well. A potential danger of this approach is that complete substructures might be repeated while you don't see them in your selection. For example, a node like NetProfit can have a lot of descendants and each time this node is used in other KPI's potentially all these descendants are duplicated as well, resulting in a lot of duplicate fact records in the cube: By setting PreAggregateAccounts = 1 we will pre-aggregate the 2nd occurrence of NetProfit and give it the name ...NetProfit: From a data point of view, NetProfit and ...NetProfit are 100% equal. However, drilling into the Descendants of ...NetProfit is not possible. The advantage of this approach is that it keeps the Account dimension smal(ler) and it generates less records in the cube. The disadvantage is that if you drill into e.g. OtherInfo you will never reach members like InterestInc(Exp).	1 = True 0 = False
WYSIWYGModeForCustomDimensions	As mentioned above, Custom dimensions (A01, A02, ... in CXO) can be generated in two ways. WYSIWYG mode: WYSIWYG stands for What You See Is What You Get. It means that the A0# dimension will exactly follow the expand and collapse actions in the Dimension Member selection screen. In other words, if the selection and expansion is as follows: then the resulting CXO dimension looks like: Tree Selection mode (if in this mode, you will see the selected members in red): if you select a member, you automatically select all the descendants, unless you explicitly deselect some of them. In this mode the same selection and resulting CXO dimension is:	0 = do not apply to any custom dimension Comma separated list of Custom dimensions you want to see in WYSIWYG mode (e.g., 1,3,4,7) Recommended value: 0
WYSIWYGModeForAccountDimension	See WYSIWYGModeForCustomDimensions (and the description of the Account dimension)	Recommended value: 0
UnaryOperatorsInAccountDimension	New parameter since 6.2. It is highly recommended to set this to value 0 (False). This is the default value for version 6.3.2. For older versions it should be set to 0 manually. When set to False, this parameter ignores the Unary Operator field of the Account dimension in CXO. That means that all numbers are always rolled up with a '+' in the parent accounts. During the extraction process 'Negative' accounts are multiplied by -1 to ensure correct values for the parent accounts. Omitting Unary Operators makes the dimension faster (from an MDX perspective). The only reason to set / keep this parameter to 1 is when in the HFM chart of accounts members of type FLOW are accumulated in parent members with type EXPENSE. For versions lower than 6.3.2, setting/keeping this parameter to False (0) must be accompanied by removing the following (yellow marked) tag from the XMLA definition of the Account dimension (ACC) (right-click the dimension, select script dimension as / alter / to new query window and press F5 after removal): For version 6.3.2 or higher this is not needed	Recommended value: 0
UnaryOperatorsInEntityDimension	New parameter since 6.2. It is highly recommended to set this to value 0 (False). This is the default value for version 6.3.2. For older versions it should be set to 0 manually. Like with Accounts, when set to False we ignore the Unary Operator of the Entity Dimensions and do a simple roll-up of Entities in their parents. In cases where this will lead to a wrong value on parent-level, we put a compensation value on the parent entity. This is also done to speed up the query times. For versions lower than 6.3.2, setting/keeping this parameter to False (0) must be accompanied by removing the following (yellow marked) tag from the XMLA definition of the Entity dimension (ENT) (right-click the dimension, select script dimension as / alter // to new query window and press F5 after removal): For version 6.3.2 or higher this is not needed.	Recommended value: 0
fetchSizeOracleQueries	This is a technical setting with a default value of 0 (= don't use). The parameter can be set to a value > 0 (typically 500,000 or 1,000,000) to break the data-extraction in smaller pieces. This can be useful if data is extracted from a cloud-server with an Oracle database or in case the CXO application server has limited RAM. It can also be used for (remote) SQL Servers: in that case the parameter quantifies that no of records copied in one copy-action.	In case of on-premise database server: Recommended value: 0 In case of cloud-based database server and failing or extremely slow extraction: Recommended value: 500,000 - 1,000,000
UseTabLock	When set to 1: the tables in the hfmexpressstaging and fact databases are proactively locked before updating or writing data. This may boost performance. Ignore this by setting the value to 0.	Recommended value: 1
multiPeriod	When set to 1: for one Scenario / Year combination all to be extracted periods are retrieved with a single query. This can considerably boost the extraction speed. A potential - but unlikely - drawback is because bigger data-objects are loaded in memory, CXO application servers with limited RAM (8 Gb) can slow down. When set to 1, periods will be extracted one by one.	Recommended value: 1

Cube Calculations for YTD and Periodic View

The following Cube-calculations are required for the proper calculation of YTD and Periodic views.

These Cube-calculations should replace the existing definitions of YTD and Periodic. For new applications YTD and Periodic can be defined as embedded Cube-calculations in the Cube. For existing applications this depends:

If YTD / Periodic are defined as Cube-calculations in the Repository (i.e. maintained via the Designer) then you are obliged to do this again;
Otherwise, replace the existing definition of YTD / Periodic within the cube.

YTD

case when [ACC].[ACC].CurrentMember.Properties('Rate')
then 1
else -1
end
*
CASE WHEN
[ACC].[ACC].CurrentMember.Properties('Sec Class')
THEN
[VIW].[VIW].[YTD_Input]
else
case 
when [ACC].[ACC].CurrentMember.Properties('Is ICP')
then SUM([ACC].[ACC].CurrentMember.Children,[VIW].[VIW].[YTD_vsFlow])
else SUM([ACC].[ACC].CurrentMember.Children,[VIW].[VIW].[YTD_vsBalance])
end
end

Periodic

CASE WHEN
IsEmpty (LookupCube("XchgRate","(" + MemberToStr([CAT].[CAT].CurrentMember) + ", " + MemberToStr([YER].[YER].CurrentMember) + ", " + MemberToStr([PER].[PER].CurrentMember) + ")"))
THEN
NULL
ELSE
CASE WHEN [ACC].[ACC].CurrentMember.Properties('Is ICP')
THEN 
[VIW].[VIW].[YTD]-([VIW].[VIW].[YTD], [PER].[PER].PrevMember)
ELSE 
[VIW].[VIW].[YTD]
END
END

Note that the condition 'IsEmpty...' can also be replaced by the condition that is currently in use to prevent Periodic calculations in periods beyond the last period of a category

The following 'helper' cube-calcs must be defined as embedded cube-calcs

YTD_vsFlow

CASE WHEN 
[ACC].[ACC].CurrentMember.Properties('Is ICP')
THEN [VIW].[VIW].[YTD_Input]
ELSE
-[VIW].[VIW].[YTD_Input]
END

YTD_vsBalance

CASE WHEN 
[ACC].[ACC].CurrentMember.Properties('Is ICP')
THEN -[VIW].[VIW].[YTD_Input]
ELSE
[VIW].[VIW].[YTD_Input]
END

Advanced topic: Default Members

The Custom dimensions (Custom1, Custom2, ...) often have default values like '[None]', or certain movements that appear in any schedule. Often, these Default Members are used multiple times in various alternative hierarchies. In general, you should avoid selecting these members multiple times within a Load Set, but sometimes these copies are really needed.

In these cases, the Default Members can be entered in a table named defaultMembers:

In this example the Custom 1 (= Analytical Dimension 1) contains multiple occurrences of Adv and OpenAdj and Custom 4 contains multiple occurrences of [None]. By entering them in this table, they will not be duplicated in the OLAP Cube and this can save a lot of records (and also reduces the time needed for processing the Cube).

When doing this, you must create embedded cube-calculations in the CXO-cube to make sure that missing copies of Default Members are replaced by a reference to the existing one. These cube-calculations must be defined within no or rarely used dimensions. For example, to account for multiple members in the Custom1 dimension we could define a 'default' member within the Custom 10 (A10) dimension. For Custom 4 (in the above example) we can 'abuse' the unused A09 dimension.

The cube-calculation within the A10 dimension, intended to check the default members in A01 is:

create member currentcube.[A10].[A10].[Default]
as
case when [A01].[A01].CurrentMember.Properties("Has Custom Rollup")
THEN [A10].[A10].[[None]]] + SUM(StrToSet([A01].[A01].CurrentMember.Properties("Custom Rollup")),[A10].[A10].[[None]]])
else
[A10].[A10].[[None]]]
end
, VISIBLE = 0 , ASSOCIATED_MEASURE_GROUP = 'Cxo Fact' ;

The cube-calculation within the A09 dimension to check the default members in A04 is:

create member currentcube.[A09].[A09].[Default]
as
case when [A04].[A04].CurrentMember.Properties("Has Custom Rollup")
THEN [A09].[A09].[[None]]] + SUM(StrToSet([A04].[A04].CurrentMember.Properties("Custom Rollup")),[A09].[A09].[[None]]])
else
[A09].[A09].[[None]]]
end
, VISIBLE = 0 , ASSOCIATED_MEASURE_GROUP = 'Cxo Fact' ;

Now, do make sure that these Cube-calculations are entered in the StoredCubeCalculations table in the fact-database and are used as values for the @A##_Total variables within the CXO application.