| Table of Contents | ||
|---|---|---|
|
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 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.
...
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.
...
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. - 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:
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.
...
An important rule for the Period dimension is that you only select select periods that belong to the native frequency of the selected Scenario: The Actual scenario is a monthly scenario, hence we only select select months.
Let's add another Subset named CurAct (the actuals for current year):
...
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:
If only the total level is needed, we can pre-aggregate the values and in that way flatten the dimension (saves records and an extra hierarchy). This is governed by a system variable. See section System Variables.
Note that - opposite to the older adapter versions - the HFM Value dimension is not stored in the CXO VAL dimenson anymore but in the CXO 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.
Entity
When selecting members from the Entity dimensions you should expand the tree to the level you want to include. For example:
...
Like with Entity, the members that you do do not select will select will not be 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:
...
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. It is no problem in the following situations:
...
Custom Dimensions
The same logic as the Account dimension applies to selecting members from Custom dimensions:
...
You can now Extract the Base Load Set.
System Variables
In the 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
Right-click the table and choose Edit:
Before changing anything in this table:
- Never change any cell of the av_name column
- Do not change any of the grey-marked cells at all
- Strictly follow the description below
| Name | Description and options | Possible values |
|---|---|---|
| DeleteZeroRecords | When set to 1, all records in the HFM database with Data = 0 will be skipped (and hence becomes NULL / Empty in the CXO reports). When set to 0, also records with Data = 0 will be extracted. Only do this if there are not too many zeros in the HFM database (e.g., as a result of automatic load of data) and you really want to distinguish between 0 and NULL. | 1 = True 0 = False |
| 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
|
| IncludeInputValues | When set to 0, the Value-dimension (stored in the CXO GAP dimension will be flat: When set to 1, the original hierarchy will be retained:
Only set IncludeInputValues = 1 if you need the Adjs detail | 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 |
StoreParentEntity | When set to 1 the entity-names will always be stored as a combination of parent.child:
This variable must be set to 1 when the value [Contribution Total] is selected and the Entity selection contains duplicates. Note that if you change this setting, entity lists already created in CXO might get invalid. | 1 = True 0 = False |
| UseCustomRollupForAccounts | Technical setting. Set to 0. | |
| UseCustomRollupForCustomDimensions | Technical setting. Set to 0. | |
| WYSIWYGModeForCustomDimensions | As mentioned above, Custom dimensions (A01, A02, ... in CXO Cockpit) can be generated in two ways.
| 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) |
Technical preparations
-- create CC_PERIOD table
[Contribution Total]
-- when StoreParentEntity = 0 and each entity appears only once → [Contribution Total] just acts as any other Value (GAP)
-- when StoreParentEntity = 1 then, to get the right data you have to select the Entity (ENT) in combination with the right Parent (FLO). To automate that we have stored the parent-name (just the name, so not as parent.child) on the UDA1 property of the entity dimension.